How to Rebuild an Excel Rate Sheet as a TMS Rate Grid

A carrier rate sheet is not a database export. It is a document laid out to be read by a pricing manager who already knows the carrier's conventions: which cells are per 100 kg, which zone the asterisk refers to, that Corsica is quoted separately, that the sheet replaced an earlier one in January. Every one of those conventions lives outside the cells. A structured rate grid holds only the cells.

That is why rebuilding a rate sheet fails silently. You key the cells in, the grid saves, the engine returns a number, and the number is plausible. Nothing throws an error. The defect surfaces four weeks later as a carrier invoice above the price you quoted, on a shipment you have already delivered and already billed.

Six defect classes account for almost all of it. They are worth naming, because each has a different signature at verification time.

The sections below take each defect in turn, then close with the control procedure. This guide assumes you already know which of the four grid models you are mapping onto — if not, start with the four types of rate grids and the field-by-field walkthrough in turning an Excel rate sheet into instant quotes. The rest of the category sits under rate grids and pricing.

Excel merging is a display instruction, not a data structure. When a carrier merges four rows to write Zone 1 once, the value is stored in the first cell and the three below it are genuinely empty. A human reads the block as one thing; row by row there are three rows with no zone. Those rows either get skipped or get keyed against whatever zone came last, which is worse — the rates are real, they are simply on the wrong line.

Merged cells travel with company. The same workbooks usually carry four other layout defects:

• Numbers stored as text. A cell containing 17,90 € is a string, not a number. It reads as zero, or as nothing at all. Left-aligned numbers in a column of right-aligned ones is the tell.
• Repeated header bands. Sheets built for printing repeat the bracket header every 30 rows. Each repeat looks like a data row with text where a rate should be.
• Hidden rows and columns. A hidden column is still exported. A superseded zone hidden rather than deleted will be re-keyed unless you look for it.
• Footnotes inside the data range. "* islands quoted on request" written in the cell below the last zone becomes a phantom zone with no rates.

The two-minute check that finds all of them

Select the rectangle of rate cells and read the status bar: the Count of numeric cells must equal rows × columns exactly. A 12-zone by 8-bracket block must report 96. Anything less means blanks, text or merges inside the block. Then save the tab as CSV UTF-8 and open the CSV in a text editor. The CSV is the tariff stripped of its layout — no merges, no colours, no currency formatting. If a line reads Zone 3,,,,,,, you have found your merge; if it reads Zone 3;"1 245,00"; you have found a decimal comma and a thousands separator that will need declaring.

Fix the file, not the grid afterwards. A cleaned tariff file is an asset you re-use at every repricing; a workaround built around a broken layout has to be rediscovered next January.

Carriers describe zones in prose because their own pricing team resolves the prose from memory. The recurring formulations are worth learning to spot, because each one hides a different rule:

• "Rest of country" — a real zone with a real rate. Create it, and make it the fallback only for destinations inside that country.
• "except 01100–01130" — an exception carved out of a larger range. The carved-out postcodes belong to another zone that is named somewhere else in the file, often in a footnote.
• "same as zone 2" — an alias. Duplicate the rates rather than pointing at zone 2, because the two will diverge at the next repricing.
• "on request" / "nous consulter" — not a zone. Leave the cell empty. The engine must return no rate and route the request to a manual quote.
• "islands and offshore excluded" — a coverage limit, not a price. It belongs in the carrier constraints, not in the zone map.

The coverage audit

Once the zone map is explicit, test it against reality rather than against the file. Export the destination postcodes of every shipment you moved in the last twelve months, deduplicate them, and run the list through the zone map you built. Three numbers come out: how many resolve to a zone, how many resolve to two zones, and how many resolve to none. The middle number is the dangerous one — a destination matching two zones means the map has an overlap, and a lookup that matches twice will pick one without telling you.

Decide the key granularity once and hold it for the whole grid: two-digit department, three-digit postcode prefix, or full postcode. Mixing levels inside one grid makes the coverage audit meaningless, because a two-digit entry and a five-digit entry will both match the same address.

Unmatched destinations must fail, not degrade. In Cargavo, a TMS for freight brokers and transport commissionaires, an unresolved destination returns no_zone and the request continues as a manual quote — the path described in handling a quote request with no rate for the lane. A refusal you can see beats a plausible price you cannot check.

Bracket labels are printed for a reader who will never send a shipment weighing exactly 100 kg. Your engine will, several times a month, because chargeable weight is a computed figure that lands on round numbers far more often than actual weight does — rounding rules push it there.

Read the printed labels in pairs and classify each boundary:

The convention that survives contact with a pricing engine is a single ascending list of upper bounds, with the rule that a weight falls into the first bound it does not exceed. Under that rule 100.0 kg belongs to the 100 bracket and 100.1 kg to the next one. Carriers assume exactly this and never write it down, which is why the "0–100 / 100–200" layout is so common and so harmless on paper.

Two boundaries nobody checks

The bottom: below the first bracket, the minimum charge applies, and the minimum is usually printed once per zone in a corner of the sheet rather than in the table. A grid built without it underprices every small shipment, and small shipments are where a broker's margin percentage is highest.

The top: a sheet ending at "2 000 kg and above" is one thing; a sheet whose last column is "1 500–2 000 kg" with nothing after it is another. In the second case the carrier has not priced above 2 000 kg, and extrapolating the last rate invents a price the carrier never agreed to. The correct behaviour is to return out_of_range and quote that shipment manually. Cargavo, a TMS for freight brokers, treats the top of the last bracket as a hard limit for that reason.

Units are the defect with the largest blast radius, because a unit error is a multiplication, not an offset. Take a zone priced at 12.90 and a shipment of 1 000 chargeable kilograms:

• Read as per 100 kg: 10 × 12.90 = 129.00 €
• Read as per kg: 1 000 × 12.90 = 12 900.00 €
• Read as a flat bracket price: 12.90 €

Three readings, three orders of magnitude, one cell. The magnitude sanity check settles it in seconds: a European groupage rate per 100 kg is a two-digit number that falls as the bracket rises. A column of values dropping from 28.40 to 11.20 is per 100 kg. A column of values rising from 62.00 to 224.00 is a flat price per bracket.

Then the units that are not printed in the rate table at all, because they live in the carrier's general conditions: kg or lb (a CWT tariff prices per 100 lb, not per 100 kg), the volumetric ratio in kilograms per cubic metre, and the LDM floor in kilograms per linear metre. These belong to the grid, not to your software: a carrier using 1 500 kg per LDM and one using 1 750 kg per LDM must coexist without either being a global setting. The full treatment is in linear metre (LDM) pricing explained.

Currency is a property of the grid

A Polish carrier quoting in PLN, a Swiss one in CHF and a British one in GBP will all send you cells containing bare numbers. A grid saved without its currency, silently assumed to be EUR, prices every lane wrong by the exchange rate — and the error is invisible, because the numbers look like plausible euro amounts.

Cargavo, a TMS for freight brokers, stores the currency on the grid across EUR, USD, GBP, CHF, CAD, MAD and PLN, and refuses a mismatch between the grid currency and the customer currency with invalid_grid rather than converting at an undocumented rate. A conversion baked in on the day you keyed the grid is a rate frozen in the past; six months later the margin you thought you set is not the margin you are taking. Margin arithmetic is covered in margin-safe freight quoting.

Carrier rate sheets arrive as attachments named Tarif 2026 VF.xlsx, then Tarif 2026 VF definitif.xlsx, then Tarif 2026 VF definitif (2).xlsx. None of them carries a machine-readable effective date, and the version you keyed in is whichever one was in the folder on the day you did the work.

That is survivable until something goes wrong, at which point you need to answer three questions and cannot:

• Which rate table produced the price on quote Q-YYYY-NNNN, sent eleven weeks ago?
• Was the shipment S-YYYY-NNNN priced before or after the April increase?
• Which of my open quotes are now below the carrier's new buy rate?

Storing validFrom and validUntil on the grid answers all three, and it costs one line of data entry per tariff version. Two working rules make it useful rather than decorative.

Never overwrite, always supersede

When a carrier reprices, enter the new tariff as a new grid with its own validity window and close the old one — do not edit the cells of the existing grid. Overwriting destroys the only record of what your buy price was on the day you quoted, which is exactly the record you need when a customer disputes an invoice six months later. Storage is free; reconstructing a superseded tariff from an email thread is not.

Watch the gap between quote validity and tariff validity

A quote sent with 30 days' validity against a tariff expiring in 10 days is a 20-day exposure you agreed to without noticing. Either shorten the quote validity as the tariff approaches its end date, or accept the exposure deliberately and price it. Retroactive repricing makes this sharper: carriers routinely send a new sheet in March effective 1 January, which means shipments already delivered and invoiced were priced on rates that no longer exist. When that happens, list the affected shipments, recompute them on the new grid, and decide once whether you absorb the difference or rebill — do not discover it invoice by invoice. The maintenance rhythm for a whole carrier portfolio is in carrier rate grid management.

Testing a new grid against random shipments proves nothing, because most shipments exercise the same two rules. Choose five deliberately, so that each one can only pass if a specific rule is right:

Test 3, end to end

Three non-stackable pallets of 1.20 × 0.80 × 1.35 m at 210 kg each, to zone 2. Grid: 12.90 € per 100 kg in the 1 000–1 999 kg bracket, LDM floor 1 500 kg per linear metre, volumetric ratio 333 kg per cubic metre, fuel surcharge 9.2 %, tail lift 28.00 €.

The carrier invoice for that shipment reads 281.56 €. If the grid returns 211.13 €, the LDM floor was not declared: the engine priced the volumetric weight of 1 300 kg instead of 1 800 kg, giving 13 × 12.90 € = 167.70 € of transport plus 15.43 € of fuel and the same 28.00 € tail lift. The gap is 70.43 €, or 25.0 % — and it would have been invisible on a dense shipment, which is precisely why test 3 exists.

Read the shape of the gap

Compare lines, never totals: two errors that cancel on one shipment will stop cancelling on the next. And be clear about what this is not. Cargavo, a TMS for freight brokers, holds the tariff the carrier already sent you, keyed in as a structured grid: you pick the model, declare the zones and brackets and enter the cells. There is no file to upload, no carrier API and no EDI link, so a repriced tariff is re-entered the same way. It is not a load board, not a tendering marketplace and not a fleet or dispatch system. Plan limits and grid counts are on the pricing page.