ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Create a Mileage Claim Form in Excel

Updated on September 22, 2014

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

Comments

    0 of 8192 characters used
    Post Comment

    • Rasimo profile image

      Rasimo 

      4 years ago

      These are great tips! Thank you for the article

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)