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? Starting with Excel 2010, you can now add values to, or remove values from, all cells using a list for data validation by simply adding or removing those values from the source table. 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
=RNG_DATA_VALIDATION. - 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.
Caution: when removing values, do not simply select the table value and press delete. Blank table rows will disable Excel’s data validation and allow users to enter any old value they wish into the cell using data validation. Instead, select the record you wish to remove and navigate to Home → Delete → Delete Table Rows.
31 responses to “Dynamic Data Validation with Excel 2010 & 2013”
Great exercise!
My only comment: this will not work if the new addition to the source table is not entered directly. In other words, it will not extend the list if you have formulas in the source table [e.g. I have C1=CONCATENATE(D1,D2) ]
Or will it???
Actually, it will! If you enter a formula as a new entry into the source list, Excel will display a little sigma icon next to the cell. When you click on the sigma icon, Excel asks how you would like it to handle the formula. Your choices are:
If you choose Place Formula Inside Table, Excel will expand the table to include your formula, and you will notice that the result of the formula is displayed as a new entry in your in-cell drop-down list. So in your case, the concatenated value of D1 and D2 would appear as a choice in your drop-down list.
Hey Vlad, just wanted to point out a much easier version of CONCATENATE for you. Try doing C1 = D1&D2… you’ll be amazed — or at least, I was.
I am trying to create a worksheet where the columns all have a drop down list but once an item is chosen in that list it can not be duplicated in the same row for example; lets say you have column 1 thru 5 and each column had names in it John, Jen, Pete, Harry Linda and MIke and if I choose Mike in column 1 row 1 how do I block the row from allowing me to choose Mike again in that row but still have the ability to choose Mike in lower rows
It is complicated but possible. The contextures website has an explanation of how to approach it here: http://www.contextures.com/xlDataVal03.html
The site also provides a sample file that does exactly this, if you want to see what it looks like: http://www.contextures.com/excelfiles.html#DV0016
Good luck.
Wayne did you ever find out how to do this? I have the same question. I want to “rank” entries and once the rank is used it can not be used again.
Thanks a lot…. it is useful info….. This is an interesting site….
Thanks – glad you found it useful!
I want to keep my table in a separate workbook so that it can be shared as a validation table by several other workbooks (all in the same folder – if this is relevant). Excel 2013 seems to be telling me this is not valid.
How can I prevent copy and paste from overwriting my data validation rules? Please help
David,
There are a few ways to do this, but rather than re-create the wheel have you tried something like this first?
http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/
Great, I knew something like this existed and Excel help was worse than useless. My only gripe is why Excel doesn’t allow your to enter the Table[Field} directly into data validation in one step rather than a two stage approach.
Many thanks
Hi. I’m trying to create a summary that will pull results from attached worksheets based on my selection in a drop down menu. For instance…I have to business scenarios reflecting to different financial results on two separate sheets. I’d like to summarize each independently on the sheet using data validation with the options yes and no. So on that very same sheet with the data validation applied, If I select yes to option A, then it’ll display the results for A. If I choose B, then it’ll display results for B. If I chose A and B, then it’ll show both. Any ideas anyone???
Hi Willie,
You could use Excel’s
IFfunction to accomplish this task in a quick and dirty fashion. Excel’sIFfunction has the following format:=IF(logical_test, [value_if_true], [value_if_false]). Let’s say you setup the data validation with the text Option A or Option B in cell A1, the revenue for Option A is cell A1 on Worksheet A, and the revenue for Option B is cell A1 on Worksheet B. Then you would put this formula in each cell on your summary worksheet where you wanted to either display a value for Option A from Worksheet A, or a value from Option B from Worksheet B:=IF(A1="Option A",'Worksheet A'!A1,IF(A1="Option B",'Worksheet B'!A1,0)).If Option A is selected, Excel will display the result from Worksheet A, cell A1. If Option B is selected, Excel will display the result from Worksheet B, cell A1. Otherwise, Excel will display zero.
Hi guys,
I have been searching for two days for someone’s data validation steps that makes sense and I am honestly excited about finding this article. Thanks for posting. I need some help please, I am busy making a spread sheet that will:
1. Allow the User to select a shape in my first drop down list. (For ex. User selects a square shape.)
2. After they have selected a shape I need a table to appear asking the user to enter the sizes required to do calculations for that shape. (For ex. Length, width, height.)
3. I want to display to my user the Area, Volume and other qualities unique to that shape in a summary.
My approach:
1. I Created a table on Sheet 1 and a drop down list for the different shape options on Sheet 2.
2. I created another table on Sheet 1 displaying measurements required for that shape.
2. When I select the Shape on Sheet 2, my next drop down list displays all the measurements required. But, it shows them one by one, as I can only choose either length, or width or height from that list. and I need it to display all 3 options at once.
Lol, can anyone maybe help? Busy confusing myself ..
Thanks,
Karen
Hi Karen,
Sorry for the terribly delayed response – were you able to figure this out, or do you still need help? If you figured it out, would you mind posting how? If not, let me know and we can try to get you setup properly!
I’m trying to do a data validation using a dropdown box where I would have all 12 months in the year in my dropdown. The month would refer me back to a different sheet on my workbook that has all of the data. The data for each month is in columns H = January thru S = December and the month is listed on line 2684. If I select January from my dropdown in cell A2 what would my formula be to to grab the January cells that I want from my Data sheet in column H?
Jason,
Have you tried using VLOOKUP that uses as its lookup value whatever is selected in your dropdown, and the range is the one you described in columns H through S?
I am trying to create a formula that will auto-populate a cell with data from a different sheet. Ex: 123=ABC From Sheet 2
If on Sheet1, I enter “123” in cell A2, I want ABC to auto populate in cell A1
Please help I have tried everything, I am a little familiar with excel, any suggestions?
Maury,
You can do this using VLOOKUP. On Sheet 2 type “123” into cell A1, and “ABC” into cell B1 – this is your “lookup table,” and you can have any number of rows in it. On Sheet 1, in cell A1 type
=VLOOKUP(A2,Sheet2!A1:B1,2,FALSE)and press Enter. At first cell A1 should display#N/A, but when you then type “123” in cell A2, “ABC” will auto-populate in cell A2.Thanks Ryan!
You’re very welcome!
Hi, Ryan!
How do you do this for a large range. I have employee ID numbers in one column that are listed in ascending order, with employee names in the column 2 beside their number. I need on one worksheet to automatically populate the name of the employee based on the number as I am only given data with the employee ID number and have to convert that. I don’t know why this seems so difficult than other complicated formulas I have used. Each cell needs to be able to produce any name based on any of the number put in.
Hi I am trying to use data validation within a table and need it to extend as rows are added to the table. Is there a way of doing this?
Hi Alix,
There is an easy way to do this starting with Excel 2010 and later, when Microsoft introduced Table objects. As an example highlight cells A1 through A10, navigate to the Insert tab on the ribbon and select Table. Excel should automatically format your selected range and add “Column1” in cell A1 as your table header – this range is now a Table object. Now select cells A2 through A11, then add a data validation that only allows whole numbers greater than zero. After clicking OK, test your validation by entering 0 in cell A11. You should get an error similar to “The value to be entered must be a whole number greater than 0.” Click Cancel, then right-click on cell A11, select Insert > Table Row Below, and then try entering 0 in the newly created cell A12. You should get the same error without having to add a new data validation on cell A12.
With Excel Table objects, any data validation that was applied to an entire column will automatically apply to new cells when Excel adds additional table rows. Hope this helps.
Hi ! I am trying to create a data validation list on a separate Workbook to be shared by many other Workbook within the same folder.
I have done it in Excel 2007, but recent upgrade to Excel 2013 jeopardized all my previous data validation formating and now it does not work anymore.
Could you provide some tips to enlighten the audience here ?
Thank you.
Hello all,
I am not sure if this has already been addressed and if it has can you please let me know where I can go to find the answer. I am trying to set up a workbook to keep track of all the required training for all of the members in my work center. I have a fairly large work center of 40 members and a list of 35 courses that need to be completed in 12 Month, 36 Month or 48 Month intervals. In the workbook I would like to haves a drop down list in column “C” that that will let me choose either 12,36 or 48 months. That part is fairly simple; but here is what I am having a problem with (and I am not even sure if this is something that is able to be done in excel, but …) I would like to be able to select the required training interval to for particular training course, and have that selected interval automatically assign a set of conditional values to all of the cells that are in the same row so that those cells will automatically change color from green to yellow to red depending on the date the member completed their training and the remaining time allotted for that particular training course. Any help would be greatly appreciated.
Thank you
Hi, I’ve followed your instructions above, all worked great until I protected the worksheet locking all cells except the column with the table (the dropdown is on a different tab). The table length doesnt expand to include a new entry when the protection is on – is there any way around this? The spreadsheet will be used by number of people and other columns need to be protected.
I need a combination of data validation and dynamic ranges. I can easily set up a data entry data validation field where users are limited to the values in a named range on another sheet. Check. I can also create a second data entry field where values in the second field are related to the value chosen in the first field. Check. My need is to allow users the ability to enter a new value in both fields AND have the supporting named ranges add the new entry and display it in the data entry fields. I do not want to return to the source table again and again to add rows. I want the hierarchical validation to work but also add entries to my source table(s) without manually renaming the ranges. Is there VBA or anything that will accomplish this? I am more interested in keeping the hierarchy than dis-allowing new entries.
I need a form of data validation that has 5 levels each of the four lower levels referring back to the first, so that if Primary is chosen in the first level the 2nd, 3rd, 4th and 5th levels all have choices relevat to the Primary sector. If Adult is chosen then the choices in the lower levels are relevant to the Adult sector, etc. Is this possible?
Good Day,
I have a work book with multiple work sheets, each containing its own table with material specific data. The work book is intended to function as a database of materials in our storage room. There are many items for each type of material, this is why each sheet has its own table. The table types are as follows:
Belt, Roller, Pneumatic, Electrical, Automation, etc
Within each table there are the following headers:
Part Type (Col 1), Part Number (Col 2), Description (Col 3), Equipment (Col 4), etc
The User Interface on Sheet 1 is set up so that the user can select a Part Type – Cell H3 (refers to a table). The next list is “Description” – Cell H4. Then Equipment – Cell H5, and so on. Ultimately, the user should select the Part Type from a pre-determined list. The “Description” Drop-down list data would be pulled from the table referenced in Cell H3 and list options from the Descriptions column of that table.
I hope my attempts at describing the scenario have not created confusion. Let me know if you require more information.
Any assistance would be most appreciated.
Thanks 🙂