Excel: Use Table As Data Validation List Drop-Down



Data validation: How to make a cell to select values from a drop-down

Creating a table in  MS Excel‌  can be very frustrating if you repeat the same input over and over again.

At the same time, it is very convenient if you just select the input contents from a certain drop-down list.

Let's see how to use the validation rule, a function that makes it possible.

The table below is an expense list of an individual. The date, type of expense, and value are entered every time some money is being spent.

Using this table, let's set column B so that the expense types can be selected drop-down list, and only from there, to avoid setting them up every time, and avoid any mistake in data entry.

Solution 1: Create a dependant drop down list by direct input

Let's start by Selecting the cell for which we want to set the drop-down list.

Select Data Validation from the Data tab in Excel, it might be displayed as a small icon only.

In the settings tab, display the allow drop down option and select the List option. This will allow you to create or select a list of allowed values for selected data cells.

In the source field, simply enter the values you want to allow, separated by a comma.

Simply validate by clicking OK, and your drop-down validation list will have been created for the selected cells.

If you select one of these cells, an icon will appear at the end of the cell, and by clicking on it, the drop-down validation list will be displayed, letting you select the value to be automatically entered in the cell based on your direct input list.

Solution 2: Create a dependant drop down list by selecting a range of existing values

Similarly, instead of typing the values manually, you can specify a range of allowed values by selecting a list that already exists in the workbook.

A best practice when creating several drop-down validation lists in  Microsoft Excel‌  is to have one or more specific sheets that contains only the various validation lists, to make it easier to find them and update them.

After have created your list of allowed values, in our example on the same worksheet to make it visually more accessible, select the cells on which the Data Validation should be enforced.

Then, open similarly the Data Validation menu from the data tab, select the list type in the drop-down menu, and for the source, instead of entering your own values, click on the button on the right side of the field.

You will be redirected to your worksheet, and all you have to do is to click on the first cell of your Data Validation table, move your mouse to the last cell containing one of the valid values, while holding your click, and release the click only after you have reached the last cell of Data Validation values.

If you have created a table for your data validation values and named it, you can use the table name instead of cells range to reference it

The values you have selected that way will be referenced in your Data Validation list, and you can simply add values to your Data Validation - or remove them - by updating that list.

Set data validation entries on a different sheet


In order to better organize your workbook, it might be easier to create your Data Validation lists in a specific workbook, so you can easily find and access them, and won't have to wonder where they are set.

Put your data there, select the table, and use the option tables:table in the insert tab, to transform your list of values into a table that will automatically be rezised as you add or remove values - and so will be your data validation.

Then, click anywhere in your stylized table, and enter a table name in the table design tab.

This will allow you to reference this table anywhere, including in a Data Validation list, by simply entering the table name instead of the range!

How to allow blank cells in a data validation list

If you want to allow blank values in a field that has Data Validation enabled, you must select one of the cells for which Data Validation is activated, and go to the data tab, to open the Data Validation menu (see above where the button is).

There, make sure that the ignore blank option is checked, and click on apply these changes to all other cells with the same settings before clicking OK.

You will then be able to empty a cell by selecting it and removing content, without getting an error message specifying that cells content is not allowed.

How to disable blank values in a data validation list

If you do not want blank values to be allowed in a Data Validation drop-down list, and get an error message the value in this cell is invalid or missing whenever you select it, start by select the range of cells on which Data Validation applies, or any cell in that list.

Then, open the Data Validation menu in the data tab, and uncheck the ignore blank field. If you have selected only one cell, check the apply these changes to all other cells with the same settings before clicking OK.

Back at your Data Validation drop-down list, the error will appear - and it will not be possible to leave a cell blank anymore.

How to remove a data validation drop-down list

Whenever you are done with limiting the range of allowed values in a list, select any cell in that list.

Then open the Data Validation menu from the data tab - see above in first section how to do it - and make sure that the apply these changes to all other cells with the same settings option is checked, otherwise you will only remove the Data Validation on the currently selected cells.

Then, click on clear all and the Data Validation you have selected will be removed from the  MS Excel‌  workbook!

VBA: Add validation list

Adding a validation list with VBA is as easy as using below function, where A1 is the cell in which data will be validated against the Data Validation list stored in cells B1 to B5.

Range("a1").Validation _ .Modify xlValidateList, xlValidAlertStop, "=$B$1:$B$5"

By using the advanced properties of the VBA Data Validation list function, it is possible to create multiple dependent drop down lists in Excel VBA that each validate various fields against various Data Validation lists, and have their own titles, error titles, messages and error messages.



International Business Consulting
About the author - International Business Consulting
Yoann is an International Business Consultant, facilitator and coordinator. He worked more than 10 years in global business, supply chain, financial, SAP ERP projects, Web and SEO optimization for various industries, such as logistics, cosmetics, consumer goods, beauty care, or fashion, in more than 30 countries around the globe.
 


Comments (0)

Leave a comment