Excel dropdowns for Incoterms and shipment status

If five people type "FOB", "F.O.B", "fob" and "Fob" in the same column, no lookup or pivot table will group them properly. A dropdown list fixes this.

Step 1: Make your list

On a separate sheet called Lists, type the values in a column. For Incoterms 2020, the eleven rules are:

EXW, FCA, CPT, CIP, DAP, DPU, DDP, FAS, FOB, CFR, CIF.

Put the header "Incoterm" in A1 and the values below it. Select the range and press Ctrl+T to make it a Table, and name the table tblLists in the Table Design tab. A Table grows by itself when you add values.

Step 2: Give the list a name

1. Go to Formulas, Name Manager, New.
2. Name: IncotermList
3. Refers to: =tblLists[Incoterm]

Step 3: Add the dropdown

1. Select the cells where people will enter Incoterms.
2. Go to Data, Data Validation.
3. Under Allow, choose List.
4. In Source, type =IncotermList.
5. Click OK.

The cells now show a small arrow with the list. Anything not on the list is rejected.

Make the messages helpful

In the same Data Validation window:

• On the Input Message tab, add a short hint such as "Pick an Incoterm from the list".
• On the Error Alert tab, choose Stop and write a clear message such as "Please choose a value from the list".

Do the same for status and customers

Repeat the steps for shipment status (for example In transit, Delayed, Delivered) and for your customer list. Keep every list on the Lists sheet so you maintain them in one place. If you also use a shipment tracker, make sure the status dropdown uses the same words as your formulas.

Know the limits

Data validation only checks what people type. It does not stop someone pasting over the cells, because pasting can remove the rule. If this matters, protect the sheet: go to Review, Protect Sheet, and unlock only the cells people should edit (select them, then Format Cells, Protection, untick Locked, before protecting).

Check for blanks

To find rows where someone skipped the Incoterm, use conditional formatting with the rule =$C2="" applied to the row, and choose a yellow fill. Empty cells are easy to spot, and your reports stay complete.