It’s often a good idea to use Data Validation feature to limit user errors and streamline data entry process. In addition to built-in rules, like size of a number or length of a text, you can define your own rules in a Custom fashion. Let us show you how to allow Excel uppercase characters only, using Data Validation.

Syntax

=EXACT( cell reference ,UPPER(cell reference ))

Steps

1. Begin by selecting the input cell (e.g. C2).
2. Open the Data Validation window by clicking DATA > Data Validation
3. Select Custom from the Allow dropdown.
4. Type in your formula into Source =EXACT(C2 ,UPPER(C2))
5. Click OK to continue and apply your settings.

 How

Excel's Data Validation feature is a great tool to ensure that users enter the correct type of values. While built-in options can cover most scenarios, the tool also allows you to create your own rules using formulas. Selecting Custom in the Allow dropdown shows the Source input where you can enter formulas.

Using formula support, you can apply validation based on the output value of a formula. If the formula returns TRUE, validation will pass, if the formula returns FALSE, then validation will fail.

The EXACT function can validate a string and check whether it's uppercase or not. It compares two string values and returns TRUE if they are the same, and FALSE result if they're not. We can use the EXACT function to compare the cell value with its uppercase version using the UPPER function. The UPPER function essentially converts a string into all uppercase characters. If we assume our cell reference is C2, then the formula should be:

=EXACT(C2,UPPER(C2))

An important thing to note here is that the formula's absolute/relative states for references. Excel updates the relative references (e.g. C2) when you copy formulas and leaves absolute references (e.g. $C$2) as is. Try to make references relative in order to copy the same functionality to other cells.