Subscriber only lesson.
Sign up to this course to view this lesson.
Data Validation explained.
When to use
This feature restricts what end users may type into a cell. Similarly to styles: many aren’t aware of this function in Excel, but once you use this functionality and understand what it can do for you, you never go back to whatever it was you were doing before.
- Only allows certain entries in a cell
- Can use lists similar to drop down boxes
- Difficult to identify
- Does not update automatically
Example 1 - List
- To access data validation, from any cell in Excel:
- On the Data tab of the Ribbon, go to the Data Tools group and click the Data Validation icon (ALT + A + V + V)
- Change the selection in the ‘Allow’ drop down box to be ‘List’.
- With ‘List’ selected, the dialog box prompts for a source for the list. In the illustration, the entries have been selected from the data in cell references from the worksheet
Example 2 – Whole Number
- Instead of selecting ‘List’ change the selection in the ‘Allow’ drop down box to be ‘’Whole Number’
- In the ‘Data’ dropdown list change select ‘Greater than’
- In the ‘Minimum’ box type ‘0’
- With the ‘Show input message when cell is selected’ checked, if the end user selects the data validated cell the message typed in here will appear. This can make data inputs in a model much simpler as end users are ‘spoon fed’ with a pop-up box detailing what to do. A message can be typed in here such as the one shown above “Hint - Enter a positive whole number”
- The third tab selects what to do if invalid data is entered in the cell. This alerts the end user when an invalid entry has been made. A message can be typed in here such as the one shown above “Incorrect entry - Enter a positive whole number”
Lesson notes are only available for subscribers.