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

Want to learn more about Microsoft Excel? If you prefer live courses and live in South Africa look at the MS Excel training courses available. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.