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.

 

NEWS

IRS sets start date for tax season

The IRS announced that tax season will start in late January and that it will issue refunds to taxpayers despite the partial shutdown of the federal government.

PODCAST

Why CPAs can’t wait on automation tools

What do accounting firms waiting on others to develop AI, automation, and data analytics tools have in common with a baseball fan sitting in a stadium filling with water at an exponential rate? The answer could determine your firm’s fate.