Find A Goal In A Spreadsheet


Q. I frequently have to search through large spreadsheets to find sales figures that either exceed or fail to meet a goal. It’s a tedious task. Can Excel capture those figures?

A. Excel has a function called Conditional Formatting, and as the name implies, it automatically formats data that meet a condition set by the user—acting like a filter. I’m sure that function will solve your problem.

I’ll demonstrate a simple scenario. Once you’re comfortable with the process, however, you can step it up to handle very sophisticated conditions. In this example, we’re going to tell Excel to locate numbers between 50 and 25 and to color them red so you can spot them easily. We’ll even set a second condition: Color numbers under 25 green. Any numbers over 50 would remain black.

Begin by highlighting the cells to be covered by the conditional formatting. Then click Format, Conditional Formatting , and a dialog box will appear. The box can handle my two conditions, but if you click on the Add>> button you can list more conditions. Use the controls in the dialog box to specify thresholds or ranges.

Click on the down arrow at Cell Value Is and you can choose between setting a value or a formula:

In this case, we select a value. Then click on the next box and you have more choices:

Click on Format in the dialog box and another dialog box appears— Format Cells , which lets you change the filtered numbers’ color or font or even provide borders and patterns.

 

SPONSORED REPORT

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: Economy and health care changes top CPAs’ list

CPA decision-makers’ economic outlook and the House Republicans’ proposed tax changes as part of replacing the Patient Protection and Affordable Care Act received attention recently. See how much you know with this short quiz.