- Choosing the correct formatting type for phone numbers will mean they display correctly.
- Use the built-in Phone Number format for US phone numbers.
- Use Text formatting for international phone numbers.
Unfortunately, Excel doesn’t always handle phone numbers correctly – or, at least, it doesn’t handle them as we’d expect. There are many different ways phone numbers can be formatted, and simply entering them into Excel doesn’t usually provide the correct formatting. This guide will show you how to format phone numbers in Excel.
How to Format US Phone Numbers in Excel
Phone numbers from the United States usually look like these examples:
(202) 555-0143
(312) 555-9987
(213) 555-1098
As you can see, they follow a consistent format, with the leading 3 digits (also known as the area code) being enclosed in brackets.
Fortunately, these are quite simple to format correctly in Excel, as there’s a built-in format for them.
Step 1. Select the list of phone numbers in your Excel spreadsheet by clicking the letter at the top of the column that contains the data. Note: Make sure that you deselect the header row, if present. You can do this by holding down the Ctrl key on the keyboard and clicking on the cell you want to deselect.
Step 2. Once you’ve selected the list of phone numbers, right-click the column. In the menu that appears, click the Format Cells option.
Step 3. In the Format Cells dialog box, you’ll find different ways of formatting cells – General, Number, Currency, and so on. Selecting a format type lets Excel interpret the data in the chosen cells.
For US phone numbers, there’s (as mentioned before) a built-in formatting type. You’ll find it in the Special category on the list. Click Special, and you’ll be presented with a list of formatting options.
To see the list, ensure that the Locale (location) setting is set to English (United States). In this list, you’ll find formatting options specifically related to number formats commonly used in the United States.
Select the Phone Number and then click OK.
How to Format Phone Numbers in Excel With Country Code
International phone numbers pose their own set of challenges in Excel. These numbers usually look like this:
+44 20 7946 0821
+91 22 4090 5678
+82 2 3456 7890
The numbers start with a plus sign (+) followed by a 1, 2, or sometimes a 3-digit number. This is known as the country code.
The plus sign can cause problems, as Excel will try to perform a calculation. This can result in phone numbers being displayed incorrectly. As you can see in the screenshot below, Excel has tried to calculate something from the phone number +447854662365, resulting in an odd-looking cell.
By far the simplest way to handle international phone numbers is to format them as text. Here’s how:
Step 1. Select the list of numbers – again ensuring to deselect the header.
Step 2. Right-click the selected column and then Format Cells.
Step 3. This brings up the Format Cells dialog box. This time, choose the Text option in the category list.
Click OK, and Excel will interpret all of the phone numbers as text, meaning that it won’t attempt to do any calculations and the numbers will display as you intend.
