Level 3 · Advanced: pivot tables & charts

3.4Data validation & drop-down lists

Goal: frame data entry with data validation to avoid errors at the source.

Estimated time: 12 min · self-paced reading · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

Block errors before they pollute: Data ▸ Validation imposes entry rules — closed list, number within a range, date, text length.
The drop-down list closes the choices: you select instead of typing — goodbye to the variants that falsify pivot tables and lookups; source in a structured table for a list that grows by itself.
Validation has a limit: it protects only manual entry — a copy-paste bypasses it, so check afterward on critical data.

Running mini-case — Wassim and the three Algiers

Wassim, an accountant in Algiers, finds «alger», «Alger» and «ALGER» in his Region column: his pivot table shows three rows for a single city, and his XLOOKUPs fail. He sets a drop-down validation pointing to the regions table — the team selects instead of typing, and when «Béjaïa» is added, the menu offers it by itself. Clean data at the source, pivot tables right without cleaning.

The same case returns in the course, the diagram, the script and the 48h challenge.

1. Preventing errors at entry, not after

The data validation (Data ▸ Validation) imposes entry rules: allow only numbers within a range, a date, a text length, or a list of values. This blocks mistakes before they pollute the analysis — far better than correcting after the fact, when the pivot table has already mixed everything up.

You can add an input message (a help that appears on selection) and an error alert (blocking or informative). Wassim puts blocking alerts on critical fields (region, amount) and informative ones elsewhere: the severity follows the stakes.

2. The scalable drop-down list — and its limit

The most useful case: the drop-down list. You offer a closed choice (Algiers, Oran, Constantine, Annaba) → the user selects instead of typing, which eliminates the variants. The list can point to a range or a structured table to update itself when a value is added — like Wassim's «Béjaïa».

Limit to know: validation applies only to manual entry — a copy-paste can bypass it. For critical data, Wassim completes it with an a posteriori check (conditional formatting of anomalies, filter on values outside the list). Validation prevents, the check verifies.

Common misinterpretation: «With validation, my data is guaranteed 100% clean — and a hard-coded list is enough». No, twice. A paste bypasses validation and it does not revalidate existing data: on critical data, you need an a posteriori check. And a hard-coded source freezes the menu — as soon as the list moves, point to a structured table for a scalable list. Prevent plus verify, not prevent only.

You target a range, you set a criterion (list, number, date); entry becomes controlled

Text version of the diagram (SEO / screen readers / translation): 1) Target: Wassim selects the Region column instead of letting people type freely. 2) Criterion: list validation pointing to the regions table, help message, blocking alert. 3) Life: the team selects, «Béjaïa» added to the table appears by itself in the menu — and a check spots pastes outside the list. Lesson: closed choice, living source, final verification.

🔎 Concrete example — A drop-down list that grows by itself

Wassim's list of regions in a «Regions» table; validation in column A:

Validation > List > Source: =Regions[Name]
=> drop-down menu Algiers/Oran/Constantine/Annaba in column A
Adding «Béjaïa» to the Regions table
=> «Béjaïa» appears automatically in the menu

Reading: pointing to a structured table makes the list scalable.

Lesson: you enter clean, and you no longer have to re-edit the validation on each addition.

Section vocabulary

Data validation

Rules that limit what can be entered in a cell (list, number, date, length).

Simple example: Wassim's region limited to the closed list.

What it is not: a verbal instruction «write carefully».

Reflex: shared field = rule set.

Drop-down list

Menu of allowed values: you choose instead of typing, zero variants.

Simple example: Algiers/Oran/Constantine/Annaba in the Region column.

What it is not: free entry with three spellings of Algiers.

Reflex: finite choice = drop-down list.

Dynamic source

List pointing to a structured table: any added value appears in the menu.

Simple example: «Béjaïa» added, menu up to date by itself.

What it is not: a hard-coded source to re-edit on each addition.

Reflex: source that moves = structured table.

Input message

Help tooltip shown on selecting the cell — guides without blocking.

Simple example: «Choose the region from the list».

What it is not: an instructions tab nobody reads.

Reflex: help as close as possible to the cell.

Error alert

Message shown if the entry violates the rule: blocking (forbidden) or informative (warns).

Simple example: Amount outside 0-100 blocked at Wassim's.

What it is not: an informative alert on a critical field.

Reflex: critical = blocking, secondary = informative.

In practice — Controlling data entry

Create a drop-down list of categories from a structured table.
Add a value to the table and check that it appears in the menu.
Impose «number between 0 and 100» on a column of percentages.
Add a help message and a blocking error alert.

You guarantee clean, homogeneous data at entry.

Ready-to-say script — imposing the list on the team (30 s)

«From now on, the Region column is chosen from the list — no more typing. The list follows the regions table by itself, and if you paste data, a check flags the values outside the list. Three Algiers meant three wrong pivot tables.»

To say at a team meeting; show the menu and the check.

Step-by-step template — scalable drop-down list

1) Choices table: Ctrl+T on the list (column «Name»)
2) Select the entry column > Data > Validation
3) Allow: List > Source: =Regions[Name]
4) Input message: «Choose from the list»
5) Error alert: Stop (blocking) on critical field

Freelancer

Closed lists on living sources and your analyses no longer go into cleaning.

SME in Algiers

Wassim closes the Region entry: the team selects, the pivot table stays right.

Project team

Impose validation + structured source on every shared field of the workbook.

Beginner

This week: a drop-down list on a structured table + an addition test.

✓ Best practices

Close the choices with drop-down lists.
Point to a structured table (scalable list).
Add a help message + error alert.

✗ Common pitfalls

Relying on validation alone (copy-paste bypasses it).
Listing a frequently changing source «hard-coded».
Informative-only alerts on critical fields.

Key takeaways

Validation frames data entry (number, date, length, list).
The drop-down list eliminates entry variants.
A structured-table source makes the list scalable.
Limit: copy-paste can bypass validation.

48h challenge — apply tomorrow

Set a drop-down list on a structured source on 1 shared column, with a help message and a blocking alert.

Section exit checklist

My shared entry is closed by lists.
My lists follow a structured table.
My critical fields have a blocking alert + a check.

Self-assessment 1-5: is there still free entry where a closed choice would suffice?do my new values appear without re-editing the validation?

← Previous : Slicers, timelines & simple dashboards (V3)Next : Conditional calculation: SUMIF(S), COUNTIF(S) (V3) →

Frequently asked questions

My drop-down list does not show the new values?

Its source is probably a frozen range. Point to a structured table (=Table[Column]): the list then automatically includes any added value.

Does validation guarantee 100% clean data?

No: it protects manual entry, but a paste can bypass it and it does not revalidate existing data. For critical data, add an a posteriori check (conditional formatting of anomalies, filter).

Check your understanding

What is the main benefit of a validation drop-down list?

Additional resources (optional, to go further)

Excel data validation: drop-down lists

YouTubeEN

Scalable Excel drop-down list with a structured table

YouTubeEN

Level 3 examFresh, harder questions to test what you've learned