How to Make Charts in Google Sheets

Easily create your own custom spreadsheet charts in Google Sheets. In this example, we use your spending data imported by Tiller.

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

728x90

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.

spending pivot table
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 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.

728x90

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.

spending bar chart google sheets
Use the sidebar to customize your Google Sheets chart

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.

edit chart axis google sheets
Double click the axis or title to edit.

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!

Google Workspace Marketplace

Start Your Free Trial

Try Tiller free and track all your accounts in one place, always know where your money goes, and confidently plan your financial future.

Default image
Heather Phillips
Heather comes from a background of user experience design & customer support. She loves helping others learn, explore and discover better ways to use applications and products that improve their lives. When she’s not coaching customers on Tiller best practices, tweeting or writing blogs, she’s probably at a yoga class, out for a hike in the Blue Ridge, or off volunteering for a variety of non-profits.