Expense tracker template in Google Sheets

expense-tracker-template-google-sheets

Written by Laura Tennyson

Oct 14, 2020

Keeping on top of your team’s spending is a key part of budgeting and business management. Rather than waiting to collect a pile of receipts at the end of the month (and possibly getting a nasty shock at how much money your employees have spent), it’s good to keep track of expenses over time. If you run an international business or manage a large sales team, for example, travel and hospitality expenses can quickly add up.

Many small to medium-sized businesses don’t have a formal system for submitting and reimbursing expenses. Often, staff will simply submit a pile of paper receipts or send an email with an uploaded image attached.

For accountants, keeping track of receipts and reimbursements can be a nightmare. It’s more efficient to have all of that information centralized in one place, online. Although there are specialist expenses tools available for this task, there’s no need to spend money on expense management software.

Why use Google Sheets to manage expenses?

  • Flexible: Online spreadsheets allow you to create a versatile expenses submission and tracking system that’s customized to your business. You can adapt and expand the system over time, without any coding skills.
  • Accessible: As Google Sheets is cloud-based, your colleagues can submit expenses from their laptop or mobile at any time, even when they’re on the road. Admin staff can process expenses quickly and easily, without any special software.
  • Compatible with everything: Every piece of software integrates with Google Sheets. As a result, you can easily import data from other areas of the business, giving you everything you need in your Google Sheets.
  • Easy to use: It’s likely that your entire team can already use spreadsheets so rolling out the system is straightforward. A Google Sheets expenses solution is future-proof and easy to maintain.

How to get started

Sheetgo’s spreadsheet-based workflow template is a pre-built system for automated expense tracking that can be used by any business.

The template lets team members submit their expenses quickly and easily while giving managers an up-to-date overview of spending in the master sheet and dashboard:

expense-tracker-template-dashboard

What is a spreadsheet-based workflow template?

Whereas a regular spreadsheet template is a single file containing tables, charts, layouts, and formulas, a Sheetgo workflow template is a complete system.

It’s a group of interconnected Google Sheets that exchange data between them. This enables you to share data with others while working autonomously in your own individual file.

A spreadsheet-based workflow template gives you:

  • Organization and data privacy: Each file is dedicated to one single task, which keeps data secure and separated. Employees submit expenses in their own spreadsheet and keep an eye on their spending in their personal dashboard. Data from all the individual spreadsheets flows into the master sheet, so only the manager can see everybody’s expenses.
  • Scalability: Google Sheets has a powerful sharing function but multiple people working in the same spreadsheet can make the file heavy and slow. Separating data into individual spreadsheets keeps the system working smoothly. When you need to expand the template, simply duplicate one of the Individual Expenses spreadsheets and share it with the new employee.
  • Integration: As well as allowing you to import data from other software packages, a spreadsheet-based workflow allows managers to merge data from departments. In a few clicks, you can connect your expenses workflows with your budget workflow (or those from other departments) to generate directors’ reports.

Every employee gets their own personal dashboard to track their expenses:

expense-tracker-personal-dashboard

What you get with this template:

When you install the Sheetgo Expense Tracker template, four Google Sheets files with ready-made Sheetgo connections are saved to your Google Drive:

  • Three Individual expenses spreadsheets. Share these files with the team members who will submit expenses and make as many copies as you need for additional staff. Each employee uses their copy of this spreadsheet to submit expenses, share receipts, and monitor their own spending.
  • One Team Expense tracker master sheet, for the administrator or company accountant. In this file, they receive expenses submissions from across the company. In the automated dashboard, they get a visual overview of expenses per day, month, expense type, and individual.
Expense tracker template in Google Sheets: Master Sheet and Individual Expense Tracker Sheets

Do you work alone?

If you’re not personally responsible for expense tracking, share this article with your manager so they can set up the workflow template in your company.

If you work in a very small team and you’re happy to manage your expenses in the same file, check out our basic expenses template.

How to get the workflow template

Click on the button below to install Sheetgo’s Expense Tracker template and then

  1. Log in to Sheetgo with your Google account. As this template is based in Google Sheets, the files will be saved to your Google Drive.
  2. Click Use template.
  3. Wait a few moments while Sheetgo installs the template and creates the connections.
Expense tracker template

Google Sheets workflow template with your colleagues to register and analyze expenses.

The Team expense tracker master sheet opens inside Sheetgo. Take a look at all the files to familiarize yourself with the template. You will find that the spreadsheets contain sample data to demonstrate how the template works. Just delete or overwrite this information when you start using the template.

To open any of the spreadsheets, double click on the file in the sidebar on the right. When you want to close the sidebar, just slide it to the right using the blue arrows.

expense-tracker-template

How to use the Expense tracker template

Step 1: Share the Individual expense tracker files

From inside Sheetgo, go to Files or Workflow on the right-hand panel of the screen.

Click on one of the Individual expense tracker files to open it inside Sheetgo. Close the sidebar by dragging the blue arrows to the right of the screen.

In the top right-hand corner of the screen, click Open in new.

expense-tracker-template-open-in-new

The Individual expense tracker spreadsheets contain sample data. You can delete this before sharing the files with your colleagues or instruct them to do it.

Rename the file for the colleague and share the spreadsheet with them using the green Share button.

Step 2: Colleagues enter their expenses

When a member of staff pays for something that they need to put on their expenses, they should record it in the Expenses tab in their personal spreadsheet. In Column F they can enter a link to a photo of a receipt that they have uploaded to Google Drive.

expense-tracker-template-individual-expenses-tab

Step 3: Update the workflow

Once your colleagues have started updating their spreadsheets, you want to import that data into the manager’s master sheet.

To do this, click Workflow on the sidebar to the right of the screen and then click Run on the floating menu bar.

expense-tracker-template-run-workflow

The Sheetgo connections now pull data from all the Individual expense tracker spreadsheets straight into the master sheet.

You can see this data in the Consolidated expenses tab and quickly check how much each team member is spending.

As you will notice, this tab has a padlock icon. That’s just to remind you that this is a connected tab, pulling data from the other files — so don’t try to edit this tab.

There are also some hidden tabs in this file. These contain formulas to prepare the data for the dashboard.

expense-tracker-template-consolidated-expenses

Step 4: Adjust the settings (optional)

The charts in the master sheet dashboard are configured to display expenses data from the past year. To adjust this, just enter the number of days in the white box on the dashboard.

You will also notice that the template contains 7 options for Type of expense. These are Air & Transport, Lodging, Fuel, Meals & Tips, Phone, Entertainment, and Other.

If you want to change these options, go to the Consolidated expenses tab in your Team expense tracker (master sheet).

Select a range of cells in Column E (Type of Expense) and click on Data Validation.

Edit the criteria next to List of items and click Save.

The next time you update the workflow, this data will be sent to the Individual Expense Tracker files. The new list will be available via the dropdown menu in each spreadsheet.

expense-tracker-template-data-validation

Step 5: Automate the workflow

Once your colleagues are regularly submitting their expenses into their spreadsheets, there is no need to run the workflow manually. Instead, you can schedule automatic updates to run at regular intervals.

Open the Workflow bar on the right-hand of the screen and click Automate. Select how frequently you want the data to be updated: from once an hour to once a month.

expense-tracker-template-automate

How to add more Individual files to the workflow

Adding another colleague to the Expense management workflow is pretty straightforward. Simply copy one of the Individual expense tracker files and connect it with new Sheetgo connections.

Here’s how to do it:

  1. Go to your Google Drive > Sheetgo Templates > Expense tracker.
  2. Copy one of the Individual expense tracker files by right-clicking on Make a copy.
  3. Rename the file as appropriate.
  4. Open Sheetgo and go to your Expense Tracker workflow > Connections.
  5. Click on the 3-dots menu () Edit Connection.
expense-tracker-template-edit-connection

Under Source data > Multiple files, you will see a list of the three files that are already connected as part of the template.

  1. Click +Add another source file.
  2. Locate the new file you have just created inside your Google Drive.
  3. In the FILE TAB box, select Expenses. This is really important to ensure that you import data from the correct tab (sheet) in the spreadsheet. 
  4. Click Finish & Save.
expense-tracker-template-add-new-file-finish-save

In the workflow view, you will see that there are now 4 Individual files connected to the master sheet.

expense-tracker-new-workflow-four-files

If you have multiple employees and a lot of timesheets, you can also use Sheetgo to consolidate data from an entire folder of spreadsheets.

This enables you to automate another step of the process. By simply dropping a new Individual expense tracker file into the folder, it will automatically be included in the workflow.

Build a complete accounting system in Google Sheets

To create a more advanced system, you can customize the template further or link it to other company workflows such as your budget monitoring or cash flow system.

If you’d like us to build a tailor-made system for you, or you need assistance customizing your Expenses tracking template, talk to our support team.

Looking for a basic template?

Do you work alone or with a couple of colleagues? If you need a simple spreadsheet template to track expenses yourself, try our one-file template. This template does not contain multiple files or connections, just a simple way to log expenses, store receipts, and track spending.

Did you like this post?

If you found this article useful, share it with your friends and colleagues using the social media buttons on the left!

You may also like…

Share This