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.
