How to easily track your expenses with Google Sheets (updated for 2019)
[HOW TO USE: if you want to use the template, please go to the ‘Digital Household Booklet’. In the upper left corner, click on ‘File’ > ‘Make a copy’ (here’s a how-to vid, or a screenshot below).
You’ll then have your own copy to edit. Yay!
Please do NOT click the ‘Share’ button. Instead, make a copy😉! This copy will be your own personal version, that only you can see and edit, and no one else.
If you need help, please feel free to write me. ] TL;DR below!
With the new year having approached, many people might have summed up their new year’s resolutions. A common issue we see coming up every year is overspending and properly tracking ones expenses. First of all, don’t be an idiot and wait for a date to change before you take action. Just start today, start now with bad habits you want to get rid of, and replace them with better ones. Which will of course take time, but hey, that’s life. Some of you might have issues with not throwing money down the drain where more of it could be saved. There are a bunch of apps out there to help you with that. I won’t be talking about any of these. What I have for you is an easy to use alternative for which you will only need Google Sheets.
Go on, have a peek at the ‘Digital Household Booklet’.
“Why do you use a Google Sheet when there’s a bunch of apps out there to track your expenses for you?“ Well, I probably would use an app for that. I however -at the time of publishing- use a prehistoric iPhone 4S which runs out of battery more often than the amount of times you go to the toilet during the day. I’d be able to save my expenses into the app, but once it runs out of battery again, I’d still have to write half of my expenses down elsewhere, or keep the receipts, wait for my phone to charge, and then save it in the app. A new phone is scheduled, but not just yet. In the meantime, this is how I handle it.
“But writing down every expense you make or saving all the receipts you receive each day! Such a hassle!”, you might think.
Here’s who could benefit from this ‘Digital Household Booklet’ and how, as well as my reasons for using this sheet:
- Paying for many things in cash: I live in Berlin, where in many restaurants, shops and the like, you unfortunately cannot (yet) pay by card. I probably just got used to it? I am saving receipts either way or making a quick note on my phone (if the battery hasn’t already died) of what I just paid for. If you’re in a similar situation, it won’t be possible to track everything automatically by just linking your banking account to an app. You’ll need to make an effort to take a pic of a receipt anyway and upload in in an app, which might read out the digits for you. I personally don’t need the receipts. So it depends on what you prefer.
- By now I have firmly established the habit of manually inputting everything into my sheet. (This manual handling also forces me to face the consequences of my decisions. I spend big money, cash? Then I have to face the horror of adding it to the sheet, and seeing how my total saved amount immediately drops down. Helps to keep me accountable and it serves as a mental reminder telling me “Ok, that was fun, but cut it down the rest of the week/month, okay?”)
- Not fluctuating fixed costs: I don’t do any crazy stuff with my expenses. My fixed costs are pretty much exactly the same each month. They don’t fluctuate. That’s also why they’re called ‘fixed’. I don’t at the moment need a program to track them for me. A simple Google Sheet will do just fine. So what I mean to say, is that it would work best for people with simpler financial flows.
- Yearly overview of expenses in every category: With the way this Google Sheet is set up, I have an immediate overview of my yearly incoming and outgoing money, up to the very cent. By switching tabs, I get to see exactly what I spent my money on, if needed.
- People who live with and in their Google Drives: If you already spend a lot of time in your Google Drive, then working with this sheet will feel familiar. (Google Drive is also free if you stay under the 15 GB storage limit, unlike the better features in some apps.)
- Your shitty old phone doesn’t allow for more apps to be installed: If you use a prehistoric phone with limited storage space and deteriorating battery life, you will need to find other ways to get smart with your money.
That is where my sheet comes in! Follow this link to the ‘Digital Household Booklet’, make a copy of your own, so you can edit it to your liking, and here is how it works.
Tutorial: The Elaborate Version (TL;DR below)
The first tab is called “Overview”. We are going to fill out some data in the fields only once to start. After that, this sheet will automatically pull data from the other tabs (named ‘jan 18’, ‘feb 18’ and so on) once you fill out your expenses there.
Green area: This area is for incoming money. Let’s start by filling out the salary in B3. All other cells (D3 to Z3) will copy this value. There’s also rows for ‘Gifts’ and ‘Selling stuff’ in case it’s e.g. your birthday and your Auntie Martha sends you some money. Or you’re planning a garage sale and expect to make some extra cash.
Important: Make sure to write out all amounts and numbers in dot notation. So if your salary is €1234,56, write this as 1234.56 so with a dot separating euros and cents (or whatever).
I occasionally sell ‘old’ stuff, and write down the netto ‘profit’ (what I ‘earned’ after deducting packaging and transportation costs) in the ‘Selling stuff’ row for the applicable month. Change it to whatever you want.
Red area: This area is of course for outgoing money. We have the fixed costs and non-fixed costs. Now we are only going to fill out the fixed costs. In the fields B10 to B21 we are filling out how much you pay for things such as rent, utilities, your phone and public transport subscription. Whatever is applicable to you. Delete or adjust a category depending on what you need. If needed, you can insert an extra row here. (Oh, and in case the fixed costs happen to change, you can always overwrite the field for this month.)
Regarding the non-fixed costs: go to the ‘non-fixed categories’ tab. In this table, fill out the categories for non-fixed expenses you’d like to track! This is the only place you have to fill out the categories.
After you’ve filled them out here, they will be automatically shown in the ‘Overview’ tab (A24 to A35, see field A24), and in all the dropdown menus in the monthly tabs. The only place you’ll ever have to adjust/remove/update a category, is in the ‘non-fixed categories’ tab! Nice ey?
Great job! We’re now done setting up the ‘Overview’ sheet.
Moving on to the other tab! As it’s January, we go to the ‘jan ‘18’ tab. I entered some dummy data for you. Row E has a dropdown menu to choose the type of expense (this is directly linked to the ‘non-fixed categories’ sheet). If needed, you can insert one or several row or rows here as you spend your money on things more than once per day. When on a Mac, use the following shortcuts:
As you can see, the data which is entered in here can also already be seen in the ‘Overview’ tab in the fields D24 to D35, along with the yearly average per category in cells B24 to B35. The absolute total of money spent on non-fixed things per month can be seen either in ‘Overview’ in row 36, or in the ‘jan ‘18’ tab, all the way at the bottom next to the ‘TOTAL’ field, which automatically sums up the grand total of all expenses filled in above.
In column AB in the ‘Overview’ tab, we’ll be able to find our total income (field AB7), our total fixed (field AB10 to AB21), and non-fixed costs (field AB24 to AB35) for the entire year.
Row 38 called ‘GRAND TOTAL’ refers to how much you were able to save each month, with the absolute total amount of money you saved by the end of the year visible in field AB38.
— TL;DR: Summary & Tutorial — The Speedy Gonzales Version
- Copy this google sheet to your own Google Drive by clicking on ‘File’ > ‘ Make a copy’. Rename the sheet if you want, and save it wherever you want.
- Fill out your monthly netto salary in B3.
- Fill out your monthly fixed costs in B10-B21, adjust categories and/or add rows where necessary.
- Go to ‘non-fixed categories’ tab, and in this table, fill out the categories for non-fixed expenses you’d like track! Do not touch ‘Overview’ A24 to A35. It’ll pull automatically from the ‘non-fixed categories’ tab.
- Start filling out the expenses you make on a daily basis in the ‘jan ‘18’ tab, using one row per expense. There’s some dummy data to give you an example.
- Insert more rows where and when necessary in the monthly tabs. (Keyboard shortcut for inserting new rows mentioned below!)
- See your ‘Overview’ tab for data to be pulled from all other tabs automatically, after you filled out data in the monthly tabs.
- Download the Google Sheets app!! Open the app and find your template. Press on the 3 dots on the right next to the template. Then press ‘Make available offline’. Wherever you are, you’ll be able to edit the template, your data will be saved. Once your phone connects to the internet, your template will be updated in the Cloud and synced across devices so that it’s up to date everywhere.
I hope this was not too difficult to follow. What I hope for even more, is that this ‘tool’ can help you in becoming more aware of what you are spending your money on by being confronted with exactly how much you spend and have left over each month by just looking at this sheet in despair and then wanting to die. Just kidding.
Being confronted with the facts forces you to stand still for a moment and think about whether or not you (and your bank balance and the contents of your wallet) are happy with the way you’ve been going along at this so far. If not, you’ll become more aware of where possible bottlenecks lie and where you could cut down your spending, just by analysing this sheet. Of course, your wallet and bank balance would never lie to you either.
EDIT: I added some percentages to the Digital Household Booklet:
- In ‘Overview’ C10-C21 you will now be able to see how much of your monthly salary you spend on fixed costs, in a percentage. I only added this to in Column C (and not to every month) because these percentages will be the same each and every month.
- To already see how much % of your monthly salary you spend on fluctuating costs, I added the percentages in here as well for each month (row 23).
- I also added a yearly overview of this to the fluctuating costs in C24-C35. B24 and C24 to B35 and C35 will keep changing as you structurally fill out your expenses in the monthly forms in the separate tabs. That means that you’ll be able to evaluate the digits in these fields at the end of the year.
EDIT 2: I changed some categories in the non-fixed costs section.
- I removed ‘Junkfood/drinks’ (this can live under ‘leisure’).
- ‘Farmacy/toiletries/salon’ is now the more general ‘Medical’.
- To my horror I noticed I didn’t have an ‘Education’ section and thought to myself, ‘wtf’. So I added it in.
EDIT 3 (Jan 2019): Automatically populating non-fixed categories, and all dropdown menus in the monthly tabs.
- Fields in ‘Overview’ tab non-fixed section, and the dropdown menus in the monthly tabs, will now be populated automatically. All you need to do is fill out the categories of your choice once, in the table in tab ‘non-fixed categories’, et voila!
Edit 4 (June 2019): I just noticed I never added a Grand Total Out section. Whoops! (It never bothered me, I was more focused on the saving money part). Now it’s added in in line 37 on the ‘Overview’ sheet!
Thank you for reading! If you thought this was helpful in any way, give it a clap so that others can find it!
I would really appreciate it if you leave a comment with your experiences using the template! I’m very open to feedback, so if you have any recommendations or improvement suggestions, please do let me know! ❤