Free Expense Tracker: A Simple Excel Spreadsheet May Be All You Need
One of my numerous resolutions this year is to keep track of how I spend my money. However, in keeping this resolution, I didn’t want to deal with the hassle and cost for software like Quicken or MS-Money. After all, I didn’t want my expense tracker to be another expense. Also, I just wanted to get an idea of how much I spend per day, and where I’m spending it so I can determine how well I’m sticking to my budget.
I get paid to do research for a living, so I thought that my internet search for a free expense tracker would be easy. Let me save you a lot of time by telling you not to bother looking. The main problem with freeware expense trackers is the limited access to features. For instance, if you would like to record all the transactions you make in a year, you will easily surpass the 250-400 transactions limit on the freeware versions of a program before having to upgrade to a paid subscription. And if ever you want to make a report of your expenses, forget about getting that for free.
I did find some free expense trackers that were essentially overly-complicated Excel spreadsheets, but even in these programs, there was very little ability to customize things like what category you can assign to an expense. So I got to thinking, why not just make my own Excel spreadsheet that I can make to suit my needs?
Here’s what I did, and it takes very little Excel expertise (Click image to enlarge):
1) Set up columns as displayed. As you add an expense to your table, be sure to keep names for your categories consistent for easy sorting purposes in the future.
2) Two rows below the last entry in your Cost column, click on the sigma sign in the tool bar to enable the AutoSum feature. You now have a subtotal. (I added the term subtotal in the cell to the left to give clarity to the spreadsheet.)
3) Insert a new row each time you and add an expense to your expense tracker. The subtotal will update automatically each time you add an expense.
That’s it!
The sorting capabilities of a simple spreadsheet like this has potential for powerful applications. You can easily get an idea of how much you’re spending on eating out for instance, by doing the following below:
1) Click on the row of your header column (Row 1). Go to the Data dropdown menu, and select AutoFilter. Your worksheet will now look like this (Click image to enlarge).
2) Click on the arrow in the Category Column, and select Eating Out from the drop down menu. Then click on the cell in which the dollar amount of the subtotal appears. Click the AutoSum button (sigma sign) again and press Enter. You now know how much you’ve spent on eating out. See below (Click image to enlarge):
If you go back and deselect AutoFilter, your expense tracker goes back to looking like normal, and you get your original subtotal back. With something this simple and powerful, why spend your money on something that gives you less control?
The other cool thing about having your expenses on an Excel spreadsheet is that if you store this document online on something like X-drive or Elephant Drive or GMail as a hard drive, you can access this document from anywhere. You don’t have to be at home or work to input expenses in a program loaded on just one computer. You can easily stay on top of your expenses this way.
If you have any tips on free expense trackers, please feel free to share. We all have different needs, and an Excel spreadsheet might not suit some readers. But if you’re just starting out with tracking expenses like I am, a simple spreadsheet may be the way to go.
Ah, nice! I just a month ago built a little Excel (er, NeoOffice 🙂 spreadsheet to track expenses, but yours is easier on the eyes, and you have some detail in there that I hadn’t considered. I’ll be using some of your ideas. If I think of anything else, I’ll post it here. Thank you!!
You could put each pay period or month or quarter in a different tab so you can compare.
At the end of the period you could sort by expense type. Then after your list, type each expense type on a different line, and next to that, put the subtotal. I would do it by typing this:
=sum(
and then selecting all the cells for the given expense type (which are adjacent now that you’ve sorted) and then type the close parentheses:
)
and press Enter.
(Then sort by date again to make it look right. To sort, put your cursor in the column–not on the header–that you want to sort, and then press the button at the top with the A and Z on it.)
Next to these you can calculate what percentage of your budgeted amount or total spending or earnings this amounts to.
What I wish I could figure out though is how to easily keep track of all the subtotals at once as the month is progressing. If I could figure this out, I would totally steal this idea and start using it.
One more thing–technically Excel isn’t free unless you already own it. But there are sources of free spreadsheets both as part of free office software and other places like Google Spreadsheets.
Tip for Debbie and anyone else who wants to keep track of subtotals for specific categories as the month progresses. The following link helped me figure out a formula for this particular spreadsheet:
http://support.microsoft.com/kb/150362
A few rows after your last expense, make a cell for your desired category (let’s continue the Eating Out example). Label it “Eating Out Subtotal”. In the next cell over to the right (in Column C), put in the following formula:
=SUMIF(INDIRECT(“B2:B”&ROW()),”=”&B3,INDIRECT(“C2:C”&ROW()))
In this example, B3 represents the criteria “Eating Out”. Press enter, and you get the subtotal. As you add rows of expenses to your Expense Tracker, the “Eating Out Subtotal” cell will automatically add only Eating Out expenses.
However, let’s say you want to keep track of Transportation expenses as the month progresses. In the row under “Eating Out Subtotal,” make a new label for Transportation, use the same formula in the cell to the right as you did for Eating Out, but this time replace B3 in the formula with B4 (for the criteria “Transportation”). Press Enter. Voila! You’ll also have a cumulative running total of transportation expenses.
Debbie brings up a good point that Excel is only free if you already have it. Google Spreadsheets annoyingly doesn’t have an AutoSum feature yet. However, you could use a running total formula in Column G as a solution to this problem. Label the G column “Running Total.” The formula is this:
Your first formula in G2 would be =Sum(C2). Then, in G3, your formula is =SUM(G2+C3). As you add rows of expenses to your Expense Tracker, copy the formula in the last row of the G column, and paste in the cell of the next row. Your running total is automatically updated.
Thanks Debbie for your feedback! I hope that helped some people out.
I’m a big fan of PearBudget m’self.
I clicked through from Mint. Nice article on using excel. I have been using excel to track expenses for two years now, and have been happy at the results. My spreadsheet is able to track catagories broken out by month using formulas. However, I would recommend getting into pivot tables. They work better than formulas, and you can make nice pivot charts.
Aaahh…love it! I just started searching for an idea of an excel expense tracker and yours popped up. This saved me a ton of time. I recently downloaded a free trial version of Microsoft Money, wasted an evening messing around with it and got nowhere. It was more complicated than I needed and couldn’t do what I really wanted to (or maybe I just couldn’t figure out how). Anyway, thanks a bunch for the idea…I’m stealing it!
My favorite expense tracking tool is InEx Finance inexfinance.com online money management software. Nice design and quite easy-to-use.