# Using data from two tables in conditional formatting

I have two pivot tables. Both have the same size / structure, but they visualize two different aspects. I can use conditional formatting to change e.g. the background color of the tables cells (e.g. <=0 -> gray, >0 -> light red). Now i want to change the formatting of one tables cells by combining the values of both tables, e.g. like this:

table1.cell = (0 or empty) -> light red (table1.cell = (0 or empty) and table2.cell >0) -> red table1.cell >0 -> normal

I tried to formulate a formula in the dialog for conditional formatting, but i don't know how to write the reference so that the formatting applies to all "connected" cells.

With connected cells i mean: table1.A1 is connected to table2.A1 table1.A2 is connected to table2.A2 .. table1.B1 is connected to table2.B1 table1.B2 is connected to table2.B2

edit retag close merge delete

Sort by » oldest newest most voted

First select Table1.A1. Add the conditional format for (table1.cell = (0 or empty) and table2.cell >0) -> red. Formula Is

(Sheet1.A1 = 0) AND (Sheet2.A1 = 0)


Add another conditional format for table1.cell = (0 or empty) -> light red Cell Value is, Equal to, 0

In the formulas above make sure you don't include $or this stops the reference for other cells in the range from being adjusted. Then in the box Cell Range specify the cells to apply the conditional format by selecting the box and then selecting the range of cells, example $A$1:$C\$9

more