Latest posts by techwriter (see all)
- 7 Tips to Write Great Essays - January 5, 2018
- How to Eliminate Abstract Nouns - January 3, 2018
- 3 Important Differences Between Academic versus Technical Writing - January 1, 2018
By BJ Johnston
One of the most important factors in good data analysis is clean data. Dirty data can make even simple analysis tasks become much more complicated than they need be, or even give incorrect trends in data.
Ensuring that data is entered correctly is best carried out by enforcing data validation and essentially guiding users to enter the data to your specific requirements. Data validation can be easily controlled in Excel. Here I am going to show you how to force your users to type into Excel in UPPER case. I have found this most useful for postcode entry for the postal services.
We can force the upper case entry by using the EXACT Function in Excel along with Data Validation. Here’s how to use it:-
Select your list of cells that you wish to apply data validation to, for example it could be a list in cells A2:A20. These will be the cells that we will be using for typing postal codes in upper case.
To apply the validation formula
Data Validation Button
Select Allow Custom Validation Criteria
Type the following into the formula bar
Then just hit Ok. You then need to drag the formula down the length of the column of cells you want this validation to apply to. In our case it is A2:A20. So, drag the formula down to A20.
That’s all well and good, the user will be warned if they do not enter upper case characters into the specified cells. If we leave this validation as is they will receive a generic message that will warn them that the value they entered is not valid.
A better solution would be a more informative message to the user, guiding them to required entry into the specified cell. This is more productive, informative and less likely to cause user frustration and compliance.
The way to do this is to create a ‘Custom Message’. So, let’s get back to our Data Validation Tab and select Error Alert.
Select the Icon to display, you have the choice of
AND you can type a custom message to your users. In this example something like ‘Please Use UPPER CASE only’ would be good, and I would maybe use the warning sign as a Icon to grab the users attention.
This is an example of using a formula (in the case EXACT) with the Data Validation ensuring your data is in the correct format at entry stage to aid or enforce the practice if clean and accurate data.
BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com/?page_id=2 a site that shares Excel tips and tricks with it’s enthusiastic members.