How to Audit Excel Data Validation and Find Invalid Entries

A shared workbook can have tidy drop-down lists and sensible date rules, yet still contain values that should never be there. A user pastes a block from another file, an export replaces a column, or a formula returns an unexpected status.

That is the weak point in many spreadsheets: Data Validation helps at entry time, but it is not a one-time data-cleaning guarantee. You need to audit the values already in the sheet.

This guide starts with Excel’s fastest built-in check, then shows how to repair the rule, test realistic paste scenarios, and add a visible helper check when the workbook needs an ongoing audit trail.

Quick solution: circle invalid data in Excel

In the Excel desktop app, select the validated range or any cell within it. Then go to Data > Data Validation > Circle Invalid Data.

Excel draws red circles around cells whose current values fail their assigned validation rule. This is the fastest way to find invalid dates, quantities, codes, or drop-down selections that were pasted, filled, entered by a formula, or added by a macro.

1. Select the column or range you want to review.
2. Choose Data > Data Validation > Circle Invalid Data.
3. Correct each circled value, or change the validation rule if the value is a legitimate exception.
4. Choose Data > Data Validation > Clear Validation Circles when the review is complete.

Microsoft documents that invalid values can arrive through pasting, filling, formulas, and macros, which is why this check is useful after an import rather than only while someone is typing. See its guidance on displaying circles around invalid data for the feature’s behavior.

Expected result: only cells that break an existing validation rule receive a red circle. A blank cell is not necessarily invalid; that depends on the rule and whether blanks are allowed.

Why valid-looking worksheets contain bad values

A drop-down list is a good way to standardize a Status column, but it does not make every possible change impossible. Data can enter a workbook through routes that do not behave like a person choosing an item from the list.

How the value arrived	Example	What to do
Paste from another workbook	In Progress is pasted where only Open, Pending, and Closed are allowed.	Circle invalid data after the paste or import.
Fill down	An old product code is copied down into new order rows.	Check the filled range against its validation rule.
Formula result	A formula returns Cancelled but the allowed status list uses Canceled.	Fix the formula or expand the approved list deliberately.
Import or automation	A CSV refresh loads text quantities such as N/A.	Audit after loading and correct the import process.

Also check whether the cells still have validation at all. Select a problem area, then use Home > Find & Select > Go To Special > Data Validation. Choose All to select cells with validation, or Same after selecting a known good validation cell to find matching rules.

Worked example: audit an imported order-status column

Suppose an import populates order statuses in column B. Your permitted values are stored in H2:H5, and cells B2:B1000 use List validation with the source =$H$2:$H$5.

Cell	Imported status	Expected result
B2	Open	Valid
B3	Pending	Valid
B4	In Progress	Invalid and circled
B5	Closed	Valid
B6	cancelled	Invalid if it is not in the approved list

Run Circle Invalid Data. Excel should mark B4 and B6. Do not immediately overwrite them just to remove the circles. First decide whether each value is:

• a spelling or mapping error to correct,
• a real business status that belongs in the approved list, or
• an exception that needs a separate process rather than a new drop-down item.

If your list itself needs rebuilding, use this guide to create and maintain an Excel drop-down list. Keep the source list in a controlled area of the workbook rather than typing list items separately into multiple validation rules.

Check the validation rule before you fix the data

Select one of the validated cells and open Data > Data Validation. Review these three areas.

1. Settings

Confirm the validation type matches the data:

• List for approved statuses, regions, departments, or codes.
• Whole number or Decimal for quantity and budget limits.
• Date for dates within an allowed period.
• Custom for rules that need a formula.

For example, an order quantity in C2:C1000 could use Whole number validation set to between 1 and 500. A pasted quantity of 0 or 900 should then be circled in an audit.

2. Error Alert

On the Error Alert tab, make sure Show error alert after invalid data is entered is selected. Then choose the style deliberately:

Alert style	Best use	Practical effect
Stop	Rules that must not be broken, such as approved status codes	Prevents normal manual entry of an invalid value.
Warning	Values that are unusual but may be legitimate	Lets the user choose whether to keep the entry.
Information	Soft guidance and noncritical checks	Informs the user but allows the entry.

For controlled reporting fields, use Stop. Warning and Information can be appropriate, but they are not enforcement. Microsoft explains these alert styles and the validation-cell selection tools in its Data Validation reference.

3. Ignore blank and the validation range

Ignore blank does not mean “treat any empty-looking situation the same.” A truly empty cell, a formula returning "", and a cell containing spaces can behave differently in later formulas and reports. Define whether the field is required, then test a blank row and a partially completed row.

Also confirm the rule reaches all intended rows. For a growing data set, convert the range to an Excel Table with Ctrl+T. Tables make it easier to extend consistent formulas and formatting as new records arrive. This guide explains how to auto-expand Excel Tables without repeatedly rebuilding your working range.

Add a helper-column audit for repeatable checks

Red circles are excellent for a visual cleanup. A helper column is better when you need to filter exceptions, assign them to someone, or retain a record of the import check.

With the approved statuses in H2:H5, enter this in C2 and copy down:

=IF(B2="","",IF(COUNTIF($H$2:$H$5,B2)=0,"Invalid status","OK"))

The formula leaves blank status cells blank. For any nonblank value, COUNTIF counts exact matches in the approved list. A count of zero returns Invalid status; otherwise it returns OK.

Filter column C for Invalid status to create a focused correction list. This is especially useful after a CSV import, because it gives you an auditable result even after you clear the red circles.

If imported codes or numbers appear valid but lookups fail, inspect their data type as well. For example, 00125 stored as text is different from the number 125. Use the steps for an Excel number stored as text before changing validation rules that were not actually the cause.

Retest the workbook after repairing it

Do not assume a corrected rule will survive the next real-world update. Copy the workbook first, then test the routes your team actually uses.

1. Paste an invalid status into the validated column.
2. Fill an old value down into several new rows.
3. Refresh or import a small sample file, if that is part of the workflow.
4. Run Circle Invalid Data.
5. Check that the helper column, if used, identifies the same exceptions.

AI can be useful before this stage: ask it to propose plain-language rules, likely exception categories, and clear error-alert text. Then verify the proposed list against your business process and configure Excel as the final enforcement and audit layer.

Troubleshooting invalid-data audits

Problem	Likely cause	Direct fix
No red circles appear	The selected cells have no validation, or all current entries meet the rule.	Use Go To Special > Data Validation to confirm the rule exists, then test with a known-invalid value in a copy.
Only some rows are checked	Validation was applied to a limited range before new rows were added.	Extend the validation to the full input range or use a Table-based workflow.
Users can keep invalid typed entries	Error Alert is set to Warning or Information, or the alert is disabled.	Enable the alert and use Stop for mandatory rules.
A helper formula says invalid but the value looks right	Extra spaces, text-versus-number differences, or a different code spelling exists.	Compare against the source list and clean the imported value before changing the rule.
Legitimate exceptions are repeatedly circled	The approved list is incomplete or the field needs a separate exception category.	Update the controlled list deliberately; do not weaken the rule for every value.

Excel Data Validation audit checklist

• Run Circle Invalid Data after major paste, fill, import, or automation steps.
• Use Go To Special > Data Validation to confirm the rule covers the intended cells.
• Use Stop alerts for values that must be controlled.
• Keep list sources in one maintained range.
• Add a helper-column check when exceptions need filtering or review evidence.
• Test the same paste and import paths that cause problems in production.

FAQ

Does Data Validation stop pasted values in Excel?

Not reliably in every workflow. Pasted, filled, formula-generated, and macro-entered values can leave invalid entries behind. Audit the finished range with Circle Invalid Data.

Can I find every cell that has Data Validation?

Yes. Use Home > Find & Select > Go To Special > Data Validation, then choose All.

Should I use a drop-down list or a helper formula?

Use both when the field matters. The drop-down guides entry; the helper formula creates a filterable exception report after imports and bulk changes.

Why are invalid-data circles still visible after I fix values?

Use Data > Data Validation > Clear Validation Circles to remove the display marks after your review.