Learn how to ensure data consistency and accuracy within your worksheets using Data Validation, a powerful tool that establishes standards for data entry across specific columns or fields in your table.
Data Validation is a powerful tool for making sure your table data is consistent and adheres to standards you set – for what kind of data and which values can be entered into specific columns, or fields, in your table. The use of Data Validation also makes data entry simpler for people who add records to your worksheets.
As shown in this table of insurance policies, I’ve established a data validation for the Region field. Each cell in the column displays a drop list when clicked, offering the only acceptable values for that field. East, Northeast, Midwest, and Central are the only acceptable values – so nobody can enter North or West or use an abbreviation such as NE for Northeast. This matters, because if the table were to be used in a PivotTable or otherwise filtered for certain values, wanting to see all of the Northeast region policies won’t include any records with just North or the abbreviation NE in the field.
I’ve also set one for the Policy Number – that forces the entry of 6 digits – so that nobody entering in a new policy or editing an existing one can short the value or put in too long a number.
To set one up, all you have to do is select the field – every cell in the column, by clicking in the first cell below the field name and then pressing Ctrl + the down arrow twice. We press the down arrow twice to go past existing values to the last cell in the worksheet for that column. If you’re setting up a new table and don’t have any records yet, you only need to press the down arrow once.
With the entire column selected, go to the Data tab and click the Data Validation button. If you see the menu, just reselect Data Validation from the 3-item list. I’m going to set a Data Validation rule for the Construction field, so that only approved types of construction can be entered into that field – no misspellings, no different terminology, and no insuring an unacceptable type of structure.
In the resulting dialog box, we choose the type of validation we want to set up. You can experiment with all the options, but we’re going to make 2 different kinds – first a List and then a Whole Number validation. For the Construction field, List is the best choice, so I’ll pick that.
Note that the dialog box changes a bit, and now there’s a Source box – which is where Excel is asking to know what the values for the list are. You can type them directly into the field or cite a range of cells in another worksheet – either way. I’ll type the values here, right into the dialog box – Fire Resist, Frame, Masonry, and Metal Clad.
Next, I’ll click the Error Alert tab, to create the error message that appears if someone tries to enter a value other than the ones in the list. You can also create an Input Message, which appears before anyone attempts to enter anything into any cell in the column – but this can be really annoying to users, so I tend to only use the Error Alert tab.
Here, I choose how strict the Validation rule will be – choosing Stop means nobody can enter anything other than what’s in the list and can’t move on without either typing a correct value from the list, choosing from the drop list, or leaving the cell blank. The other two alert options aren’t quite so strict, so if it’s important, choose Stop.
After choosing the type of alert, enter the message you want someone to see if they violate the validation rule – I’ll type “Make a choice from the drop list” – so anyone who didn’t notice the drop arrow knows it’s there, and it also explains the mistake they made. Once they see the drop list, they see which values are acceptable. Looking at my Data Validation in effect, I’ll make a choice in one cell and then try to enter the wrong amount in another – producing the error message.
Now let’s make sure nobody can set up a policy for an Insured Amount of less than $100,000. Selecting the cells in that column, I’ll click Data Validation and choose Whole Number as the type of validation.
Next, after choosing Whole Number, I set it to Greater Than or Equal To rom the Data drop list, and then enter 100,000 as the Minimum.
I can then set the Error Alert to Stop, and type my error message. That’s all there is to it! Attempt to enter the wrong amount, and the error message appears.
You can edit any Data Validation by reselecting the column cells and using the Data Validation dialog box to change your settings. To get rid of a validation entirely, click the Clear All button.
Oh, and a TIP! If you’ve already got data entered and you want to see which existing records don’t work with the new Validation rule set, select the data and then click Circle Invalid Data. Excel then highlights the records that don’t match the new rule’s parameters. You can then edit those cells’ entries as needed. To get rid of the circles without editing the data, choose Clear Validation Circles.