I recently was asked if there’s a way of adding the Celsius symbol to a value in Excel.

The answer is yes, and the method can be applied to all sorts of useful symbols / notation.  e.g. bullet points, m2, km/hr, bbl, currency symbols etc.

This is how….

Pick a blank cell then go to the Insert ribbon and select Symbol then find Superscripts and Subscripts and select the O symbol and click Insert followed by close.

Newsletter Pic 1

You should now have the superscript O in a cell, just type a capital C after it.

Highlight these 2 characters and copy them (you can use Ctrl+C). Then press Esc.

Now you have 2 options, depending on whether you want to add this to a value or a piece of text.

Option 1:  Number Formatting

Right-Click in the cell you want to format and select Format Cell > Number > Custom and in the Type: box enter this

0.0⁰C;  [Blue](0.0⁰C);   0.0⁰C

(Note the ; separates how you format positive values, negative values, and zeroes)

Newsletter Pic 2

You could use the same technique for Fahrenheit (if you still haven’t converted to a modern temperature system!):

0.0⁰F;  [Blue](0.0⁰F);   0.0⁰F

… you could even use this for Kelvin temperatures:

0K;;“-“

… since you can’t have a negative temperature in Kelvin, that part isn’t relevant.  Since it’s theoretically not possible to reach absolute zero, this is formatted as a dash.

Option 2:  Autocorrect for adding it to text

I’m a fan of Autocorrect and use it for all sorts of tricks (see here for how to create an INDEX MATCH formula using it)

As for Option 1 you first need to copy the Superscript O and C using Ctrl+C then press escape.

Now go to Autocorrect (via File > Options > Proofing > Autocorrect)

Newsletter Pic 3

In the “With:” box do a Paste (Ctrl + V)

In the “Replace:” box type  dc  or any other shortcut that you aren’t going type for any other reason. Then click Add.

Now when you are typing you can just write “the temperature was 6 dc ”  and the dc will Autocorrect to your symbol.   Once you close Excel and open Outlook or Word or PowerPoint this Autocorrection will work in those applications too.

I frequently use this trick to enter bullet points in Excel.[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]