Combine a custom Excel format with data validation and free staff from input perfection by letting Excel do the work.
Microsoft Excel data validation is essential to avoid erroneous information. For example, a phone number that isn’t actually a phone number is of no use to anyone. This can happen when someone types letters or too few or too many numeric characters. You won’t do this on purpose, but mistakes do happen. To ensure that the phone number is a valid phone number, you can go with a custom number format, but that won’t be enough.
In this tutorial, I’ll show you how to combine a custom number format and data validation to force users to enter the correct number of numeric digits. Unfortunately, nothing can prevent the user from entering the wrong digits.
SEE: Windows, Linux, and Mac Commands Everyone Needs to Know (Free PDF) (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web supports custom formats and data validation.
How to determine requirements in Excel
Telephone numbers have 10 digits and generally use the format (xxx) xxx-xxxx. Let’s break down our phone number requirements:
- Must be 10 characters long
- Must display the format (xxx) xxx-xxxx
- Must be numeric digits
The first thing that catches your eye is the (xxx) xxx-xxxx format. A custom format can take care of that. The other two require data validation because a custom format does not reject an inappropriate entry – it accepts the entry but does not apply the format.
There are two custom formats that are useful when formatting phone numbers:
- (###) ###-#### — The # character is a numeric digit placeholder
- (000) 000-0000 – the 0 character is also a digit placeholder, but displays a 0 if there are not enough input digits.
Figure A shows both custom formats applied to the same phone number. What you quickly realize is that none of the custom formats solve every problem. As long as the input value is 10 digits, both formats work as expected. However, if it doesn’t, then none of the formats will do the job:
- Neither format works when the input value contains a non-numeric character.
- Neither format works as expected when the input value is less than 10 digits.
- Neither format works as expected when the input value is more than 10 digits.
The bottom line is that you need both a custom format and data validation.
How to apply a custom format in Excel
The solution requires a combination of a custom format and data validation. To illustrate, let’s put the same input values into a Table object and see how a custom format and data validation handle them.
First, let’s create the custom format for column C of the table object shown in Figure B as follows:
- Right-click on C3 and select Format Cells from the resulting submenu.
- In the category list, click Custom at the bottom of the list.
- In the Type control, select General, type (###)###-#### and press Enter. If this custom format is already in the list, select it. There is a space between the characters ) and #.
- click OK.
C3 is empty, so there’s nothing for Excel to do yet, but we know that if the input value is 10 digits, the format will display the value as expected.
The next step is to add data validation.
How to apply data validation in Excel
Excel’s data validation feature allows you to restrict data entry and ensure your data is correct. Most data validation controls comply with business rules. For example, you might want to limit input to dates or numeric values. Those are simple examples, but some requirements are much more complex and the data validation is up to the task. In this case we use an expression.
Now let’s enter a data validation rule to fix the other possible errors:
- If necessary, select C3.
- Click the Data tab
- In the Data Tools group, click the Data Validation option.
- In the resulting dialog box, select Custom from the Allow drop-down list.
- In the Formula control, type =AND(ISNUMBER(C3),LEN(C3)=10 (Figure C).
- click OK.
The expression =AND(ISNUMBER(A2),LEN(A2)=10 checks for two conditions:
- Is the entry a number?
- Is the number exactly 10 characters long?
If either condition is no, data validation rejects the entry.
Now let’s look at how the two functions work together to enforce correct formatting of phone numbers. Type 1234567890 in C3 and press Tab. The custom format takes care of this value, as you can see in Figure D.
Figure E shows the results of entering a234567890. Because the input value contains a non-numeric character, e, the data validation shows an error. Click Repeat, replace a with 1 and press Tab. Data validation accepts the input. Remember that since we are using an Excel spreadsheet object, Excel will copy both the custom format and data validation to new records.
Entering 12345678 and 12345678901 also fails during data validation. The former has too few digits and the latter too many. Make the necessary corrections so that data validation accepts both, as shown in Figure F.
Nothing can prevent staff from entering the wrong numeric character. However, the combination of these two features helps with other input errors. Many users don’t know that you can combine these two features for better input control.