Using Data Validation to circle specific data in Excel
Q: I have an Excel spreadsheet that I update weekly with our employees’ Actual Sales and Sales Goals. I manually insert a red circle to indicate those who did not reach their sales goal by more than a certain amount. Is there a way to automate this task?
A: Inserting red circles around items of note brings attention to things like missed sales goals, low exam scores, insufficient billable hours, and so many other things. You can do this manually, but it’s time consuming and prone to error.
Fortunately, you can automate this task using Data Validation tools.
I have created an example spreadsheet to mimic the issue described in the question. You can access a copy of this workbook here.
See the screenshot below for a snippet of the spreadsheet.
In this example, let’s make it so that all differences $700 or more below the sales goal are circled. Select all the cells that contain the differences. In this example, that would be E2:E51. Go to Data on the Ribbon, and in the Data Tools group, choose Data Validation. The Data Validation window that pops up allows you to set your criteria in many ways. For our example, choose Whole Number for Allow:, greater than or equal to for Data:, and -700 for Minimum:. The screenshot below shows the completed Data Validation window.
Click OK. At this point, your spreadsheet won’t look any different than it did before. Click the dropdown arrow next to Data Validation on the Data tab. Choose Circle Invalid Data. That’s it! If you decide you want the circles removed, simply click the dropdown arrow next to Data Validation and choose Clear Validation Circles. See the screenshot below for the circled spreadsheet.
When you update this spreadsheet with new or different data, you won’t have to re-create the validation rule, but you will need to click Circle Invalid Data again.
This tool would be useful to bring attention to many situations, without much effort. I recently added this feature to my to-do list to circle any items more than two days past due. Boy, was that eye-opening!
A video demonstration of this process is available at the bottom of this article.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
