Tip #118 - Easy Excel Formatting Tricks

100 (or more) Computer Tips in 100 (or more) Days

Tip #118 - Easy Excel Formatting Tricks

I was recently reminded about how easy it is to format many special types of frequently used entries.

Phone Numbers
Do you have any lists that include phone numbers? Isn't it a nuisance to have to type the parentheses and the dashes? Well you don't have to! Just type the numbers themselves, don't add spaces between the parts of the phone number...and apply the phone number format. The entry magically appears with the necessary spaces, dashes and parentheses. Wow! If the whole column is going to be phone numbers then select the column before you apply the format.


Social Security Numbers
Phone numbers are probably the most annoying entry but social security numbers are equally annoying. If you apply the Social Security Number format to the column you can again just type the numbers themselves and when you use the Enter key the social security number is properly formatted with the dashes.

These formats are available from the Home tab, Number group. You can either click on the box that says "general" or click the little box in the lower right corner of the Number group to access the dialog box.

Keep in mind that these formats are what's referred to as "masks". They don't change the entry in the cell but rather add a format, much like clicking on the $ option to add dollar signs and commas. These dollar signs and commas don't actually exist (aren't stored) in the cell but are displayed in the cell. If you need to do a mail merge to create a directory that includes the phone numbers or social security numbers formatted with the special formatting than you will NOT be able to use these masks.

Zip Codes
Now that you've seen the options you might be tempted to use the Zip Code format. If you live in the New England area or need to enter zip codes from this area you will find that Excel drops the leading zero in the zip code. This is an issue that is brought up in nearly every Excel class. Do not be tempted to use the Zip Code format to fix this problem. The Excel worksheet will display the leading zero but that zero, like the dashes and parentheses in the other formats, are display characters and are not stored. If you need to do a mail merge to create mailing labels using this Zip Code format you will find out that the leading zero is not there. Ugh.

So what's the answer for zip code formatting that will retain the leading zero? Select the zip code column and format it as Text. You will see that the numbers in the column scurry to the left as text is always aligned. You will probably also see the little green triangle in the cells and if you hover to see what Excel is warning you about, you will see that the program is pointing out that you have numbers that are being formatted as text, exactly as you want it.

My second book, Excel's Amazing Pivot Tables, should be out in a few months. I'll let you know when it's available for purchase.

Happy Computing!

Diane

Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when Diane adds a new post.

Want more tech news or interesting links? You'll get plenty of both if you keep up with Diane McKeever, the writer of 100 Computer Tips in 100 Days, by subscribing to her Facebook posts or visiting her web page, www.dianemckeever.com .