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 improved conditional formatting 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

______________

You may also like...

11 Responses

  1. Khushnood Viccaji says:

    Hi,
    I tried another possibility in Excel 2007, and it works.
    Instead of defining the range name ‘DataStart02’ as Sheet2!$A$1 (absolute),
    I kept its RefersTo reference as Sheet2!A1 (relative).

    Then I don’t need to use the OFFSET function in the Greater Than dialog cell reference box.
    Just enter >DataStart02 in that box.

    Any disadvantages with this approach ?

    Khushnood

    • Sal says:

      Khushnood,

      Actually your approach is much better – why? Offset is a resource hog and no matter how small the spreadsheet, good principles and practices need to be performed all of the time according to my standards.

      Sal.

  2. Riya says:

    Hi, I am working on 2 sheets. Sheet 1 has parts purchased. And sheet 2 has description of each part. I want to highlight those cells in sheet 2 whose parts are selected in sheet 1. Plzzzzzzz help, pl.

  3. Tejpal Bhalla says:

    dear sir/ madam

    i want to copy another sheet with particular date condition.

    waiting your reply
    thank
    Tejpal

  4. Steve says:

    I was having a problem with this, because my cell selection kept inserting dollar signs ($) into the cell reference. When it does this, it will compare the entire selected range in the first sheet all to the single cell selected in the second sheet (instead of to the matching cell in the second sheet). If you delete the dollar signs in your cell reference, it will work properly. Deleting the single quote marks in the sheet reference is not necessary.

  5. Steve says:

    Also, if you want to mark all unmatched values, not just those that are greater than, you can select “New Rule” (instead of “Highlight Cell Rules”) and set the Rule Type to “Format only cells that contain”, then set the dropdown that says “Between” to “not equal to” instead, set your cell reference as above (deleting the dollar signs if necessary).

    To make it highlight the cells, click ‘Format…’, select the ‘Fill’ tab, select your highlight color, and click ‘OK’.

    Click ‘OK’ again on the New Formatting Rule box to enable it.

  6. Deepak Panchal says:

    I have a following condition:
    If A1 = “ZERO” then colour of B1 should become orange if the value of B1>0 or B1 should become yellow if B1=0.

  7. Mike says:

    I tried thin in XL2013 & got an error message saying “You may not use references to other workbooks for Conditional Formatting criteria.” I assume it’s b/c this application of the feature has been removed from versions of Excel subsequent to 2010.

  8. Ardy says:

    This worked fine for me but I’m looking to take it a step further. Is there a way I can compare the cell from one sheet to multiple cells within a range on another? This is so that for example:

    Sheet1B1 > Sheet2B1 and Sheet1B1 > Sheet2B2 and so on and so forth, highlighting Sheet1B1 if it is greater than the value of any cell within the designated range of the Sheet2

    Thanks!

  1. February 2, 2013

    […] Well, I don't have 2003 installed anymore to test it but here's something to try…. Conditional Formatting From Different Sheet | Contextures Blog […]

Leave a Reply

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