Create a Visual Savings Chart With Google Sheets and Tiller
Setting personal finance goals is one thing, but actually measuring your progress towards them is another.
Luckily, tracking your personal finance goals in a spreadsheet is incredibly simple as well as motivating.
1. Getting Started with Personal Finance Goals
In our example, we’re setting savings goals for a six-month emergency fund, a vacation and maxing out an IRA.
To establish these goals, first start with the Tiller Budget with Rollovers template from Tiller Labs. If you’re not yet a member of Tiller Money, you can sign up for a 30-day free trial here. Tiller Labs offers free experimental tools supported in the Tiller Money Community.
After creating your first budget, visit the “Categories” tab, and add a category for each savings goal. We’re going to delineate each one of them as a transfer using the drop-down menu in Column C.
In Column F, insert your end goal for each category. Vacation Fund, for example, is set at $3,000.
2. Categorize Transactions Appropriately
Now you’re going to want to make sure that when you’re transferring money to your savings account or IRA, it’s posting properly.
Head over to the “Transactions” tab. Check to make sure contributions to your vacation goal is registering as “Vacation Fund” in Column D. If it’s not, use the drop-down menu to change it.
3. Create Savings Goals Data Sheet
Before you can make that pretty chart, you’ll need data to draw that chart from. You already have most of it; you just have to tweak the way it’s presented a little to get it to show up as a stark visual.
First, copy Columns D and E from the “Transactions” tab. Then, add a new sheet, head to H1, and paste. You should now see the transaction categories and amounts on your new sheet in Columns H and I.
Now head to A1. Create a title row where A1 is the goal/category name, B1 is the amount you already have saved, C1 is a control for absolute value, D1 is the difference between what you have saved and your total goal and E1 is your total goal.
Filling out Column A is as easy as typing in the category names. In Column B, you will want to write a SUMIF formula in order to add together all the spending from each savings category. For row 2, that formula looks like this:
=SUMIF(H1:H, “6-Month Emergency Fund”, I1:1)
This formula tells Sheets to look at Column H (H1:H). If Column H reads, “6-Month Emergency Fund”, then Sheets is to pull its value from Column I. Once it has all the qualified values, it is to add them up together and put the sum in B2.
This number will be a negative, as it’s money that was taken out of your linked checking. You need it to show up as a positive value on the end chart, though, so use Column C to derive the absolute value. In row 2, you’ll do that with the following formula:
=ABS(B2)
All you have to do is change the row number from B2 to B3 to B4 and so on as you move down the rows.
You will then move over to Column E where you’ll pull the total savings goal from the Categories tab. For our six-month emergency fund, the formula in Column E will look like this:
=Categories!F24
The exclamation point separates the tab—Categories—from the cell from which you want to pull the data, which in this case is F24. As you move down Column E, simply replace the cell number after the exclamation point to correspond with the appropriate category.
Finally, you’ll need to move back over to Column D to calculate the difference between your actual savings and your target goal. To do this, you’ll add together Column B where we had those negative values and Column F. Your formula will look something like this, with the numbers changing as you move down the rows:
=SUM(E2, B2)
4. Create a Visual Savings Chart
Charting your progress can be a huge motivator as you work towards your savings goals. You can play around with several different ways to display your data, but my personal favorite is progress bars, which allow you to quickly see how far you’ve come along with how far you have to go. That’s the type of chart we’ll focus on today.
First, highlight cells C2 through D4. The number after “D” may vary depending on how many savings goals you are currently working on. Go all the way to the last one.
Right click and choose to create a chart. You’ll want it to appear in its own, separate tab. Once your chart is generated, go to the Chart editor on the right of the chart. Make sure your chart is setup to be a stacked column chart with standard stacking. Scroll to the bottom of the setup menu and make sure the “Use Column A as labels” option is checked.
From here, all your numbers should automatically update to display your progress as long as the transfers to your savings or IRA accounts are categorized properly in the “Transactions” tab. For example, here is what the chart would look like at the end of the year if we kept saving at the pace:
[Applying to Other Personal Finance Goals
You can use this same basic concept of creating charts to visualize and track your personal finance goals across all areas—not just savings.
You can create charts for debt using categories and the =SUMIF() formula to pull the sum of what you have paid to date. You can track investing over time—both what you project and what the market actually returns.
At the core of it all is having the raw data. That’s why we aggregate your transactions for you in Tiller Sheets, making it easily accessible and easy to manipulate. It makes creating a visualization of your dreams a breeze—whatever they may be and however you’d like to measure them.
Start Your Free Trial
Keep a clear, confident view of all your money in one place, with flexible templates, powerful privacy, and top-rated support