How to Add an Order Status Dropdown That Colours Itself

Subtitle: Data validation, conditional formatting, counts per status, a "stuck" flag, and a check that catches statuses typed by hand

Written with AI assistance.

A status column is the simplest way to know where every order is. It's also where small sheets go wrong: "Shipped", "shipped", "Shipped " with a trailing space and "Sent" all mean the same thing to you, and four different things to a formula.

Here's how to lock the column to a short list, colour each row by its status, count what's in each stage, and flag orders that have sat still too long. It uses data validation, conditional formatting, COUNTIF and SUMIFS.

1. The status list

Make a tab called Lists and type your stages in A2:A6:

New, Packed, Shipped, Delivered, Cancelled

Keep it short. Five stages you actually use beat twelve you don't.

2. The dropdown

Orders from row 5: A Order ID, B Customer, C Amount, D Status, E Status date (the day the status last changed). Put an As of date in B2 (=TODAY() works).

Select D5:D104, then:

• Excel: Data → Data Validation → Allow: List → Source: =Lists!$A$2:$A$6. On the Error Alert tab, keep Stop.

New entries now have to come from the list. But a dropdown doesn't clean rows that were already there, and pasted values can slip past it, so section 5 adds a check.

3. Colour each row by status

Select A5:G104 and add one conditional formatting rule per status, each with a custom formula:

Rule formula	Suggested fill
=$D5="Delivered"	light green
=$D5="Shipped"	light blue
=$D5="Cancelled"	grey
=AND($D5="Packed",$F5>2)	light red (stuck, see below)

The $ before D and F keeps every cell in the row looking at the status column. Without it, only the status cell changes colour.

4. Days in status and a "stuck" flag

F5, days since the status last changed:

=IF(E5="","",$B$2-E5)

G5, a flag column:

=IF(D5="","No status",IF(COUNTIF(Lists!$A$2:$A$6,D5)=0,"Not on list",IF(AND(D5="Packed",F5>2),"Stuck",IF(AND(D5="Shipped",F5>7),"Stuck",""))))

Set your own limits: here an order packed for more than 2 days, or shipped for more than 7 without being marked delivered, gets flagged. Remember to update the status date whenever you change the status.

5. Counts per status, and the check

Put the five stages in J5:J9, then:

Cell	Formula
K5: Orders	=COUNTIF($D$5:$D$104,J5)
L5: Amount	=SUMIFS($C$5:$C$104,$D$5:$D$104,J5)
K11: Status not on the list	=SUMPRODUCT(($D$5:$D$104<>"")*(COUNTIF(Lists!$A$2:$A$6,$D$5:$D$104)=0))
K12: Orders with no status	=COUNTIFS($A$5:$A$104,"<>",$D$5:$D$104,"")
K13: Accounted for	=SUM(K5:K9)+K11+K12
K14: All orders	=COUNTA($A$5:$A$104)
K15: Check	=IF(K13=K14,"OK","CHECK")

Fill K5 and L5 down to row 9. K11 and K12 are the useful ones: they show orders that the per-status counts silently skip. The check in K15 confirms every order is either in a stage or listed as a problem.

6. What the sample showed

Seven made-up orders, as of 21 Oct 2026:

Order	Status	Days in status	Flag
O-101	Delivered	11
O-102	Shipped	4
O-103	Packed	5	Stuck
O-104	New	1
O-105	Cancelled	9
O-106	"shipped " (typed, trailing space)	2	Not on list
O-107	(blank)	(blank)	No status

Counts: one order in each of the five stages (New 650, Packed 500, Shipped 800, Delivered 1,200, Cancelled 300), plus 1 not on the list and 1 with no status. 7 accounted for out of 7 orders, so the check said OK.

The typed "shipped " is the reason for K11. COUNTIF ignores capital letters, but not the trailing space, so that order would have been missing from the Shipped count without the check. Retype it from the dropdown and it moves into Shipped.

7. Test it before you trust it

Add rows you know the answer to, then confirm:

• a status pasted in with a trailing space (should say "Not on list")
• an order with no status (should say "No status" and count in K12)
• a Packed order with a status date 3+ days old (should say "Stuck" and turn red)

Then delete them.

---

Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker

Practical spreadsheet templates for small businesses, made with AI assistance.