Invalid and inconsistent data is one of most common problems in Spreadsheets. If the data supplied is not in the right format or is not entered consistently then any following formula’s or calculations will either not work or worse still provide a result that is not correct.
Excel provides a built-in Data Validation feature that can be included to any spreadsheet. Data Validation provides error checking and instructions for the user as they enter data to a spreadsheet. In this post we will demonstrate some simple examples of Data Validation on a customer address list.
We start with a basic list of customers and addresses, we want to make sure that the values supplied for “Post/Zip Code” and “Country” are valid values and consistently entered to the list. We will use Data Validation on Column D and Column E to ensure that this happens for each new entry.
We will now create a Data Validation rule for the “Post/Zip Code” column to ensure that only a valid whole number can be entered to those cells. Select the first cell in the column D7 and then to access Data Validation click on the “Data Tab” then click on Data Validation. For this validation select “Whole Number” and “Greater Than” to ensure that all entries in the cell are numbers greater than 0. Then click OK and the Data Validation will be applied for that cell.
Now to test the validation for that cell try and enter something other than a whole number. The following error message will be returned. Later in the post we will demonstrate how to customise this error message to give specific instructions to the user on the error that has occurred.
Now we will include Data Validation for the Country column. This validation will be based on a list of countries in our spreadsheet. In another worksheet a list of countries is developed and named “ReferenceCountryList”.
Back on the Customer List worksheet we select the first cell in the Country column which is E7, we then select the Data Tab, Data Validation and specify a “List” from the options for validation. In the source we specify the name of the country list we created earlier.
Now when that cell is selected a “Drop Down” option is presented for selection. The user can only select a value from the list or they receive the standard error message.
We can also customise the “Input Instructions” and “Error Message” that is presented to the user for any cell that has Data Validation. Access the Data Validation options for the “Post/Zip Code” cell D7. Then for the “Input Message” include any details that need to be described to the user for completion. For this demonstration I have included the following…
Which results in the following display on the worksheet when the user has selected that cell for entry
We can also customise the “Error Message”, on the Data Validation dialog box specify the following
Which shows as the following error message when the user provides an invalid entry.
Lastly we need to make sure that all cells in the columns that need validation have the required validation in place. To do that select cell D7 and E7, then select Copy, now select all the cells that require the Data Validation, Right Click on the selection and select Paste Special. Then selection “Validation” and the Data Validation will be applied to all cells in that selection.
Click on the following link to download a copy of this sample Data Validation Workbook
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.