How to Change Text to Uppercase in Excel

When consolidating data in Microsoft Excel from multiple sources, you will often encounter formatting inconsistencies. One user might type “NEW YORK”, another “New York”, and a third “new york”. If you attempt to sort, filter, or use formulas like VLOOKUP on this data, Excel may treat these as three distinct categories. Standardizing text capitalization is a crucial data cleaning step. If your organization requires all data to be capitalized for database entry, the UPPER function is the fastest way to achieve this.

What is the UPPER Function?

Unlike Microsoft Word, which has a button on the ribbon to instantly change text case (the ‘Aa’ button), Excel relies on formulas to manipulate text. The UPPER function takes the contents of a specific cell and outputs an exact copy with every single letter converted to uppercase. It completely ignores numbers and punctuation, leaving them untouched.

Step-by-Step: Converting Text to Uppercase

Because UPPER is a formula, it cannot change the text inside the original cell directly. You must create a new “helper column” next to your data.

1. Open your Excel worksheet. Assume your messy text is in Column A, starting at cell A2.
2. Click on an empty cell directly next to it, such as cell B2.
3. Type the formula: =UPPER(A2)
4. Press Enter. Cell B2 will now display the uppercase version of whatever is in A2.
5. To apply this to the rest of the list, click back onto cell B2. Notice the small green square in the bottom-right corner of the cell (the Fill Handle).
6. Double-click that green square, or click and drag it down. Excel will copy the formula down the entire column, instantly capitalizing your entire list.

The Crucial Final Step: Removing the Formulas

You now have a column of perfect, capitalized text (Column B). However, these cells contain formulas, not actual text. If you delete the original messy data in Column A, Column B will instantly break and display a #REF! error. You must convert these formulas into hard, static text.

1. Highlight your entire new column of capitalized text (Column B).
2. Press Ctrl + C (Windows) or Command + C (Mac) to copy it.
3. Without clicking away, Right-click anywhere inside that highlighted area.
4. Under the ‘Paste Options’ section of the context menu, click the icon that looks like a clipboard with the numbers 123 on it. This is the Paste as Values option.

By pasting as values, you strip away the underlying =UPPER() formula, leaving only the raw, uppercase text behind. You can now safely delete the original, messy Column A.

Related Functions: LOWER and PROPER

If uppercase isn’t what you need, Excel has two related functions that work exactly the same way:

• LOWER: Converts everything to lowercase (e.g., =LOWER(A2) turns “New York” into “new york”).
• PROPER: Capitalizes only the first letter of every word (e.g., =PROPER(A2) turns “new york” into “New York”). This is incredibly useful for cleaning up lists of names or cities.

By mastering the UPPER, LOWER, and PROPER functions, you can rapidly sanitize thousands of rows of text data without manual retyping.