A Primer to Getting Started With Pivot Tables in Google Sheets

With a little explanation, you will come to appreciate the utility of pivot tables in Google Sheets. With just a few clicks, you can turn dull data into a tool for deeper understanding and faster decision making.

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.

Further Reading About Pivot Tables in Google Sheets

Sign Up for the Tiller Money Memo

A weekly email with useful ideas about personal finance, investing and managing money, along with tips for using Tiller to optimize your finances.

You May Also Like

Email A Daily Budget Sheet Summary Using Google Scripts

Want to get a daily email summary of your budget dashboard progress? In this post we cover the simple steps of setting up this automatic email using a Google Script. You can have it email you and someone else every day, once a week or at your chosen time interval. Get a quick summary of the transactions that need to be categorized and your progress on your budget in your inbox every day! 
View Post

Monthly Spending Pivot Tables in Your Financial Spreadsheet

Sometimes we need a broader view of our spending. For example, a way to track how much we’re spending at restaurants every month. Tiller’s capability expands beyond the weekly charts and reports. In this post we explore how to easily create a monthly spending pivot table using a new column available on the transactions tab.
View Post