Technology

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.
Bill JelenFebruary 24, 2011


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.

Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

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

 

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

MrExcel 2-24-11 Fig06

 

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

MrExcel 2-24-11 Fig07

 

The dialog starts out with an even-more-dizzying array of choices, as shown below in Figure 8.

Fig. 8

MrExcel 2-24-11 Fig08

 

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

MrExcel 2-24-11 Fig09

 

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.

 

 

Understanding Which ERP Modules Your Business Needs – And When