How to Validate Phone Numbers in Excel (Custom Format and Data Validation)

Step-by-step

A phone number column should only accept exactly 10 digits and display them as (123) 456-7890. A custom number format handles the display; data validation rejects bad entries. You need both.

1. Add a custom format

Select the phone column, right-click > Format Cells > Custom and enter:

(###) ###-####

The # is a digit placeholder. An alternative is (000) 000-0000, where 0 shows a zero when there aren’t enough digits.

2. See why a format isn’t enough

Both formats work for a 10-digit number, but neither stops a problem entry: one containing a letter, fewer than 10 digits or more than 10. The format accepts the entry and simply doesn’t apply.

3. Add data validation

Select the first phone cell (C3), go to Data > Data Validation, set Allow to Custom and enter:

=AND(ISNUMBER(C3),LEN(C3)=10)

ISNUMBER checks the entry is a number (no letters). LEN checks it has exactly 10 characters. AND requires both; if either fails, Excel rejects the entry.

4. Test it

Type 1234567890: it’s accepted and displays as (123) 456-7890. Type a234567890, 12345678 or 12345678901: Excel shows an error and asks you to retry.

5. Use a table so it spreads

If the list is an Excel table, both the format and the validation copy to new rows automatically. Nothing can stop someone typing the wrong digits, but these two features catch most input errors.

Go further

My Excel Tips & Shortcuts: 100+ Powerful Hacks course collects a hundred time-saving techniques like this one.