Hello and welcome to the first official instalment of my new series: Learning to save (when you’re terrible at saving).
If you missed my first post, I’d head back and read it. It was an introduction into the series and what to expect, alongside a little look into my history with money and saving. TL;DR: I was shit with money for various reasons but now I’m being an ~adult~ and saving for a house, so this series is going to be a real-life, real-time account of that happening.
I was going to write this post about a month ago, but my spreadsheet still had some kinks I had to work out and sort. I’ve now used it for 2 solid months, and a little of a 3rd month, and I feel totally settled in how I’m using it and advice I can try give to anyone who wants to make one. So, lets get cracking!
Warning: this post is going to be MAMMOTH. Maybe go grab a brew before you dig in…
Step one: Creating your budgeting spreadsheet
Honestly, this was the hardest part for me. I’m a complete Excel noob so I had to Google practically every single step. But, if anything, that just goes to show you can definitely do it if I can.
I created my spreadsheet in Google Sheets so I can access it from my phone. If you have a Google account, I would 100% recommend doing this – it’s great to have it on the go. If not, Excel works fine as well! I tried to make it in Numbers (the Mac version of Excel, for anyone who doesn’t know) but honestly I find it even harder to use than Excel so scrapped that plan almost immediately. If you’re going to be using Numbers, apologies in advance if any of the formulas/instructions don’t work!
I’m going to insert a blank template at the end of this post, for anyone who wants to just use mine and crack on. BUT. Before you do that!! I would highly encourage you to either create your own or at least read through the instructions before you download the template, especially if you’re not an excel whizz. It means you can create a spreadsheet/edit the blank template to fit your needs. What I track/record vs what you do might be completely different. So, maybe give making your own a try before you head straight to the template. Creating mine from scratch really helped me understand the whole thing better
Now, let’s get started.
Decide what you want from your spreadsheet
- Are you looking to just track your income and outgoings?
- Do you want to set savings goals, or are you just budgeting?
- Are you wanting to get out of an overdraft, or pay off a credit card?
- Do you need to track any work expenses?
- How detailed do you want to be?
All these things (and probably more) will affect how your spreadsheet looks, so really do think about it in as much detail as you can. But remember – you can always change things month-to-month. I had to use mine for a couple of months before realising I needed to add certain things and remove others. Think of it as a fluid thing and you’ll stress about it a lot less!
Start creating your spreadsheet
Just a note: I’m going to talk you through how to make a budgeting spreadsheet based on my own. You’re obviously more than welcome to add new things or emit things I’ve included in mine.
My spreadsheet works on a monthly basis, from the first to the last of the month, rather than payday to payday. I find it easier to manage and because my payday changes date (it’s the last Friday of the month, rather than having a set date), this just works better for me. I have a new tab for every month, but every tab is the same basic template.
For every month, I have 6 different sections:
- A monthly budget, split by category
- An income tracker, also split by category
- An account tracker, where I manually keep track of how much I have in each account at the beginning of the month vs the end of the month (e.g. current account, any savings accounts, credit cards etc).
- A section that looks at any key dates coming up in the month that might affect how I budget
- A work expenses tracker
- A full expenses and income tracker
For the sake of this ‘how to’ post, I’m only going to show you how to create the sections that involve any Excel formula. This means, I’ll only show you how to set up the Monthly Budget section, the Income Tracker and the Expenses tracker. The Account Tracker, Month at a Glance and Work Expenses Tracker are all completely manual, so you can do what you like with those!
Here’s a screenshot of what mine looks like. Excuse the gap in the middle, had to take 2 screenshots so you could see the whole thing…
Expenses & Income Tracker section, part 1
This is the section you’ll use the most, day-to-day. I try to fill it in every couple of days so I can properly keep track of my finances but definitely don’t leave it more than week. I was so busy at work in late February I didn’t fill it in for 2 and a half weeks and it meant I went over budget on basically everything because I just wasn’t paying attention to my spends…
It involves very little formula, but you’ll need to tie it in with your Monthly Budget section (which I’ll talk about next).
(This section is technically at the bottom of my spreadsheet, so make sure you leave a bit of space above so you can fit your Monthly Budget in above it. As an example, you can see how I’ve laid out my spreadsheet in the screenshot I posted above. My Expenses and Income Tracker section begins on row 27).
I have a table with 6 columns:
- Date (of the transaction)
- Description (what the transaction was – I try be as detailed as possible so I can remember exactly what I spent my money on. So, instead of just ‘Morrisons’, it’s ‘weekly shop from Morrisons’, or instead of ‘Boots’ it’s ‘new foundation from Boots’).
- Category (which category any outgoing transaction fits into. You’ll decide on your own categories in the Monthly Budget section of this post, don’t worry!)
- Debit (how much money has come out of my account, if the transaction is outgoing rather than income. This is attached to the previous ‘category’ column).
- Credit (how much money has come into my account, if the transaction is income, rather than outgoing. This is attached to the next ‘category’ column)
- Category (which category any income transaction fits into. Again – you’ll decide what these are later).
The only bit of formula you’ll need will be for each category column and, before you do that, you’ll need to set up your Monthly budget section…
Monthly budget section
This is where I decide on my budget each month. I prefer my spreadsheet to be as detailed as possible, so this is quite a hefty section for me. I’ve split mine into 17 sections which covers everything from bills and debts to supermarket shops, takeaways and going out. I give every section it’s individual budget for the month, and that all automatically adds together to create one overall monthly budget, which is just under the amount I get paid every month.
This is where I start every month. I begin with the things that have a set cost every month (e.g. bills) and then continue down the list of priorities for that month. Sometimes I know I’m going to have a busy month, so I assign more to my going out budget than normal, sometimes I know I’m running out of a bunch of makeup/skincare things so I’ll assign more to my personal care/toiletries than normal. This is what I’m talking about when I say it’s fluid – no two months are going to have the same spends in every category for me.
The budget section is essentially a table with 4 columns – Category, Budget, Actual and Difference.
‘Category’ is just that – what category the spends come under. ‘Budget’ is how much money I’ve assigned to that category that month. ‘Actual’ is how much I’ve actually spent on that category that month and ‘Difference’ is the difference between by ‘Budget’ and my ‘Actual’ columns. ‘Budget’ is entered manually at the start of every month, ‘Actual’ and ‘Difference’ are automatically calculated as the month goes on and I add to the Expenses and Income Tracker section.
‘Category’ column
This requires absolutely no Excel formula. All it requires is you deciding what categories you need to budget for.
I currently have Bills, Debts, Savings, Supermarket, Transport, Petrol, Shopping, Takeaways, Going Out, Lunch, Personal Care, Entertainment, Gifts, Big Spends (aka things like holidays that don’t happen every month but can throw off a budget), Miscellaneous, Blog Spends and Work Expenses. I don’t include any work expenses in my final budget as they all get paid back to me, but they’re still good to track in my opinion!
Other than that, there’s just a “Total” cell, but again – no formula as it’s essentially just a title. The formula for this is in the next column.
‘Budget’ column
This requires super minimal Excel formula. I formatted every cell as ‘currency’ but I manually update this section every month. The only bit of formula is in the ‘total’ cell we talked about earlier. The formula for this is:
=SUM(CELL:CELL)
Just change where it says ‘CELL’ to the cell numbers you’re going to be adding together. For example, the formula in mine says =SUM(C5:C20) because it’s adding together all data from cell C5 to cell C20 – every individual budget I’ve set – to create one full budget.
Alternatively, click on the cell you want your total to appear in, click on the ‘functions’ button, then click on ‘SUM’. Then select all the cells you want to add together to create the total!
This is the same formula you’ll need for the ‘Total’ cells in your ‘Actual’ column and your ‘Difference’ column, as well. Just make sure the cells correspond to the correct column.
‘Actual’ column
This is where the Excel formula starts to get a little more complicated. In your Expenses and Income Tracker section we talked about earlier, you’ll log every single outgoing and income transaction you make/receive throughout the month. You’ll say what it is, how much it was and you’ll also categorise it into one of the categories you’ve set in your Budget section. This ‘Actual’ column, takes every single thing you’ve logged and categorised in your Expenses and Income Tracker section and keeps track of what you’ve spent in every category.
You basically want to tell each cell in your ‘Actual’ column to only take notice of transactions from your Expenses section if they’ve been categorised correctly.
For example, in the ‘Actual’ column cell in the row you’ve categorised as ‘Bills’, you only want it to pick up on transactions you’ve marked as ‘Bills’.
Still with me?
The formula for this is:
=SUMIF(CELL:CELL,”CATEGORY”,CELL:CELL)
The first ‘CELL:CELL’ is where it will take the data from the first ‘Category’ column in the Expenses section – AKA, the drop down menu of categories you assign to each debit transaction. For me, this is F28:F103.
The “CATEGORY” part is the category you’re wanting to track in this cell. E.g. if it’s in the ‘Bills’ row of your Budget section, this bit will say ‘Bills’. Spelling is ESSENTIAL here, if one letter is out compared to the ‘Category’ column in your Budget section, it won’t pick up on the right thing.
The second ‘CELL:CELL’ is where it will take the data from the ‘Debit’ column of the Expenses section – AKA, how much you spent. For me, this is G28:G103.
As an example, the formula in the ‘Actual’ column for the ‘Bills’ row of my Budget section is =SUMIF(F28:F103,”Bills”,G28:G103)
‘Difference’ column
You’ll be happy to hear this is an easy one! The formula is simply
=CELL-CELL
And all you need to do, is make sure it’s the cell from the Actual column minus the cell from the Budget column. For me, that is =D5-C5. And in the next category, it changes to =D6-C6 and so on. If you copy and paste this formula into any cell, it should automatically change to be correct – you won’t have to manually type every single subtraction formula.
Colour coding the ‘Difference’ column
This is completely optional, but I did it so I can instantly see where I’m under/over budget.
I used what’s called Conditional Formatting to do this. It means if I’m over budget, the ‘Difference’ column cell will automatically turn red. And if I’m under budget, it will turn green.
Notice, however, in my ‘Savings’ and ‘Debts’, the colours are reversed. This is simply because if I’m over my savings budget, it means I’ve put more money into my savings than planned, which is a good thing! If I’m under budget, I’ve saved less, which is bad. Essentially the exact same principle, just flipped on its head.
To do this, select the cells you wish to apply the conditional formatting and go to Format > Conditional Formatting
I did this twice, once for normal categories (e.g. bills, supermarket, transport) and once for savings/debts. For normal categories, I set the cells to turn red if the value of the cell is greater than 0.1 and set the cells to turn green if the value of the cell is less than 0.
For my savings and debts cells, I set them to turn green if the value is greater than 0.1 and red if the value is less than 0.
Income Tracker section
I have a separate little table for tracking income. It works in the same way as the main Monthly Budget, except it only has 2 columns – ‘Category’ and ‘Income’. The ‘Category’ column in your Income Tracker section works exactly the same as the ‘Category’ column the Monthly Budget section – you decide on the categories and input them manually.
The ‘Income’ column works in the same way as the ‘Actual’ column in the Monthly Budget section. You’ll need to use the same instructions as above, and the exact same formula:
=SUMIF(CELL:CELL,”CATEGORY”,CELL:CELL)
Except this time, instead of using the data from the debit ‘Category’ column, you’ll be using it from the income ‘Category’ column – the last column in the Expenses and Income Tracker section. And each category will be the income categories you’ve decided on.
Expenses tracker section, part 2
Now you’ve got your Expenses Tracker and Monthly Budget all set up, it’s time to go back to the Expenses Tracker and put the formula into the ‘Category’ columns.
Let’s start with the debit Category column.
Select all the cells in this column and go to Data > Data Validation.
Under ‘cell range’ select all the categories you decided on in your Monthly Budget section. Not the column title, just the categories.
Tick the box that says ‘show dropdown list in cell’ and click save.
You should then have a little drop down arrow in every cell, which brings up a list of all the categories from your Monthly Budget section.
Do the same in the Income ‘Category’ column, except when selecting the cell range, select the categories you set for your Income Tracker section.
And that’s it!
I hope this all made sense. Excel isn’t something that comes naturally to me and, like I said, I had to Google every single step whilst creating this spreadsheet. But hopefully you’ll all manage and it will help you keep track of your finances.
I mentioned at the beginning of the post that I’d post a downloadable template for anyone who doesn’t want to create their own. Don’t worry, I’m keeping to my word. Although, again like I said before, I do recommend you try make your own first. It means you’ll be fully to grips with it if anything goes wrong!
Here’s a Dropbox link to my budgeting spreadsheet in Excel format. I’ve stuck some instructions into the first page on how to upload it to Google Sheets if you’d prefer it to be available and editable anywhere, but you’re also welcome to keep it as an Excel sheet instead! There are also some slightly more concise instructions on how to edit the pre-built sheet, if that’s what you’d prefer.
Feel free to edit to your heart’s content – it absolutely must work for you for it to work at all!
If anyone has any questions, just Tweet me and I’ll do my best to help. Hopefully this helps you as much as it’s helped me so far this year!
Happy budgeting!
