A while ago, a friend of mine asked, “Why should we even use Pivot Tables?” This question was more than just a curiosity; it was an opportunity to transform how they interacted with data. Pivot Tables and Pivot Charts in Google Sheets do wonders for your data by turning seemingly chaotic fragments of data into coherence with just a few clicks.

Imagine facing thousands of rows of data — sales figures, customer demographics, expenses — and attempting to make sense of it all through traditional methods. The sheer amount of manual effort would drive anyone to frustration. Pivot Tables and Pivot Charts are the combo that makes data analysis not only manageable but also enjoyable and easy.

Pivot Tables allow you to summarise large datasets quickly and efficiently. You can effortlessly group data by different categories, like region or product, and then perform various calculations such as sums, averages, or counts. If you have sales data, you can create a Pivot Table to see total sales by region, by product category, or even by individual salesperson. It’s a game-changer for anyone looking to turn raw data into meaningful insights.

Pivot Charts take this a step further by providing visual representations of your Pivot Table data. Charts help you spot trends and patterns that might not be immediately obvious from the numbers alone. Whether it’s a line chart showing sales trends over time or a bar chart comparing sales across regions, Pivot Charts make your data visually appealing and easier to interpret.

What makes Pivot Tables and Pivot Charts in Google Sheets particularly appealing is their user-friendly, drag-and-drop interface. You don’t need to be a data wizard to use them. With minimal effort, you can create comprehensive reports that would take hours to build manually. Plus, they’re incredibly versatile—you can update your data, and with a simple refresh, your Pivot Table and Pivot Chart reflect the changes instantly.

For this post, I have created 2 examples to explore and practice, and a third dataset as an exercise:

https://docs.google.com/spreadsheets/d/17LpYwF_unhte9Rs-GdSmuYAKDFC3FZ-03DWPepDPOKI/edit?gid=0#gid=0

1. Summarising Sales Data by Region and Product

When it comes to the difference between using formulas in Excel/Google Sheets and Pivot Tables, there is a crossover point with respect to ease of output and the formulas required for that result. I would argue that Pivot Tables are much easier to navigate than Formulas, especially when it comes to a point-and-click approach to developing insights for managerial decisions. I feel the point is further made when you deal with “large” datasets, especially when it comes to load time. This is why I have generated 17,000+ rows of data to show the difference in ease and time.

Creating the data

I often enjoy this part of the process — making materials from scratch. I have chosen the INDEX(MATCH()) approach for this dataset as I intend to make it a “large” dataset, specifically more than 17,000 rows. When working with datasets that contain tens of thousands of rows or more, it is true to say that using INDEX(MATCH()) is far more(time)  efficient. VLOOKUP, while handy for simpler tasks, tends to slow down considerably when dealing with larger volumes of data. This slowdown becomes especially noticeable if your sheet utilises multiple VLOOKUP formulas.

I’ll start by creating a sale for each day by entering ‘01/01/2024’ in A2 and the following ARRAYFORMULA in cell A3, since I want to publish this post on 30/09/2024. (As an FYI, I copied this formula a fair few times to get to the ‘big data’ ballpark):

=ARRAYFORMULA(A2+SEQUENCE(DATEDIF(“01/01/2024”,”30/09/2024“,”d“)))

I’ll create an inventory of stock for people to purchase. I’ll create an inventory list with the following dimensions: index, product, category, price, and location:

I’ll explain the INDEX’s located above the table by outlining how I utilised them in the sheet. For the sales data, I want to enter a product sold on each day, so I’ll use that inventory list, then randomly allocate a Product from the list (Game, Computer, Calculator, etc) using the RANDBETWEEN function and INDEX(MATCH()) using the index in column K as a reference:

=INDEX($K$2:$N$37,MATCH(RANDBETWEEN(MIN($K$2:$K),MAX($K$2:$K)),$K$2:$K,0),2)

We can now use the MATCH function on the Match the Product with the Category in the sales data table:

=INDEX($L$2:$N,MATCH(B2,$L$2:$L,0),2)

Using some arbitrarily assigned Sales figures for each product, then randomise an upvalue for each product between 1% and 25% for each price of each product sold:

=INDEX($L$2:$N,MATCH(B2,$L$2:$L,0),3)*(1+(RANDBETWEEN(1,25)/100))

Finally, allocation of randomly assigned Region for each Products (Australia, New Zealand, Philippines, etc):

=INDEX($K$2:$O,MATCH(RANDBETWEEN(1,12),$K$2:$K,0),5)

This process is then auto-filled for a total of 17,536 transactions:

Formulas

Using the function/formula approach, it shows varying levels of knowledge you have to possess to report accurate figures. It also implies the tedium required to account for various dimensions (category, region, date (year, quarters, etc)) in one function/formula. The following are 3 examples of some metrics to observe; Sales Revenue by Region, Average Sales Revenue by Category and Region, and Top 3 Products Sold and Revenue:

Sales Revenue by Region

=SUMIF($E$2:$E,H$3,$D$2:$D)

Average Sales Revenue by Category and Region

=AVERAGEIFS($D$2:$D,$C$2:$C,$G8,$E$2:$E,J7)

Top 3 Products Sold and Revenue

I utilised the ARRAYFORMULA to automatically account for the number of top product, should a user want to change that LIMIT value in the QUERY function:

=INDEX(QUERY(B2:B,“SELECT B, COUNT(B) WHERE B IS NOT NULL GROUP BY B ORDER BY COUNT(B) DESC LIMIT 3 LABEL COUNT(B)”),,1)

=ARRAYFORMULA(COUNTIF($B$2:$B,$G12:$G14))

=AVERAGEIF($B$2:$B,G12,$D$2:$D)*H12

Pivot Table

When we take this dataset into a Pivot Table format, it becomes a click-and-drag process where you are telling the table which dimension to add (along which dimension) and what aspect of that dimension you’re interested in viewing (year, month, quarter, product, total sales, average sales, etc). You can see a previous post on accomplishing Pivot Tables:

Sales Revenue by Region

Average Sales Revenue by Category and Region

Top 3 Products Sold and Revenue

Charts

Can also be easily created from these Pivot Tables. You can see a previous post on Pivot Charts and implementation:

2. Monthly Financial Reporting

Another example that we can use is Monthly Financial Reporting information. Specifically, expense data. Using a similar approach to the first example, I developed some financial data between January and August:

Formulas

Again, like the first example, I create some metrics to observe in the data: Total Expenses per Month; Total Expenses per Month by Category; and Change in Expenses per Month:

Total Expenses per Month

When using dates with the SUMIFS functions, you need to get specific about the dates you’re interested in evaluating, hence the need to mark specific start and end times:

=SUMIFS(Table2[Amount],Table2[Transaction Date],“>=”&E4,Table2[Transaction Date],“<=”&EOMONTH(E4,0))

Total Expenses per Month by Category

I felt the easiest, and dynamic, approach to this was to first list out all of the individual expense categories (Column F, using the UNIQUE function):

=UNIQUE(Table2[Expense Category])

Followed by integrating this Category list with the previous example’s solution, increasing the SUMIFS’ criteria by one additional dimension:

=SUMIFS(Table2[Amount],Table2[Transaction Date],“>=”&E13,Table2[Transaction Date],“<=”&EOMONTH(E13,0),Table2[Expense Category],F13)

Change in Expenses per Month

As you can see, you can build on your previous work and knowledge to finesse future solutions. So, I started by listing the categories (7 in total) and mapped each day for those categories (7x 1st January entries, followed by 7x 1st Februrary entries, etc):

=UNIQUE(Table2[Expense Category])

From there, it was a copy and paste of the previous solution’s work to find the amounts:

=SUMIFS(Table2[Amount],Table2[Transaction Date],“>=”&E30,Table2[Transaction Date],“<=”&EOMONTH(E30,0),Table2[Expense Category],F30)

Lastly, I wanted to track the spending changes between the months, but also accounting for no expenditures in particular months. Using the INDEX(MATCH()) method allowed me to accurately and quickly traverse the data. You will notice that the initial numeric values in the INDEX and MATCH functions are partial absolute references: $E30:$G$50 and $F30:$F$50. I’ve done this specifically so the solution in these previous month’s lookups don’t get confused between the number of months to consider the change:

=ROUND(IFERROR((G37-INDEX($E30:$G$50,MATCH(F37,$F30:$F$50,0),3))/INDEX($E30:$G$50,MATCH(F37,$F30:$F$50,0),3),0),2)

Pivot Table

Again, we can see how easy the Pivot Table navigation can be:

Total Expenses per Month

Drag the Transaction Date dimension to the Rows:

Right-click on any date then click Create pivot date group > Month:

Now this looks a lot cleaner:

Finally, drag the Amount dimension into the Values:

Total Expenses per Month by Category

Change in Expenses per Month

Google Sheets (Pivot Tables) currently do not support the function ‘% Difference From…’ in their Values dimensions. Additionally, the Calculated Field functionality is also limited in its ability to traverse conditional operations, e.g., monthly groups within transactional daily data. However, we are able to assign each element within the row with its percentage of the total row value. Here, we can observe the same increase for each category over each subsequent month:

Homework/Exercise: 3. Analysing Customer Demographics

I’ve created this last sheet as an exercise for you to complete. I have provided customer information with the following categories:

• Order ID,
• Date,
• Region Product,
• Category,
• Salesperson,
• Customer Name,
• Gender,
• Age,
• Income Level,
• Units Sold,
• Unit Price, and
• Total Sales.

Task 1: Create a Pivot Table to summarise total sales by region and income level,

Task 2: Create a Pivot Table to filter sales data by gender and drill down into sales details for a specific product category,

Task 3: Create a Pivot Chart showing sales trends over time, with slicers for filtering by customer age group and product category,

Task 4: Create a Pivot Table to analyse sales by customer age group and region to identify top-performing age groups in each region,

Task 5: Create a Pivot Table to compare sales performance by product category and income level.

Task 6: Create a Dashboard with the information you have created to summarise this information on a Managerial level.

Conclusion

Reflecting on the journey through Pivot Tables and Pivot Charts in Google Sheets, it’s clear that these tools are indispensable for transforming raw data into meaningful insights. Exploring how they simplify data analysis, turning complex datasets into clear, actionable information with minimal effort will hopefully show my mate why we should be using them.

Pivot Tables allow us to group and summarise large volumes of data efficiently. Whether it’s categorising sales by region or analysing product performance, Pivot Tables offer a straightforward way to navigate through vast amounts of information. This capability is a game-changer, especially when dealing with extensive datasets that would otherwise be overwhelming.

Pivot Charts take these insights further, providing visual representations that highlight trends and patterns not immediately obvious in raw numbers. The ease with which you can create these charts, coupled with their powerful visualisation capabilities, makes data interpretation more intuitive and accessible.

The user-friendly, drag-and-drop interface ensures that you don’t need to be a data expert to utilise these tools effectively. The ability to update data dynamically and reflect these changes instantly in your Pivot Tables and Charts enhances both the efficiency and accuracy of your analysis.

Pivot Tables and Pivot Charts in Google Sheets are essential for anyone looking to streamline their data analysis process. They transform data from an intimidating mass into a coherent narrative, making it easier to derive insights and make informed decisions. By embracing these tools, you can significantly enhance your data handling capabilities and ultimately drive better outcomes in your projects and analyses.