Create a Mileage Claim Form in Excel

Although Excel’s greatest strength is its ability to analyse huge amounts of data, it is also great for creating forms. We can create formulas, apply validation rules and use techniques to make our forms user friendly, secure and efficient in Excel.

This article illustrates how to create a mileage claim form in Excel. Let’s get started.

You can download the mileage claim form to check out the techniques used in this article.

Mileage claim form Excel template
Mileage claim form Excel template

Lookup the Mileage

If the distance in miles to the destination is recorded somewhere in Excel, we can use a lookup to automate the mileage entry in the form.

In range D6:D10 of the mileage claim form, a Vlookup function is used to return the distance in miles of the office entered in cell C6:C10.

An IF function has been used to control when the Vlookup function will run. The Vlookup function will only run when an office has been entered, otherwise the cell in column D stays empty.

Find more on the Vlookup function in Excel.

Looking up the mileage for an office
Looking up the mileage for an office

Validate the Office Entry

The Data Validation feature of Excel is used to provide a list of offices for the user to select from when completing the form. It also validates the entry ensuring that an office cannot be entered incorrectly.

Firstly a range name needs to be created so that the Data Validation tool can access the list on the Offices sheet.

Create a Range Name

  1. Select the range A2:A6 on the Office sheet
  2. Click inside the Name Box situated to the left of the formula bar
  3. Type Offices as the name you want to use for the range
  4. Press the Enter key

Create a Data Validation List

  1. Select range D6:D10 on the Mileage Claim Form sheet
  2. Click the Data tab on the Ribbon
  3. Click the Data Validation button in the Data Tools group
  4. Click the Allow list arrow on the Settings tab and select List
  5. Type =Offices in the Source box and click Ok

Data Validation has also been used to create a list in cells E6:E10 for the user to select whether they used their own or a company car.


Create a Data Validation List of offices
Create a Data Validation List of offices

Calculate the Expense

To calculate the expense, we need to first check if an office has been selected and if they are using their own or the company car has been selected.

If these tasks have been done then we shall apply a formula to calculate the expense depending on whether it was their own, or the company car used.

The formula below is entered into cell F6 and copied down. The OR function has been used to test two cells. Two IF functions are used; one to control when to perform the calculation, the other to decide what calculation to perform (own car or company car).

Calculate the mileage expense
Calculate the mileage expense

Hide and Protect the Offices Sheet

To prevent users from accessing the mileage table and potentially affecting the figures, the sheet can be hidden.

  1. Right mouse click on the Offices sheet
  2. Select Hide from the shortcut menu

Hide the worksheet
Hide the worksheet

Although the sheet has been hidden, it has not been protected. At the moment, anybody could unhide the sheet if they wish to.

To prevent people accessing the hidden sheet, you need to protect the workbook.

  1. Click the Review tab of the Ribbon
  2. Click the Protect Workbook button in the Changes group
  3. Ensure that the Structure box is checked, enter a password and click Ok

Protect the workbook to stop users unhiding sheets
Protect the workbook to stop users unhiding sheets

Protect the Formulas

Worksheet protection can be used to protect the formulas on the workbook. Accidental changes made to our formulas will destroy the sheets functionality.

Before we protect the worksheet, the cells which a user will need to change will be unlocked. All cells on a worksheet are locked by default so if we protected it without unlocking the cells, the sheet would be read only.

  1. Select cell C3
  2. Click the Home tab on the Ribbon
  3. Click the Format button in the Cells group and click Lock Cell
  4. Repeat these steps for cells E3, B6:C10 and E6:E10.

By protecting the worksheet it will also function more like a form with the user able to tab between the unlocked cells from C3 to E3 and then to B6 and so on.

To protect the worksheet;

  1. Click the Review tab on the Ribbon
  2. Click the Protect Sheet button in the Changes group
  3. Enter a password, choose what features a user can use by checking the boxes and click Ok

Lock formulas by protecting the worksheet
Lock formulas by protecting the worksheet

More by this Author

  • Create an Interactive Excel Chart using Option Buttons
    6

    Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...

  • Simulate a Cup Draw using Excel
    0

    Microsoft Excel can do some amazing things. I like to attempt little projects testing what Excel can do. I was asked the other day to simulate a cup draw using Excel and this is the result. This article will give you...

  • Brock Lesnar Nutrition and Workouts
    2

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...


Comments 1 comment

Rasimo profile image

Rasimo 2 years ago

These are great tips! Thank you for the article

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working