Automated Expense tracker template in Excel (2022)

Organizations have a lot to gain from keeping a tab on their employees’ expenses. As such, it is generally better to keep track of expenses over time than waiting for the receipts to arrive at the end of the month. In companies with large sales teams that do a lot of traveling, incurred travel expenses can quickly pile up. Moreover, companies prevent unpleasant surprises by having a reliable tool in place to track expenses in real-time.

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.

Automated Expense tracker template in Excel (1)

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.

(Video) Excel Budget Template | Automate your budget in 15 minutes

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.

Automated Expense tracker template in Excel (2)

How to get the Expense tracker template in Excel

Click the blue button below to get started.

Automated Expense tracker template in Excel (3)

Expense tracker template

Automated Expense tracker template in Excel (4)

Spreadsheets workflow template with your colleagues to register and analyze expenses.

GET STARTED

(Video) 🔥 Automated EXPENSE TRACKER Software in Excel 2022

  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.

Automated Expense tracker template in Excel (5)

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.

Automated Expense tracker template in Excel (6)

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.

Automated Expense tracker template in Excel (7)

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.

(Video) Expense tracker for Excel (with automated dashboard)

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.

Automated Expense tracker template in Excel (8)

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.

Automated Expense tracker template in Excel (9)

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:

(Video) Create an Expense Tracker in Excel in 14 Minutes
  • 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.

Automated Expense tracker template in Excel (10)

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

Automated Expense tracker template in Excel (11)

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!

(Video) How to track expenses AUTOMATICALLY in Excel | BUDGET SPREADSHEET

FAQs

How do you make an expense tracker in Excel? ›

Create an Expense Tracker in Excel in 14 Minutes - YouTube

How do I create a daily expense sheet in Excel? ›

Using the Expense Report Template in Excel:
  1. For each expense, enter the date and description.
  2. Use the dropdown menus to select payment type and category for each expense.
  3. For each expense, enter the total cost.
  4. Attach all necessary receipts to the document.
  5. Submit for review and approval!

How do I keep track of income and expenses in Excel? ›

How to Create a Formula for Income & Expenses in Excel
  1. Open your income and expenses Excel worksheet.
  2. Select an empty cell beneath the last item in your "income" column.
  3. Type "Total Income" in this cell, then press the "Enter" key.
  4. Select the cell directly beneath the "Total Income" label.

How do I track my small business expenses in Excel? ›

If you want to track business expenses in Excel, you'll need to create a spreadsheet and fill in the appropriate information. The most important columns are likely to be “date,” “description,” “category,” and “amount.” You can also add additional columns if needed, like “vehicle number” for tracking car expenses.

How can I track my expenses automatically? ›

Best expense tracker apps
  1. Best overall free app: Mint.
  2. Best app for beginners: Goodbudget.
  3. Best app for serious budgeters: You Need a Budget (YNAB)
  4. Best app for small business owners: QuickBooks Online.
  5. Best app for business expenses: Expensify.

How do I create an expense tracker? ›

How to create a simple Expense Tracker
  1. Choose simple expense tracker software. ...
  2. Create your expense categories. ...
  3. Create your expense input sheet. ...
  4. Create your summary tab. ...
  5. Try Sheetgo's automated expense tracker template.

How do you keep track of expenses on a spreadsheet? ›

A spreadsheet that keeps track of expenses can serve as a ledger. Use the top row of each column for the categories you've defined. Use the far left-hand column for the date, and the column second to the left for the name of the vendor. Enter the amount of each expense in the column that corresponds to its category.

How do I create an expense tracker in sheets? ›

Select all cells in column E (in the Jan 2021 sheet), starting from cell Es3.
  1. Navigate to Data->Data Validation.
  2. Click on the dropdown next to Criteria.
  3. Select 'List from range' from the dropdown list.
  4. In the next input box, enter the range containing the category list. In our case, it is 'Expense / Budget Summary'!

How do small businesses keep track of expenses? ›

Here's how you can track your business expenses:
  1. Open a business bank account.
  2. Choose an appropriate accounting system.
  3. Choose cash or accrual accounting.
  4. Connect financial institutions.
  5. Begin managing receipts properly.
  6. Record all expenses promptly.
  7. Consider using an expense app.
May 18, 2022

How do you manage monthly expenses in Excel? ›

Creating a Family Budget with Excel - YouTube

How do I make a daily expense report? ›

In short, the steps to create an expense sheet are:
  1. Choose a template or expense-tracking software.
  2. Edit the columns and categories (such as rent or mileage) as needed.
  3. Add itemized expenses with costs.
  4. Add up the total.
  5. Attach or save your corresponding receipts.
  6. Print or email the report.

How do you record daily expenses? ›

Really simple way to record your daily expenses – just keep your receipts for the day, then add up each type of spending by category. Add in the total for that specific day, and move on with your life.

Videos

1. How to Track your expenses in Excel Spreadsheet in 2021
(Keene Letsholo)
2. How To Create This Excel Expense Tracker With Mobile Receipt Upload From Scratch [Free Download]
(Excel For Freelancers)
3. How To Create A Fully Automated Expense Tracker With PDF & Image Parsing & OCR In Excel + Download
(Excel For Freelancers)
4. Expense tracker template in Google Sheets [AUTOMATED DASHBOARD]
(Sheetgo)
5. Daily Expenses Tracker in Excel | Expenses Tracker Template in Excel
(Smart Office)
6. Automate your daily spending transactions and balances into Microsoft Excel
(Tiller)

Top Articles

Latest Posts

Article information

Author: Moshe Kshlerin

Last Updated: 12/08/2022

Views: 6185

Rating: 4.7 / 5 (77 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Moshe Kshlerin

Birthday: 1994-01-25

Address: Suite 609 315 Lupita Unions, Ronnieburgh, MI 62697

Phone: +2424755286529

Job: District Education Designer

Hobby: Yoga, Gunsmithing, Singing, 3D printing, Nordic skating, Soapmaking, Juggling

Introduction: My name is Moshe Kshlerin, I am a gleaming, attractive, outstanding, pleasant, delightful, outstanding, famous person who loves writing and wants to share my knowledge and understanding with you.