It’s Christmas, and like many of you I find myself thinking about dynamic data validation using the structured tables that Microsoft introduced in Excel 2010. Welcome to my life.
What’s Data Validation?
When I say data validation I mean the ability to specify exactly what kind of data users may (or may not) enter into certain cells. This is particularly useful when you are creating forms or spreadsheets for other users to fill out, and you want to make sure that they enter the data you were expecting. For example, you can use data validation to prevent other users from entering numbers or special characters into cells or fields when you only wanted text. Using data validation helps keep your data clean, and everybody should like clean data.
The Old Way: Static Data Validation
In the olden days most people created a one column “source list” on a separate worksheet or somewhere within their current worksheet to contain the source values for in-cell drop-down lists (i.e., data validation). For example, if you only wanted to allow users to enter the values “A,” “B,” “C,” or “D” into a particular cell, you would first type a list of those values, one per line. You would then select the cell on which to use data validation, navigate to Data → Data Validation, select “List” under “Allow” and your list of letters under “Source.” It would look something like this:
The problem with this approach is that if you wanted add or remove values from the in-cell drop-down lists, first you had to find and edit the source list, then you had to manually update the range (i.e., the “Source”) for every single cell where that source list was used for data validation. In our example above, in order for us to add “E” as an available value in our in-cell drop-down, first we would have to add “E” underneath “D” in our Source List then update the Data Validation “Source” range for our “Select a Value” cell to include cell A12.
In other words, it was a pain in the ass. If you had ten cells referring to a single source list, updating those ten cells to include the updated range might not be so bad. But what if you had 100 cells, or 1,000 cells, all referring to that source list? . No need to find and update the “Source” range for all of the cells using that list for Data Validation. Excel will take care of updating all the cells referring to this source list automatically.
The New Way: Dynamic Data Validation Using Excel Tables
- Highlight cells A1:A5 and navigate to Home → Format as Table, and select a table format that suits your style. If you already have a header for your table, check the “My table has headers” box, otherwise leave blank.
- If you left “My table has headers” blank, rename “Column 1” to something more meaningful. I typed Data Validation. I then entered values 1 through 5 into the table, but you should enter any values that you actually want to use.
- Select any row on the table and navigate to Table Tools → Design → Table Name: and rename the table to something meaningful. I entered DATA_VALIDATION.
- Select all records on the table (excluding the header), then navigate to Formulas → Define Name and give the highlighted range a meaningful name. I entered RNG_DATA_VALIDATION. Notice in the “Refers to” section is the name of our newly created table.
- Select cell C1 and navigate to Data → Data Validation. In the Data Validation dialog box, on the Settings tab, choose List under “Allow.” Under “Source” enter the equal sign (=) then the name of the newly named range (note, do not use the table name). In my case I entered
- Click on the Error Alert tab if you want to display a pop-up box with an error message when the user enters invalid data. I find it’s usually good practice to offer some sort of helpful error message, such as “Invalid Entry: Please choose a value from the in-cell drop-down list.”
- Click OK.
You should now see a drop-down arrow appear when you select cell C1; when you click that drop-down you should see the values from the table created earlier. Test your data validation by entering values not contained on your table – if you did it correctly, Excel should display an error message when you try entering a value not found on the in-cell drop-down list.
The most important point here is that you can easily add values to this list by simply adding new values to the source table. Since our data validation references a named range, and since that named range is based on a named Excel table, we never need to adjust the data validation range if we want to add more values to this list. Excel handles it all behind the scenes.
To see what I mean, select the first available cell below your table, type a new value and press Enter. In my case I selected cell A7, typed 6 and pressed Enter. Excel automatically adjusted the table size to include this new record. When you click on the in-cell drop-down list in cell C1, the new value should appear.
Notice that we didn’t have to go back into the data validation settings for cell C1 and adjust the range to include cell A7? This might not seem like a big deal using data validation in only one cell, but imagine how much time (and headaches) you can save when dealing with multiple (say a few hundred or more) cells using list-based data validation.