How to calculate fuel cost and mileage in Excel

Step 1: A trip log from odometer readings

You drive for work a few times a week and need to claim the miles, or you just want to know what the car really costs. Log each trip with the date, Work or Personal, and the odometer at the start and the end. Miles is =D2-C2.

Odometer readings beat guessing distances from a map. They catch detours and the drive to lunch, and they let you spot gaps. If a trip's start reading doesn't match the end of the one before, some miles went unlogged.

In Excel 365, =D2:D9-C2:C9 fills the whole Miles column at once: 52, 39, 146, 46, 115, 77, 68 and 41, which totals 584.

Step 2: Fuel cost per trip and cost per mile

Keep the fuel price and your car's MPG in their own cells (H2 and H3) and point every formula at them with $. When the price changes, you change one cell.

Gallons used is miles / MPG, and cost is gallons x price. The 146-mile trip at 31 MPG and 3.29 per gallon uses about 4.7 gallons and costs 15.49. Wrap it in ROUND(...,2) if the result goes on a claim form.

Cost per mile is just price / MPG: =$H$2/$H$3 gives about 0.106, so roughly 11 cents of fuel per mile. That covers fuel only, not wear, tires or insurance.

Step 3: Totals by month and by purpose

SUMIFS adds the Miles column with conditions. A month is "on or after the 1st and before the 1st of the next month". That pattern catches every date in October, whatever day it ends on. October has 475 miles, 321 of them for work.

Work miles for the whole log are =SUMIF(B2:B9,"Work",E2:E9): 389, or 66.6% of all driving. Multiply by the rate in H4 for the amount to claim: 389 x 0.50 = 194.50. The 0.50 is only a placeholder; use the rate your employer or reimbursement policy sets.

For October's fuel cost, divide the month's miles by MPG and multiply by the price: about 50.41.

Step 4: Your real MPG from fill-ups

The MPG on the sticker or the dashboard is a guess. Your real number comes from fill-ups. Fill the tank to full each time, note the odometer and the gallons, and divide the miles since the last fill-up by the gallons you just put in.

The gallons go with the second fill-up, not the first. That's the fuel that refilled what you burned since the last stop. Using the wrong row, =(K3-K2)/L2, gives 33.4 MPG instead of 31.1.

One tank can be off if the pump clicked early. Average over several tanks with =(K4-K2)/SUM(L3:L4): 30.7 MPG across the last two. Put that number in H3 and the trip costs get more accurate.

Kilometres and litres instead

Outside the US, fuel economy is usually litres per 100 km, where lower is better. With an odometer in km and litres at the pump, it's =100*litres/km. The fuel cost formula becomes km x L/100km / 100 x price per litre.

To convert, 235.215 divided by US MPG gives L/100 km. 31 MPG is about 7.6 L/100 km, and converting the Oct 12 fill-up from gallons and miles to litres and kilometres gives the same 7.6. The 235.215 figure is for US gallons; UK MPG uses a larger gallon, so use 282.481 there.