Weekly driver settlement in Excel: a step-by-step guide

Want a ready-made workbook? Download the free driver settlement template — it implements every step below.

A driver settlement answers one question per driver: what do we owe them for this period? In practice that's freight earned on trips, minus fuel advances, minus other deductions — but the inputs come from three or four files that were never designed to be combined. Here's a method that works in Excel, and the five places it usually goes wrong.

What you need

Trip register (from your TMS or a sheet): trip number, date, driver code, freight or driver pay.
Fuel card statement(s): transaction date, card or vehicle, trip/load reference if your drivers enter one, litres, amount.
Deductions list: cash advances, tolls paid by the company, damage recoveries, uniforms — with driver code and amount.
Optional: the customer's shipment report, to check that what you pay the driver for was actually billed.

Step 1 — Put each file on its own sheet and build a clean key

Different systems write the same trip differently: T-1001, t1001, T 1001. Before any lookup, add a helper column in every sheet that normalises the reference:

=UPPER(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"-","")," ",""))

Do the same for driver codes. Lookups on raw values are the single biggest source of "missing" fuel fills that aren't really missing.

Step 2 — Remove duplicate trips before totalling

Trips entered twice are common when two people update the register. Add a check column on the trip sheet:

=COUNTIF($F$2:$F$500, F2)>1

Filter on TRUE, decide which copy is real, and remove the other. Never let duplicates flow into SUMIFS — the driver gets paid twice and nobody notices until a dispute.

Step 3 — Attribute fuel fills to drivers

Fuel statements usually identify a card or vehicle, not a driver. If drivers enter the trip number at the pump, look the driver up through the trip:

=XLOOKUP(G2, Trips!$F:$F, Trips!$C:$C, "NO TRIP")

Anything returning NO TRIP needs a human: a typo, a fill on another vehicle, or a card used outside a trip. One trip can have several fills — that's fine, SUMIFS will add them.

Step 4 — Total per driver and compute net pay

On a settlement sheet with one row per driver code:

Freight     =SUMIFS(Trips!E:E, Trips!C:C, A2)
Fuel        =SUMIFS(Fuel!F:F, Fuel!H:H, A2)
Deductions  =SUMIFS(Deductions!D:D, Deductions!A:A, A2)
Net payable =B2-C2-D2

Then reconcile the totals: the sum of the Freight column should equal the trip register total (after removing duplicates), and the same for fuel and deductions. If they don't, something didn't attribute.

Step 5 — Cross-check against what the customer was billed

If customers send shipment or remittance reports, match them to trips on the same clean key and compare amounts. Trips missing from the customer's report are revenue at risk; amount differences are short payments you should raise before paying the driver in full.

The five errors behind most settlement disputes

IDs formatted differently between files, so lookups silently miss rows.
Duplicate trips or deductions counted twice.
Dates read the wrong way round — 03/04 becomes 4 March instead of 3 April when a CSV is opened with US settings, moving a fill into the wrong week.
Amounts stored as text ("₹4,250.00", N/A) that SUMIFS quietly ignores.
Overwritten formulas and no trail — when a driver queries a number weeks later, nobody can show where it came from.

When to stop doing it by hand

The Excel method is fine for a handful of drivers. Once you have more than ~15 drivers, several fuel vendors, or customers sending their own reports, the weekly rebuild starts taking most of a day and the error rate climbs. Reconza runs exactly these steps automatically — key cleaning, duplicate removal, fuel attribution, customer cross-checks — and produces a statement per driver with every exception explained. You can also try the core matching for free with the Excel comparison tool.