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.
So the steps to follow are:
- Highlight the 2 cells
- Click on HOME, CONDITIONAL FORMATTING and then NEW RULE
- Choose the ‘Use Formula to determine which cells to format’ and in the resultant formula bar
- Type =
- Click on the other sheet and cell
- Enter the logical test (in this case =”Yes”
- Click on the ‘Format’ button and choose what you want to happen with the cells
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.