HomeBlogResourcesTrack Online Orders and Shipments in Excel: Template, Formulas & When to Automate

Track Online Orders and Shipments in Excel: Template, Formulas & When to Automate

17TRACK

You can track online orders and shipments in Excel with one row per package, a consistent status list, and formulas that flag overdue deliveries and outdated checks. The template below gives you a practical starting point. Excel can organize and prioritize shipment information, but formulas alone do not retrieve live carrier events; those updates require manual checks or a connected data source.

Track Online Orders and Shipments in Excel Template Formulas When

This guide is designed for small ecommerce teams and anyone managing multiple incoming orders. It separates the shipment record from the follow-up work, so your spreadsheet answers two different questions: where is the package, and who needs to do something next?

Build the tracker around packages, not just orders

An order number identifies a purchase. A tracking number identifies a shipment within a carrier's system. They are not interchangeable. One order can ship in several packages, and each package can have a different delivery date or status.

Use one row per package, repeating the order ID when necessary. If an order has not shipped, create a provisional row without a tracking number, then update or split that row once the shipment details are available. Do not put several tracking numbers into a single cell: it makes filtering, checking duplicates, and updating statuses harder.

For a personal purchase tracker, the owner can simply be you. For a business tracker, assign an owner who will investigate exceptions, contact the carrier or seller, or communicate with the customer. A status without a responsible person is easy to overlook.

A copy-and-paste Excel shipment tracking template

Copy the tab-separated header below and paste it into cell A1 in a blank worksheet. The headings should occupy columns A through L. This is a starter layout, not a downloadable file or a live tracking integration.

Order ID	Tracking number	Carrier	Ship date	Promised delivery	Status	Last carrier event	Last checked	Tracking URL	Days open	Review flag	Owner

Before pasting order data, format columns A and B as Text. Tracking numbers are identifiers, not quantities. Treating them as numbers can remove leading zeros or alter long numeric strings. Changing the format afterward will not reliably restore information that was already lost; reimport the original identifiers if necessary.

ColumnWhat to enterWorking rule
A: Order IDThe store or internal order referenceRepeat it for split shipments.
B: Tracking numberThe identifier supplied for the packageKeep it as text and preserve its exact characters.
C: CarrierThe carrier associated with that numberUse consistent names rather than several abbreviations.
D: Ship dateThe date used to start shipment agingDefine whether this means dispatch or confirmed carrier acceptance.
E: Promised deliveryThe applicable delivery commitmentLeave blank when no promise exists; do not invent a deadline.
F: StatusA standardized shipment categoryChoose from a controlled list.
G: Last carrier eventThe latest event text and event timePreserve the detail behind the standardized status.
H: Last checkedWhen someone last verified the tracking informationEnter a fixed timestamp, not a continuously changing formula.
I: Tracking URLA verified tracking-page linkKeep the destination separate from the tracking number.
J: Days openA calculated shipment ageUse calendar days unless you deliberately build another rule.
K: Review flagA calculated follow-up promptThis is an internal rule, not a carrier status.
L: OwnerThe person responsible for follow-upAssign ownership before an exception occurs.

Set up the sheet before adding more rows

  1. Convert the range to an Excel Table. Use Insert > Table and confirm that the range has headers. Tables make filtering easier and can carry calculated-column formulas into additional rows.
  2. Apply date formats. Format D and E as dates. Format H with both date and time. Confirm that imported values are real Excel dates rather than text that merely looks like a date.
  3. Create a status dropdown. Use Data Validation with a list such as Not shipped, Label created, In transit, Out for delivery, Delivered, Exception, and Returned.
  4. Freeze the header row. Keep the column labels visible while reviewing longer lists.
  5. Add a check threshold. Put “Recheck after days” in O1 and enter 2 in P1. This is an example internal review interval, not a carrier service standard.

Keep carrier event wording in column G even when you normalize column F. For example, “Label created” should not automatically become “In transit”: creating a label does not by itself establish that the carrier has received the parcel.

Formulas that make the tracker useful

The formulas below use the column layout above, English function names, and comma separators. Some regional Excel settings use semicolons instead. Enter them in row 2 and fill down, or let the Excel Table apply them as calculated columns.

Calculate calendar days open

Enter this in J2:

=IF(OR(D2="",F2="Delivered",F2="Returned"),"",MAX(0,TODAY()-INT(D2)))

This returns the calendar days since the ship date for an open shipment. It leaves completed or returned shipments blank rather than continuing to age them indefinitely. The formula does not measure transit performance against a carrier's business-day commitment.

If you need historical delivery duration, add a separate Delivered at column and calculate the difference between that recorded date and the ship date. Do not use today's date as a substitute for the actual delivery date.

Flag the shipments that need review

Enter this in K2:

=IF(A2="","",IF(F2="Delivered","Closed",IF(F2="Returned","Review return",IF(F2="Exception","Review exception",IF(AND(E2<>"",TODAY()>INT(E2)),"Past promised date",IF(B2="","Check dispatch",IF(H2="","Check tracking",IF(NOW()-H2>=$P$1,"Recheck status","Monitor"))))))))

The order of these checks matters. Delivered shipments close first; returns and exceptions receive specific prompts; shipments past their promised date take priority over routine checks. A package without a tracking number is routed to a dispatch check rather than treated as an ordinary tracking update.

“Past promised date” means the date in your sheet has passed. It does not establish that a parcel is lost, that compensation is owed, or that a carrier has breached its terms. Check the source of the promise and the latest shipment details before escalating.

The stale-check rule measures elapsed time since H2. With P1 set to 2, it flags a record after two days without a recorded check. Adjust that interval to your workflow. TODAY() and NOW() update when Excel recalculates; they are not background monitoring services.

Count the open review queue

Place this summary formula outside the tracking table:

=COUNTIF(K2:K1000,"Review exception")+COUNTIF(K2:K1000,"Review return")+COUNTIF(K2:K1000,"Past promised date")+COUNTIF(K2:K1000,"Check dispatch")+COUNTIF(K2:K1000,"Check tracking")+COUNTIF(K2:K1000,"Recheck status")

This counts package rows requiring attention, not unique orders or customers. Extend the range when necessary, or replace it with the relevant structured table reference. Keep the distinction visible in any dashboard: one order with three packages can contribute three shipment records.

Turn a verified tracking URL into a readable link

In an optional extra column, use:

=IF(I2="","",HYPERLINK(I2,"Track package"))

This opens the address stored in I2. It does not fetch shipment events or confirm that the address is safe. Use links from a trusted tracking service or the carrier, and avoid constructing carrier-specific URLs unless you have verified their format.

Use formatting to support decisions, not decorate the sheet

Apply conditional formatting to the review flag or the whole row. Use a strong warning color for exceptions and past-promised-date records, a lighter warning for routine rechecks, and a muted style for closed shipments. Keep the text label visible so the worksheet remains understandable without color.

For a whole-row rule applied to A2:L1000, a formula such as =$K2="Past promised date" highlights the corresponding shipment. The dollar sign fixes the status column while allowing the row number to change.

Also check for duplicate records before importing another batch. A repeated order ID is normal for split shipments; a repeated carrier-and-tracking-number combination deserves investigation. It may be an accidental duplicate, but do not delete it automatically without checking the underlying order relationship.

A daily workflow for keeping the tracker trustworthy

  1. Add or import new shipments. Preserve identifiers as text and confirm that each package has its own row.
  2. Filter by review flag. Start with exceptions and overdue promises, then work through missing tracking details and stale checks.
  3. Verify the tracking source. Open the carrier page or use a multi-carrier service to track the package. Compare the tracking number and carrier with the spreadsheet.
  4. Update the evidence. Record the latest event in G, select the appropriate status in F, and enter a fixed check timestamp in H.
  5. Assign the next action. Add an action-note column if the owner needs to record a customer message, investigation reference, or follow-up date.
  6. Reconcile closed records. Confirm that a delivered status applies to the correct package, not automatically to every package in the order.

Do not enter =NOW() in “Last checked.” It changes on recalculation and can make an untouched record appear recently reviewed. The timestamp must represent a real check or a successful automated update.

Keep customer information to the minimum necessary. A shared operational tracker rarely needs a full delivery address, phone number, and complete order details in every row. Restrict access and avoid distributing uncontrolled copies containing personal information.

When to automate shipment tracking

Automate when keeping statuses current becomes the bottleneck, not simply when the worksheet becomes long. There is no universal shipment-count threshold. A spreadsheet can remain workable for a larger, slow-changing list while a smaller list with urgent exceptions may require more frequent updates than a person can reliably perform.

Observed problemPractical next stepWhat to verify
Repeated copying from order exportsStandardize imports or use Power Query for supported sources.Identifier formatting, column mapping, and duplicate handling.
Repeated manual carrier checksEvaluate a tracking data connection or API-backed workflow.Support for your actual carriers, services, and shipment routes.
Exceptions are discovered too lateAdd scheduled processing or event-driven alerts.Trigger definitions, responsible owners, and failed-alert handling.
Several people overwrite the same recordsUse a controlled shared workflow or operational system.Access permissions, change history, and record ownership.
Customers depend on someone checking a workbookEvaluate an order-tracking workflow connected to the store.Order mapping, split shipments, and customer-facing status accuracy.

Power Query can connect to supported data sources, including web sources, but that does not make every public tracking page a dependable data feed. Authentication, page structure, access restrictions, and refresh configuration can affect whether a connection works. Prefer a documented interface over scraping a page that was designed for people to read.

For a custom integration, the 17TRACK Tracking API is a relevant option to evaluate. Check its official documentation against your intended data flow before implementation. Do not assume that an API offering is a native Excel connector or that it will update an existing workbook without additional integration work.

A practical architecture is to collect tracking updates in a controlled service or data store, map them to your package records, and then expose the resulting table to Excel. Keep credentials out of widely shared workbooks. Store both the event timestamp and the ingestion timestamp so a delayed update is distinguishable from a new shipment event.

Test any automated workflow on ordinary deliveries, split shipments, missing events, exceptions, and returned packages. Confirm how it handles retries and repeated updates. Keep a visible “last successful refresh” indicator: an old delivered-looking dashboard can be more misleading than an openly incomplete spreadsheet.

FAQ

Can Excel update tracking numbers automatically?

Excel can display refreshed shipment data when it has a suitable connection or integration. A tracking number in a cell, a hyperlink, or a TODAY() formula does not retrieve carrier events. You need a supported source and a configured process for importing its updates.

Why did Excel change my tracking number?

Excel may have interpreted it as a number rather than text. Format the destination column as Text before entering or importing identifiers. If digits or leading zeros have already been lost, copy the identifier again from its original source; visual formatting alone cannot reconstruct missing information.

Should I use the estimated delivery date or the promised date?

Keep them in separate columns if both matter. A carrier estimate can change as a shipment progresses, while a customer promise may come from the seller's checkout or confirmation. The overdue formula in this template uses the promised date. Label any formula based on a carrier estimate accordingly.

How do I track an order with multiple packages?

Create one row for each package and repeat the order ID. Update each package independently. If you need an order-level summary, mark the order complete only after all packages required to fulfill it have reached the appropriate outcome.

Does a long period without scans mean a package is lost?

No. Missing updates alone do not prove loss. Verify the carrier's latest information, compare the shipment with its delivery expectations, and follow the applicable investigation process. A stale-data flag should prompt a check, not automatically label a parcel as lost.

Sources