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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.