Data Validation in Excel: Building Dropdown Lists That Actually Work

Data Validation restricts what someone can type into a cell, most commonly by turning it into a dropdown list of allowed values. Instead of hoping everyone types "NSW" exactly the same way every time, a dropdown forces them to pick from a fixed list, which quietly prevents a huge amount of the messy, inconsistent data that causes formulas to fail later.

I once inherited a spreadsheet where the "Region" column had been typed freely for over a year by half a dozen different people. Between them, NSW had been entered as "NSW," "N.S.W," "nsw," and, memorably, "Sydeny" (missing the second S, presumably typed in a hurry). Every SUMIF and COUNTIF built on that column was quietly wrong, because Excel treated each spelling as a completely different value. Fixing the historical data took a proper cleanup, but the bit that actually stopped it happening again was dead simple — turning that column into a dropdown so nobody could type a fresh region name incorrectly ever again.

How do I create a basic dropdown list?

1. Select the cell or range where you want the dropdown to appear
2. Go to Data > Data Validation
3. Under Allow, choose List
4. In the Source box, either type your options separated by commas (like NSW,VIC,QLD,WA,SA) or click the range selector and select a list of cells elsewhere in the workbook
5. Click OK

Typing options directly into the Source box works fine for a short, fixed list that basically never changes. For anything that might grow or change over time, referencing a range of cells elsewhere is the better approach.

Why does my dropdown list stop updating when I add new options?

This is the most common frustration with dropdowns, and it comes down to how the Source range was set up. If your Source references a fixed range like Sheet2!$A$1:$A$5, and you add a sixth option below it, the dropdown won't include it, because the range reference itself never grew.

If you've read my post on "Excel Tables", this is a genuinely good use case for that. If the source list is stored inside an Excel Table, the range automatically expands as new items are added, and any dropdown referencing that Table's column grows right along with it, without you ever touching the Data Validation settings again.

Can I build a dropdown that removes duplicates automatically?

Yes, and this pairs well with dynamic arrays. If you've read my post on "FILTER, SORT and UNIQUE", you'll remember UNIQUE strips duplicates from a range live. You can use it to build a clean, self-maintaining source list for a dropdown, rather than manually managing a separate list of options.

1. In a helper cell, use =UNIQUE(A2:A200) to spill a clean list of distinct values from your raw data
2. In Data Validation, set the Source to reference that spilled range, using a formula like =$F$2# (the # symbol refers to the entire spilled array, whatever size it currently is)
3. As new unique values appear in your raw data, the dropdown list grows automatically to include them

How do I create a dependent dropdown, where the second list changes based on the first?

This is a common request — picking a category in one dropdown, then only seeing relevant sub-options in a second dropdown.

1. Set up separate named ranges for each category's list of sub-options (for example, a range called "NSW_Suburbs" and another called "VIC_Suburbs")
2. Create your first dropdown normally, listing the categories (NSW, VIC, etc.)
3. For the second dropdown's Data Validation Source, use: =INDIRECT($A2), where A2 is the cell containing your first dropdown's selection

INDIRECT converts a piece of text into an actual cell or range reference, so if A2 says "NSW," INDIRECT($A2) effectively becomes INDIRECT("NSW"), which points at your named range called NSW_Suburbs. The named range names need to exactly match the values in your first dropdown, including capitalisation, or this won't work.

Why is my Data Validation dropdown showing an error when I try to enter something?

By default, Data Validation blocks anything not on the list and shows an error message. A few things worth knowing:

1. You can customise the error message under the "Error Alert" tab in the Data Validation dialog, which is worth doing so it actually explains what's expected rather than showing Excel's generic default text.
2. You can change it from blocking to just warning, by setting the Error Alert style to "Warning" or "Information" instead of "Stop," which lets someone type something off-list if they genuinely need to, with a heads-up rather than a hard block.
3. Pasting data in can bypass validation entirely in some cases, which catches people out — validation mainly protects against typing, not always against copy-pasted values.

FAQ

Can I use a dropdown list from a different sheet? Yes — reference the range on the other sheet in the Source box, like =Sheet2!$A$2:$A$10. Just be aware that referencing a range on a different workbook (not just a different sheet) has more restrictions and generally needs both files open at once.

Why does my dropdown show blank options at the bottom of the list? This usually means your Source range includes empty cells below your actual data, often because the range was set wider than needed "just in case." Tightening the range, or switching to a Table-based or UNIQUE-based source as covered above, fixes this.

Can I allow multiple selections from one dropdown? Not natively with standard Data Validation — it's designed for a single selection per cell. Multiple selections generally require a short VBA macro, which is beyond what basic Data Validation can do on its own.

Does Data Validation stop someone from copying and pasting an invalid value into the cell? Not always reliably — depending on how the paste is done, it can sometimes bypass the validation rule entirely. If strict enforcement really matters, it's worth periodically auditing the column rather than relying on Data Validation as a complete guarantee.

What does the "Circle Invalid Data" option do? Found under Data > Data Validation's dropdown arrow, it draws a red circle around any existing cells that don't currently meet your validation rule. This is handy for auditing a column that had free-typed entries before you added a dropdown rule to it, exactly the kind of cleanup my "Sydeny" example above needed.