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?

1 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:

  data, Sheet1!A1:L200, 
  values, sequence(38, 1, 0), 
  freqs, frequency(data, values), 
  table, hstack(values, freqs), 
  map(data, lambda(v, 
      vlookup(v, table, 2, false), 
      "format Col1 '""" & v & """ ""(""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().

  • Using formatting to 'hide' the numerical value in this way is absolute genius. Nov 2 at 19:44
  • Thanks. The format clause has always been in the query language, but it only became available in Google Sheets a year or two back. It's a great help. Nov 2 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.