How to Use the UPPER Formula in Google Sheets

Google Sheets is a powerful spreadsheet that lets you use formulas to accomplish many things. Such a set of formulas allows you to change the text cases in cells for any purpose.

For example, if you write “quick brOWn fOX” in a cell, you can use uppercase (QUICK BROWN FOX), proper or title letters (Quick Brown Fox), sentence letters (Quick Brown fox), or lowercase (quick brown fox).

As you might have guessed, UPPER is the function that converts your text in the cell to uppercase. Here’s how you can use it.

What is the UPPER function?

As a text function, UPPER only works on text strings in your cells. It is useful when you need to convert text to uppercase, e.g. B. If you use Google Sheets to track stock values ​​and you want to make sure the stock symbols are entered correctly.

You can use Google Sheets UPPER function to instantly capitalize any letter in a range of cells or a cell.

However, you should be aware that:

1. Numbers, punctuation marks and spaces are not affected by this function.
2. Any text passed directly to the UPPER function must be enclosed in quotes.
3. A cell reference containing the text you want to convert to uppercase can also be used as an argument.

Syntax of the UPPER formula

Syntax is how you write a formula. Imagine you are using an algebraic expression, substituting values ​​into the formula.

The syntax for the UPPER formula is:

=UPPER(TEXT)

Let’s break this down into its parts and better understand what each aspect does:

• = (the equal sign): In Google Sheets, we always start a function with an equals sign. Google Sheets uses this to determine whether we want to perform a calculation or call a function.
• UPPER, HIGHER(): This is the function name and tells Google Sheets which function to retrieve and apply to the formula, in this case UPPER.
• Text: This is the only and essential argument of the function. It is the text that needs to be capitalized.

Only one argument is allowed for the UPPER function. It changes the text in a string to uppercase. Also, that single argument can just be a cell reference or a text string.

3 examples of TOP formula in Google Sheets

The following examples show many approaches to capitalizing a text string using the UPPER function in Google Sheets.

1. Capitalize all letters in a cell

Let’s start with a simple example: we capitalize the text entered into a single cell in Google Sheets.

By using the UPPER function, you can avoid manually capitalizing each letter in the text. Instead, the built-in feature effortlessly converts every character in all text to an uppercase version.

This can be useful if you want to highlight a cell without conditional formatting. Below is a step-by-step illustration of the process.

1. Type or copy unformatted text into a cell. In the example above, we used cell A1.
2. In another empty cell, type the = sign to start the formula
3. Type UPPER, HIGHER(
4. Enter the cell reference that contains the text you want to capitalize—A1 for example.
5. Press Enter

As you can see in the example above, all letters in the phrase that were first written in lowercase have been converted and rewritten to uppercase.

2. Capitalize text in strings that contain both text and numbers

If there is content other than text in the cells of the spreadsheet, the UPPER function in Google Sheets can still be used to capitalize the letters around the numbers. The formula still works correctly when a number or punctuation mark is mixed with the text referenced by the UPPER function.

All letters are capitalized while non-text characters are ignored, and that’s because the UPPER function ignores the other characters.

Such use of the UPPER function is ideal for correcting product IDs or similar text data that should actually be entered in upper case.

You can follow the steps from the first example to use the UPPER function. We’ve provided an example below to show how it works.

As you can see, the text containing numbers and punctuation marks in the example above has been capitalized, making it easier to read and giving the codes a consistent, clean look since the capital letters are the same height as the digits.

3. Use the UPPER function to capitalize all cells in a column

The data in the previous figure was capitalized by inserting a formula in each range cell.

The fill handle allows you to apply the formula to multiple cells. Google Sheets may also suggest autofill to make your life easier. If this is the case, you can just click Yes to apply the UPPER formula to the suggested cells. However, both methods are slightly slower than nesting the UPPER function with an ARRAYFORMULA.

An ARRAYFORMULA helps in situations where you may want to capitalize an entire column by applying a single formula to many rows.

Below is an illustration of the process:

If you were to enter the UPPER formula =TOP(A2)it only affects the first cell in the list that you want to capitalize (A2).

But if you change the cell reference like this: =TOP (A2:A5), It still only returns the first result instead of applying it to all. Therefore you must use an ARRAYFORMULA.

To use the ARRAYFORMULA function in Google Sheets, you need to nest the same formula inside it. In our case it would look like this:

 =ARRAYFORMULA(UPPER(A2:A5))

As you saw, a single formula turned every cell into an uppercase text string in the list.

You can also nest the UPPER function with numerous other formulas in Google Sheets. This prints the final answer in all uppercase.

Easily fix how your text appears in Google Sheets with UPPER

Now that you know how to use the UPPER function, it’s important to use it occasionally. Although it’s easier to enter data in uppercase, using the UPPER function instead helps avoid capitalization errors.

Since this function is a simple formula, it should be in your arsenal as you learn more about Google Sheets.