Conditional Formatting From Different Sheet

Conditional Formatting from Different Sheet

A nice new feature in Excel 2010 is the ability to refer to a different worksheet when creating conditional formatting and data validation. Let’s take a look at how the conditional formatting from different sheet feature works, and create a workaround for older Excel versions.

Compare Cells on Two Sheets

In this example, you’d like to highlight the cells on Sheet1, if they’re higher than the matching cell on Sheet2.

CondFormatSheet2010

To highlight the cells in Excel 2010:
  • Select cells A1:C8 on Sheet1, with A1 as the active cell
  • On the Ribbon, click the Home tab, and click Conditional Formatting
  • Click Highlight Cell Rules, then click Greater Than
  • In the Greater Than dialog box, click in the cell reference box
  • Click on the tab for Sheet2
  • Select cell A1 on Sheet2, and click OK

CondFormatSheet2010a

The cells on Sheet1 are now highlighted if their value is higher than the matching cell on Sheet2.

CondFormatSheet2010b

Conditional Formatting Workaround for Earlier Versions

In earlier versions of Excel, you can’t refer to another sheet in conditional formatting or data validation. With data validation, you can’t even click on the other sheet, to try and create a reference.

In conditional formatting, you can enter the reference in the dialog box, but then Excel yells at you.

CondFormatSheet2007

However, you can refer to named ranges anywhere in the workbook, in both conditional formatting and data validation, so you can use that as a workaround.

To highlight the cells in Excel 2007 and earlier versions:

CondFormat2007d

  • Select cells A1:C8 on Sheet1, with A1 as the active cell
  • On the Ribbon, click the Home tab, and click Conditional Formatting
  • Click Highlight Cell Rules, then click Greater Than
  • In the Greater Than dialog box, click in the cell reference box
  • Type an OFFSET formula that refers to the named range on Sheet2. This will refer to the cell on Sheet2 that is in the same row and column as the active cell.

=OFFSET(DataStart02,ROW()-1,COLUMN()-1,1,1)

  • Click OK

CondFormatSheet2007e

The cells on Sheet1 are now highlighted if their value is higher than the matching cell on Sheet2.

CondFormatSheet2007b
______________