How to calculate shipping cost by weight in Excel

Step 1: Set up the rate table

You sell on Etsy or your own shop and want to know what each order really costs to ship. Copy your carrier's rate chart into a table: the starting weight of each band down column A (1, 2, 3, 5, 10, 20 lb) and the zones across row 1 (2 to 5). The numbers in this sample are made up; use your own carrier's.

Each row means "from this weight up to the next row". So the row for 5 covers 5 up to (but not including) 10 lb. Column A must be sorted smallest to largest, or the band lookups below give wrong answers with no error.

For a single zone, XLOOKUP with match mode -1 (exact or next smaller) is the simplest: =XLOOKUP(N2,$A$2:$A$7,$B$2:$B$7,,-1) finds the zone 2 rate for each billable weight: 8.20, 9.85, 15.75, 11.40, 9.10 and 24.90.

Step 2: Round up and add dimensional weight

Carriers bill whole pounds and most round any fraction up, so 2.3 lb is billed as 3. CEILING(x,1) rounds up to the next whole number. CEILING(x,0.5) would round to the next half pound if your carrier works that way.

Big, light boxes are billed by size. Dimensional weight is L x W x H in inches divided by the carrier's divisor (139 and 166 are common for inches and pounds, 5000 for centimetres and kilograms). Keep the divisor in an input cell, Q2, and bill whichever is higher, the real weight or the dim weight.

Order 1003 weighs 4 lb, but its 16 x 12 x 10 box has a dim weight of 13.81 lb, so it's billed at 14. That's the order that surprises people. Some carriers only apply dim weight above a certain box size, so check your carrier's rules.

Step 3: Two-way lookup by weight and zone

With several zones, you need a row (the weight band) and a column (the zone). INDEX picks the cell where they cross. The first MATCH uses 1 to find the band the billable weight falls into. The second uses 0 for an exact zone match.

In Excel 365 one formula fills the whole column: 8.20, 11.30, 17.20, 14.80, 9.10 and 31.50, a total of 92.10. In older Excel, use the single-row version (N2 and L2) and fill it down.

Order 1003 costs 17.20 at its billable 14 lb. At its real 4 lb it would have been 10.45. The 6.75 difference comes from the box size, which is the case for a smaller box.

Step 4: Free shipping and what the customer pays

If you offer free shipping over 75, the customer pays 0 when the order total is 75 or more. IF handles it: =IF(M2>=$Q$3,0,rate). Keep the threshold in Q3 so you can test 50 or 100 and see the effect.

Keep two numbers apart: what the carrier charges you (92.10 for these six orders) and what customers pay (60.10). Orders 1003 and 1004 ship free, but you still pay 17.20 and 14.80 for them. That gap is part of your cost per order.

Step 5: Weights the table doesn't cover

A weight below the first band has nothing to match, so XLOOKUP(0.6,...) returns #N/A. Rounding up with CEILING first avoids that, because 0.6 becomes 1. If a 0 or blank weight can sneak in, wrap the lookup in IFERROR(...,"Check weight").

Weights above the last band get the last band's rate. If your carrier charges differently over 20 lb, or you want to quote those by hand, add a check: =IF(N2>20,"Quote",rate).