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.
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.
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
- Select the range A2:A6 on the Office sheet
- Click inside the Name Box situated to the left of the formula bar
- Type Offices as the name you want to use for the range
- Press the Enter key
Create a Data Validation List
- Select range D6:D10 on the Mileage Claim Form sheet
- Click the Data tab on the Ribbon
- Click the Data Validation button in the Data Tools group
- Click the Allow list arrow on the Settings tab and select List
- 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.
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).
Hide and Protect the Offices Sheet
To prevent users from accessing the mileage table and potentially affecting the figures, the sheet can be hidden.
- Right mouse click on the Offices sheet
- Select Hide from the shortcut menu
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.
- Click the Review tab of the Ribbon
- Click the Protect Workbook button in the Changes group
- Ensure that the Structure box is checked, enter a password and click Ok
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.
- Select cell C3
- Click the Home tab on the Ribbon
- Click the Format button in the Cells group and click Lock Cell
- 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;
- Click the Review tab on the Ribbon
- Click the Protect Sheet button in the Changes group
- Enter a password, choose what features a user can use by checking the boxes and click Ok