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.

Previously you needed to make use of Named Ranges but the latest 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 Excel Dashboards course