Excel Data Validation: How to Circle Invalid Data Cells

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, open Data > Data Tools > Data Validation using its drop-down arrow, then choose Circle Invalid Data. Excel places red circles around cells on the active worksheet whose current contents do not meet their data validation rules; the command flags the entries but does not change or repair them.

What the circles show

Data validation is a set of rules attached to cells. A rule might limit a cell to whole numbers from 1 to 100, dates within a specified period, a choice from a list, or text no longer than a certain number of characters. A custom rule can use a formula to decide whether an entry is acceptable.

Circle Invalid Data compares existing cell contents with the validation rule currently applied to those cells. A red oval is a visual warning that a value does not satisfy its rule. For example, if a cell is limited to whole numbers from 1 through 100 and contains 125, Excel can circle it. The marker is not a new cell value, and it does not tell you what the correct replacement should be.

This is especially useful when a workbook already contains data before validation was added, or when values were entered by a route that did not display the usual validation prompt. It lets you inspect questionable entries without sorting or filtering the data first.

How to circle invalid data in Excel

  1. Open the worksheet you want to check. The command checks the active sheet, so repeat the process on other sheets as needed.
  2. On the ribbon, select Data, then find Data Tools.
  3. Open the drop-down attached to Data Validation and select Circle Invalid Data.
  4. Review the red circles and determine which cells need correction or a review of their rules.

If you do not see the command, confirm that you are using the desktop version of Excel and that you opened the Data Validation drop-down rather than only opening the settings dialog. Ribbon layouts can vary with window size and customization. In Excel for the web, open the workbook in the desktop app if the circle command is unavailable. On a Mac, look under Data for the validation options; their placement and availability can vary by Excel build.

To remove the visual markers, return to the same Data Validation drop-down and choose Clear Validation Circles. This clears the circles, not the validation rules or the underlying cell values. It is safe to use after reviewing the flags if you want a clean worksheet display.

What Excel considers invalid

A cell is invalid when its current value fails the rule currently assigned to it. The rule may be a whole-number or decimal limit, a date or time boundary, a text-length limit, a list, or a custom formula. A list rule, for instance, may allow only department names such as Sales, Support, and Finance. A misspelled or unlisted value can be invalid even if it looks reasonable to a person.

Circle Invalid Data does not search every cell for mistakes. A value outside a validation rule is not flagged if the cell has no applicable validation rule. Nor does the command judge whether a value is sensible for your business; it checks the rule, not the meaning. If a rule says any decimal is allowed, Excel will not circle an unusually large decimal simply because it seems implausible.

The validation rule itself matters. If a rule has been changed since data was entered, Excel assesses the contents against the rule that exists now. A cell that was permitted under an earlier rule may therefore be circled after the rule is narrowed.

A concrete example with a custom rule

Suppose cells A2:A100 should contain numeric scores from 1 through 100. Select A2:A100, choose Data > Data Validation, set Allow to Custom, and enter the following formula. The reference starts at A2 because that is the first cell in the selected range; Excel adjusts the relative reference for the other rows.

With scores in A2:A100 and the validation selection beginning at A2, use =AND(ISNUMBER(A2),A2>=1,A2<=100). This returns TRUE for a numeric score from 1 to 100 and FALSE for 125, zero, or text stored in the cell. Once the rule is in place, run Circle Invalid Data to find existing entries that fail it.

For a simple list, a cell can instead be restricted to entries in a source list. This is often preferable to asking people to type a category from memory, since differences such as extra spaces or inconsistent spelling can matter. If a cell contains a value that is not allowed by its list rule, it may be circled during the review.

Why invalid values can get into a validated cell

Validation is useful for guiding data entry, but it is not an all-purpose data-cleaning or security system. In the Error Alert settings, a Stop alert blocks an invalid entry made through the normal entry process. Warning and Information alerts can let a user proceed after a warning or acknowledgement. As a result, a value that does not match the rule can remain in the sheet.

Data may also have been entered before validation was added, brought in from another workbook, or pasted into the worksheet. Some paste operations can replace destination settings as well as contents, depending on what is copied. After importing or pasting data, check that the intended validation rules are still present and use Circle Invalid Data to review the values.

Formulas and changing source lists can also affect the result. A formula-based rule is evaluated according to its current references and the value it returns. If a list rule points to a range that has changed, an entry previously on the list may no longer qualify. When a surprising circle appears, inspect both the cell and the rule before assuming the entry alone is at fault.

How to investigate and fix circled cells

Treat each circle as a prompt to investigate, not an instruction to delete the entry. First identify the rule, then compare it with the cell’s value and the purpose of the column. A date outside an allowed period might be a typo, or it might show that the rule needs to cover a wider period.

  • Correct the value if it is a clear entry error, such as 125 in a score field limited to 1–100.
  • Review the validation rule if the value is legitimate and the permitted range or list is too restrictive.
  • Check cell type and formatting when a value looks right but still fails. A number stored as text, for example, may not satisfy a numeric rule.
  • Check the source list or custom formula if the rule depends on a range, a formula, or another cell.
  • Clear the circles after review with Clear Validation Circles. Do this only when you no longer need the visual audit markers.

Before making bulk corrections, consider saving a copy of the workbook. If you need a record of what was flagged, note the cell addresses or make a separate review list before clearing the markers. The circles are a convenient visual aid, not a permanent audit log.

Common problems and misunderstandings

No circles appear: Check that the active sheet contains cells with validation rules and that you used the Circle Invalid Data command. A data-quality problem in a cell without validation will not be detected this way. Also verify that the rules are still applied to the expected range.

A circle appears around a value that seems correct: The value may be stored in an unexpected form, the list source may have changed, or the current rule may be narrower than intended. Inspect the rule through Data > Data Validation and check the value itself. Do not rely only on its displayed formatting; a date or number can look normal while being stored differently.

Clearing circles does not fix the data: That is expected. Clear Validation Circles removes the visual indicators only. To make a value valid, edit the value or revise the rule. Conversely, deleting the validation rule is not the same as clearing the markers: removing the rule stops that cell from being checked against it in future.

The command does not circle every questionable entry: Excel has no way to infer rules that were never defined. If a column should contain unique IDs, for example, a rule that only checks whether entries are whole numbers will not identify duplicates. Use a suitable validation rule or another review method for the requirement you actually need.

Circle Invalid Data versus conditional formatting

Circle Invalid Data is a quick, temporary-style review tool tied to validation rules. It is useful when you want Excel to mark existing entries that violate those rules, then clear the markers after review. It does not create a reusable visual policy for every future change.

Conditional formatting is different: it applies formatting based on conditions and can remain visible as values change. It may be a better choice when a worksheet should continuously highlight a condition, such as scores below a threshold or duplicate IDs. But conditional formatting does not automatically know your data validation rules; you must create conditions that represent the checks you want. For one-time checking against existing validation, the circle command is usually the more direct choice.

Frequently Asked Questions

Does Circle Invalid Data change or delete cell values?
No. It adds a visual circle around cells that fail their current validation rules. You must edit the values yourself if they need correction.

How do I remove the red circles in Excel?
Open Data > Data Tools > Data Validation and choose Clear Validation Circles from the drop-down. This removes the markers but leaves the values and validation rules intact.

Why does Excel not circle an invalid value?
The cell may not have a validation rule, the active sheet may be the wrong one, or the entry may actually meet the current rule. Check the cell’s rule and its source ranges; the command does not detect errors that have no corresponding validation rule.

Can I circle invalid data across the whole workbook at once?
The command checks the active worksheet. Run it separately on each relevant sheet. If you need a workbook-wide review, make a list of the sheets and repeat the check on each one.

Does circling invalid data prevent future mistakes?
No. It flags existing entries for review. To control future input, set an appropriate validation rule and choose an error alert that suits the workbook, such as Stop when users should not be allowed to enter an invalid value through normal editing.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.