Excel Guides

Conditional Formatting for Errant Phone Numbers in Excel

If you're working with a lot of data in Excel, you may find yourself needing to format phone numbers. Unfortunately, there's no built-in way to do this in Excel. However, there is a workaround that you can use to conditionally format phone numbers. Here's how:

  1. First, open the Excel file that contains the data you want to format. Then, select the cells that contain the phone numbers.
  2. Next, click on the Home tab and then click on the Conditional Formatting button. From the drop-down menu, select New Rule.
  3. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
  4. In the Format values where this formula is true text box, enter the following formula: =AND(LEN(A1)=10,ISNUMBER(A1))
  5. Click on the Format button and then select the font and font color that you want to use for the formatted phone numbers. Then click OK.
  6. Finally, click on the OK button to close the New Formatting Rule dialog box.

That's it! The phone numbers in your selected cells should now be formatted according to your specifications.

Note: This method will only work for phone numbers that are 10 digits long. If you have other formats (e.g. 11 digits or 12 digits), you'll need to use a different formula.

Tip: You can also use conditional formatting to format email addresses and website URLs. For more information, see this article.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.