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

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.