To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Reader Fernando Arellano wins a copy of the book Pivot Table Data Crunching for his question: “How can I use conditional formatting to highlight the largest and smallest values in a table?”
Conditional formatting has been in Excel for more than a decade. Even the old conditional formatting that is part of Excel 2003 and earlier versions could handle this task — although the setting was fairly hidden.
One thing to remember: when the conditional formatting rule has to look at other cells in the range, you should use the Formula version of conditional formatting. For example, say you want to highlight the largest and smallest values in C10:I16. You should start in the top left cell (C10) and select the entire range. Note that the active cell in Figure 1 is C10. You will have to design your conditional formatting formula relative to this active cell.
In Excel 2003 or earlier, choose Format, Conditional Formatting. The dialog box initially appears with “Cell Value Is” in a drop-down field as shown in Figure 2.
Open the drop-down and choose Formula Is. You now have the obscure formula version of conditional formatting. In the formula box of the conditional formatting dialog, design a formula that will evaluate to True or False. Use a relative reference such as “C10” to refer to the active cell. Use absolute references such as “$C$10:$I$16” when referring to the comparison range.
In Figure 3, you can see that the conditional formatting formula of =C10=MAX($C$10:$I$16) will identify the largest value in C10:I16. Similarly, a second formula of =C10=MIN($C$10:$I$16) will identify the smallest value in the range.
There are a number of variations to this formula. To mark the three largest cells, you could use:
To identify the largest value in each individual column as shown in Figure 4, you could use:
Figure 5 presents another common scenario. You want to highlight the entire row for the scenario with the largest profit in column 1. You would use:
Using Conditional Formatting Formulas in Excel 2007/2010
In Excel 2007, the conditional formatting commands were moved to the Home tab of the ribbon. Open the Conditional Formatting drop-down. Near the bottom of the flyout menu, choose New Rule. In the New Formatting Rule dialog box, choose “use a formula to determine which cells to format,” as shown in Figure 6.
You can now use any of the formulas as shown earlier in this article.
New Options in Excel 2007
If you have upgraded to Excel 2007 or Excel 2010 — and if everyone who might open your workbook is using Excel 2007 — you can avoid the formula version of conditional formatting. Microsoft added the Top/Bottom Rules to Excel 2007. Note that although the menu choices say “Top 10,” you can use these new rules to mark the top 20, top 5, or even the top 1 value in a range. To set up conditional formatting using the Top 10 rule, follow these steps:
- Select the entire range of your output cells;
- Choose Home, Conditional Formatting, Top/Bottom Rules, Top 10 Items;
- In the Top 10 Items dialog, indicate that you want to mark only the top 1 item, and choose a green formatting (see Figure 7). Click OK to finish the rule to highlight the top cell;
- Choose Home, Conditional Formatting, Top/Bottom Rules, Bottom 10 Items;
- In the Top 10 Items dialog, indicate that you want to mark only the bottom 1 item, and choose a red formatting. Click OK to finish the rule to highlight the smallest value.
Bill Jelen is the author of 32 books, including Pivot Table Data Crunching, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.