Pivot tables in Google Sheets are powerful tools with an uncanny ability to create a room full of confused faces.
But have no fear. With a little explanation, you will come to appreciate their usefulness. With just a few clicks, you can turn dull data into a tool for deeper understanding and faster decision making.
So what exactly is a pivot table in Google Sheets?
Pivot tables eliminate the need for pouring over lines and lines of a data set. They reorganize data, enlivening the information in to make it easier to understand.
As Google says, "You can use pivot tables to narrow down a large data set or see relationships between data points. For example, you could use a pivot table to analyze which salesperson brought the most revenue for a specific month."
When should you use a pivot table in Google Sheets?
Pivot tables can be used to summarize any data set, but they are especially useful for budget tracking and planning.
In order to adequately monitor your finances, you need to know where your money is going. You could painstakingly add up each transaction in every budget category, or you could let a pivot table do the work for you.
- Open your budget spreadsheet, and you will see a column for Date, Category, Transaction Description, and Amount.
- Select the columns of data you want displayed in your pivot table. In the menu bar, choose “Data” and then “Pivot Table.”
- Finally, select how you want your data organized.
Now you can easily see spending trends by category over time, allowing you to quickly design your budget based on historical spending.
So what if you want to see more detailed information?
Without a pivot table, you would need to use filters or comb through the entire data set. Instead, right click on the desired cell within the pivot table, and then click “Show details.” Sheets will open a new tab showing all the transactions in that category. Now you can see exactly where your money is going.
What if my data set has outliers that skew the data?
Overspending or underspending during the month can affect the accuracy and usefulness of monthly and year-to-date data in your pivot table. To develop an accurate budget, you need to correct for such inconsistencies.
The only way to do so without deleting the outliers from the original data set is to look at your average spending. Luckily, your pivot table can display this information for you.
By default, Sheets will organize the data in the pivot table by sum. However, you can summarize the numerical values by average by changing the settings in the sidebar.
Click on any cell in the pivot table, and a sidebar will appear on the right; under the “Values” section, change the “Summarized by” option to “AVERAGE.”
A note of caution: changing the settings for numerical values in one column will change the settings for all columns containing numerical values.
The averages in the “Grand Total” column to the far right will show you average spending in each category over all the months within your data set, which is useful for budgeting. Unfortunately, the values in the month columns will also be changed to averages, specifically the average transaction amount within each category.
The information may be interesting, but it isn’t particularly helpful for budgeting. While the fact that one useful change unnecessarily changes all the other columns might be annoying, looking at your average total spending is the easiest way to ascertain your monthly budget when dealing with outliers.