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 CO2 as one of the row headings.

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

superscript02

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!

superscript01

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? Here’s how you can do it 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.

superscript03

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

superscript04

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

superscript05

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).

superscript06

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 sample code is shown below, and you can use other color index numbers, instead of Red.

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.

__________

3 comments to Change Characters to Superscript

  • JLeno

    This is an interesting problem you describe. I personally don't work with superscripts that much, but I do understand it could be a hassle for people who do.

    Another way to tackle this problem is to use BBCode tags (e.g. [p]superscript[/p] and [s]subscript[/s]), which you can parse with a SheetChange event. To make a general solution, the event should of course be and XL Event, in a dedicated class module.

    I haven't bothered to actually work out a solution, since this problem doesn't concern me that much. If anyone is interested I just might... (probably you can also find it somewhere on the web)

    Cheers!

  • Matt

    Good post except that in chemical notation the 2 would be a subscript.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>