Objective: In his latest ExcelZone tutorial, Simon Hurst aims to make it quicker and easier for users to enter correct data.
The perils of data entry
Often, the real threat to a spreadsheet working as intended comes when the creator unleashes their work on unsuspecting users. Although, when you create the spreadsheet, it might seem perfectly obvious that the user should enter a number between 1 and 12 in the cell labelled 'Month', and it might seem equally obvious to the user that they should enter January, Jan or even Yes. Excel's data validation feature provides a straightforward, though far from infallible, way to guide users towards entering the expected value.
We'll start by using the month number case as an example. We are using a month number in cell B15 to choose the results for that month as the basis for a chart. We want the user to enter a whole number between 1 and 12. We can create a prompt to do so and also check whether they do so correctly using the data validation option in the Data Tools group of the Data Ribbon tab:
When we click on data validation, the dialog appears with three tabs: Settings, Input Message and Error Alert. We will look at each tab in turn.
This is where we choose the type of entry we want to allow and set any restrictions as to the actual value. First of all, the Allow dropdown lets us choose from a list of data types:
Whole Number, Decimal, Date, Time and Text length are reasonably self-explanatory and we will look at List and Custom in more detail later. We want to be as precise as possible so need to choose 'Whole number' in order to avoid the entry of 7.32 as a month number.
Next, we can set the limits as to the actual value to be entered. The availability of these choices depends on the data type chosen in the Allow box. The dropdown is disabled for List and Custom. In our case we want to choose 'between' which displays a Minimum and a Maximum input box. We can type in values directly, or refer to the contents of cells or even enter a formula in each box.
Generally, if there was any chance that the limit value would need to be changed, then the box should refer to a cell that contains the required value to make it easier to update the limit when necessary. Here, we make the assumption that we will continue to produce results based on a 12 month year, and therefore enter 1 and 12 as our upper and lower limits.
The next tab allows us to guide the user to entering the correct value by displaying a custom title and message whenever the cell is selected. Again, we want to be as precise as possible with our message so we specify 'whole number' rather than just number:
The last of our three tabs allows us to control the message the user sees if they transgress the rules that we set. If we don't enter our own message, Excel will inflict its own generic message on our hapless user. Here we can see our Input Message displayed as the cell is selected, and the generic error message is displayed because we have entered a number outside the designated limits:
In order to give the user more helpful guidance we can type in our own Error Alert. Although it can be tricky to avoid resorting to sarcasm and entering something along the lines of 'Sometimes I wonder why I bother…', in almost all cases it would be preferable to restate the original Input Message:
Note that the Error Alert tab also includes a Style dropdown. A cursory look might suggest that all this does is choose the style of the icon displayed in the message box: for Stop a cross in a red circle, for Warning a yellow triangle with an exclamation mark and for Information, a lower case i in a blue circle. However, Style is much more important than that. It also controls the options presented to the user as to how to proceed if they have entered an 'error' value. Only the Stop style doesn't include an option to allow the user to continue to accept the value entered:
Here are some other examples of data validation settings.
This uses Allow: Date, less than or equal to and the TODAY() function to prevent the user entering a future date:
And here we are ensuring that the user enters a five character text string, perhaps as a client code:
Finding cells that use data validation
There is no obvious sign that a cell has had data validation settings applied to it until you click on it. If you want to identify cells that use data validation, you can do so using the Special section of the Go To dialog. Press the F5 keyboard shortcut to display Go To, click on the Special button and then choose data validation:
You can choose to select all the cells on the worksheet that have any data validation setting, or just those that have the same data validation setting as the currently selected cell.
Copying data validation settings
By default, copying and pasting a cell that has a data validation will also copy the data validation settings (or absence thereof). It is possible to copy and paste just the validation settings using the appropriate option in the Paste section of the Paste Special dialog:
Data validation and lists
As well as controlling the type of data and range of values allowed, it is possible to use data validation to enable a user to select an item from a list. When you select List in the Allow: dropdown a Source: input box will appear allowing you to specify a list of items separated by commas or, more usually, a range of cells containing the required items.
The way data validation copes with references to other sheets varies between different Excel versions:
Although in Excel 2007 you cannot select cells from a different sheet directly from the ‘Source’ box, if you type in a reference that includes a different sheet name, it will be accepted:
Excel 2010 onward
In Excel 2010 and later versions, you can select an area on another sheet directly from the Source: box.
Using Tables and Range Names with Lists
There is a related issue that affects the automatic inclusion of items added to existing lists. If you just set your list up as a normal block of cells, adding an item to the end will not lead to its automatic inclusion in your data validation source. However, if you turn your list into an Excel Table, additional items will be automatically included in the dropdown list, as long as the source is on the same sheet as the data validation cell. If the Table is on a different sheet, added rows will not automatically appear in the list source. When the list source is on a different sheet, you can use the ability of a Table to automatically expand to include new rows by allocating a Range Name to the Table column to be used, and then referring your list source to that name.
As the Table expands, the range of cells the Range Name refers to will adjust to incorporate the new rows which well consequently be included in the Data validation dropdown whichever sheet it is on.
Other data validation features
The Custom option in the Allow: dropdown displays a formula box. The allows the entry of a formula which calculates the value or range of values that will be accepted. In this example, Formula: checks that the third character of the value entered in cell Q19 is the letter A (upper or lower case):
Circle invalid data
The data validation dropdown in the data ribbon includes an option to Circle Invalid Data. This will draw red circles around the first 255 in the worksheet that fail data validation criteria. There is also an option to clear those circles: