Transpose cell highlighting

pthwaites

New Member
Joined
Apr 14, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have a data sheet containing two correlation matrices. In the sample attached, the matrix on the left shows the Pearson correlations (r), and on the right is the p value (i.e. the statistical significance of the corresponding Pearson correlation). In the sheet on the right, I've used a cell highlighting rule to flag all of the cells which have a p < .05. I would like to transpose ONLY THE CELL HIGHLIGHTING from the right matrix to the left one. That is, if a box is coloured red in the right sheet, I want it also to be coloured red in the left sheet; but I don't want the numerical value in either sheet to be changed. For example, in the example below, the only red cell is H7, which shows p=0.0067 between P.EFSET.Total and VLT.5. The corresponding correlation score in the left side is 0.89. I want this cell to become red, but I don't want its value to change (note: in the actual sheet, there are many more rows and columns, with many more red-highlighted values).

NB. I realise that a simple workaround would be to create a new rule for the left sheet, highlighting values of e.g. r > .8. But this would not be an accurate solution to the problem because the p values are not solely determined by the size of r.

Sample correlation matrices.xlsx
ABCDEFGHIJKLM
1r.EFSET.Totalr.VLT.1r.VLT.2r.VLT.3r.VLT.4r.VLT.5p.EFSET.Totalp.VLT.1p.VLT.2p.VLT.3p.VLT.4p.VLT.5
2EFSET.Total10
3VLT.10.4810.280
4VLT.20.23-0.1210.620.790
5VLT.30.330.680.2310.470.0920.620
6VLT.40.470.140.580.5110.280.760.170.250
7VLT.50.890.680.160.480.4710.00670.0950.730.270.280
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H1:M7Cell Value=0textNO
H2:M7Cellcontains a blank value textNO
H2:M7Cellcontains a blank value textNO
H2:M7Cell Value<0.05textNO
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
200
Office Version
  1. 2016
Platform
  1. Windows
Try applying a custom formula conditional formatting:
B
BCDEFG
1r.EFSET.Totalr.VLT.1r.VLT.2r.VLT.3r.VLT.4r.VLT.5
21
30.481
40.23-0.121
50.330.680.231
60.470.140.580.511
70.890.680.160.480.471
Sheet16
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:G7Expression=AND(H1>0,H1<0.05)textNO
 
Solution

pthwaites

New Member
Joined
Apr 14, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Try applying a custom formula conditional formatting:
B
BCDEFG
1r.EFSET.Totalr.VLT.1r.VLT.2r.VLT.3r.VLT.4r.VLT.5
21
30.481
40.23-0.121
50.330.680.231
60.470.140.580.511
70.890.680.160.480.471
Sheet16
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:G7Expression=AND(H1>0,H1<0.05)textNO
Perfect, thanks very much!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top