Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.


Conditional formatting based on another sheet

Conditional formatting is a great tool for dashboards and data visualization. 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.

YouTube Conditional formatting based on another sheet

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