Recently a Tiller customer asked how she could add a pie chart to her budgeting sheet to visualize her actual spending by category. We thought this would be a great opportunity to demonstrate how to add the chart to your own Tiller Budget spreadsheet.
Accessing the Data and Adding the Chart
You might not have noticed, but there are several columns hidden to the right of the budget summary areas in the Tiller Budget template. In order to access the data for the pie chart we’ll need to unhide those columns. Hover over the right edge of column N and two opposing arrows should appear. Click those arrows to unhide column O - AI.
Now that the data is unhidden we need to grab our expense categories to use as labels in the chart. We can do this using a simple formula. Select a cell above the “Needs Categorizing” area of the Budget sheet. In this example we select cell K5. Enter this formula: ="Y"&match("Expense",W16:W)&":Y" to get the data range for your expense categories list that appears on this budget sheet.
Next, we can add the pie chart. Open the Google Sheets Insert menu and choose “chart” - this will insert a blank chart on top of your Budget dashboard. We’ll move it later, but let’s leave it here to get set up. Choose the Doughnut Chart (or one of the other pie chart options) from the Chart Editor that appears on the right side of the sheet.
Wiring the Spending Data to the Chart
Our next step is to connect the chart to the data. Enter the data range for the chart, which will be the output of the formula you added in cell K5, and then the actuals amounts found in column AA. The actuals start in the same row number as the one indicated by the formula output. So if your formula output is Y19:Y, then your actuals will be in AA19:AA. Type these two data ranges separated by a comma in the data range field of the Chart Editor. Your data range should look like this: Y19:Y,AA19:AA. Your pie chart should now populate with your actual spending data per category.
Customizing Your Spending Pie Chart
From here you can close the chart editor. We also want to hide those columns on the Budget sheet again, just to keep that data protected from accidental edit. Select columns O - AI by clicking the column header O, holding the shift key, scrolling right, and then clicking column header AI. Right click a column header and choose "hide columns O - AI".
Make sure you choose to "include hidden/filtered data" when the message pops up after you hide the columns so your chart will continue to work.
We don’t want the pie chart sitting on top of our Budget dashboard so let’s move it to a new sheet. First, let’s copy the chart by clicking to select it, and then press CTRL+C (Windows) or CMD+C (Mac). Add a new sheet using the plus sign in the lower left corner of the Google Sheet. You can name it Spending Chart, or whatever you like. Paste the chart into the new sheet by right clicking and choosing Paste, or press CTRL+P (Windows) or CMD+P (Mac). Finally, delete the original chart from the Budget sheet by clicking it and pressing the delete key.
Now you can start to customize your new spending pie chart. Double click the chart to open the Chart Editor. Click Customize and then start configuring your options. You can add a title, make it a 3D chart, add additional labels, customize the fonts, change the size of the doughnut hole, and more.
Note About Adding Additional Income Categories
If you add new Income categories to your Categories sheet, and they’re not marked as “hidden” on the categories sheet,” you’ll need to open the Chart Editor and update the data range for your expense categories.
We hope that you find this tutorial useful. Please reach out to us in support if you have feedback or issues getting it set up. Email firstname.lastname@example.org or use the chat window on the Tiller Console Happy Tracking!