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.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.