It’s easy to visualize actual spending by category using a pie chart in Google Sheets.
Tiller Money makes this even easier by automating your daily banking transactions and balances into the Google Sheet.
AutoCat in the Tiller Money Feeds Google Sheets add-on takes it a step further by making transaction categorization faster, and more customizable.
If you’re using the Tiller Money Foundation template for Google Sheets and are curious on how to build a top 10 expenses pie chart for your budget follow along below.
Adding a pie chart to your budget spreadsheet
First, add a new sheet to your Google Sheet. Click the plus sign in the lower left corner of your Google Sheet to add a tab. Double click the new tab to rename it “Pie Chart.”
In column A, enter the following query into row 1:
=query('Monthly Budget'!T15:W209,"Select V,W where T = 'Expense' order by W ASC",-1)
This should bring in a list of expense categories and their actuals ordered by highest to lowest by amount value.
This simple Google Sheets query works because the information we need for our budget pie chart is on the Monthly Budget sheet in hidden columns T through W.
The query is just leveraging the calculations the Monthly Budget sheet is already doing. We only want to pull in spending data for categories that are “expense” types, which is customized on the Categories sheet. And since we want to see the top 10 spending categories, we’re ordering the expense actuals from highest to lowest, or ascending.
Now, let’s reformat the amounts so the pie chart can use them. In Column C, row 1 add a header called “Amount.” Then enter the following formula into row 2 in column C.
This is going to change the negative amount values to absolute values. For whatever reason, pie charts don’t like negative amounts so without this step your chart comes up empty.
Right click the column B header and choose “Hide” to hide this column. We don’t really want to see it and hiding it will make creating the pie chart for our budget easier.
Select cell A1, which should be “Category” and hold the shift key then select the category in row 11. These are your top 10 expense categories for the month that’s selected on the Monthly Budget sheet.
Now press and hold the Command key and select C1 and then drag down to C11. You should have the data in Column A selected for the first 10 categories and Column C selected for the first 10.
Click the “chart” icon in the toolbar at the top of your Google Sheet to add a chart.
Customizing your budget pie chart
The default chart that pops up for Google Sheets is usually a bar or line chart. You can customize the style as well as the chart title, slice colors, labels, and much more in the chart editor. I prefer a donut style chart 🍩.
You can move the chart around to an appropriate position on the Pie Chart sheet.
The last little finishing touch for this budget pie chart is the date reference information. When you visit your pie chart sheet it’s helpful to know which month of data you’re actually reviewing in your budget.
In Cell D1 and E1 respectively add the following formulas which will pull in the selected month and year from the Monthly Budget sheet’s dropdown selector:
='Monthly Budget'!H3 ='Monthly Budget'!H2
If you want to review a different month’s top 10 expenses, just select a different month on the Monthly Budget sheet.
You can include more than the top 10 expenses if you like, I just prefer limiting it to the top 10 because the pie chart can get a bit unwieldy if you have a lot of categories and spending in a particular month.