Special Data Formatting: The Easy Way

Do you enter a lot of special data into MS Excel, such as zip codes, phone numbers or social security numbers?

Ever find yourself wishing the parenthesis and/or dashes would just fill themselves into the correct locations? I mean, wouldn’t it be faster if you could just key 5551234567 in for a phone number and have (555) 123-4567 appear in the cell after you hit the Enter key?

Yeah, I like that idea too. So, let’s see what Excel has to offer!

First, you’ll need to highlight the cells you’re going to work with and then navigate to the Format Cell dialogue window.

For those of you using older versions of Excel, you’ll go to the Format menu, Cells choice.

In Excel 2007, you can get there from the Home tab on the ribbon under the Number section. Click on the little down arrow in the bottom right hand corner or choose “More Number Formats” from the bottom of the Number Format drop down list.

Also, for all versions, Ctrl + 1 or a right click and the Format Cells choice works too.

With the Format Cells dialogue window open, you’re looking for the Number tab.

Toward the bottom of the Categories list, you’re looking for the Special choice.

Take a look at the choices offered on the right hand side.

See them?

Yep, that’s right. All you have to do is choose one of the listed formats (double check that the location choice below is correct, so you’ll get the formatting you expect) and click OK.

When you return to the worksheet, you’ll find that any data entered without the dashes or parenthesis has been transformed. In addition, any new data entered into the cells will not require formatting. Just enter in the digits and let Excel do the extra work for you!

~ April