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
______________

14 thoughts on “Conditional Formatting From Different Sheet”

  1. 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

    1. 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. I have a different problem. I am maintaining a list of IP addresses for a WAN that has multiple physical locations. I have one workbook with separate worksheets for each physical location. I need to make sure that none of the IP addresses are duplicated. Thus, any IP address entry on ANY worksheet needs to be compared against ALL IP addresses on ALL the other sheets.

      In this case, the IP address entries are in column D and begin on row 3. There are no more than 50 addresses on any given sheet. (D3 to D54.) Any thoughts on how to do this in Excel 2007?

      Thanks in advance,
      Dan

  2. 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. dear sir/ madam
    i want to copy another sheet with particular date condition.
    waiting your reply
    thank
    Tejpal

  4. 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. 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. 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. 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. 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!

  9. Hi, I was wondering I you could help me. I have a workbook and one one sheet I have a column that will have slot Id’s and when I click on that cell I want the value (slot ID) to highlight on my layout sheet that will show me the location of that item in my warehouse layout. I am not sure what I should do or use. Basically I want to click on the cell with the slot ID and have it highlight on another sheet where that slot ID is located. Each cell on that column will be a different slot ID and I want to be able to click on any cell and have the location highlight on my layouts page.
    Can you help?

    Thank you,
    Kris

  10. i need to Create Notifications using Conditional Formatting in Microsoft Excel and i need to show the different sheet Notifications in one sheet

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.