Cellwise Start free

Excel guide · 5 min read

How to make a mileage log in Excel

Quick answerGive each trip a row: date, purpose, Business or Personal, start odometer and end odometer. Miles is =end-start, and a check column, =IF(D3=E2,"OK","Gap of "&D3-E2), shows any miles you forgot to log. Total business miles by month with SUMIFS on the type and the month's dates, then multiply by the per-mile rate in an input cell and round to cents.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1DatePurposeTypeStart odometerEnd odometerMilesCheckRate per mileMonth
2Oct 27, 2026Client visit, Lakeview DentalBusiness4821048252420.65Oct 1, 2026
3Oct 28, 2026School run and groceriesPersonal482524826816OKNov 1, 2026
4Oct 29, 2026Supplier pickup, GreenleafBusiness482684833163OK
5Oct 30, 2026GymPersonal48331483398OK
6Oct 31, 2026Site survey, Harbor GymBusiness483394838849OK
7Nov 2, 2026Bank and post officeBusiness483884840113OK
8Nov 3, 2026Client workshop, Northwind HotelsBusiness484314852089Gap of 30
9Nov 4, 2026DentistPersonal485204853313OK
10Nov 5, 2026Trade showBusiness4853348688155OK
11Nov 6, 2026Client visit, Lakeview DentalBusiness486884873042OK

Download this sheet as an Excel practice file (.xlsx)

Set up the columns

The log above covers two weeks of driving for a self-employed consultant. Each row is one trip: the date, what it was for, whether it was Business or Personal, and the odometer at the start and end. Miles (F) and the check (G) are formulas.

Log personal trips too, not only business ones. It's the only way the odometer readings join up from one row to the next, and that continuity is what makes the log easy to check. Write a purpose specific enough to mean something in a year's time ("Client visit, Lakeview Dental", not "Meeting").

  1. Select C2:C200, then Data > Data Validation. Under Allow pick List, type Business,Personal in Source and click OK, so SUMIFS never misses a "business" typed in lower case or with a trailing space.
  2. Select the whole log and press Ctrl+T to make it a table. New trips added at the bottom then pick up the formulas in F and G automatically.
  3. Put the rate per mile in J2 and give it a fill colour so it's clearly an input.

Miles and the odometer check

Miles is end minus start: =E2-D2. From the second trip on, column G compares each trip's start reading with the previous trip's end. If they match, nothing was missed. If they don't, the difference is miles you drove and didn't log.

In the sample, the 2 Nov trip ended at 48,401 but the next one started at 48,431. That's 30 miles unaccounted for: maybe a business trip worth claiming, maybe a personal one. Either way you now know to look, rather than finding out when someone asks.

=IF(D8=E7,"OK","Gap of "&D8-E7)

Result: Gap of 30

Gap of 30 on the 3 Nov row. Every other row shows OK.

How do I count the gaps?

Count the check cells that aren't "OK" or blank: =COUNTIF(G3:G11,"Gap*") returns 1 here. Add conditional formatting with the formula =LEFT($G2,3)="Gap" to colour the row.

Business miles by month with SUMIFS

For each month, put its first day in column L and add up the Business miles from that date to the end of the month. EOMONTH(L2,0)+1 is the first day of the next month, and "<" against it catches every trip in the month without worrying about 30 or 31 days.

=SUMIFS(F2:F11,C2:C11,"Business",A2:A11,">="&L3,A2:A11,"<"&EOMONTH(L3,0)+1)

Result: 299

299 business miles so far in November (13 + 89 + 155 + 42). The October row (L2) gives 154. In the summary the ranges are locked with $ so the formula fills down to as many months as you need.

What share of my driving was business?

Divide business miles by the total odometer distance, last end reading minus first start reading. Using the odometer rather than the sum of logged miles means unlogged gaps count against the share, not for it.

=SUMIFS(F2:F11,C2:C11,"Business")/(E11-D2)

Result: 0.87115384615 shows as 87.1% with Percent format

0.8712: 453 of the 520 miles on the odometer were business. The other 67 are 37 personal miles and the 30-mile gap.

The claim at your rate per mile

Multiply each month's business miles by the rate in J2 and round to cents: =ROUND(M3*$J$2,2). The $ keeps every month pointing at the same rate cell.

The 0.65 in J2 is a placeholder, not an official figure. The rate you can claim depends on your employer's policy or your country's rules, and official rates change, often once a year. Put the rate that applies to you in J2; if it changes mid-year, start a new summary table for the new period rather than changing the old one.

=ROUND(M3*J2,2)

Result: 194.35

194.35 for November's 299 business miles at 0.65. October's 154 miles come to 100.10.

Logging from a map instead: one-way miles and round trips

If you don't note the odometer, you can log trips by distance from a map. Then record the one-way distance and a Yes/No Round trip column, and double the trips where you came back. This is the only case where doubling applies: odometer readings already include the drive home.

The small table in P:T works that way. The airport drop-off was one way, so it counts once.

=IF(S4="Yes",2,1)*R4

Result: 63

63 miles: the 31.5-mile trip to Greenleaf Supplies and back. The Lakeview trip gives 42 and the one-way airport run 34.

Can I mix the two methods?

Keep them in separate tables and add the totals together. Mixing map distances into an odometer log breaks the continuity check on every row.

Common mistakes

Logging only business trips

Then every row shows a gap and the check is useless. Log every trip and mark it Business or Personal.

Doubling odometer-based trips

End minus start already includes the return drive. Only double trips logged as one-way distances.

Typing the rate into each formula

Keep it in one input cell. When the rate changes you edit one cell, and nobody mistakes a placeholder for an official number.

Filling in the log months later

A log written from memory is hard to trust. Add each trip the same day; a phone with the Excel app makes that quick.

FAQ

How do I calculate mileage in Excel?

Subtract the start odometer reading from the end reading, =E2-D2. For a claim, total the business miles with SUMIFS and multiply by your rate per mile. If you haul freight, Excel for trucking turns the same miles into cost per mile, rate per loaded and total mile, and profit per load.

Is there a free mileage log template for Excel?

Yes, the Cellwise mileage log template has odometer columns, monthly totals and an empty rate cell for the rate that applies to you. Build your own as above if you want the continuity check and business share.

How do I total mileage by month in Excel?

Put the first day of each month in a column and use SUMIFS with dates from that day to EOMONTH(day,0)+1, plus a Business condition if you log personal trips too.

What should a mileage log include?

At least the date, the purpose, the start and end point or odometer readings, and the miles for each trip. Check your employer's policy or your local rules for anything else they require.

Functions used

Related guides

Updated