Expense tracker template in Google Sheets

by Sep 30, 2019

Why track expenses?

Keeping on top of your team’s spending is a key part of budgeting for your business. Rather than waiting to collect a pile of receipts at the end of the month (and possibly getting a nasty shock at how much your team have spent) it’s good to be aware of each individual’s expenses over time. If you run an international business, a sales team, or you frequently entertain clients, travel and hospitality expenses can add up to a large chunk of your company’s spending. You don’t need to be a bookkeeper to monitor staff expenses effectively, and you don’t need any special software.  The easiest way to track real-time expenses is using a tool you already know and use: spreadsheets. To make it even easier we’ve created a ready-to-use expense tracker template for you in Google Sheets.

Why use an expense tracker template?

Using our ready-to-use expense tracker template you can:

  • Monitor your team’s expenses on a daily or monthly basis—without a bookkeeper.
  • Budget better, by tracking spending over time.
  • See a breakdown of spending per item: e.g. fuel, accomodation or meals.
  • Get an at-a-glance overview in an automated dashboard.

All in one spreadsheet.

What do I need?

We’ve created the template for you in Google Sheets, so all you need is a Google Account. It’s completely free.

Using this standalone template you simply enter the data, and our spreadsheet automatically generates the visual dashboard. Easy. We’ve already written the formulas for you.

  • Free Sheetgo standalone expense tracker template
  • No need for special software
  • Easy to use—in Google Sheets

To get a copy of the template, simply click the green button below:

How to use the expense tracker template

The template is a Google Sheets file named Team Expense Tracker and it will save to your Google Drive automatically. You will see the first tab in the Sheet includes a list of instructions; it’s really simple.

Step 1:

Go to the green All Expenses tab and start entering your data. You will see the template is provided with sample data so you can just delete and/or overwrite this with your own information.

Step 2:

Enter the date, description, invoice number, amount, type of expense (using the dropdown menu) and the link to the receipt (if you have it).

In the Source column, enter the name of the colleague.

If you want to change any of the settings in the spreadsheet (e.g. to add a different type of expense) you can alter this in the hidden tab (Data preparation). Access this via the All Sheets button at the bottom of the spreadsheet, to the left of the visible tabs.

Step 3:

Your team expense tracker is up and running!

Check our your dashboard tab—you will see an automated visual display of all your key expenses metrics such as the amount spent on travel, and expenses per day.

Adjust the time period in the box.

Working in a large team? Want staff to track their own expenses?

It might not be practical for you to enter all of your team members’ expenses into the spreadsheet. You could give all staff access to the file, but having many people access the same spreadsheet can lead to errors, deleted and overwritten data—not to mention the fact that individuals would be able to see other colleagues’ expenses. This isn’t usually a good idea. 

Spreadsheets are a brilliant tool for tracking expenses but they can rely on a fair amount of time-consuming manual work and copy-pasting can lead to mistakes. Imagine if individual colleagues could enter their expenses into their own spreadsheet, and then their spreadsheets could “talk” to yours. 

With Sheetgo then can.

You can expand the team expense tracker template to create an entire automated system within your company. Using the Sheetgo add-on for Google Sheets, you can connect your colleagues’ expense trackers to your own master sheet. Each person keeps their own spreadsheet updated and the data flows into your spreadsheet automatically.

Expense tracker template in Google Sheets: Master Sheet and Individual Expense Tracker Sheets

Create an automated expense tracking system with Sheetgo

If you know how to use spreadsheets, you can use Sheetgo to automate expense monitoring in your organization. This system allows you to:

  • Separate data—for confidentiality
  • Give each colleague their own spreadsheet
  • Create a clear and simple expenses tracking system: fewer mistakes
  • Get automatic updates—view accurate, up-to-date data in your master sheet
  • Save time on manual work, copy-pasting and unnecessary communication

How to connect spreadsheets to track expenses 

Step 1:

Install the Sheetgo add-on.

Step 2:

Open your team expense tracker template (the file we gave you earlier). This will now function as your “master sheet”.

Make a copy of this input spreadsheet. It’s another ready-made template in Google Sheets, called “Individual #1”. Make as many copies as you need and allocate them to colleagues, by renaming the files and sharing them using Google Sheets’ share function. Create a folder in Google Drive for all of these spreadsheets and give the folder a name.

Step 3:

Ask your colleagues to fill out their spreadsheets with their own expenses; remind them to delete and/or replace the sample data provided. There is an instructions tab in each spreadsheet.

Step 4:

From inside your master sheet (team expense tracker) go to menu > add-ons > Sheetgo.

Click “Connect Files” and then select the folder (containing the input spreadsheets) as your source.

Step 5:

Select the source tab from your input spreadsheets (Expenses). Under Settings, make sure you change the connection name to match the name of the destination tab in your master sheet exactly (All Expenses).

Step 6:

Under Advanced Settings, select Identify Source > Enable Source Identifier. This will allow your master sheet to identify which input sheet the data came from (e.g. which colleague’s expenses have been imported!).

That’s it!

You have connected spreadsheets using Sheetgo. Take a look at the All Expenses tab in your master sheet and you will see that the data from your colleagues’ input spreadsheets is now flowing in automatically.

In the Sheetgo add-on, you can adjust the frequency of updates to suit you.

If you need any assistance using Sheetgo, just get in touch with our support team.

 

Interesting in streamlining and automating more of your business processes? Hundreds of everyday systems can be managed in spreadsheets without the need for any special software, saving you time and money spent on accountants and bookkeepers. Check out our post on how to use a budget vs actual template in Google Sheets. 

Share This