Hello Excellers, a warm welcome back to another #formulafriday blog post. in my 2019 series. The Excel formula tip today is a recent favorite of mine at the moment, trying to get users to enter data in upper case only. Well, it can be an extra hard struggle to get 100% compliance. So, as a solution, sometimes there is no choice but to force the user to enter in upper case.

We can achieve this by the Data Validation feature in Excel. Validation is used to control what a user can enter into a cell. Sounds an ideal solution for us doesn’t it?. We can also turbocharge this by choosing to combine it with the EXACT function or formula.

Let’s take a look at an example. We need all postcodes to be in UPPERCASE for our database.

Using Data Validation

It is really easy to set up validation. Follow the simple steps below.

• Data Tab
• Data Validation Button
• Custom
• In the formula box type the following

 

How Does The Data Validation Work?

Data Validation has been applied to cells D2:D10 as we require postcodes in UPPER case to be entered into these cells. The syntax of the EXACT Formula is

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.

=EXACT(text1, text2)

The EXACT function syntax has the following arguments:

• Text1 Required. The first text string.

• Text2 Required. The second text string

 

By entering the formulas =EXACT(D2,UPPER(D2))

We are testing if the string in D2 is in UPPERCASE. We now drag the formula down to D20. (The end of the data entry area). Now, if someone tries to type lower case in any of the cells D2:D20 they will be warned with a dialog box with a standard warning.

Additional Information To The User

You can turn up the volume here and create a custom message that asks the user to type upper case into the cells. Just follow the simple steps below.

• In the Data Validation Tab
• Select Error Alert
• Select your Icon to display either Stop Warning or Information, and type a custom message.

 

In my example, I typed “Please Enter Detail In Upper Case!”

That’s it all you need to do is test your data entry. Did you get that warning?. Good. Your solution is working as expected. Now users will have no choice but to enter data correctly into the data set.

 

 

If you want more tips then sign up to my monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.

 

How To Excel At Excel – Formula Friday Blog Posts.