Automated Expense tracker template in Excel

In many SMEs, implementing a formal system for submitting and reimbursing expenses is not a top priority. Usually, employees simply submit their receipts at the end of the month, which often results in a lot of manual work for accountants or the person responsible for handling expenses.

While there are a lot of software solutions available to track expenses, most of these come with a hefty price tag or steep learning curve. Instead, using an expense tracker template in Excel provides companies with an easy and reliable way to track expenses in real-time.

Why use Microsoft Excel to manage expenses?

• Flexibility: It is possible to customize Excel spreadsheets to your company’s needs and requirements. Indeed, online spreadsheets allow you to create a versatile expenses submission and tracking system. No coding skills are required to adapt and customize the expense tracker template in Excel.
• Accessibility: Microsoft Excel is cloud-based so your colleagues can submit expenses from their laptop or mobile at any time, even when they’re on the road. Accordingly, administrators can process expenses quickly and easily.
• Compatibility: Microsoft Excel is easily compatible with other software that your company might be using. Therefore, data is easily imported into Excel spreadsheets.
• Easy to use: Many of your employees are probably familiar with spreadsheets and if they are not, it is easy to train them. Indeed, using an Excel-based template is ideal for beginners and advanced users alike.

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 Excel files 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: Excel 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.

What you get with this template

• 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. The automated dashboard projects a visual overview of expenses per day, month, expense type, and individual.

How to get the Expense tracker template in Excel

Click the blue button below to get started.

1. Sign up for Sheetgo using your Google, Microsoft, or Dropbox account. It is free, no credit card is required.
2. Click Install template and choose your preferred platform to store your copy of the template.
3. Please allow a few moments as Sheetgo creates the connections and saves a copy of the template files to your chosen storage.

How to use the Expense tracker template in Excel

Step 1: Share the Individual expense tracker spreadsheets

Double click one of the Individual expense tracker files in the main workflow. The spreadsheet now opens in a new tab. Please note that the template contains sample data that you can overwrite.

Simply click the green Share button at the top right of your screen. If you prefer working directly inside the Excel application, open the template in Excel by clicking Open in Desktop App.

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 their chosen cloud-based platform.

Step 3: Update the workflow

Import your colleagues’ data into the master sheet by updating the Sheetgo connections in the template.

Go back to your main workflow view and click Run in the menu bar.

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.

Step 4: Adjust the settings (optional)

The charts in the master sheet dashboard are configured to display expenses data from the past 30 days. 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 > Data Validation.

Edit the criteria next to Source and click OK.

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.

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 transfers to run at regular intervals.

In the main workflow view, click Automate instead of Run and select your preferred time schedule for transfers.

How to add more files to the worfklow

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:

• Go to your Drive storage (OneDrive, Dropbox, Google Drive) > Sheetgo Templates > Expense tracker.
• Copy one of the Individual expense tracker files by right-clicking on Make a copy.
• Rename the file as appropriate.
• Open Sheetgo and go to your Expense Tracker workflow > Connections.
• Click on the 3-dots menu (⋮) Edit Connection.

1. Click +Add another source file.
2. Locate the new file you have just created inside your Drive storage.
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 Save changes.

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

That’s it!

Your Expense tracker Template in Excel now is up and running, congratulations!

Looking for more Excel finance templates ?

Sheetgo has a large range of templates available. Check out our income statement template in Excel and discover how Sheetgo helps you automate your entire finance department.

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!