Expenses Template

EXPENSES TEMPLATE

Make it with Google Sheets

Google Sheet Expenses Template

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. 

Google DriveNavigate 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 you haven’t signed in, 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.

 

Create Google Sheet

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.

table 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.

data validationTo 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.

List Items

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:

  • if it says fortnightly then we will have to divide it by 2 to get a weekly amount.
  • if it says monthly then we will have to multiply it by 12 and divide it by 52 to get a weekly amount.
  • if it says quarterly then we will have to multiply it by 4 then divide it by 52 to get a weekly amount.
  • if it says annually then we will have to divide it by 52 to get a weekly amount.

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.

Explaining how the formula is made

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!

Weekly Column

=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)))))

Fornightly Column

=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)))))

Monthly Column

=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)))))

Quarterly Column

=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)))))

Annually Column

=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 formula

 

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.
total

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.

Seeing in action

If you want to see it in action, you can view the google sheet I have set up here

Looking for quality design service?