How to use Excel 2010's new conditional formatting with references

By Susan Harkins

Takeaway: Excel 2010’s new referencing option makes conditional formatting more powerful than ever before!

Excel 2010’s conditional formatting feature lets you reference different sheets—something you couldn’t do before. In earlier versions you had to copy or link data to the same sheet. Now, you just include the reference to another sheet as you would any other reference!

To illustrate this new feature, we’ll use the simple products sheet shown below. (I based this example on a subset of the Products sheet in the Northwind database, but changed it considerably for this example.) There are two regions, Northwest (shown below) and Southwest. Each region has its own sheet for tracking product sales.

Now, let’s suppose you want to see where the Northwest region is outselling the Southwest. Using Excel 2010’s new referencing option, it’s easy to reference another sheet, as you’ll see:

1. Select the sales values in Northwest, that’s D2:11.
2. Click the Home tab.
3. In the Styles group, click Conditional Formatting | Highlight Cells Rules | Greater Than. The resulting dialog box will display a default format.
   
4. Click the RefEdit icon (circled in the above picture).
5. Click the Southwest tab.
6. Click cell D2 (in Southwest).
7. Alter the absolute cell reference $D$2 to $D2—that way the row number can adjust to accommodate the entire column in Northwest. In other words, every product in Northwest will evaluate the same product in Southwest.
8. Click the RefEdit icon.
9. Choose an appropriate format, such as Green Fill With Dark Green Text.
10. Click OK. According to the conditional formatting, the Northwest region is outperforming the Southwest region in four products.

This new referencing option doesn’t work with grouped sheets, which makes sense. If you want to see where the Southwest is outperforming the Northwest, just repeat the same process, but start by selecting the sales values on the Southwest tab. Then, in step 5, click the Northwest tab instead of the Southwest tab.

Both Excel 2003 and 2007 will let you go through the motions of selecting a cell or range on another sheet, but when you try to commit the reference, Excel displays an error.
This new conditional formatting option is easy to implement and one you’ll probably find many uses for. Try it out and let us know how you like it!