• Technology
  • CFO.com | US

Spreadsheets: Color By Numbers

Formatting a cell's color based on data from another cell can be challenging. But there are a few ways to make it work.

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

MrExcel 2-24-11 Fig01B


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

MrExcel 2-24-11 Fig02


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

MrExcel 2-24-11 Fig03B


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

MrExcel 2-24-11 Fig04


Mark the entire row of any duplicated values, but not the first row:

Fig. 5

MrExcel 2-24-11 Fig05


Your email address will not be published. Required fields are marked *