Adding New Charts to Your Personal Finance Spreadsheet

Many of our money management spreadsheet templates include charts, but you’re not limited to the charts in the templates. You can easily create your own charts based on your data. In this blog, for example, I’m going to look at my spending across several categories per month for the entire year of 2016. Check out our quick video tutorial at the bottom of this post.

Visualize monthly spending for a few categories by month

Visualize monthly spending for a few categories by month

A great way to do this is to start out with a pivot table or QUERY. You can learn how to do this in a few easy steps. It’s a great way to get the data organized, but it’s not an engaging visual format. So for those of you who’d like to digest this data with a little color, we’re going to give you a few quick steps to get this into a nice bar chart.

Get your spending data ready

In this example, we’ll start out by creating a pivot table to get our data for the chart. Again, the detailed instructions for that are here, but to recap you’ll select all the data on your transactions tab, open the Data menu from your Google Sheet and choose Pivot Table. From there you can set the rows to pull in the Categories and the columns to pull in the Month. The chart values will be the Amounts.

Monthly Spending Pivot table without category filters

Monthly Spending Pivot table without category filters

Here’s where we’ll branch off from the original Monthly Spending Pivot Table blog post. You’ll likely want to add a couple filters to your pivot table. In this example I want to look at my spending for each month for only some of my categories, specifically the ones that are “living and fixed.” So, things like my cell phone bill, rent, health insurance, groceries, etc. In order to do this I’ll add a filter for “Category” and choose only the ones that I want to see right now.

Next, I’ll want to add another filter so that I’m only looking at 2016 data. I’ve been using Tiller since 2015, and I don’t really care to see all my historical data, so I add a filter for the Month. I’ll click Clear to deselect all the months, then type “2016” into the search box, and then click Select All to quickly select all the 2016 months and exclude 2015 and 2017.

Time to visualize your spending

Now we’ve got the data ready for our beautiful chart. This is the easy and fun part. I’ll select all the data in the pivot table by clicking cell A1. I’ll hold down the SHIFT key on my laptop and then select the bottom right cell that’s not including any Grand Total data. For my case, it’s M7. I don’t want to include the Grand Total data because that is not a value I want in the chart.

Once I’ve selected the data I will then open the Insert menu from the Google Sheets navigation at the top and choose Chart. It’s going open a configuration window and give me the option to choose my chart. You’ll notice that some of the chart types are not available in this view because of the way the data is formatted.

Advanced edit menu of Google Sheets charts

Advanced edit menu of Google Sheets charts

My favorite chart option for this analysis is the stacked horizontal bar chart that you’ll find if you scroll down a bit on the Chart Types tab of this configuration window. After I click Insert I have a beautiful new chart visualizing my spending across several categories for each month in 2016. As a last step I can customize it a bit more by updating the chart title and the horizontal and vertical axis titles. I can double click the chart name and the axis title to edit them.

Customize your spending chart

Customize your spending chart

Visualizing spending throughout the year with charts

I like to use this type of chart throughout the year, not just at year end, to visualize my spending. Now that it’s built you can edit the Category filters to review different categories or different months. The only thing to keep in mind when you edit the filters is that you may need to update the Chart’s selected range.

You can edit the selected range by clicking on the chart and then click on the small down arrow in the upper right of the chart to open the Advanced Edit screen. From there you can update the range to make sure it’s including all the updated categories or months. If you explore this advanced edit configuration you’ll also see you can switch to a different chart type and change the chart and axis titles.

Click the chart to access the Advanced Edit

Click the chart to access the Advanced Edit

These charts are an easy and powerful way to visualize your spending. If you want multiple charts to visualize your spending you can create a new pivot table on a separate sheet using the same process as before or you can copy the entire pivot table and paste it elsewhere on that same sheet. From there you can customize the new pivot table to give you a different data set. The possibilities are endless!

Video How To