Conditional formatting based on frequency

Question

I have a range (A1:L200) that has only numerical values (0-37). I have a formula that shows the frequency that each value appears: =FREQUENCY(A1:L200, N1:N38)

How can I use this formula or something similar with conditional formatting to depict a heat map over the range where the most frequent values are some color and lesser frequencies are varying lighter shades?

Answer 1

Heat maps can be created with the Color scale option in conditional formatting, but that requires that the cells being formatted contain the values that determine the color.

One way to create a frequency heat map is to calculate the frequencies, map each value to its frequency, and put the frequencies in a table formatted so that the cell displays the value along with its frequency. This way, the cell contains the frequency but displays the value. The color scale format will only see the frequency and thus shows a frequency heat map as specified in the question.

To implement that, choose Insert > Sheet and put this formula in cell A1 of the new sheet:

=let( 
  data, Sheet1!A1:L200, 
  values, sequence(38, 1, 0), 
  freqs, frequency(data, values), 
  table, hstack(values, freqs), 
  map(data, lambda(v, 
    query( 
      vlookup(v, table, 2, false), 
      "format Col1 '""" & v & """ ""(""0"")""' ", 
      0 
    ) 
  )) 
)

When the value 37 has a frequency of 1, the relevant cell in the new sheet will display 37 (1) and will have a value of 1. Format the new sheet as Format > Conditional formatting > Color scale.

See let(), map(), sequence() hstack(), query() and vlookup().