# 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. I edited it. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).*

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, which work the same way in Excel and Google Sheets.

## 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**.
- **Google Sheets:** Data → Data validation → Add rule → Dropdown (from a range) → `Lists!A2:A6` → "Reject the input".

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

**About me, and a disclosure:** I'm Ashish, a Kolkata-based Excel and Google Sheets builder with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.