How to capitalize all letters in Google Sheets with the UPPER function

If you want to quickly capitalize all letters in a cell, or range of cells in Google Sheets, this can be done by using the UPPER function. The UPPER function is very easy to use, since all you need to do to use it, is to designate the cell that contains the text which you would like to capitalize.

For example, if you apply the UPPER function to the text “important”, then the formula will display “IMPORTANT”.

To capitalize all letters in Google Sheets, do the following:

1. Type “=UPPER(” into a spreadsheet cell or click “Insert” → “Function” → “Text” → “UPPER”
2. Type a cell reference to designate the cell that contains the text that you want to capitalize
3. Press “Enter” on the keyboard, and your text will now be capitalized

How to extend the UPPER formula down the column:

1) Click and drag the fill handle to copy the formula down

2) Use ARRAYFORMULA to extend your formula

Click here to get your Google Sheets cheat sheet

UPPER formula examples:

Capitalize all letters

• =UPPER(A2)

Capitalize all letters in a column

• =ARRAYFORMULA(UPPER(A2:A))

This article focuses specifically on the UPPER function, which is one of the three “change case” formulas in Google Sheets. Where the UPPER function will capitalize every letter, the LOWER function will make every letter lowercase, and the PROPER function will capitalize the first letter of every word.

To learn more about all three of these case changing formulas, check out the article below. But in this article we will stick to using UPPER.

“How to change text case in Google Sheets with UPPER/ LOWER/ PROPER”

The Google Sheets UPPER function:

The Google Sheets UPPER function will automatically capitalize every single letter in the text that it refers to.

The UPPER function is a very simple function that only requires you to assign a cell reference to specify the location of the text that you want to capitalize.

Google Sheets description for the UPPER function:

Syntax:
UPPER(text)

Formula summary: “Converts a specified string to uppercase”

In the examples below, I will show you several ways to use the UPPER function in Google Sheets, to capitalize a string of text, or in other words how to capitalize the text that is contained in a cell.

Then later I will also show you how to capitalize an entire column with the UPPER function.

Capitalize all letters in a Google spreadsheet

Let’s begin with a simple example, where we will capitalize the text that is entered into a single spreadsheet cell in Google Sheets.

Let’s say that you had previously written a message for others to see in a spreadsheet, and that you want to change it to all capital letters so that it will stand out and so that others will know that it is important.

Instead of changing each letter to uppercase manually, you can use the UPPER function to automatically change every letter of the sentence to uppercase.

The task: Capitalize every letter in an entire sentence

The logic: Capitalize all letters in cell A1, by using the UPPER function

The formula: The formula below, is entered in the blue cell (B1), for this example

=UPPER(A1)

You can see that in the example above, every letter of the sentence that was originally lowercase, has now been capitalized.

Capitalize text in strings that have both text and numbers

The Google Sheets UPPER function can also be used to capitalize letters in situations where there is more than just text in the spreadsheet cells.

When there is punctuation or numbers mixed in with the text that the UPPER function refers to, the formula will still function properly, and will capitalize all letters while ignoring non-text characters.

For example, let’s say that you have a list of product codes that contain both letters and numbers that were given to you at your job… and that they were given to you with the letters in lowercase format even though they must be entered into the system with all uppercase letters.

Instead of asking an employee to re-type the product codes in the correct format which would be time-consuming, you can simply use the UPPER function to automatically capitalize every letter in the data that was given to you.

In the example below, this is exactly the task that we will perform.

But an added component to this example, is that the formula has been copied into the cells below it, so that each product code is capitalized with a new formula in each row.

The task: Change the letters in the product codes to uppercase letters

The logic: Use the UPPER function to capitalize all letters that are found in the range A2:A15

The formula: The formula below, is initially entered in the cell C2, and then copied into the cells below it, for this example

=UPPER(A2)

Notice that in the example above, all of the letters in the product codes have now been capitalized, which makes them easy to read, and makes the codes look very uniform and clean as the capital letters are the same height of the numbers.

Using the UPPER function to capitalize text in a column

In the last example, we capitalized the data in a column by copying a formula into every cell within a range. But you may sometimes want to capitalize a whole column, by using a single formula that applies to multiple cells.

You can do this by using the ARRAYFORMULA function to extend the UPPER function, so that a single formula applies to a range of cells.

To capitalize every letter of the text that is in an entire column in a Google spreadsheet, do the following:

• Enter an UPPER formula that applies to a single cell at the top of the list that you want to capitalize, like this =UPPER(A2)
• Modify the cell reference so that it represents a range of cells, like this =UPPER(A2:A) Note that this formula will not apply to a range yet, because you must use ARRAYFORMULA
• Wrap your formula in the “ARRAYFORMULA” function, like this =ARRAYFORMULA(UPPER(A2:A))

Below, is an example of using ARRAYFORMULA with UPPER, to capitalize multiple cells by using a single formula.

In this scenario, let’s say that we have a list of addresses in a spreadsheet, and that we need to make it so that the addresses appear in all capital letters. This time, we let’s say that we don’t want to have to copy formulas down the column in order to capitalize every address.

This is where we will use the ARRAYFORMULA function to capitalize the text in a whole column.

The task: Capitalize all of the addresses

The logic: Combine the UPPER function with the ARRAYFORMULA function to change all of the letters in column A to uppercase

The formula: The formula below, is entered in the blue cell (C2), for this example

=ARRAYFORMULA(UPPER(A2:A))

You can see that in the example above, every address in the list has been completely capitalized, with the use of a single formula.

Pop Quiz: Test your knowledge

Answer the questions below about the UPPER function, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.

Classroom downloads:

UPPER function cheat sheet (PDF)

Click here to get your Google Sheets cheat sheet

Click the green “Print” button below to print this entire article.

Question #1

Which of the following is a correct formula that will capitalize ALL letters?

1. =UPPERCASE(A1)
2. =UPPER(A1)
3. =PROPER(A1)

Question #2

Which of the following options represents how the this phrase “I like Google Sheets” would appear after the UPPER function was applied to it?

1. I LIKE Google Sheets
2. I Like Google Sheets
3. I LIKE GOOGLE SHEETS

Question #3

True or False: If you referred to a cell that contained this string “123abc?” with the UPPER function, it would cause a formula error.

1. True
2. False

Answers to the questions above:

Question 1:  2

Question 2:  3

Question 3:  2