I’m Asian, so you know that Math is already in my blood with generations of accountants in my family. That was a joke – well, a half joke since my grandpa was an accountant and my mum is too. Anyway, if you are learning how to adult, the best thing you can do for yourself is learn how to make a budget. Often you get bills that are either weekly, fortnightly, monthly, quarterly, or even annually. How can you tell how much you are spending each month when they all have different cycles? Well, here’s a little simple solution that can help you using Google Sheets! We can create a space that we can first place our bills then have the sheet calculate it out for us.
Not big with Math? Not to worry, here’s a step-by-step on how I made my Google Sheet Expenses Template.
Spoiler: you can see a link to the sheet in action at the bottom of this article.
To do this, you’ll need to sign up for a Google account. Google accounts have a FREE version where they give you 15GB of space in your Google Drive. Google also has some many benefits for your company but we will talk about that later.
Navigate to your Google Drive account. You can find this by opening a new tab in Chrome and if you’re already logged in, then there should be a grid button next to your profile on the top right. If not, sign in first then you can navigate to this button. It will bring up all the options available including Google Drive which is the triangular icon with a triangular hole in the middle.
On your drive, you can right click anywhere and a menu will show up. Scroll down to Google Sheets and create a New Spreadsheet.
To see your expenses into perspective, you’d want to see how much it accumulates Weekly, Fortnightly, Monthly, Quarterly, and Annually. We would need a column for each of these, plus a column for the description. However, to make it easier for us later, we will have two additional columns: Amount and Frequency.
These two will be placed just after the Description but before the rest of the columns.
What does this do? Different things are billed at different cycles. You might have your phone bill billed monthly but your rent is billed weekly. This helps automate our calculations in the long run and save us some time. We will make the Frequency column a dropdown option for Weekly, Fortnightly, Monthly, Quarterly, and Annually. Then depending on these options, this will calculate automatically how much the expense is for the other columns.
To create our frequency dropdown, select the cell that is immediately underneath the title word “Frequency”.
While you have it selected, Go to the Menu > Data > Data Validation.
This will bring up a pop up with a few options. In the pop up, pick List of Items and tick the Show Dropdown list in cell. In the texbox type:
Weekly,Fortnightly,Monthly,Quarterly,Annually
The commas separate each option. Then click Save.
The next colums are a little trickier. We will start with the Weekly column. We want to make a statement that if the dropdown in the frequency column says weekly, then the amount next to it will be reflected, otherwise:
This is a massive IF statement formula which looks like this:
=if(C2=“Weekly”,B2,if(C2=“Fortnightly”,B2/2,if(C2=“Monthly”,(B2*12)/52,if(C2=“Quarterly”,B2/13,if(C2=“Annually”,B2/52,0)))))
Basically you need to make sure you’re not missing a bracket.
The very first IF STATEMENT is:
=if(condition statement, then state what happens if it’s true, otherwise state what happens if it’s not true)
In the condition statement we are saying to look at the Frequency Cell and check if it says Weekly.
Then we have to say what happens when it is true (or correct), which in the Weekly column, we would want it to just reflect the data in the Amount Cell.
The final part is saying what happens if it is not true, which we have four other options to sift through. We have to make the full IF STATEMENTS (bracketes included) for each one and place them in the not true part. Hence we end up with this super long IF STATEMENT with a whole bunch of commas and brackets so don’t miss a single one!
=if(C2=“Weekly”,B2,if(C2=“Fortnightly”,B2/2,if(C2=“Monthly”,(B2*12)/52,if(C2=“Quarterly”,B2/13,if(C2=“Annually”,B2/52,0)))))
=if(C2=“Fortnightly”,B2,if(C2=“Weekly”,B2*2,if(C2=“Monthly”,(B2*12)/26,if(C2=“Quarterly”,(B2/13)*2,if(C2=“Annually”,B2/26,0)))))
=if(C2=“Monthly”,B2,if(C2=“Fortnightly”,(B2*26)/12,if(C2=“Weekly”,(B2*52)/12,if(C2=“Quarterly”,(B2*4)/12,if(C2=“Annually”,B2/12,0)))))
=if(C2=“Quarterly”,B2,if(C2=“Fortnightly”,(B2*26)/4,if(C2=“Monthly”,B2*3,if(C2=“Weekly”,B2*13,if(C2=“Annually”,B2/4,0)))))
=if(C2=“Annually”,B2,if(C2=“Fortnightly”,B2*26,if(C2=“Monthly”,B2*12,if(C2=“Quarterly”,B2*4,if(C2=“Weekly”,B2*52,0)))))
Instead of repeating the formula each time and painstakingly changing the cells, you can drag the formulas down and it will automatically adjust.
Drag it down at least 10 times as you probably have this many (or more) expenses.
At the bottom of all your expenses, we need a row that shows us the total expenses. These should add up all the data above.
We can’t total the Amount as it varies from weekly to monthly, etc. We also can not total the frequency. However, we can total the Weekly, Fortnightly, Monthly, Quarterly, and Annually columns. This helps us see how much our expenses cost all together; whether it is weekly, fortnightly, monthly, quarterly, or annually.
To add everything in a column, you can write the formula:
=SUM(firstcell:lastcell)
As long as the data is all lined up in one column. Example for the image above:
=sum(D2:D30)
My expenses run until the 30th column so my last cell is D30.
If you want to see it in action, you can view the google sheet I have set up here.
Jozzelle has been working professionally in the graphic design area since 2006 and tackled freelance design and animation since 2011. She has acquired a Bachelor of Interactive Entertainment (Animation) back in 2012 and completed a Cert IV in Training and Assessment in 2013. These days, she teaches graphic design with RTO's whilst she continues her work with current clients. If you would like to work with Jozzelle, please email hello@e-studios.com.au