0

I have created a heatmap in excel with correlation data. Now I want to indicate the p value significance. How do I enter * or ** without changing the color of the cell?

AB3
  • 1

1 Answers1

0

You have some options. VBA being the best.

But VBA is not always feasible or possible as boss folks don't always allow it and users don't usually know how to use it for too much anyway. So I shall give the simplest approach.

You don't give even the essential details in your question, nor any response to Comments, so I shall assume as I care to.

Overall assumption is that you created this heatmap using Conditional Formatting ("CF").

The standard way to do so has two difficulties for your situation:

  1. It has to operate on the CF cells directly as there is no way to use the simplest heatmap, a three-color gradient, with a formula in the rule that CF uses to apply it. It operates upon cell numerical values and that's it.

  2. You want two kinds of values in single cells. But that's not something Excel is set up to do and this kind of situation is reflective of that.

If you were doing two such things to single cells and both used the same numerical data that was present in the cells, no problem. If you were doing two somethings that could both use a formula on the data in the cell, no problem. But you're not.

What you CAN do though is place the data in the cells and set up the gradient rule based upon those values. Then set up the p-value significance data in a different set of cells (a helper table of data) either with just the "/**" characters or a string of "/**" and the actual data in the heatmap cells. Harder the second way, as one would need a CF testing-formula that strips that data back off, but doable in about a minute of frustrating use of the CF functionality tools.

Then, set a format using, say, fill patterns that do not obscure the values and heatmap color, but are still visible enough to catch the eye. Use different colors to distinguish the two possibilities, or perhaps a single one for whichever one of them and no special formatting for the other, as seems best to you. Set one for two asterisks and then one for a single one. Move these to be rules 2 and 3 and set the Stop if true box at the right for the second one (the two asterisk test).

Now CF will apply the heatmap, then test for 2 and if necessary, 1, asterisk characters, applying the appropriate fill pattern. (Naturally, other distinguishing formatting could be applied or ALSO be applied if helpful.)

A harder alternative, but perhaps preferred as it WOULD place the "/**" characters in the cells, at least to the viewer's eye, would be to use the separate helper table for the "/**" characters, as above, but instead of using fill patterns, or bold text perhaps, or whatever else seems best, set the numerical format to numbers preceded by the appropriate number of asterisk characters. For example:

\*\*0;0;0;@ ...for the double "" ones
\*0;0;0;@ .....for the single "" ones

You'd have to bear in mind the cells don't actually contain the asterisk characters and that formulas using the data and looking for the characters need to reference the helper table, not heatmapped data, but that's the trade-off. That, and also users calling and saying "I don't see the asterisks in the cells. Yeah, they're on the screen, but they're not in the cells. Yeah, like how do I know they're accurate if they're not in the cells? Yeah, but how do I know?" Etc. ("Ad nauseum" being more on point.)

So two approaches, same idea. Some range there in how you show the output, but really the same underlying approach.

(VBA gets you past all of that.)

Jeorje
  • 21
  • 2