Pivot tables for freight cost by lane and month

A pivot table turns a long list of shipments into a clear summary in under a minute. If you only learn one Excel feature for reporting, make it this one.

Prepare your data

Your data should be one table with one row per shipment, and a header on every column. For example:

Avoid blank rows, merged cells and totals inside the data. Press Ctrl+T to make it a Table first.

Build the pivot table

1. Click inside the table and choose Insert, PivotTable. Pick a new worksheet.
2. Drag Lane to Rows.
3. Drag Mode to Columns.
4. Drag Cost to Values. Make sure it says "Sum of Cost".

You now see total cost for each lane and mode.

Group by month

Drag Date to Rows. In newer versions of Excel, dates group into months and years automatically. If they do not, right-click a date in the pivot table, choose Group, and select Months (and Years if your data covers more than one year).

The cost per kg trap

You will want to see cost per kg for each lane. The tempting method is to add a column =Cost/Weight to your data and then average it in the pivot table. This gives a wrong answer, because it treats a 100 kg shipment and a 10,000 kg shipment as equally important.

The right way is to divide total cost by total weight. In the pivot table, go to PivotTable Analyze, Fields, Items & Sets, Calculated Field. Name it "Cost per kg" and use the formula:

=Cost/Weight

A calculated field works on the totals, so you get a weighted result. For the lane to look right, format the numbers with two decimals.

Show each lane as a share of the total

Right-click a cost value, choose Show Values As, then "% of Grand Total". This tells you where your freight money goes.

Refreshing

A pivot table does not update by itself. After you add new rows, right-click the pivot table and choose Refresh. If your data is a Table, the new rows are picked up automatically.