I want conditional formatting with color scale to easily highlight values from lowest to highest within a row. However, I have many rows. If I go for range A1:X50, the formatting will only be based on the entire range, and not per row. But it seems really exhaustive to have to write A1:X1, A2:X2, A3:X3 etc. It beats creating individual rules per row, but it ends up being a very long string for something I would assume has some shortcuts with asterisk or similar? Tried and failed a bit with tests like (A1:X1)*(A50:X50), and so on... but no luck. What am I missing?

1 Answer 1


it seems really exhaustive to have to write A1:X1, A2:X2, A3:X3

That will not do what you want. A color scale rule will look at all the values in the ranges you specify, collectively.

There are three approaches to get row-by-row heatmaps:

  1. Create a bunch of color scale rules, as you mention above. There will need to be as many rules as there are rows in the data.

  2. Create a bunch of regular conditional formatting custom formula rules to show a heatmap per row. You will need as many rules as there are columns in the data.

  3. Copy the values to another tab using a formula that replaces values with ranks for the heatmap, while formatting each cell to display the actual value from the source cell. That can be done with query() using the format clause. You will only need one color scale rule to format the whole range.

To try the third approach, choose Insert > Sheet and put this formula in cell A1 of the new sheet:

  highlight_, lambda(values, how_to_color, show_values, let(x,values,y,how_to_color,z,if((y="highlight")+(len(y)+rows(y)*columns(y)=1),map(x,lambda(v,match(v,x,0))),if(y="count",map(x,lambda(v,countif(x,v))),y)),makearray(rows(x),columns(x),lambda(r,c,let(v,index(x,r,c),f,index(z,r,c),if(len(v),query(f,"format Col1'"""&v&""""&if(show_values,""" (""0"")""",)&"'",0),iferror(ø))))))), 

  data, Sheet1!A1:X50, 
  ranks, byrow(data, lambda(row, 
    map(row, lambda(v, rank(v, row))) 

  highlight_(data, ranks, ) 

Then format the range A1:X50 in the new sheet with a color scale.

Your Answer

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

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