Automatically Track Your Expenses With Excel Online and OneDrive.

Automatically Track Your Expenses With Excel Online and OneDrive.

Many businesses have a streamlined process for managing invoices and bills, but recording irregular expenses such as train tickets and store receipts can be a whole different matter. 

For busy employees, recording their spending as it’s happening can feel impossible. When it’s the end of the month and their expense reports are due, your employees may then waste hours hunting through pockets, bags and inboxes for missing and misplaced receipts. Your HR department may also waste time chasing employees for missing reports – only to then receive hastily put-together spreadsheets full of holes and mismatched receipts. 

In this article, I’ll show you how to streamline the expenses process for everyone involved. 

We’ll be using Microsoft Flow to build a form where your employees can quickly and easily report each expense on their smartphone or tablet, and can even take photos of receipts, invoices and tickets, using their device’s camera. Microsoft Flow will then automatically log all of this information in an Excel spreadsheet, creating a “living” record of the employee’s spending. 

At the end of the month, your HR department just need to grab a copy of this spreadsheet, and they’ll have all the information they need, including photographic evidence of that employee’s spending. 

What we’ll be building

In this blog, we’ll be creating the following: 

  • A Flow for Mobile button that launches a form. The employee can then enter all the information about this expense request, and attach a photo of the receipt, invoice, ticket, or any other documentation that supports this request. 
  • A OneDrive folder where any photographic evidence will be stored.
  • An Excel spreadsheet that records information about each expense request, including a link to any photographic evidence that’s stored in OneDrive.

Create your Excel Online Business spreadsheet

Let’s start by creating our Excel spreadsheet. 

Every time the user submits an expense request via the Flow for Mobile app, all of their information will be recorded in this spreadsheet, including a link to that employee’s photographic evidence, where available. 

  1. Sign into your Office 365 account
  2. Select the “App launcher” icon in the upper-left corner of your web browser. 
  3. Select “All apps.” 
  4. Choose “Excel.” 
  5. Select “New blank workbook.” 
  6. Once this workbook has been created, click to place your cursor inside the title field and give this document a descriptive title; I’m using “Expenses.” 

  1. Give your columns the following headers: Timestamp, Amount, Category, ImageLink, and Address. 
  2. Now, we need to format this workbook as a table. To start, highlight all the columns that you want to add to your table (Timestamp, Amount, Category, ImageLink, and Address). 
  3. Select “Format as Table” from Excel’s toolbar. 
  1. In the subsequent popup, make sure that Excel is adding the correct columns to your table. 
  2. Select “My table has headers,” and then click “OK.” 

Excel will save your spreadsheet automatically, so this spreadsheet is now ready to use. 

Storing photos in OneDrive for Business

Next, I’m going to create a folder in OneDrive where we’ll store all the employee’s photographic evidence, such as receipts, tickets, and invoices. 

Once we’ve created our workflow, Microsoft Flow will copy the link to each photograph and paste it into the correct cell within our Excel spreadsheet. 

  1. In your Office 365 account, select the “App launcher” icon in the upper-left corner. 
  2. Select the “OneDrive” application. 
  3. In your OneDrive account, click “+ New” followed by “Folder.” 
  1. Give this folder a distinctive name; I’m using “Receipts.” 
  2. Click “Create.”

Microsoft Flow: Creating our expense reporting workflow

Now, we need to create the workflow that’ll present a simple form to the employee, and then record their responses in the correct section of our Excel spreadsheet. If the employee provides a photo, then this workflow will also upload that photo to OneDrive and copy/paste its link into our Excel spreadsheet. 

The easiest way to create this workflow, is to use one of Microsoft Flow’s ready-made templates: 

  1. In your Office 365 account, select the “App launcher” icon in the upper-left corner. 
  2. Select the “Flow” application. 
  3. In Microsoft Flow’s left-hand menu, select “Templates.” 
  4. Search for the “Track my expenses in an Excel Online (Business) spreadsheet” template, and select it when it appears. 
  5. The initial part of this workflow is completed for us, so scroll to “If yes: Add Expense Item Row No Receipt” as this is where we’ll need to enter the details of the table we just created: 

  1. Open the “Table” dropdown and select the table that you just created. 
  2. Open the “Location” field and select “OneDrive for Business.” 
  3. Open the “Document Library” dropdown and select “OneDrive.” 
  4. Find the “File” field, and click its accompanying “Folder” icon. 
  5. Select your “Expenses” workbook from the subsequent popup. 
  1. Next, scroll to the “If no: Add Expense Item Row” section, and repeat the previous steps: 
    1. Open the “Location” field and select “OneDrive for Business.” 
    2. Open the “Document Library” dropdown and select “OneDrive.” 
    3. Find the “File” field, and click its accompanying “Folder” icon. When the option appears, select your “Expenses” workbook. 
    4. Open the “Table” dropdown and select the table that you just created. 
  2. Now, we need to tell Microsoft Flow where it should store all photographic evidence provided by the employee: 
    1. Find the “Create file” section and click to expand. 
    2. Find the “Folder path” section, and click its accompanying “Folder” icon. 
    3. Select “/Root” from the dropdown, followed by the name of the folder you just created, which in my instance is “/Receipts.” 

There’s lots of additional settings that make up this workflow, so you may want to spend some time customizing this template by exploring its various dropdowns and text fields. However, we’ve done enough to get this workflow up and running, so let’s hit “Save” and put Microsoft Flow to the test! 

Recording your expenses using Flow, Excel and OneDrive

This workflow is designed to be triggered manually through the Microsoft Flow for mobile app. If you don’t already have this app setup, then you can download it for Android or iOS, and then sign in using your existing Office 365 username and password. 

Once you’ve setup the Microsoft Flow app, you can trigger this workflow at any point: 

  1. Launch the Microsoft Flow for Mobile app. 
  2. Select the “Buttons” tab. 
  3. Find the “Track my expenses” button and give it a tap. This launches a form where you can enter all the information about your expense request. 
  1. In the “Amount” field, enter the amount of money you’re requesting. 
  2. Give “Category” a tap and then choose the appropriate category from the dropdown. 
  3. Select “Receipt image.” At this point, you can upload a photo from your device’s library, or you can take a photo using your device’s camera. 
  4. Once you’re happy with the information you’ve entered, tap “Done.” 

All of this information will now be logged in your Excel for Business spreadsheet, and the photo will be uploaded to your OneDrive account. If you head over to the Excel spreadsheet, then you’ll see that your request has been added as a new row. 

If you copy/paste the ImageLink into your web browser, then it’ll load the photo you provided as part of your expense request. Alternatively, if you navigate to your OneDrive account and open the “Receipts” folder, then you’ll see that your photo has been added as a new file. 

Interested in using more methods to automate your work flow? Take a look at our tutorial on how to send a working from home email automatically with Microsoft Flow. 

Claim Your One Month FREE Trial of Microsoft 365 E5 Today.

Speak to a member of our team today 0114 292 2911 or email sales@systemsassurance.com if you need any assistance.

Share this article on social media

If you found this article useful, please share it on social media. 

Subscribe to our blog…

We will only use your email to send you new blog posts.