Ask Your Question

Apply Results of Conditional Formatting to Other Cells

asked 2016-12-30 03:32:18 +0200

Ludd1te gravatar image

I'd like to apply the _results_ of conditional formatting to other cells, but not the act of conditional formatting.


  1. I have a table of correlations; below that I have a table of the p-values for each of those correlations.
  2. I apply conditional formatting to the lower table of p-values, making all cells with p-values less than, say, 0.01 bold.
  3. I would now like to apply the pattern of which cells are bold and which are not to the table above that of correlations.

If I simply apply the conditional formatting per se to the upper table of correlations, I would bold all the _very low_ (and negative) correlations, not the sufficiently large ones. Instead, I want to use the lower table to note which correlations in the upper table are sufficiently large.

As a simple example:

Upper Correlation Table

.9     .1

.1     .2

Lower Table of p-Values

*.001* .3

.3     .2

I.e., the p-value for the top-left correlation is .001, which is less than my criterion and so is made bold. I would like to copy this pattern of the top-left cell being bold and all others being not bold to the "Upper Correlation Table".

I tried copying just the formatting (Copy Special>Formatting), but this copies the conditional formatting, not just the pattern of which are bold and not.

Any ideas?

Thank you all in advance.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-02-20 09:02:41 +0200

Just use formula in conditional formattings in Upper Correlation Table (UCT). E.g., if UCT is A1:B2, and Lower Table of p-Values is A4:B5, then first select A1:B2, go to Format->Conditional Formatting->Condition..., and define condition as Formula is - A4<0.01. I.e., you define condition for first (top-left) cell in range. As you don't use absolute addressing ($), you will get this formula changed appropriately (like "if value of cell three rows below in the same column is less than 0.01") when applied to other cells in range.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-12-30 03:32:18 +0200

Seen: 58 times

Last updated: Feb 20 '17