Say what you want about budgeting, but the act is useless without tracking your spending to evaluate how closely you adhered to it.
Furthermore, it’s difficult to make a budget that is realistic for your life if you have no clue where your money is going every month (or week, year, whatever time period you budget in).
I remember when I first wanted to start tracking my spending a few years ago, I had no idea where to start.
Here’s what I have learned so I can track my spending in Excel:
Put Everything On Your Credit Card/Debit Card
Having all of your expenses and discretionary spending pulled from one account will help you streamline the tracking process. I put everything on my rewards credit card, so that I can collect rewards on my every day purchases. In Canada, we are usually charged a fee on debits from our bank accounts (except with ING Direct. Shameless referral link – use my Orange Key to sign up for an account and get $25! 35611511S1) , but that’s not applicable to credit cards so it’s a good way to avoid being charged extra banking fees.
For things that can’t go on my credit card (rent, mortgage, banking fees) I use an account that is easy to track.
Try not to use cash! It’s difficult to track cash. If you do use it, keep your receipts and track where the change goes; often spare change is spend impulsively and we don’t account for it.
Export Your Statements
When the month is over, and all of my monthly spending is shown on my credit card statement online, I export my statement to an Excel spreadsheet.
I use TD Canada Trust, but other banks and credit card statements will have this feature as well. You can download it to Quicken, too, but I prefer just a plain Jane spreadsheet.
Play with the Spreadsheet
When my statement is exported, I play with the spreadsheet a bit. I delete the columns for the balances and the payments, because I don’t really need to know about those when it comes to tracking my spending.
The items with the green around them are an example of what you will be paying attention to. I delete the columns with the red arrows.
I pretty it up a bit so it’s easier to look at ( I blanked out the cities I was spending in ):
Create Categories
You can have any number of categories; I’m more specific (I try not to lump my restaurant and fast food spending with groceries as my “food” spending, because I want to know exactly where I’m spending), so I tend to have more categories than most people need. For instance, some might consider their gym membership a “personal care” expense, but I wanted to know what I was spending on personal care aside from my gym membership.
Populate
Populate the categories with the items that fit in the categories. If you don’t know how to use Excel to do this, click on the cell beside the category, press =, and list the rows and columns of the dollar amount of the item. For instance, in my “eating out” categories I would have listed “=C3+C11+C15” (Cafe, KFC and Royal Tandoori Indian). Press enter and Excel will give you the total.
Not all of my categories are listed here, and you can have as many as you want that fit your spending.
Compare With Your Budget
The real value of tracking your spending comes when you compare your spending with what you budgeting in that category for the month. If you budgeted $100 for eating out, but you spent $300, you know that you either under-budgeted for your lifestyle, or didn’t reach your goal and need to make some adjustments.
It’s easy to track your spending in Excel and should only take a half an hour at the end of the month.
Really good post on budgeting. You’re right about tracking. When I first started to budget I tried keeping track in my head. Bad idea. There’s a tonne of apps available to budget, but you can’t beat a good spreadsheet!
We use mint, which I prefer because it does all of this on the fly each month.
I struggle with Mint, because I find that it takes a lot of work – I usually have to recategorize about half of my spending every month which is time consuming.
This is pretty much the exact same way I used to track my spending before I got an app on my phone to do it. Excel is such a powerful tool if you know how to use it properly.
Excel is such an effective financial tracker. I use it for some of my fluctuating investments since the formula keys make it a cinch to change cell values with one simple click. Love this tutorial!
Great tips! I recently stated tracking my spending in an Excel spreadsheet on Google Drive and it’s really helped me figure out where, exactly, my money is going! I use Mint too, but Mint’s categories feature is lacking. I have to recategorize almost half of my transactions!
That’s a good explanation of how to use excel. I don’t track my spending explicitly (I just keep set amounts for categories and stay within those amounts) but I do keep my “official” master budget in an excel sheet.
I love using excel for my budget. Rather than using Mint or whatever else, I find excel simple and straight forward. And also takes the least amount of time in my opinion.
We track every expense in our excel spreadsheet that we designed. We typically input data weekly so it’s not overwhelming at month end. We keep all receipts in a folder and go from there. It’s taken a while to get the budget where we want it to be with lots of changes. It’s worth the extra work. Cheers
I do something very similar. I have a 3 column budget/expense page for every month. The first column is for the category, the second is for the budgeted monthly amount, and I just type in the actual spending amounts in the last column and add them together for each category. We also spend mostly on credit cards, so I just spend an hour or two every month entering every expense and seeing the results. Yay for tracking!
Excellent step by step guide. I also use Excel to do my budgeting, but don’t export my bank statements, I’ll have to start doing that as well. I’ll definitely share!
– Dorethia
I use quicken 🙂 I used to use excel a lot though.
Nice post Daisy! I use Excel a lot to track our expenses and budgeting. It’s such a useful tool and can really do a lot of things with it when you set up some good formulas and worksheets.
I’m all about excel. I’ve got formulas to auto-calculate totals for categories, and just use a drop-down to select which category each item falls under. Thanks for the simple breakdown, really easy to follow.
I love Excel! Seriously, it’s unhealthy. I do, however use YNAB since it also has an iPhone app. The program does a good job at tracking and helping budget, but I also use an excel spreadsheet that shows my monthly budget and amounts I need to allocate in YNAB.
Excellent guide. It suggests a tool that is readily accessible without expensive software or other online stuff. Perhaps it’s nice to include a chart. Thanks.
There are a slew of software programs (some free) that can do all of this automatically without the effort of excel. I have a great program I use that even lets me teach the computer rules so it remembers isolated bills and spending.