Excel Vehicle Mileage Log: Setup & Formulas

Formulas & functions · Updated November 22, 2026

You can create a vehicle mileage log in Excel by setting up a table with columns for Date, Purpose, Start and End Odometer, and Miles Driven, then using a simple formula like =D2-C2 to auto-calculate trip miles. This gives you a permanent, sortable, and filterable record that you can adapt for IRS business mileage deductions, client billing, or personal vehicle maintenance tracking.

What a Mileage Log Actually Needs

A useful mileage log goes beyond just recording miles. The IRS, for example, requires specific information if you plan to deduct business mileage: the date of each trip, the destination or purpose, the starting and ending odometer readings, and the total miles. Even if you are not tracking for taxes, adding a few columns makes the log far more valuable for maintenance scheduling or reimbursement.

  • Date – the day of the trip
  • Purpose – business, personal, medical, or charitable
  • Start Odometer – reading at trip start
  • End Odometer – reading at trip end
  • Miles – calculated difference
  • Notes – client name, destination, or reason

You can add more columns like tolls, parking, or vehicle ID if you track multiple cars. The key is to keep the structure simple enough that entering a new trip takes under ten seconds.

Setting Up the Spreadsheet Step by Step

Open a new Excel workbook. In row 1, enter the column headers: Date, Purpose, Start Odometer, End Odometer, Miles, Notes. Format the Date column as a date (Home > Number Format > Short Date). For the odometer columns, use a number format with no decimals.

  1. Click on cell E2 (the Miles column for the first data row).
  2. Type the formula =D2-C2 and press Enter.
  3. Select E2, then drag the fill handle (the small square at the bottom-right corner) down to row 100 or more. This copies the formula, so future rows will auto-calculate.
  4. To protect the formula from accidental edits, select the Miles column, go to Home > Format Cells > Protection, check “Locked,” then go to Review > Protect Sheet. But only do this after you have finished entering the formula.

If you are using Excel for the web or Mac, the same steps work; the fill handle is visible in both. On a Mac, the ribbon is the same but the shortcut to copy down is Cmd+D instead of Ctrl+D.

Adding Totals and Running Sums

A single total at the bottom is often enough. In cell E102 (assuming your data goes to row 100), enter =SUM(E2:E100). This gives you total miles for the period. For a running total that updates as you add rows, use a structured table instead of a plain range.

Select your data range (A1:F100) and press Ctrl+T (or Cmd+T on Mac) to convert it to an Excel Table. Now you can add a Total Row from the Table Design tab. The total row will automatically show the sum of the Miles column, and it updates as you add new rows. If you want a running cumulative column, add a new column header “Cumulative” and in G2 enter =SUM($E$2:E2), then drag down. The dollar signs lock the first cell so the range expands as you go down.

Using PivotTables for Monthly or Purpose Summaries

Once you have a few months of data, a PivotTable gives you instant summaries without rewriting formulas. Select your table, go to Insert > PivotTable, and place it on a new worksheet. Drag “Date” to the Rows area, “Miles” to the Values area, and “Purpose” to the Columns area. Excel will group dates by month automatically if you right-click a date in the PivotTable and choose “Group” > “Months.”

This shows you total business miles per month, or personal vs. business splits. For a quick filter, add a Slicer (Insert > Slicer) for the Purpose column, letting you click to see only business trips. This is far more flexible than a static SUMIF formula, though =SUMIFS is also an option if you prefer a formula-based approach.

Common Mistakes and How to Avoid Them

The biggest error is entering odometer readings that are not sequential. If you forget to record the end reading of one trip and use the next trip’s start, your miles will be wrong. Always double-check that the end odometer of trip N equals the start odometer of trip N+1 if they are the same vehicle and no driving happened in between.

Another frequent issue is formatting the odometer as text. If you type a comma or a space (like 12,345), Excel treats it as text and the subtraction formula fails. Use plain numbers without separators. Also, be careful with the formula if you have blank rows; =D2-C2 will return 0 for blank rows, which can skew totals. Use =IF(OR(C2="",D2=""),"",D2-C2) to leave the cell blank until both readings are entered.

Lastly, do not overwrite the formula column with manual numbers. If you need to correct a mistake, edit the odometer readings, not the miles value.

Comparing Excel to Dedicated Mileage Apps

Excel is free (if you already have Office), fully customizable, and works offline. Apps like MileIQ or Everlance automatically track trips via GPS, which saves manual entry but costs a subscription and may raise privacy concerns. Excel requires you to enter each trip manually, but you have complete control over the data and no third-party server stores your location history.

If you drive dozens of trips daily, an app is more practical. If you have a handful of trips per week, Excel is simpler and gives you the same IRS-compliant record as long as you record the required fields. You can also export data from many apps to Excel, so you can start with an app and switch later.

Costs, Templates, and Free Alternatives

Excel itself is part of Microsoft 365 (about $10/month) or you can use the free Excel for the web at office.com. There are many free mileage log templates built into Excel: go to File > New and search for “mileage log.” These templates already have the formulas and formatting, saving you setup time. They are safe to use because they are from Microsoft, but always check the formulas match your needs.

If you do not have Excel, Google Sheets offers a similar experience with the same formulas (except the table total row works differently). LibreOffice Calc is another free desktop option. The core logic—subtracting odometer readings and summing—works identically in all of them.

Frequently Asked Questions

Does the IRS accept an Excel mileage log? Yes, the IRS does not require a specific format. It requires contemporaneous records (written near the time of the trip) showing date, mileage, destination, and business purpose. An Excel sheet with those columns qualifies, provided you keep it updated. Keep a backup copy.

How do I calculate reimbursement with a mileage log? Multiply total business miles by the IRS standard mileage rate (for 2024, that is 67 cents per mile). In Excel, if your business miles are in E2:E100, use =SUM(E2:E100)*0.67. Check the current rate on irs.gov, as it changes annually.

Can I use a mileage log for multiple vehicles? Yes, add a “Vehicle” column and enter the plate or name. Then use a PivotTable or =SUMIFS to sum miles per vehicle. For example, =SUMIFS(E2:E100,A2:A100,"Car1") if the Vehicle column is A.

What if I forget to record a trip? You can add it later, but the IRS prefers contemporaneous records. If you are audited, a log with gaps may be questioned. Best practice is to enter trips weekly, using a calendar or credit card statements to jog your memory.

How do I protect my mileage log from accidental changes? Use Review > Protect Sheet, but leave the input columns (Date, Purpose, Odometer) unlocked so you can still add trips. Select those cells, go to Format Cells > Protection, uncheck “Locked,” then protect the sheet. This prevents formula edits while allowing data entry.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.