Mar 02 2014
I wrote about tracking our household expenses last year, and my talented photographer friend Steph took one look at my feeble attempt to use Excel (above) and offered to clue me in on a few basics. Not many people know this about Steph: She crunches numbers for breakfast and hasn’t yet been outed as a closet Excel geek. Sorry Steph!
Anyway after our session I went home to create a brand new Excel file–one far more efficient than what I was working with previously.
If you look at the picture above, you’ll see that each of my headers (Date, Month, etc) are now clickable with the up/down arrows, and this is thanks to the AutoFilter function. You apply the AutoFilter function by selecting your header/first row and clicking on Data > Filter > AutoFilter. You can read more about AutoFilter here. In my previous Excel expense tracker, my data was static–I couldn’t shift the information around or view my spending in a selected month or category. And I was manually adding up daily purchases so I could enter a final figure for each spending category, before using the Sum function to total up the spending for the week. I had to do this every day, and if I was backlogged, I stored receipts by sorting them out into the different days and stapling them together, by day. It was definitely an operation, and not one that I particularly enjoyed.
With this new system, I toss receipts into a ziploc bag and once every couple of weeks, I grab a fistful of receipts and sit at my laptop to update the file. I no longer have to enter information in chronological order, because at any point, I can click on the Date header, select “Sort Ascending,” and have everything arranged for me:
The other thing AutoFilter allowed me to do was view my spending data by month AND category. That was very useful in helping us see exactly how much money we were spending on, say, drinks that we didn’t really need:
That, by the way, is $124 spent on unhealthy drinks in a month. Save on that for six months and we could treat ourselves to a little holiday!
Steph showed me another function called Pivot Table to automatically extract and summarize data; I’m still confused by it and for now filtering my data by the Month and Expense categories and applying the Sum function is sufficient to give me what I need. My primary purpose for tracking expenses is to see how our spending habits can be improved.
Once I had some data in my file, I realised it wasn’t easy to read everything and shading alternate rows fixed that. You can find instructions on how to do that here.
I also added a Remarks column on the far right to enter details about each purchase. This was something I didn’t have previously, and if the spending in a category was particularly high, I couldn’t remember why until I went back to my receipts and checked them. At the time, I kept every single receipt, even after the data had been entered. With the Remarks column, I no longer need to store receipts for future reference–less clutter is always a good thing!
If you look at the screen caps of my current file, you’ll see that I have separate Date and Month columns. The Date column is really just a way for me to sort the data in chronological order, and to view purchases on a particular day if I needed to. But usually I view my data by month, and I enter the month in the YYMM format so that I can potentially use this file over several years.
Why create your own expense tracker file instead of downloading one of the many free templates out there? Well we all spend money in different ways, and we want to track different expenses too. I downloaded a few templates and found that they didn’t apply to my life, while creating my own file told me a story about my variable expenses that I previously didn’t have access to. If you’re trying to spend less or stick to a budget, this is something that’s worth doing.