One of the most under-appreciated features of Excel, I think, is Excel’s custom number formatting.
I will be alluding custom number formatting in a future post series as well, but here is a quick custom number formatting tip.
Let’s say I want to work with area or volume, and need to indicate that using custom number formatting (e.g., m2, or cm3). How do we add those in Excel?
Note, here, that I am talking specifically about custom number formatting. I can type m2 very (well, relatively!) easily in Excel—all I need to do is edit the cell, type “m2” and then select the 2, and use the Format Cells dialog to make it superscript. But that is not what we are looking at here. Say I have a formula, and whatever value the format provides, must be shown together with the m2 suffix. Or I have a blank cell, and when I enter a value (e.g., 3), then it is displayed with the custom format suffix (i.e., 3m2).
The problem, of course, is that we use the Format Cells dialog to create the custom format, and we also use the Format Cells dialog to add the superscript, and the dialog cannot be invoked on itself (i.e., we cannot format the 2 to be superscripted while entering it into the custom number format box in the Format Cells dialog).
Your first thought might be: Type the m2 and then format it to be superscripted as described above, copy it, and then paste it into the custom format section of the Format Cells dialog. But a long-standing gripe I have with Excel is its inability to really work with rich text, and when you copy text while editing a cell, all formatting (such as bold, italics, or superscript) is lost.
So we need a different approach.
To do this, we start, instead, with the Insert Symbol dialog. Because the Format Cells dialog is modal, we cannot invoke the Insert Symbol dialog while adding the custom number format (so we cannot use Insert Symbol to add the superscripted two to the Format Cells dialog). But what we can do is learn the character code of the 2. So I open the Insert Symbol dialog. Figure 1 shows this dialog, with the Superscript Two symbol selected. Note that, if I wanted something like cm3, the Superscript Three symbol is there as well.
Figure 1 Insert Symbol dialog
The next step is to find out what the ASCII code of this symbol is. Of course, we could look this up in a table, but I am using Excel here to find it. Nonetheless, in the From list box, I choose ASCII (Figure 2). That shows (in the Character code box) the value 178. We could, of course, insert the value into a cell, and then use the =CODE function to get the same information, but this is slightly quicker.
Figure 2 Insert Symbol dialog showing ASCII codes
Once I have this information, I can use standard custom number formatting procedures to create my format. So, for example, showing a number with one decimal, I would use the format #.0. I would then just add “m2” to that (including the quotation marks). I add the 2 by holding down the Alt key while typing 0178 on the number keypad. Figure 3 shows the creation of this custom number format in the Format Cells dialog.
Figure 3 Adding the custom number format
And, to wrap it up, Figure 4 shows the custom number format in use. Note that $B$2:$C$10 use a custom format #.0″m” and $D$2:$D$10, $E$2 uses to format I just demonstrated, #.0″m2“.
Figure 4 Custom number format in use