Editor’s Note: 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.
Edward K. wins an autographed copy of Learn Excel 97-2007 From MrExcel for his question: “I am trying to format a cell based on data from another cell in the same workbook. I’ve tried to use conditional formatting but I can’t get it to work properly. Here is an example: Cell A2 is $1100.00 and cell C2 is blank. When cell C2 is populated with a Y (means yes, money has been swept) I would like cell A2’s font to change color. Is this possible?”
Conditional Formatting initially looks like it is really easy to set up. If you want to, for example, highlight everything above 92%, or highlight all of the records that are above average. For those options, there are big, obvious menu choices. Frustratingly, Microsoft hides the really useful versions of Conditional Formatting where no one can find them.
Let’s take a quick trip back to the old Conditional Formatting dialog in Excel 97 through Excel 2003. The dialog would start out looking like Figure 1 (below).
Fig. 1
Everyone seems to look through the dropdown menu that starts out “between” where they find choices for less than, greater than, and so on. This is not a good dropdown to use for this purpose. Most people seem to overlook the first dropdown, the one that starts out Cell Value Is. Open that dropdown and you will find Formula Is (see Figure 2, below).
Fig. 2
Change the first dropdown to “Formula Is” and you can create a formula that looks for other cells in the worksheet. In Figure 3 (below), the formula is a simple one that checks to see if C2 is equal to Y.
Fig. 3
However, you can create very complex formulas in Conditional Formatting. The following are two examples.
Mark the entire row of the largest value in green:
Fig. 4
Mark the entire row of any duplicated values, but not the first row:
Fig. 5
Note: You have to use a lot of caution when building the formula. Write it as if it applies to the top left cell in the selection, and use dollar signs liberally to make sure that you are always pointing to the correct column. The formula in Figure 5 (above) initially started out like this one in Figure 6 (below):
Fig. 6
That is, the formula looks like this:
=COUNTIF($A$2:$A2,$A2)>1
You have three references to A2 in that formula. All three references have a dollar sign before the A. The first reference freezes the row to 2, but the other two references leave the row number as relative. This allows the formula to check all cells from A2 down to A in the current row to see how many times the number has appeared.
Excel 2007/2010
The formula theory is the same in Excel 2007, it is just much harder to find in the Ribbon. From the Home tab, open the Conditional Formatting dropdown. Near the bottom of the dropdown, choose New Rule (see Figure 7).
Fig. 7
The dialog starts out with an even-more-dizzying array of choices, as shown below in Figure 8.
Fig. 8
Select the sixth item in the dialog, “Use a Formula to Determine Which Cells to Format” (see Figure 9, below). Once you choose this item, the dialog offers the same formula field as shown above for Excel 2003. You can now enter similar formulas to the ones shown above for Excel 97-2003.
Fig. 9
Bill Jelen, founder of MrExcel.com, is the author of 32 books about Excel. He recently published Slaying Excel Dragons – A Beginners Guide to Conquering Excel’s Frustrations and Making Excel Fun. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.