Build a simple shipment tracker in Excel

You do not need expensive software to track shipments. A well-built Excel sheet can show you what is in transit, what is late and what is delivered.

Set up the columns

Create these headers in row 1:

Select the headers and press Ctrl+T to turn the range into a Table. New rows will copy the formulas down by themselves.

The status formula

In F2:

=IF(E2<>"", "Delivered", IF(TODAY()>D2, "Delayed", "In transit"))

In words: if there is an actual arrival date, it is delivered. If not, and today is past the ETA, it is delayed. Otherwise it is in transit.

The days late formula

In G2:

=IF(E2="", MAX(0, TODAY()-D2), MAX(0, E2-D2))

For shipments still on the way, this counts days past the ETA so far. For delivered shipments, it shows how late they finally were. MAX(0, ...) keeps early arrivals at zero instead of showing a negative number.

Colour the delayed rows

1. Select the whole table body.
2. Go to Home, Conditional Formatting, New Rule, "Use a formula to determine which cells to format".
3. Enter =$F2="Delayed" and choose a light red fill.

The dollar sign before F locks the column, so the whole row turns red.

Add a summary at the top

Put these in a few cells above or beside the table:

=COUNTIF(F:F, "Delayed")
=COUNTIF(F:F, "In transit")
=COUNTIF(F:F, "Delivered")

Now you can see the state of all shipments at a glance.

Next steps

Add dropdowns so nobody types a customer name wrongly (see dropdowns for Incoterms and status). When you have months of data, build a dashboard in Power BI.