Do you ever use the Subscript or Superscript fonts formats in Excel? Maybe you’re reporting on chemical usage, and you need to enter CO2 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!
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.