Build an Order Tracker in Excel

An order tracker in Excel keeps every order in one table, so you can answer "where's my order?" in seconds. Each order gets a row with its ID, dates, status and amount. XLOOKUP finds an order by ID, COUNTIF counts orders by status, and simple date subtraction shows how fast you ship. This guide builds one for a small online shop or a sales team, step by step.

How the order tracker is laid out#

Use one row per order and one column per fact. The finished table looks like this:

The Order ID is the key: it must be unique, so a lookup always finds exactly one row. Turn the list into an Excel table with Ctrl+T (Mac: ⌘T) so new orders pick up formulas and formatting automatically.

Step 1: Add a status drop-down#

A status drop-down keeps the words consistent, which both filters and COUNTIF depend on. Select the Status column, choose Data > Data Validation, set Allow to List, and enter:

Processing,Shipped,Delivered,Cancelled

Keep statuses in the order an order moves through them. That makes the summary easier to read later.

To number new orders consistently, type the first ID, such as ORD-1001, and drag the fill handle down. Excel continues the series as ORD-1002, ORD-1003 and so on, because it increases the number at the end of the text. Never reuse an ID, even for a cancelled order; change its status instead. Each morning, filter the Status column to Processing and sort by Order date, oldest first, so the customers who have waited longest are at the top of your list.

Step 2: Look up an order with XLOOKUP#

XLOOKUP searches one column and returns the matching value from another. Put an order ID in H2 and, in I2:

=XLOOKUP(H2,A2:A8,E2:E8,"Not found")

With ORD-1004 in H2, the result is Processing. The parts are:

• H2 is the ID to find.
• A2:A8 is where to look: the Order ID column.
• E2:E8 is what to return: the Status column.
• "Not found" is shown if the ID doesn't exist, instead of #N/A.

Change E2:E8 to B2:B8 to return the customer, or to D2:D8 for the ship date. XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use =VLOOKUP(H2,A2:F8,5,FALSE), where 5 is the Status column's position. The XLOOKUP function guide covers every argument, and you can practise XLOOKUP in a live sheet. To build a full search panel, see the employee lookup sheet, which uses the same idea.

Step 3: Calculate days to ship#

Excel stores dates as numbers, so subtracting two dates gives the days between them. In G2:

=IF(D2="","",D2-C2)

ORD-1002 took 5 days, from 8 to 13 October. Rows with no ship date stay blank instead of showing a large negative number. If the result shows as a date, change the cell format to General.

The average days to ship is:

=AVERAGE(G2:G8)

AVERAGE skips the blank text cells, so it averages only shipped orders: (2 + 5 + 2 + 1) ÷ 4 = 2.5 days. For only working days, use NETWORKDAYS instead of subtraction. The days between dates lesson practises DAYS, DATEDIF and TODAY.

Step 4: Show how long open orders have waited#

For orders still processing, the useful number is how long the customer has been waiting. TODAY returns the current date. In a new column next to Days to ship:

=IF(E2="Processing",TODAY()-C2,"")

On 1 November 2026, ORD-1004 has been waiting 17 days and ORD-1006 10 days. TODAY updates every day, so this column always shows the current wait.

Step 5: Count orders by status with COUNTIF#

COUNTIF counts cells that match one condition. List the statuses in K2:K5 and, in L2:

=COUNTIF($E$2:$E$8,K2)

Fill it down. The result is Processing 2, Shipped 2, Delivered 2 and Cancelled 1. The $ signs lock the Status range, and K2 changes as you fill.

To add up the value of orders in each status, use SUMIF: =SUMIF($E$2:$E$8,K2,$F$2:$F$8). Open orders (Processing) are worth 1,300. The COUNTIF function guide shows more criteria, like "<>Cancelled" to count every order that's still live, and you can practise COUNTIF to get quick at it.

Step 6: Flag slow orders and duplicate IDs#

Conditional formatting makes problems jump out without reading every row.

Orders processing for more than 7 days:

1. Select the rows, A2:F8.
2. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =AND($E2="Processing",TODAY()-$C2>7) and pick an orange fill.

Duplicate order IDs: select the Order ID column and add a formula rule =COUNTIF($A$2:$A$100,A2)>1 with a red fill. A duplicate ID would make XLOOKUP return only the first match, so it's worth catching early.

If orders arrive from another system, you can check which IDs are missing from your tracker by following the guide to comparing two lists in Excel.

Connect the tracker to your stock#

An order tracker pairs naturally with stock. When an order ships, the items leave your inventory. The inventory tracker in Excel uses SUMIF to total stock movements, and you can feed shipped quantities into it from this table. For a personal version of the same pattern, the job application tracker uses statuses, dates and COUNTIF to manage a job search.

Practise it#

Try the exercise on this page: find an order's status with XLOOKUP. Then practise XLOOKUP in a live spreadsheet, practise COUNTIF for the status summary, and practise days between dates so you can measure shipping times with confidence.