# Conditional formatting based on frequency

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?

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.

• 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