Contrary to Word, Excel offers less advanced features when it comes to text modification. For example, there is no Change Case button.
We shouldn’t be surprised by that, Excel is not a word processor. But sometimes you really need this feature, especially if you have to deal with a large amount of data.
In this lesson, I’m going to show you a few simple ways you can convert lowercase, or mixed case sentences into all caps.
All caps with formula
The UPPER formula changes every letter inside a cell to uppercase. It doesn’t matter what is the combination of small caps and large caps, it changes it all to uppercase.
Here, you have some examples, how this function behaves.
All caps without formula
If you don’t want to use a formula, you can create VBA code that will convert selected cells to uppercase within the selected cells. Here is the code.
Open the VBA Editor by using Left Alt + F11, and insert the following code.
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
This is the result, you will get.
In order to create a shortcut icon for our subroutine, you have to click the Customize Quick Access Toolbar icon.
From there, select the More Commands option.
Under the drop-down Choose commands from select Macros and select our AllCaps macro. Click Add >> button. Now the macro is visible in Quick Access Toolbar.
You can keep it this way. But I suggest changing the icon, especially if you create other shortcuts from macros. I chose the following icon, but you can choose any icon you want.
Now, if you want to change the text to all caps, you can select cells you want to modify and click the icon in the Quick Access Toolbar.
In addition to the icon, you can also add a keyboard shortcut to our macro.
Open your PERSONAL.XLSB workbook and click View >> Macros >> View Macros.
A new window called Macro will appear. Click the AllCaps subroutine and then click Options.
Inside a new window, under the Shortcut key, there is a little text area. Click inside it and press Shift + A. This will assign Ctrl + Shift + A button to the macro.
Select cells you want to convert to uppercase and press the keyboard shortcut you’ve just created. You can also use other shortcuts, but remember that it will override the default Excel shortcut. Be sure you don’t use it for other tasks.