How to Use UPPER Function in Excel (4 Examples)

In Microsoft Excel, the UPPER function is generally used to convert all the letters in a string of text to block ones. In this article, you’ll get to learn how you can use this UPPER function effectively in Excel with appropriate illustrations.

upper function overview in excel

The above screenshot is an overview of the article, representing a few applications of the UPPER function in Excel. You’ll learn more about the methods along with the other functions to use the UPPER function with ease in the following sections of this article.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Introduction to the UPPER Function in Excel

upper function syntax

  • Function Objective:

The UPPER function is used to convert a text string to all uppercase letters.

  • Syntax:

=UPPER(text)

  • Argument Explanation:
Argument Required/Optional Explanation
text Required A string of text where all letters have to be converted to upper cases.
  • Return Parameter:

All upper case letters in a string of text.


4 Suitable Applications of UPPER Function in Excel

1. Basic Use of UPPER Function in Excel

While inputting a text value in the only argument of the UPPER function, you have to use double quotes (“ “) around the text data. Otherwise, the function will not be executed and an error message box will appear. In the picture below, some texts with lower cases are lying in Column B. And in Column C, the outputs are obtained by using the UPPER function.

For example, the first one in Cell C5 comes up with the formula:

=UPPER("washington")

After pressing Enter, you’ll find all the letters or alphabets in upper cases at once.

basic use of upper function in excel


2. Use of UPPER Function with Cell Reference

The use of cell reference is much easier than typing a text manually in the text argument of the UPPER function.

For example, Cell B5 in the following table contains a text with lower cases. So, in the output Cell C5, the required formula by using the cell reference will be:

=UPPER(B5)

You don’t have to use double quotes around the cell reference here.

us of upper function with cell reference in excel


3. Using Upper Function for Data Validation in Excel

Let’s think of a scenario with Excel entries where data validation is required. Suppose, in the following picture, you have to type your first and last names in Cells C4 and C5. The names must be in upper cases or block letters. But if you input a lower case letter or a numeric value, an error message will appear. How will you make it happen?

data validation with upper function in excel

📌 Step 1:

➤ From the Data ribbon, select the Data Validation command from the Data Tools drop-down.

A dialogue box will open up.

data validation with upper function in excel

📌 Step 2:

➤ Select Custom from the Allow list for Validation criteria.

➤ Inside the formula box, you have to type:

=AND(EXACT(C4,UPPER(C4)),ISTEXT(C4))

In this formula, we’re using a logical function AND that will define if the input in Cell C4 contains text data with all upper case letters.

➤ Press OK and you’re done.

data validation with upper function in excel

📌 Step 3:

➤ Now return to your spreadsheet and use Fill Handle to assign the criteria for Cell C5 too.

➤ In Cell C4, type a name in lower cases and press Enter.

An error message box will appear which will inform you that the input you have provided is not valid according to the defined criteria.

➤ Press Cancel and the message box will disappear.

data validation with upper function in excel

📌 Step 4:

➤ Now type a name in block letters in Cell C4.

➤ Press Enter and the cell will store the input without displaying any error message this time.

data validation with upper function in excel


4. Joining Texts with Partial Upper Case

In the following picture, we have now a list of random smartphone brands and their model names in two columns. The model names are in lower cases that we’ll have to make upper cases while concatenating the data alongside those two columns B and C.

In the output Cell D5, the required formula should be:

=B5&" "&UPPER(C5)

After pressing Enter, you’ll get the return texts with the model name in block letters or upper cases. Now you can use Fill Handle to autofill the rest of the cells in Column D to extract the other outputs.

joining text with keeping one part upper case in excel


💡 Things to Keep in Mind

🔺 If you need to make only the first letter upper case, then you have to use the PROPER function as the UPPER function converts all lower cases in a text to upper ones.

🔺 UPPER function does not have any crucial impact on the numeric values. But if a number is in date or currency format then the function will return the serial number of the date and remove the currency units respectively.

🔺 While inputting a text value in the argument, please do not forget to use double quotes(“ “) around the text.


Concluding Words

I hope all of the suitable methods mentioned above to use the UPPER function will now provoke you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


You May Also Like to Explore

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo