Conditional formatting based on another sheet

Conditional formatting is a great tool for dashboards and data visualisation. A common query is whether you can have conditional formatting based on another sheet e.g. in Sheet 1 you want cells to black out based on what has been input on Sheet 2.

Post Excel 2013 ‘conditional formatting based on another sheet’ Method

Previously you needed to make use of Named Ranges but the latest Excel versions allow a direct link.

So in the example below we only want cells B5 and B6 to be visible (blue) if a cell on the second sheet specifies that the email address is valid.

Conditional formatting based on another sheet

So the steps to follow are:

  1. Highlight the 2 cells
  2. Click on HOME, CONDITIONAL FORMATTING and then NEW RULE
  3. Choose the ‘Use Formula to determine which cells to format’ and in the resultant formula bar
    1. Type =
    2. Click on the other sheet and cell
    3. Enter the logical test (in this case =”Yes”
    4. Click on the ‘Format’ button and choose what you want to happen with the cells
conditional formatting based on another sheet

Now depending on what is happening on the other sheet, the cells will change their format.

More tips and trick like this are available in our MS Excel Dashboards course.

How to find cells with conditional formatting

Conditional formatting without numbers

How to remove conditional formatting in MS Excel

Change cell color based on value of cell

Online MS Excel Dashboard Course for you