How to use special formatting in Excel

There are many situations where you need to enter a number in a special format; for example, a telephone number. Excel provides four special number formats out of the box that provide automatic number formatting: a format for zip codes, a format for zip codes + 4 digits, a telephone number format, and a format for social security numbers.

Let's take a look.

Here we have a small list of numbers that need special formatting.

These numbers can be entered manually using approaches we've looked at previously.

This works fine, but Excel's Special formats can save you some time typing and keep the number formatting consistent. Special formats aren't listed in the number formats menu on the ribbon. To access, select More Number Formats. Let's go through our list and set the Special formats first, and then come back to enter the numbers.

Now when we enter these numbers, we don't need to worry about spaces, punctuation, or even leading zeros. Excel generates the required format on the fly.

For the zip codes, we can just type 802 without leading zeros, and we don't need to type the hyphen.

We can enter the phone number without spaces or parentheses.

The same is true with the social security number—we only need to enter the number. Excel will group the numbers and add the hyphens as part of the Special format.

The built-in options for Special formats are useful, but limited. However, Excel allows you to create your own custom formats as well. We'll look at how to do that in a future lesson.