Check invalid data

Excel 365
Excel has a useful Data Validation feature to display messages and prevent incorrect cell entry. This feature works fine with manual data typing. However, validation messages will not be displayed if invalid data is entered into a cell by copying or filling; a formula in a cell evaluates to an invalid result; a macro enters invalid data into a cell; etc.

Excel allows you to use Data Validation tools to check worksheets for incorrect data that could lead to inaccurate calculations or results. Cells containing invalid data are displayed with a red circle around them so you can easily find and fix any problems:

Step I: Check or modify the Data Validation rule

   1.   Create the Data Validation rule (see Check data entry for invalid entries for more details) if you haven't already created one.

For example, in the Data Validation dialog box, on the Settings tab:

  • In the Arrow dropdown list, choose Whole Number,
  • Specify a comparison operator and appropriate values (for this example, the data should be between 0 and 10):
    Number validation in Excel 365

Step II: Check the Data Validation rule

   2.   On the Data tab, in the Data Tools group, click the Data Validation dropdown list, then select Circle Invalid Data:

Circle Invalid Data in Excel 365

Excel draws circles around the invalid entries:

Circles around the invalid entries in Excel 365

Notes:

  1. If you correct an invalid entry, the circle disappears:
    Circles around the invalid entries 2 in Excel 365
  2. You can create different rules for different cells or data ranges and check all invalid data.

    For example:

    • Column Qty has the rule: Length equals 1,
    • The values in column Data should be greater than 1/1/2020,
    • Column Time should contain values between 9 AM and 5 PM (working hours),
    • The values in the column Mark should be between 5 and 12:
      Example of invalid entries in Excel 365

    After clicking the Circle Invalid Data button, Excel shows all invalid data rounded by red circles:

    Example of Circles around the invalid entries in Excel 365

Remove red circles

To clear the Data Validation check (to get rid of the circles), on the Data tab, in the Data Tools group, from the Data Validation dropdown list, select Clear Validation Circles:

Clear Validation Circles in Excel 365

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more