June 9, 2016
Hi fellow Excel Addict,
In today's 'Excel in Minutes' tutorial, I'm going to show you something that most Excel users aren't even aware of.
Now this isn't going to help save you a lot of time and get your work done faster or get you that next big promotion (...well maybe?) but it is another one of those 'unusual finds' that you may not expect to see in Excel.
However, as is the case for many Excel users, there may be times when this is exactly what you'll need. You'll see why in a few minutes.
Also, if you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you how to 'Get the Perfect Font Size'. You can read all about that here.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Easily Find Hundreds of Cool Symbols You Can Use in Excel
You may already know that you can insert various symbols and special characters into your worksheet that aren't available on your keyboard, such as © (copyright) and ® (registered trademark)**.
Just go to the Insert tab and click the Symbol command on the far left of the Ribbon. Select a symbol from the list, then click Insert and Close to insert one of these symbols into your worksheet.
You may not realize this but there are WAY MORE symbols available than what you see when you scroll through this list. In the Normal font, you can find common symbols such as copyright, trademark and bullets. However, by choosing a different font from the Font dropdown, you can access a larger variety of symbols, such as checkmarks, arrows, stars and happy faces. Yes! Happy Faces! You can find many more of these cool graphical symbols in the Webdings, Wingdings, Wingdings 2, and Wingdings 3 fonts.
Rather than scrolling through the table of symbols, depending on which version of Excel you are using, you may be able to expand the Symbol dialog box and view all of the symbols (for the selected font) by dragging its bottom right corner .
Enter symbols using the ALT code
When you select a symbol, you'll see its character code displayed in the Character code box. You can use this code with your keyboard to quickly insert those symbols that you frequently use. Simply hold down the ALT key and type the character code as a 4-digit number using the keys on the numeric keypad, not the top row of your keyboard. For example to enter the copyright symbol, hold down the ALT key, type 0169 and press Enter.
Get a list of symbols and codes
If you would like to see a list of all of the symbols and their character code, here's a neat trick to display all (or most) of the symbols in your worksheet.
1) Type 1 in cell A1 and 2 in cell A2 and drag the Fill Handle to create a series of numbers down to 255;
2) In Cell B1, type the following formula...
3) Double-click the Fill Handle to copy that formula down to row 255.
You now have a complete list of symbols and their character codes for your default font.
To see the symbols for other fonts such as Webdings and Wingdings, simply change the font for column B. The symbols will appear pretty small so you may want to increase the font size or Zoom percentage.
Create a custom list
Since there are 255 symbols and codes, and most of them are not that interesting, you may want to create your own list of favourite symbols.
1) Copy columns A and B;
2) Paste them to another part of the sheet;
3) Point to the edge of the pasted range and using the right mouse button, drag a little to the right then back. Select Copy Here as Values Only from the menu that pops up. This is the same as doing Copy, Paste Special, Values;
4) Now you can delete all of the symbols you don't need from the pasted list;
5) Type the name of the font above the list;
6) Change the font for column B on the original list and repeat the steps above.
This will allow you to create custom lists of symbols for each font which you can use as a quick reference.
** Insert symbols with AutoCorrect
AutoCorrect can be used to quickly insert symbols into a cell by typing specific easy-to-remember characters. But don't get too excited yet. This works only for symbols in the default font and all the fun ones are in the other fonts. Some symbols from the default font such as © copyright and ® registered trademark may already be included in your AutoCorrect list. Haven't you ever typed (c) and ended up with a copyright symbol in your worksheet instead?
If there is another symbol that you'd like to add to your AutoCorrect list, from the File tab click Options, Proofing, AutoCorrect Options. For example, if you want to add the degree symbol (°) to AutoCorrect, enter your 'Replace:' text (e.g. #deg) and, in the 'With:' box, hold down the ALT key and type the symbol's character code (e.g. 0176). To avoid confusion with valid text, I find it helpful to add a (#) character before my 'Replace:' text to make it unique.
This is another one of those deeply hidden Excel features that most aren't aware of.'
Do you see any opportunities for using this in your work? If so, tell me about them.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630