5 Ways to Change Font Case in Excel Sheets

Changing Font Case with UPPER, LOWER, and PROPER Functions

Microsoft Excel offers several built-in functions that make it straightforward to change the case of text within cells. Here’s how to use each of these functions:

• UPPER: This function converts text to all uppercase letters. The syntax is =UPPER(text), where text is either a reference to a cell containing the text or the text itself.
• LOWER: This function converts text to all lowercase letters. Its syntax is =LOWER(text).
• PROPER: This function converts text to title case, capitalizing the first letter of each word. The syntax is =PROPER(text).

📌 Note: These functions do not change the text directly but return the result in a new cell. To apply the change permanently, you need to copy and paste values or use a formula that automatically updates.

Example

Using Paste Special for Case Conversion

The “Paste Special” feature in Excel can also be used for quick case conversions:

1. Enter the desired text in a cell in either uppercase, lowercase, or proper case.
2. Copy that cell.
3. Select the range of cells you want to convert.
4. Right-click, choose “Paste Special,” and select “Values.”

This method is handy for bulk changes, especially when you only need to change the case once.

Using VBA for Dynamic Case Conversion

For those comfortable with Visual Basic for Applications (VBA), you can create macros to change the case of text dynamically. Here is an example of a simple VBA subroutine to change selected text to uppercase:

Sub ChangeToUpper()
    Dim r As Range
    For Each r In Selection
        r.Value = UCase(r.Value)
    Next r
End Sub

To add this code:

• Open the Visual Basic Editor with Alt + F11.
• Insert a new module from the Insert menu.
• Paste the above code into the module.
• Close the editor and use Alt + F8 to run the macro.

Flash Fill for Quick Case Changes

Introduced in Excel 2013, Flash Fill can detect patterns in your data and perform case changes automatically:

1. Type the desired case in the cell adjacent to the cell with the original text.
2. Start typing the case for the next cell. Excel will suggest a pattern for changing the case.
3. Press Enter to accept Excel’s suggestion, and Flash Fill will apply the case change to the entire column.

⚠️ Note: Flash Fill is not always 100% accurate, so ensure to check the results.

Conditional Formatting for Case Sensitivity

If you don’t want to change the text but need to highlight text with specific case conditions, conditional formatting is your friend:

• Select the cells you want to format.
• Go to Home > Conditional Formatting > New Rule.
• Choose “Use a formula to determine which cells to format.”
• Enter the appropriate formula:
  • For all uppercase: =EXACT(A1,UPPER(A1))
  • For all lowercase: =EXACT(A1,LOWER(A1))
  • For proper case: =AND(LEFT(A1,1)=UPPER(LEFT(A1,1)),MID(A1,2,LEN(A1)-1)=LOWER(MID(A1,2,LEN(A1)-1)))
• Choose a formatting style and click OK.

The ability to change and manipulate text in Excel sheets opens up numerous possibilities for data presentation and analysis. Whether you’re preparing a report, cleaning data, or just making your spreadsheet more visually appealing, Excel’s tools for case conversion are indispensable. Remember that each method has its own use cases:

• Functions like UPPER, LOWER, and PROPER are ideal for formula-based case changes.
• Paste Special is perfect for a one-time case conversion.
• VBA macros provide dynamic control for extensive data manipulation.
• Flash Fill offers a quick, user-friendly way to change case without formulas.
• Conditional formatting helps you visually differentiate case-sensitive data without altering the text itself.

Can I undo case changes in Excel?

+

Yes, you can undo case changes using Excel’s Undo feature (Ctrl + Z) if the changes were just made. However, if you’ve made many changes or saved and closed the file, you might need to revert from an autosaved version or restore from a backup.

Are these methods applicable in Excel Online?

+

Yes, most of these methods, like using functions (UPPER, LOWER, PROPER) and Flash Fill, work in Excel Online. However, VBA macros are not supported in Excel Online.

How do I change multiple cells’ case at once?

+

You can either apply a formula to a range or use VBA to change multiple cells’ case at once. For one-time changes, the Paste Special feature can also work on multiple cells.