Use a Table to Populate a Data Validation List
Data validation tools can save you a lot of grief when populating data into a spreadsheet. For example, consider the following table.
Do you immediately see a difference between the two entries for the East region? Line 4 has a space before the word East. Is that a problem? Yes, if you are using this table to feed various reports and expect all records for the East region to be grouped together. To the human eye they appear close enough not to matter, but to the spreadsheet they are two totally different records and will not be grouped together.
Human error will creep in, so why not take advantage of the data validation tools that Excel provides to minimize the opportunities for errors in your data? In this case, a dropdown list of available options that the user must choose from would have prevented this error. For example, perhaps the list could be set up as follows:
While this would work, what happens when a new division, perhaps South, needs to be added? You would have to go and edit every area where this data validation list was used (assuming you can remember them all).
In a recent post we showed that one key advantage of tables is their ability to expand automatically to include new data. Let’s see how we can use that to make our data validation lists more user-friendly.
First, we will need a table with the options we want to include in the list, such as I have created here.
Next, we need to create a named range for the data portion of this table (e.g. East, Central, West), since data validation cannot read a table, only a named range.
Note a couple of things about this step. First, we highlighted the three entries in the data portion of this list (K2:K4), then we created a new named range (Formulas, Name Manager, New). Note that in the Refers to: box it shows “=Region[Region]”, not K2:K4. Because we had highlighted all of the data portion of the table, Excel correctly assumes that we want to identify not just the three particular cells highlighted but the entire data section of this one-column table. Hence it uses the complete name to reference it (namely, Table[Column]) and shows “Region[Region].” This will be important when we add data to this table.
Second, note that the name we gave to this named range is Regionn, with a second n to distinguish it from the table Region. You can use whatever naming convention you choose to keep it straight in your mind whether you are referring to the table or to the data portion of the table. This is necessary as you can’t have both a table and a range with the same name in a workbook.
Now we are ready to use this in setting up a data validation list. Highlight the cells in the Region column of the table that you want to apply the Data Validation to, click Data, Data Validation, choose List in the Allow box, then in the Source box type “=Regionn” (or whatever you have named the range you wish to use).
Now notice that Excel recognizes that cell B4 doesn’t match the data validation criteria, as evidenced by the error warning message.
Now select the correct entry off the dropdown list, and you are good to go.
When entering the next line of data, if the region you wish has already been entered in the table you don’t have to use the dropdown list, just type the first letter or letters and let it autocomplete your entry for you. As the table expanded the data validation list expanded along with it, preventing you from entering information that does not appear in the data validation list.
Finally, and the reason for this post, if we need to add a new region to our data, such as South, all we need to do is add it to the Region table by typing it immediately below the last line of that table (K5) and hitting Enter, let the table automatically expand to include the new entry and the named range expand to also include it, and then pick the new entry off the dropdown list.
It is a good practice to have all such tables being used for data validation on a separate sheet of the workbook, perhaps in the order they appear in the database you are populating, so that they are safely out of the way of the user but easily available to add to as needed. It might also be a good idea to rename that sheet something obvious (e.g. Control Tables), then protect that sheet so it isn’t easily adjusted by unauthorized users.
A little forethought in this way will simplify the task of those adding to your data and will greatly improve the accuracy of your data.
