Using PROPER, LOWER, UPPER in Excel Formulas

The video guide shows how to change the case of text strings in Excel using PROPER, LOWER and UPPER. It shows how the data can be tidied up and transformed between columns. If you want to replace the original data with the tidied data, you will need to copy the transformed data and paste special as values over the top of the original data. By pasting as values, Excel will not get confused by the disappearance of the source cells from the spreadsheet.

Another use of the case-changing functions is to ensure Excel's matching functions (like VLOOKUP or FIND) are able to match data. At Excel4Business, our experts would generally say that if you need to match a data input to a lookup table, you should be using the lookup table as the source data for data validation. However, Excel's data validation is not case-sensitive. That means if you define an acceptable input as 'RED', users will still be able to enter 'red'. A formula of the form '=VLOOKUP(UPPER(A1),LookupRange,2,FALSE)' would correct for this by converting any lower-case entries into upper-case entries before attempting the VLOOKUP.

The benefit of combining the case-changing functions into larger formulas is that you can avoid unnecessarily duplicating a whole column of text to guard against occasional non-conforming data. It should be noted that the most popular array functions like SUMIF are not case-sensitive so adding e.g. LOWER to a formula should never be more complicated than substituting each occurrence of the cell reference e.g. A1, with e.g. LOWER(A1).

By Ed Bolton, founder of Excel4Business Ltd. Last reviewed October 2026.