Show All

Hide All

Data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.) is designed to display messages and prevent invalid entries only when users type data directly in a cell. In the following cases, validation messages won't appear and invalid data can be entered:

• When a formula in the cell calculates a result that isn't valid.
• When a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) enters invalid data in the cell.

You may want to audit your worksheets to look for incorrect data that may cause inaccurate calculations or results. You can identify cells with data validation that contain invalid data by displaying a red circle around them so that you can easily find and correct any problems.

What do you want to do?

Circle invalid cells

Hide validation circles

Circle invalid cells

1. Point to Formula Auditing on the Tools menu, and then click Show Formula Auditing Toolbar.
2. Click Circle Invalid Data .

Microsoft Office Excel displays a red circle around any cells that contain invalid data.

All cells that don't meet their data validation criteria are circled, including values that were typed, copied, or filled in the cells, calculated by formulas, or entered by macros.

Top of Page

Hide validation circles

Do one of the following:

• To remove the circle from a single cell, enter valid data in the cell.
• To hide all circles, point to Formula Auditing on the Tools menu, make sure the Show Formula Auditing Toolbar command is checked, and then click Clear Validation Circles .

Top of Page