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.
January 18th, 2007 at 2:33 pm
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!!
January 18th, 2007 at 3:52 pm
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.
January 19th, 2007 at 3:34 pm
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.
January 22nd, 2007 at 6:27 am
[...] John presents Free Expense Tracker: A Simple Excel Spreadsheet May Be All You Need posted at Queercents. [...]
January 22nd, 2007 at 7:18 pm
I’m a big fan of PearBudget m’self.
January 25th, 2007 at 10:05 am
[...] Thanks to my handy-dandy Expense Tracker, I’ve noticed that I’ll be over budget for going out to eat before month’s end. I’ve now spent nearly $200 since the start of January, mostly because Zac and I are still living out of boxes and are not settled in yet. We live close to scores of dining options, most of them very affordable. We do love to cook, but going out to eat every night is far too easy a temptation in this neighborhood. [...]
February 13th, 2007 at 10:29 am
[...] When times get busy, I can’t always keep track of every single expense on a daily basis. There are some days I have to spend an hour going over my receipts and my debit account activity to retrace my spending to keep my Expense Tracker up to date. [...]
March 18th, 2007 at 10:48 pm
[...] Our finances run the gamut with age and experience. Erin is in college, John is obsessed with tracking expenses, Rich has ideas about investing and Paula’s posts are full of career insights and general tips. Currently, Allison is writing a ten-part Tax Series. Since April 15th (or should I say April 17th) is just around the corner… it’s worth pointing you to the links: [...]
May 10th, 2007 at 4:20 am
[...] I use an excel spreadsheet I customized. Check it out on Queercents. [...]
May 15th, 2007 at 10:14 pm
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.
May 21st, 2007 at 4:23 am
[...] Our finances run the gamut with age and experience. Erin is in college, John is obsessed with tracking expenses, Allison typically writes about taxes, Rich has ideas about investing, Adam often focuses on entrepreneurship, Moorea has some helpful money stories and Paula is full of career insights and general tips. I try to play the role of fearless leader when I’m not stalking Suze Orman or killing time on Zillow. [...]
May 23rd, 2007 at 10:58 pm
[...] How is he doing it? John is adamant about understanding and tracking his expenses. There are many programs out there, but why spend the money when you can just tailor your own Excel spreadsheet to your needs. Click here for his sample. [...]
June 5th, 2007 at 2:00 pm
[...] Some people truly hate this practice because it seems high-maintenance or impractical. I used to agree until I became desperate for a way to manage my increased income. Being that I lived as a careless spender all my life, the common sense solution to understand my loose-fisted ways (as someone with a degree in behavior analysis) was to create some sort of picture of my spending patterns with qualitative and quantitative details. Simply put, I needed to track expenses to wake up to the reality that I’m spending more than I make. I didn’t think asking for an easy-to-use, free expense tracker was such a tall order. But one tiresome afternoon, I became a crazed Goldilocks combing the internet for a simple financial tool that would do a little more than just capture a list of expenses, and a bit less than download my debit and credit card charges into an overly complicated database. All I wanted was a spreadsheet with some nifty sorting and calculating functions. That’s when I decided to make my own expense tracker on MS Excel, which I wrote about as one of my first posts on Queercents. Six months later, the spreadsheet has changed a bit with some fine-tuning adjustments. I’ve made it a bit simpler to read and manipulate, but it generates surprisingly detailed information. This simple spreadsheet has been so vital to my debt reduction strategy that we at Queercents have decided to make the expense tracker available to our readers during the Almost Debt Free series. To download, you must have MS Excel on your computer and some familiarity with the program. It doesn’t take much technical skill, but you’ll be working with functions not often used at the beginner level. No need to fear. I’ll do my best to walk you through sorting and AutoSum functions with the data you input. [Click here to download the Queercents Expense Tracker. You’ll be asked to confirm your email address, which will be kept strictly confidential and never sold or distributed.] If you’re not quite ready for Excel, the spreadsheet is available in PDF format so that you can track expenses with pen and paper. The layout of the PDF version allows you to make three-hole punches in copies of the expense tracker and keep the pages in a binder. The PDF is also great for jotting down expenses in real time, and then transferring them to your Excel spreadsheet later. [...]
March 9th, 2008 at 12:26 pm
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!
February 11th, 2009 at 10:14 am
[...] their categories and formulas don’t make sense for you and your partner, why not use a simple expense tracking method with a basic Excel [...]