Data validation is a feature in Excel that allows you to create rules for what kinds of data can be entered in a cell or a range of cells. For example, you can restrict the data to only numbers, dates, text, or a list of predefined items. Data validation can also show an input message to guide the user, or an error message to prevent invalid data entry.
One of the common uses of data validation is to create dependent lists, where the items in one drop-down list depend on the selection in another drop-down list. For example, you can have a list of countries in one cell, and a list of cities in another cell, where the cities change based on the country selected.
To create a data validation based on another cell, you need to follow these steps:
- Prepare your data in a table, where each column has a unique header and each row has a unique value. For example, you can have a table like this:
| Country | City |
|---|---|
| USA | New York |
| USA | Los Angeles |
| USA | Chicago |
| Canada | Toronto |
| Canada | Montreal |
| Canada | Vancouver |
| UK | London |
| UK | Manchester |
| UK | Edinburgh |
- Define named ranges for each column and each unique value in the first column. A named range is a way to refer to a cell or a range of cells by a meaningful name instead of the cell address. To create a named range, select the cell or the range of cells, and then type a name in the Name Box on the left side of the formula bar. For example, you can name the first column as Country, the second column as City, and each country as USA, Canada, and UK.
- Create the main drop-down list for the first column. Select the cell where you want the drop-down list to appear, and then go to the Data tab, click Data Validation, and choose Data Validation from the drop-down menu. In the Data Validation dialog box, under the Settings tab, choose List from the Allow option, and then type =Country in the Source box. This will create a drop-down list with the values from the Country named range.
- Create the dependent drop-down list for the second column. Select the cell where you want the dependent drop-down list to appear, and then go to the Data tab, click Data Validation, and choose Data Validation from the drop-down menu. In the Data Validation dialog box, under the Settings tab, choose List from the Allow option, and then type =INDIRECT(A2) in the Source box, where A2 is the cell with the main drop-down list. This will create a drop-down list with the values from the named range that matches the selection in the main drop-down list. The INDIRECT function returns the reference specified by a text string, so it can dynamically change the source of the data validation based on another cell.
- Copy the data validation to other cells as needed. You can use the fill handle or the copy and paste commands to copy the data validation to other cells in the same column or row. For example, you can copy the data validation from B2 to B3 and B4, and the dependent drop-down lists will automatically update based on the selections in A3 and A4.
Here is an example of how the data validation works:
| Country | City |
|---|---|
| USA | New York |
| Canada | Montreal |
| UK | London |
In this example, the user selected USA in A2, and the drop-down list in B2 showed the cities in the USA named range. Then, the user selected Canada in A3, and the drop-down list in B3 showed the cities in the Canada named range. Finally, the user selected UK in A4, and the drop-down list in B4 showed the cities in the UK named range.
