Search code examples
excelexcel-formulaexcel-2013conditional-formatting

How to conditional format with dynamic references

I want to highlight all the cells containing a formula error or formulas followed the below steps

  1. Did a CTRL+A and selected all the cells
  2. Home > Conditional Formatting > Use a formula to determine which cells to format
  3. Applied the formula =OR(ISERROR(A1),ISFORMULA(A1))
  4. Selected a fill colour and clicked on ok

Expectation: All the cells which is having a formula or error value like #N/A need to be selected.

Please let me know what i am doing wrong here

Solution

  • Formulas in conditional formatting work with reference to the active cell in the selection. In the example given below I have selected B2 to E6 with the active cell as C3 and then applied the conditional formatting.

    Example Conditional formatting

    In the example above in the selection B1 to E6 the active cell is C3. Now when you use =ISERROR(D4), it is effectively applying it to R[-1]C[-1] because you are checking the value in D4 according to the formula and applying it on the selected cell i.e. C3 as you can see in the image.

    Thus if you now put #N/A in Cell D4, Cell C3 will become Orange

    If instead the active cell in the selection was D4 instead of C3 then the conditional formatting would highlight all #N/A

    If you want to understand clearly, try =ISERROR($D$4) and put a #N/A in D4, that will highlight all the cells B2:E6.

    Else you can also go to

    • File>Options>Formulas and check R1C1 reference style