Data validation and drop-down lists

Set up data verification

With the option “Check data” we play it safe and can set that user of our table enters only valid numbers or values. It is also necessary for the creation of drop-down lists.

In our example, we want to add the value added tax (VAT) of 19% to a price. The formulas are already ready, so the net amount only needs to be entered in cell D2. This calculation does not work with all values. If a user does not enter a number, but the text “one” – an error will inevitably occur. With data validation we can force users to enter the correct values.

For this reason, we select cell D2 and click the Data Validation button on the Data tab in the Data Tools area.

The Data validation dialog box opens. In the Settings tab, we select the Decimal option for the Allow field – this allows decimal numbers (numbers with decimal places) to be entered. For the Data field we set the option greater than and for Minimum we set the number zero to prevent users from entering a negative number.

Then we click on the Error message tab.

If the user enters a wrong number, an error message should explain why the entry was incorrect. Therefore we choose the type Stop and give a clear hint in the Error message field. Then we click OK to confirm the input.

And this is what the result looks like when an incorrect value is entered:

The user clicks Repeat and enters a valid number:

Create dropdown list in Excel

In our example we enter the condition of used cars into a table. The condition descriptions should always follow a pattern. This means that the descriptions are selected from a list of possible states. This list is called a dropdown list because when you click on it, a list of choices appears downward. This tutorial shows how to create such a list.

First, we create a list of all the choices for our dropdown list:

Note: This list can also be in another spreadsheet.

Then we select the entire list and navigate to the Formulas tab in the Defined Names area and click the Define Names button.

Note: Alternatively, we right-click on the highlighted area and choose Define Name…

The Define names dialog box appears. We enter State as the name and confirm with OK.

In the next step, we select the cells whose input we want to restrict with our list.

We click the Data Verification button on the Data tab in the Data Tools section.

In the Data validation dialog box we select the List option in the Allow field. At Source we enter the exact name of our list: =State (case sensitive!). As always, confirm the entries with OK.

We see that the edited cells have a box displayed on the left side when selected. This is the drop-down list selection.

And this is how the table might look when the dropdown list is used.