Data Validation with ALL CAPS

In recent back-to-back webinars, two different attendees asked how to check to see if a user entered a text string with all caps. Specifically, they asked how to set up a data validation rule to determine if all letters are capitalized. So, I figured I would write a quick post about the technique.


Let’s just take a moment to confirm our goal here. Our goal is to apply a data validation rule to a cell that tests to see if the user entered the value in ALL CAPS.

So, if the user enters a text string with a mix of upper and lower case letters into the input cell, we want Excel to display an error alert dialog. For example, say the user enters the value “For the Year Ended” as shown below.

Report Header by Jeff Lenning

We would want Excel to display an error alert, like the one shown below.


But, the user shouldn’t get the error if the value is entered in UPPER case, as shown below.

UPPER by Jeff Lenning

This can be accomplish by creating a custom data validation rule. Let’s jump into the details now.


We’ll just walk through the steps together.

1) The first step is to select the input cell! After selecting the input cell, make a note of its cell reference, such as B1 for example.

Note: You will be applying data validation to the input cell, and will use its cell reference in the data validation formula below.

2) Next, open the Data Validation dialog by clicking the Data > Data Validation ribbon icon. In the resulting dialog box, select Custom as shown below.


3) Then, we need to enter the formula into the dialog’s Formula field. Essentially, we are going to ask Excel to see if the value in the input cell is equal to the upper case version of the input cell. If they are, then the user entered the value in upper case. If not, the user did not enter it in all upper case, and so we need the error alert.

We can accomplish this by using the UPPER function (which converts a text string to upper case) and the EXACT function (which will compare the two values to see if they are the same). Assuming the input cell is B1, we would enter:


This is shown in the dialog below.

Data Validation formula by Jeff Lenning

Note: update the formula to refer to your input cell if not B1.

This formula basically returns TRUE when the upper case version of B1 is exactly the same as B1. If not, it returns FALSE, triggering the error alert.

If you have any other fun custom data validation rules, please share by posting a comment below…thanks!



Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. susan schiavone on November 11, 2016 at 7:38 am

    Thanks, Jeff!

    • jefflenning on November 11, 2016 at 7:39 am

      Welcome 🙂

  2. Lisa on November 11, 2016 at 9:40 am

    Took me a minute using it to realize what it does, is there a way to do the opposite? Make sure it is all proper case? instead of all caps?

    • jefflenning on November 11, 2016 at 9:51 am

      Yes, indeed! You can use the PROPER function instead of UPPER. You could also use LOWER to test for lower case.

      • Lisa on November 11, 2016 at 10:47 am

        Thank you!

        • jefflenning on November 11, 2016 at 10:48 am

          Welcome 🙂

  3. Kylie Moore on December 15, 2016 at 11:52 pm

    Thanks for sharing the solution for data validation. My friend is following the “Computer Skills for the Workplace” training classes through online. Now he got the knowledge from there.

  4. Hussain on February 20, 2020 at 5:30 pm

    Thank a lot Mr. Jeff
    Thank you very much for this great explanation
    Please have a question and please help me
    In an excel file and I want if the entry in one of the cells is in lowercase letters and after finishing and moving to the other, the letters automatically become uppercase.

    • Jeff Lenning on February 20, 2020 at 5:32 pm

      You may be able to use the UPPER function for that. Maybe write a formula in the adjacent column like =UPPER(A1).

Leave a Comment