Excel Data Validation: Check Formatting of Entered Data

Excel allows you to set certain cells to accept only a certain data format that you specify. You can prompt the user with guidelines about the proper way to enter their data. You can even specify that data must fall in a certain data range if you wish. When data entered does not match your specifications, Excel will display an Error Alert that will prompt the user to try again with the correct format.

Specify a Data Format

1. Select the cell or cells that you wish to check during entry.
2. On the Data tab, in the Data Tools group, click Data Validation to open the Data Validation dialog box.
3. On the Settings tab, specify the criteria you wish the entered data to meet:
   • Choose the Time data type in the Allow dropdown menu.
   • Choose between from the Data: dropdown menu.
   • Set your range of acceptable start and end times.
4. On the Input Message tab, you can display a tooltip with instructions to the user before they type.
   • Check the Show input message when cell is selected checkbox to turn on prompts.
5. On the Error Alert tab, specify what happens when the data does not meet your criteria:
   • Check the Show error alert after invalid data is entered checkbox to turn on alerts.
   • Choose the type of alert you wish:
     • Stop – Prevents Excel from accepting invalid data and only allows the user to Retry or Cancel.
     • Warning – User is warned that the data is invalid, but they can click Yes to enter the data anyway, No to edit the entry, or Cancel to delete the entry.
     • Information – User is warned that the data is invalid, but is not prevented from entering it. User can click OK to enter the data anyway or Cancel to delete it.

6. Click OK to apply the data validation.

Remove Data Validation

1. Select the cell or cells affected and click the Data Validation button.
2. In the Data Validation dialog box, click Clear All, then OK.

Hot Tip: To find invalid data that has already made it into your workbook, set up validation as described above and choose Circle Invalid Data [H] from the Data Validation dropdown menu. Excel will highlight the cells that don’t meet your specified criteria. Click Clear Validation Circles to remove them.