How to Make Charts in Google Sheets

Many of the spending tracker templates built by Tiller Community member include charts, but you’re not limited to the charts in these community solutions.

You can easily create your own spreadsheet charts based on your spending data. In this blog, for example, I’m going to look at my spending across several categories per month for the entire year of 2020.

Visualize monthly spending with a simple pivot table or QUERY

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 and choose to create a “new sheet” in your spreadsheet.

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.

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 2020 data. I’ve been using Tiller since 2015, and I don’t really care to see all my historical data. I customized my sheet a little further so I have a Year column in my sheet. Then I can add a filter to only view 2020 transactions.

In the Filters settings I’ll add a filter for the Year column. Next, I’ll click Clear to deselect all the other years. Finally, I’ll select just 2020 and click OK.

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 M15. 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 drop a chart into the sheet. From there I can use the sidebar to customize the chart type and other details. You’ll notice that some of the chart types are not available in this view because of the way the data is formatted.

My favorite chart option for this analysis is the stacked bar chart that you’ll find if you scroll down a bit on the Chart Types list in the sidebar. After I click Insert I have a beautiful new chart visualizing my spending across several categories for each month in 2020.

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.

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 double clicking on the chart to open the customization options in the sidebar. 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.

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!