When you work in Excel, you may have data that is invalid. In other words, it falls outside of the criteria that you’ve specified in Excel’s Data Validation feature. It’s often useful to visually identify invalid data. Fortunately, Excel offers an easy way to do exactly that. Let’s learn how to circle invalid data in Microsoft Excel.
How to Circle Invalid Data in Excel
Imagine that you have a column of data in Excel, ranging from 10 to 50. You’ve applied Data Validation rules to the data. In this set, valid data exists in the range from 35 to 55. In other words, the values 10, 20, and 30 are invalid data. It would be helpful to visually show these invalid data values with a clear visual indicator.
Fortunately, Excel offers the ability to circle invalid data. Plus, it’s an automated process. You don’t have to circle the values yourself.
To circle invalid data in Excel, begin by going to the Data tab. You’ll find that on Excel’s ribbon, the series of menus and tabs across the top of your screen. On the right side of the Data tab, you’ll see the Data Validation dropdown menu.
Click on Data Validation, and you’ll see three options. The one you’re looking for is Circle Invalid Data. Go ahead and click on it. When you do, red circles will appear around the invalid data in your spreadsheet. This is a very useful way to visually indicate the invalid data in your files.
Want to remove the circles? Go back to the Data Validation dropdown and click on it. This time, choose Clear Validation Circles. The circles will disappear.
As you can see, it’s easy to circle invalid data in Microsoft Excel. This is the perfect way to add visuals to your Data Validation rules.