Change Characters to Superscript

Do you ever use the Subscript or Superscript fonts formats in Excel? Maybe you’re reporting on chemical usage, and you need to enter CO² as one of the row headings. Here’s how to change characters to Superscript – you could use similar steps to change characters to Subscript.

Easy in Microsoft Word

In Word, you can add the Superscript and Subscript commands to the Quick Access Toolbar (QAT). Then, select a character, and click the Superscript command.

No Superscript Command in Excel

Unfortunately, it’s not that easy in Excel – those commands aren’t available when you customize the QAT.

You can see the Excel Customize screen below. The Strikethrough command is available, but no Subscript or Superscript. That doesn’t seem fair!

Missing Feature

I don’t know how many people use Superscript in Excel every day, but one of the questions in the Excel team’s AMA on Reddit pointed out this missing feature:

“In Word you can add the subscript/superscript command, but in Excel, the subscript/superscript commands are not present in the list !!”

Format as Superscript

Since there’s no QAT shortcut available, how do you add Superscript in a a cell? In this example, I’ll change “CO2”, so the “2” is in Superscript. Of course, it really should be in Subscript, so you can follow these steps, but choose Subscript instead.

Here’s how you can change to Subscript manually:

• Type the characters in the cell – CO2 in this example
• Select the character that you want to change to Superscript
• To open the Format Cells dialog box, press Ctrl + 1,
  • OR, click the Dialog Launcher button at the bottom right corner of the Font group on the Ribbon’s Home tab.

• Add a check mark to the Superscript option, and click OK

• The selected character changes to Superscript. It looks regular-sized in the formula bar, but you can see the smaller font in the cell.

Macro to Change Characters to Superscript

When I saw the question on the Reddit site, it reminded me that I created a macro recently, to change characters to Superscript. It would take a while to run, on a big worksheet, but it’s better than changing each character manually! If you have a better solution, please let me know.

• While you’re entering the data, use Red font colour to mark the characters that you want as Superscript. It’s easy to change the font colour – just right-click on the selected character and click on the Font colour command (NOTE: Use a different colour, if you’re already using Red font for other things in your file).

Later, when you’ve entered all the data, select a range of cells, and run the macro to change all the red characters to Superscript.

The Superscript Macro Code

The sample code is shown below, and it will change all the Red characters to Superscript. You can use other color index numbers, instead of Red, if you prefer.

NOTE: To make a Subscript macro, just change the “.Superscript = True” line to “.Subscript = True”

Sub ChangeRedToSuperscript()
Dim rngSel As Range
Dim c As Range
Dim lChar As Long
Dim lCount As Long
Dim lCI As Long
Dim lCI2 As Long
Set rngSel = Selection
lCI = 3 'red    '14=green   '33=light blue
lCI2 = 1 'black
For Each c In rngSel
    lChar = Len(c.Value)
    For lCount = 1 To lChar
        With c.Characters(lCount, 1).Font
            If .ColorIndex = 3 Then
                .ColorIndex = 1
                .Superscript = True
            End If
        End With
    Next lCount
Next c
End Sub

Download the Sample File

To see the sample file, and test the code, you can visit my Contextures website.

On Sample Files page, go to the UserForms & VBA section, and look for UF0025 – Change Marked Text to Superscript. The zipped file is in xlsm format, and contains macros.
__________