One of the biggest things people want to iron out in a new year are their finances (myself included). Who doesn’t look forward to the day when all their debt is paid off and they can live a life debt free? I know I definitely do. Having a good handle on finances is something I pride myself on. Even if I can’t proclaim that I am 100% debt free – yet – I definitely know how to keep myself organized and on track (see these tips on starting a cash budget to see exactly how our family budgets). As we really get going in this new year it is a fantastic idea to solidify those financial goals you have had swirling in your mind and start figuring out actionable ways to achieve them. A great place to start is by writing them down, and you can use this free printable I created to write down all your goals including the ones that are all about money. Speaking of “money”, please know that I love Pink Floyd so very much and I can’t help but throw this epic music video in here:
It may be the engineer in me but I love two things so very much: data and spreadsheets. The two together, whew, I am bursting with excitement! That’s probably why as my husband and I were ironing out the last details of our financial goals for 2016 we decided to add a little extra information to our budget spreadsheet we use in excel. We wanted a visual way to display where we started this January and where we are at the end of 2016 when it comes to our debt. You can do this any way you want to – reduction of debt or additions to your savings. Either way, I will take you step by step to creating graphs in excel to track your financial goals and lead you closer to whatever you plan to accomplish in terms of money this year! Let’s get started on how to track your financial goals in Microsoft Excel using graphs and tables!
How to Visually Track Your Financial Goals in Microsoft Excel
I will be showing you two separate ways of tracking in this tutorial:
- Tracking each debt on one graph – starting debt vs. current debt
- Tracking a single debt over a period of time
Please note that I am showing these tutorials in terms of debt but you can easily do the same thing to track your savings (perhaps for certain things or even over the period of a year)
All Debt On One Graph – Starting vs. Current
Step 1: Set up a table.
The first step is to set up your table to track dollar amounts. I created three columns: debt (for the type of debt), starting (for the amount you start with) and current (to be updated to reflect the current amount you owe for that debt). Excel has an excellent feature which allows you to format the cells to whatever type will be in them, in this case, dollar amounts. Select the “starting” and “current” columns by clicking and dragging then right click and select format cells.
Find and click on Accounting on the list and push Ok. Now the cells will automatically populate with dollar signs and “.00”.
Type your debt into the columns – you’ll probably want to set “current debt” to the starting amount for now. I have filled them in for this example as such:
Step 2: Create the graph.
This type of information is best displayed as a bar graph, in my opinion. First you need to select the starting debt column by clicking and dragging. Next you will click on Insert -> Column Bar Chart -> Clustered Column as shown below. This will create the plot of starting debt!
Step 3: Formatting the graph with starting debt.
The graph you just created is only of the starting debt and it does not display what each of the debts are called, no fun! You can easily add the labels to each debt by right clicking the graph and selecting Select Data.
Then navigate to where it says Edit for the Horizontal Axis Labels.
Click the button on the pop up that is next to “Select Range”.
Select the debt names and push Ok.
The debt names should now be shown on the Horizontal Axis Labels list and on the graph you made!
Step 4: Adding the current debt to the graph.
This part is a little confusing but it is what makes the tracking possible! First, right click the graph and choose the option Select Data again.
This time, you want to click Add on the Legend Entries side.
Next, select the current debt column as you did before and click Ok. Call the series “Current Debt”.
Change the name of “Series 1” by clicking on Edit in the Select Data Source window and typing “Starting Debt” as the Series Name.
Finally, (this is optional but helpful) if you have the newer version of Excel you can easily add a data table by click on the “+” sign when hovering over the graph and checking the “data table” box. I like this because it helps give values to the graph RIGHT THERE. It also helps remind me which colors are which with the handy legend.
You’re finished! You have made a graph that will visually track how much you have lowered your debt for each individual item. Go ahead and try it out and make sure it works. In this example I lowered the student loan debt (remember, don’t touch the “starting debt” only the “current debt” column). As you can see, the orange bar has lowered! Yay!
If you’re interested in learning how to track a single debt source over a period of time, or even your entire total debt…read on!
Visually Tracking Finances Over Time
Another very helpful way to track finances is over time. This way, you can see how much you improve by each month and also which months give you the most trouble. Setting up this table and graph is very similar to the previous example and actually a little simpler!
Step 1: Set up the table.
In this case, I divided the table into three categories: debt, month and amount. Since we are only investigating a single debt (or total) in this graph, it’s really just there to help you remember what you are graphing. For now, the amount will likely be empty for everything but January, however for the sake of explaining I have filled out values later on to show how to works.
Step 2: Create the graph.
Select the “amount” column and again click on Insert -> Column Bar Chart -> Clustered Column.
Your graph should appear! As before, we want to change the horizontal axis. Right click the graph and click Select Data.
In the pop up, click Edit under Horizontal Axis Labels and then select the month column. Press Ok.
The Horizontal Axis Labels should now list every month. Click Ok.
The dates now show up on the graph! You can change your chart title by double clicking on the chart title text and writing whatever you want to call it.
I have filled in some values to show what it will look like over time!
Now It’s Your Turn!
Creating the charts and graphs in Microsoft Excel is really just the first step to tracking the goals. Now you have to be diligent enough to update them. My husband and I choose to update our current vs. starting debt graph on the first of every month. It is so fun seeing the bars shrink next to their starting amount! While this is an awesome way to track your goals, it is also an excellent method for projecting how long it will take to pay something off (visually) or how good you have been about paying off your debt. Sometimes numbers can be confusing, but the visual aspect really helps.
Do you use Microsoft Excel for budgeting? How do you track your financial goals?
Latest posts by Sam (see all)
- Welcome Home Wednesday Link Party No. 23 - January 20, 2016
- Visually Track Your Financial Goals with Microsoft Excel - January 19, 2016
- How-To: Organize Goals (Tips + Printable) - January 5, 2016