In addition to using the Excel 2016 Data Validation feature to restrict what kind of data can be entered into cell ranges of a worksheet, you can use it to mark all the data (by circling their cells) that are outside of expected or allowable parameters.
To use the Data Validation feature in this way, you follow this general procedure:
Select the cell range(s) in the worksheet that need to be validated and marked.
Open the Data Validation dialog box by clicking the Data Validation button on the Data tab of the Ribbon or by pressing Alt+AVV, and then use its options to set up the validation criteria that determine which values in the selected cell range are out of bounds.
Choose the Circle Invalid Data option from the Data Validation button's drop-down menu on the Data tab of the Ribbon.
The figure shows an example of how you might use Data Validation to mark entries that are below a certain threshold. In this case, it's set up for Excel to mark all subtotal monthly sales cells entries in the range D4:D15 in the 2016 Sales worksheet that are above $50,000 by drawing a red circle around their cells.
To set this up in the 2016 Sales worksheet, follow these three steps:
Selected the cell range (D4:D15) with monthly sales data for the year.
Opened the Data Validation dialog box (Alt+AVV) and then on the Settings tab selected Decimal in the Allow drop-down list and Greater Than in the Data drop-down list, and entered 50000 in the Minimum text box before clicking OK.
Chose the Circle Invalid Data option from the Data Validation button's drop-down menu on the Data tab. (You can also press Alt+AVI.)
To remove the circles from the cells marked as invalid, choose the Clear Validation Circles option from the Data Validation button's drop-down menu or press Alt+AVR. To clear the validation settings from the cells, select the range and then open the Data Validation dialog box and click its Clear All button before you click OK.
