The Data Validation tool is a great tool that allows you to control what gets put into a spreadsheet.
To follow along go to 3.12
If YouTube doesn't work, click here
If for example we want to control what is input in cells A3 to A11 (red box) as we need the names to exactly match the spellings we expect we can do the following:
- Highlight the cells you want to control.
- Click on the DATA tab and Data Validation
- In the Allow dropdown, choose List
- In the Source box that appears point to the Approved list we have in I3 to I15 (note we have a few blank cells that are blue as inputs in case we want to add some names)
When you click OK, if you click on one of the cells you will get a dropdown list with your pre approved names. You can choose from this list.
You don’t have to use the dropdown. If you want you can type the name in. If you get it correct it will allow it. However, if you make a mistake (e.g. we typed Benn vs Ben below), it will give you can error message.
Controlling what happens if there is an error
You can actually control this error message.
If you highlight the cells again, click on DATA and Data Validation you get back to the dialogue box. Note that there is another tab for ‘Input Message’. In this you can enter a personalised message e.g. please choose a name from the drop down list.
There is also another tab for the Error Alert. As shown below, you can control the style of the error message. In the previous example it was set to STOP, i.e. if there is an error don’t allow it at all.
Below we have changed it to WARNING. This means that Excel will warn you that your selection is wrong but still give you an option to proceed if you want. It just warns you. Note that you can enter your own error message.
Other options available in Data Validation
Above we showed how to create a dropdown list. In this example we had a list in some other cells which we referred to. It is worthwhile to note that if you have a simple dropdown e.g. you want it to give the option of Yes or No, you can just type your options separated by a comma in the list box.
We also have other input controls we can use. As shown below your options include:
- Whole number (no decimals)
- Decimal (whole numbers and/ or decimal numbers)
- List (as above)
- Date (the dates that can be entered)
- Time (the times that can be entered)
- Text Length (how long text should be)
- Custom (formulas that allow you to change the above rules depending on what’s happening elsewhere).
In all the cases above, specific options appear depending on what you choose. So if you choose Decimals, then you control the cell so that the decimal input is:
- between (which 2 numbers MUST it be between)
- not between
- equal to
- not equal to
- greater than
- less than
- greater than or equal to
- less than or equal to
Nice trick for Dates and Data Validation
One of the ‘allow’ options is Dates.
You can control what date can be entered in a cell, but for us a more useful aspect is that you can control that a VALID, Excel date is entered. As shown elsewhere, you can enter a date that you think is a date but Excel treats as text. By using this aspect of Data Validation you can reduce the chance that a non compliant date will be entered.