Excel Data Validation feature is one of the excellent tools that allows effective options for flexibility. Now let us take an example that limits input to the static list departments.
- First, select the input cell.
- Now on the Data tab select Data Validation option. A single click on Data Tools group will open the Data Validation dialog.
- Now click on the List button to display a list of options.
- To display a static list enter the values separated by a comma in the Source control. If the list needs to be in a specific order, enter the items in that order.
- Now click on OK button and use Data Validation control to see the static list. Selecting an item sets the control’s value.
The above process seems to be very simple. But if you need to update the list occasionally you have to search for a reasonable solution. By making the dynamic list it is not required to update the source reference. You can do this operation by two ways i.e. using a named range or using a Table.
A named range always comes with some inherent problems i.e. some are easily managed and some others not. A Table is the easiest option. If you’re using a .xlsx version, you don’t have to work hard on this simply press the skip button.
Using a named range:
To start with the dynamic range solution you have to enter the list and then name as follows:
- First, select the list and the header.
- Now click on the Data tab and from the Defined Names group select Create from Selection option.
- After that accept the default and go to Top Row and then click OK.
Now you can base your list on the source as follows:
- First, launch the Data Validation dialog box and choose the List from the Allow dropdown.
- In the Source control, enter the name of the list and click OK.
Using table source:
Firstly create the Table as described below:
- Click anywhere inside the list and click the Insert tab. Now click Table option in the Tables group.
- Now in the resulting table check the My Table Has Headers option and hit OK.
- Now enter a name for the Table using the Table Name control in the Properties group.