Note: this sheet is available in the Startup File in our store.
Symbols are a way to enhance text with graphic detail, including many linguistic and cultural symbols, technical symbols, arrows, bullets, icons and emoji. They can be used alone or in the middle of a string. They fit and wrap with other text and can be processed as text. For some purposes there is no substitute. To learn how to use them requires a little background.
ASCII and Unicode define different character sets that you can choose from in any given font. Think of ASCII as basic, Unicode as enhanced. Initially, ASCII used 7 bits, which allows 2^7 = 128 characters, 95 corresponding to the keyboard and the rest for program controls. Later, an 8th bit was added, doubling the set to 256. The added range is used for modified Latin letters used in some languages, and a small set of currency, legal and technical symbols. As computer use expanded globally in the 1980’s, the need for more characters grew exponentially. Unicode was introduced in 1988 and now has over a million characters, including many different alphabets and an enormous range of symbols.
There are way more symbols than you’ll ever need, so start with some exploration. Open the Symbol dialog, Alt-N,U. Double-click the header to make it full screen, you’ll need it. First, at bottom right, select ASCII (either decimal or hex), then at top left select the font picklist, and starting at the top of the list, scroll through the fonts with the down arrow. Notice that most fonts have mostly the same characters, just different looks. If you keep scrolling, eventually you’ll find some fonts with musical notations, or other strange symbols. If you look at the bottom right you may notice that from: is no longer ASCII, but Symbol. This indicates a symbol-dedicated font.
Keep scrolling fonts, holding down the down arrow to go fast, or type a letter to jump. Stop to review Symbol, Webdings and the three Wingdings fonts. Note that each of them has a loosely-defined theme. The table on the Symbols sheet shows the ASCII set of these five symbol fonts (they have no Unicode extensions) side by side with the normal body font for reference.
Next, go back to the top of the font list (normal font), buckle your seat belt and select Unicode in the from: box. Before changing the font, scroll down the character grid to get a feel for how many different types of characters there are. Make a mental note that you may want to return here to collect some favorites in the normal font, for reasons explained later. Notice that the new Subset: picklist at top right changes as you scroll. Open it to view the subsets, and scroll the list to jump to the beginning of each group.
Now go back to the font list and scroll down it again, noting that some fonts have only the short ASCII list, while others have thousands of new characters. It’s worth checking out the new symbol sets in at least the following fonts: Segoe MDL2 Assets, Segoe UI Emoji, Segoe UI Symbol.
The point of this exercise is to help you to filter out the thousands and find the few that are useful to you. Think about how you will use the symbols. Probably you’ll use them in a cell by themselves, or combined with normal text. You might also want to use them as inputs to formulas, or pivots, or slicers, or format conditions. These don’t care about the font, only the characters, and if it’s Unicode, it may or may not display as expected.
To see how a symbol will be passed, view it in the formula bar. Below is a string of symbols from four different fonts, each group separated by a space. In cell 1, each set is formatted with its own font. The formula bar shows that some of them are passed exactly, some approximately, some as their undisguised self, and some are incognito. Cell 2 is Calibri (Body) font, in which some look like the formula bar, some like cell 1, and one symbol has disappeared entirely.
The lesson? Symbols are not always “what you see is what you get,” but it’s easy to tell by checking the formula bar. Avoid fonts and symbols that don’t pass well, if that matters. You can set symbol font on part of a constant cell, but of course a formula cell can only have one font. If you set a formula cell to a symbol font, you can no longer edit-in-cell. Instead, use the formula bar.
Now to introduce the Symbols sheet, which has two tables for ASCII and Unicode.
In the ASCII table, column [Code] lists the ASCII codes, 32 to 255 (1 to 31 are non-printable control characters). This is followed by the method of entry, and the code’s character in six different fonts, the standard Body font and five symbol fonts. The six font columns all contain the same value, =CHAR([@Code]); only the font differs.
In the Unicode table, a small set of useful symbols is shown in the Body font. This is converted to the Unicode value, =UNICODE([@Body]), and its hexadecimal equivalent, =DEC2HEX([@Unicode],4).
Interesting note: I can use =CODE(symbol) to return the ASCII code of a symbol, and =CHAR(code) to return the character of a code. So if I do =CHAR(CODE(symbol)), I should get the same symbol, right? For codes within the ASCII range, this works. If the symbol is outside the ASCII range, it returns “?” (code 63). However, =UNICHAR(UNICODE(symbol)) always returns the same symbol, because nothing is outside the Unicode range.
A third table gives another way to enter symbols, via AutoCorrect. Go to File-Options-Proofing, Alt-F,T,P, then Alt+A, to set AutoCorrect Options as shortcuts. Set them up from any Office program and they are available in all, but may work a little differently. Word and PowerPoint are text-based, so to them, + or – or = are just characters. In Excel these invoke the formula syntax, so symbol replacements don’t work. For example, standard arrow shortcuts “-->”, “==>”, etc. do not work in Excel.
Replacement symbols improve text appearance, but can break an Excel formula, for example, if you replace “>=” with “≥”. Watch as you type, and if you get an unwanted symbol, Ctrl+Z immediately to undo the substitution and continue with the characters as typed. If you don’t catch it while typing you will have an error. To avoid this, set up AutoCorrect characters that you’ll never use in a formula, or simply choose to avoid such AutoCorrect sequences.
The final shortcut to mention is to use Word to create a Unicode symbol by typing the hex code followed by Alt+X. For example, 25CF followed by Alt+X = ●. Symbols can thus be entered in Word and copied to Excel.
Comments