How to change phone number format in Excel

If you have ever typed a phone number into Excel only to see the leading zero disappear, you are not alone. Today I want to show you how to change phone number format in Excel. Being an Australian, our phone numbers all start with a zero so we encounter this issue frequently.

Excel automatically treats anything that looks like a number as a numeric value, which means formats like phone numbers, ID numbers or codes often lose their structure.

A common question I hear is, “How do I change the phone number format in Excel?” or “Why does Excel remove the zero at the start of my mobile number?” Fortunately, the solution is simple once you understand how to use custom number formats.

Excel includes built‑in formats for dates, currency, percentages and more. But when you need a very specific structure such as phone numbers, custom number formats give you control over how your data appears. This technique works for any country and any phone number pattern. I will demonstrate using Australian phone numbers, but you can adapt the approach to suit your location.

Understand Custom Number Format Symbols

There are two types of format code characters we are going to use to change the phone number format in Excel:

the hash symbol (#)

• Acts as a placeholder for optional digits
• Displays a digit only if it exists
• Useful when your numbers have meaningful digits in every position

the zero symbol (0)

• Forces Excel to display a zero if no digit exists
• Ensures leading zeros remain visible
• Perfect for phone numbers and codes

These two symbols work together to define the layout of your phone number format.

Create a Custom Phone Number Format

To create a custom phone number format in Excel, follow these steps:

1. Highlight the entire column you wish to format with a custom number format by clicking on the column heading:

2. Right mouse click on the column and choose Format Cells or press Ctrl + 1 on the keyboard.
3. From the Number tab select Custom:

4. Excel will display a list of available custom formats, you can use these as a guide for creating other custom formats however for this exercise we will create our own format.
5. You will see a lot of the custom formats have combinations of zero (0) and the hash symbol (#).
6. Place your cursor in the Type field and enter 0# #### #### to represent an Australian phone number with a 2-digit area code and 2 groups of 4 digits, don’t forget the spaces.

NOTE: This format displays a leading zero followed by the 2 digit area code and then two groups of four digits.

4. Click OK.
5. The phone numbers will now be reformatted to the new custom format:

9. Test out the custom number by entering a new number into the next blank cell (A4), don’t type any spaces and you can even leave out the first zero from the area code and Excel will adjust the format for you.

TIP: If the new entry does not format as expected you may have only selected the individual cells which contained the phone numbers instead of selecting the entire column.

10. Repeat the process for any remaining columns. The custom format for an (Australian) mobile/cell number would be 0### ### ###.

Adapting Formats for Other Countries

Once you understand how the # and 0 placeholders work, you can design layouts for any phone number pattern in the world. Here are some examples you can customise:

• United States: (###) ### ####
• United Kingdom: 0#### ### ###
• New Zealand: 0# ### ####
• International format with country code: +## ## #### ####

Simply match the number of zero or hash placeholders to the number of digits you need.

Why this method is better than storing phone numbers as text

You may have seen advice suggesting that you convert phone numbers to text to preserve leading zeros. While this works, custom formats offer several advantages:

• The underlying value remains numeric
• Sorting and filtering remain accurate
• You can reuse the format with just a few clicks

Custom number formats give you flexibility without losing functionality. I hope this gives you a great introduction to creating custom phone number formats in Excel and how you can change phone number formats in Excel.

If you liked this feature of Excel, be sure to check out:

• How to use conditional formatting in Excel, 
• How to use the AutoFill tool in Excel,
• Learn about Excel Formulas,
• 5 basic Excel Functions every beginner should learn,
• Create an Excel graph or chart in under 30 sec,
• Creating an Excel drop down list, and
• How to use VLOOKUP in Excel.

Comment below with any questions.