---
title: "Build a Dynamic Data Validation List That Updates Itself When Your Source Table Grows"
date: 2026-06-02
updated: 2026-06-27T11:15:25Z
tags: ["Dynamic Arrays", "Excel", "Power Query", "Microsoft 365", "Data Modeling"]
canonical: https://bianca.codes/blog/build-a-dynamic-data-validation-list-that-updates-itself-when-your-source-table-grows/
---

# Build a Dynamic Data Validation List That Updates Itself When Your Source Table Grows

_You'll build a dropdown list that reads from a structured table and includes every new row automatically. No named range maintenance, no 'why isn't my new entry showing up' moments._

You'll build a dropdown list that reads from a structured table and includes every new row automatically. No named range maintenance, no "why isn't my new entry showing up" moments. The setup takes about ten minutes the first time, half that once you've done it twice.

What you need to start: Excel for Microsoft 365 (or Excel 2024), one workbook, and a list of values you want in the dropdown. The example uses a list of clients on a sheet called `Clients`, but any column of values works. The technique is identical for product codes, employee names, project IDs, or anything else.

The end state: pick a cell on any sheet, click the dropdown arrow, and the list shows every value in the source. Add a new row to the source table, and the dropdown picks it up the next time you click it. No manual refresh, no named range to extend, no "did I remember to update the validation" tax every time the data changes.

## Why this approach

The traditional way to build a data validation list is to point it at a fixed range, like `$A$2:$A$50`. This works exactly once. The first time someone adds a 51st row, the dropdown stops including it. The fix most people learn is to use a named range, which is better, but the named range still has to be extended manually or wired up with `OFFSET`/`COUNTA` in a way that always looks like it shouldn't work.

Structured tables solve the problem at the source. When a table grows, every reference to that table grows with it. Spill references inherit that behaviour: if your validation list reads from a spilled range, and the spilled range comes from a table column, the validation list grows when the table grows. The validation rule never needs touching again because it's referencing a thing that knows its own size.

The MAP/SORT/UNIQUE chain in the middle is what filters and orders the list. You almost never want raw column data as your dropdown options. You want them de-duplicated, sorted, and sometimes formatted. The dynamic array functions handle all three in one expression.

## Step 1: Set up the source as a structured table

Select your source range. Press `Ctrl + T`. Confirm the "My table has headers" checkbox. Click OK.

Excel converts the range into a structured table. Rename it from `Table1` to something readable: on the Table Design tab, change the Table Name field to `tblClients`. The `tbl` prefix is a personal habit; the important part is that the name is descriptive.

The point of this step is that the table now knows its own boundaries. Add a row at the bottom and the table extends. Delete a row and it shrinks. Every other reference in the workbook that points at this table inherits the same behaviour.

## Step 2: Build the dynamic source list with a spilled formula

Pick an empty cell on a hidden helper sheet, or a corner of your data sheet, and write:

```excel
=SORT(UNIQUE(tblClients[ClientName]))
```

`tblClients[ClientName]` is a structured reference to the ClientName column of the table. `UNIQUE` removes duplicates. `SORT` puts them in alphabetical order. The result spills down from the cell you wrote it in.

Note the cell address of the formula. For this example, assume it's in cell `Lookups!A1` on a sheet called `Lookups`. That cell, with the `#` suffix, becomes the dynamic reference: `Lookups!A1#`. The hash mark tells Excel "the entire spilled range that starts here."

If you want the list cleaned further, for example trimmed of whitespace and title-cased, that's where MAP fits in:

```excel
=SORT(UNIQUE(MAP(tblClients[ClientName], LAMBDA(c, PROPER(TRIM(c))))))
```

The MAP runs the trim-and-title-case transformation on every name before UNIQUE de-duplicates them, which catches the case where two entries differ only by leading whitespace or capitalisation.

## Step 3: Point the data validation rule at the spilled range

Select the cell or range where you want the dropdown to appear. Go to Data → Data Validation.

In the Allow dropdown, choose List. In the Source field, type:

```excel
=Lookups!$A$1#
```

The hash mark is the critical character. Without it, the validation pulls only the first cell of the spilled range. With it, the validation pulls the whole spilled range, however many rows tall that range currently is.

Click OK. The dropdown is live. Click on the cell, hit the dropdown arrow, and the full list appears.

## Step 4: Test by adding a row to the source

Go back to the `tblClients` table. Add a new client name in the row directly below the last one. The table extends to include it.

Switch to the cell with the data validation dropdown. Click the dropdown arrow. The new name is in the list, in its correct sorted position. No formula re-entered, no range extended, no refresh button pressed.

If the new name didn't appear, the most common reason is that the structured table didn't actually extend. If you typed into a row that wasn't part of the table, the table boundaries didn't grow with it. Click into the last cell of the table and press Tab; this is the canonical way to add a row that the table will recognise.

## Step 5: Lock the helper sheet (optional but recommended)

The spilled formula on the `Lookups` sheet is the load-bearing piece. If someone overwrites that cell, every dropdown breaks. The defence is to right-click the Lookups tab → Hide, then protect the workbook structure (Review → Protect Workbook) so the hidden sheet can't be unhidden by accident.

This is the difference between a workbook that survives being handed to someone else and one that doesn't. The dropdown still works for the user. The mechanism is out of the way.

## Common mistakes

### **Forgetting the hash mark in the validation source.**

Without `#`, the source `=Lookups!$A$1` returns a single cell. The dropdown shows one option. The mistake is silent because the rule technically validates, it just validates against a list of length one. The fix is to add the hash to the end of the reference: `=Lookups!$A$1#`. Excel's validation dialog does not warn you when you've left it off.

### **Using a normal range instead of a structured table.**

The whole technique depends on the source being a table that knows its own bounds. If the source is a static range like `A2:A50`, the spilled list won't grow when the data does, and you're back to manually extending the range every time. The structured table is what makes the rest of the chain self-extending. Skipping `Ctrl + T` at step 1 breaks the entire setup.

### **Letting the spilled range collide with other data.**

If the cell directly below your spilled formula has anything in it, the spill fails and the formula returns `#SPILL!`. The validation rule then has nothing to read from. The fix is to put the helper formula on its own sheet, or in a column you've cleared down to the bottom of the worksheet. The Lookups sheet exists for this reason.

## Frequently Asked Questions

### **Does this work in Excel 2019 or earlier?**

No. Dynamic arrays, including `UNIQUE`, `SORT`, and the spill reference syntax with `#`, are Microsoft 365 and Excel 2024 features. In older versions, the formula returns `#NAME?` and the validation rule cannot resolve. There is no backwards-compatible version of this technique; in older Excel, you have to use `OFFSET`/`COUNTA` named ranges instead, which is a less robust setup but the only option pre-2021.

### **Can I have a dependent dropdown that filters based on another cell?**

Yes. Replace the source formula with one that filters first, like `=SORT(UNIQUE(FILTER(tblProducts[ProductName], tblProducts[Category]=A1)))`, where `A1` holds the parent selection. The validation rule still points at the spilled range, but the contents of the range now depend on whatever's in A1. The list changes when A1 changes. This is the modern replacement for the old `INDIRECT`-based dependent dropdown pattern.

### **What happens if the source table is empty?**

The spilled formula returns `#CALC!` because `UNIQUE` of an empty array has nothing to return. The validation rule then has no source. The defensive pattern is `=SORT(UNIQUE(IFERROR(tblClients[ClientName], "")))`, which substitutes an empty string when the table is empty and lets the dropdown render at least one (blank) option. Whether that's the right behaviour depends on whether an empty dropdown should be silently empty or visibly broken.

### **Can the source table be on a different workbook?**

Technically yes, but the spilled range can't be referenced across workbooks reliably; cross-workbook spill references break when the source workbook isn't open. The defensible pattern is to bring the data in via Power Query, land it as a local table in the workbook that needs the dropdown, and point the validation at that local table. Same technique, with Power Query handling the cross-workbook part.

### **Why not just use a normal named range?**

You can, but the named range has to be defined with `OFFSET` or `COUNTA` to track the table size, and named ranges built that way are volatile, which slows down large workbooks. Structured-table references are not volatile and they don't need maintenance. The structured table plus spill reference is the lower-overhead modern equivalent.

## Where to go from here

The MAP function in the cleaning step deserves a post of its own, and the [Jun 1 Magic Monday on MAP](/blog/map-apply-any-transformation-to-every-element-in-an-array-without-a-loop/) walks through the mental model that makes the cleaning step possible. For the data modelling angle, why you'd structure your lookup tables this way in the first place and what it implies for downstream reporting, see the [Jun 4 Between the Sheets](/blog/star-schema-done-right-part-1-what-a-fact-table-actually-is-and-isnt/).