If you have had experience in using Excel to create databases of information, it’s quite possible that at one time or another you have come across problems when you come to use this data in a meaningful way! More often than not, you’ll spend hours, weeks, perhaps months, collating all of this information into your lovely spread sheet, only to find that when you come to sort it, filter it, or mail merge it, your data doesn’t behave as you expected it to and you need to spend hours tidying it up before you can go any further. Steph Middleton guides us through validating data
“Keep it consistent” is a golden rule of Excel and the data validation tool can help with this. Data validation is an Excel feature that you can use to define parameters or restrictions on the data that can be input into a cell. You can also configure it to prevent the entry of invalid data, or better still, show an error message when such data is entered and prompt the user to enter valid data. These simple rules can help to keep your data uniform and make life easier later on.
Take, for example, a simple contacts database. You are collecting information in order to record renewal dates of memberships and print out address. Your table might look a little like this:
Now even within this small amount of information here, there are a number of potential variables or typing errors that can be made; postcodes, length of telephone numbers, date formats etc. and this is where the data validation tool comes in very handy!
Data validation can be applied to any cell in a spread sheet, or to multiple cells. In order for it to work, the validation criteria need to be entered before the data and so it is worthwhile thinking about this when you are building your database.
Let’s start with our membership database. We know the telephone number will be 11 digits long. In order to ensure that correct telephone numbers are entered, with no missed or extra digits, we can create a rule to check that each cell contains this.
To begin, select the cell or cells that you want to apply the rule to:
Clicking the data validation button will present you with the following box. You will see there is a list of validation criteria that you can use to create rules for your data entry. Once you have learnt how to use the data validation tool, it might be useful to try out all of the various validation criteria to get an idea of what is possible. In this instance, we are going to choose ‘text length’ from the drop down titled ‘Allow’.
The next drop down box titled ‘data’ sets the parameters for this criteria. Now as we want to ensure that telephone numbers are entered with their full 11 digits, we want to set the tool to allow only entries equal to 11 characters. You can see there are many other options available as well.
In the next box, you can enter a number, word, formula or range of cells depending on what you validation criteria may be. In this case, we will enter the number ‘11’. Here you can also check two options;
• Ticking the ‘ignore blank’ box will mean that empty cells will not be subject to the validation rule (for example if you don’t have this information or are yet to enter it)
• Tick ‘apply changes to all other cells with the same settings’ does what it says on the tin! If you are making alterations to a rule, this is useful as it will auto update all other cells that contain the same rule.
Next, you can set information boxes and warning symbols to explain to the user about information that needs to entered, or to inform them when incorrect data has been used.
Select the ‘input message’ tab and enter the message that you want the user to see when they click on the relevant cell
Use the ‘error alert’ tab to create a warning or information prompt when incorrect data is entered into the cell. As before, you can enter a title and short message and choose from 3 message styles from the drop down box
Click ‘ok’ and you will return to your spread sheet. If you click on a cell that contains the validation criteria, you will see that your input message appears. Then go ahead and enter some invalid data and see what happens!
(Note: bear in mind that the telephone number column needs to be formatted as ‘text’ in this case!)
Using the same process as above, you could also create a drop down list of entries for a user to select from. This eliminates the likelihood of spelling errors and typos and ensures efficiency when filtering and sorting your data. Do this in the following way:
Select ‘list’ from the list of validation tools and enter the values for your list in the ‘source box, separated by a comma. You could alternatively select a named range or pre-existing list that you have within your spread sheet
Click ‘ok’ and once again, click on a cell that contains the validation criteria. You will see a small drop down arrow. Click on this to select a value from the list and you will see that it is added into the cell.
So now that you know how to use the data validation tool, you can go ahead and explore the vast opportunities for using it and never have to waste time cleaning up your data again!
Steph Middleton is an experienced Virtual Assistant and MD of Outhouse UK