Force Uppercase In Cell(s)

The other day my manager asked me an interesting question. He wanted to know if there was a way for force the contents of a cell to be all uppercase.

I thought for a minute and remember reading about how to do this. Well I could not remember where I learned of this trick but I did remember how to do it.

You can force the cell to have upper case using Data validation and the =Exact function.

From the menu select Data > Data Validation 

In the Settings tab enter =EXACT(B7,UPPER(B7)) in the formula field.

But we can jazz this up a bit. In the Input Message I can enter a message to display when the cell is selected.

For the Error Alert, I also create a message (since generic errors are sometimes confusing).

Now whenever someone selects the cell they get a prompt onscreen as to what type of data is expected and if they enter in the data incorrectly, they get an error message explaining how to correct the data.

So how does this work? Well Exact compares two values. In this case the Value in cell B7 compared against the Uppercase conversion of cell B7. The uppercase function converts the entire cell to uppercase.

So the next time someone asks you for ensure the values of a cell are uppercase, consider the above, break out your mad Excel skills, and WOW them.

INTERMEDIATE, EXACT, UPPERCASE, DATAVALIDATION