How do I create one conditional formatting rule for a specific range of rows/columns

Evan I · 08/22/23

I have a sheet that has the 1st row and 1st column of data that Im using to do a countifs in another sheet. The full set of rows/columns is about 10,000 cells. I want to set up conditional formatting based on the number in the cell to create a heatmap. For example, 25-50, light yellow, 51-100, yellow and so on. There would be 6 ranges in total.

Right now, I have to create one rule for each range for each row, this would be 108 rows times 6 ranges, so 648 rules. There has to be an easier way.

Additionally, my first row is part of my countifs so I dont want that row included in the conditional formatting.

How do I set up one rule for specific rows/columns so I only have 6 rules for the whole sheet?

Paul Newcome · 08/22/23

You don't have to create a conditional formatting rule for each row. Are you able to provide some screenshots for context?

Evan I · 08/22/23

You can see that Im trying to format only where there are numbers based on a range of numbers. The sheet is 108 columns by 97 rows. I want to set up it up so I select all of the cells and create the rule once like I can do in Excel. Here is the excel example

Paul Newcome · 08/23/23

You are going to have to set up the individual conditional formatting rules for each range for each column, but you do not have to set them up for each row.

Evan I · 08/23/23

So I have to set up 648 rules? 6 rules per column * 108 columns? I cant even clone the rules. When I clone them and then reference the next column I have to set up the condition again. The formatting will stay, but the condition needs to be redone. I cant believe this is an issue this way. Could I build it in excel and then import it in? Would the import bring in Excels conditional formatting? Im guessing no.

Andrée Starå · 08/23/23

https://community.smartsheet.com/discussion/comment/392117#Comment_392117

Hi,

I hope you're well and safe!

Unfortunately, it's not possible now to import conditional formatting from Excel, but it's an excellent idea!

Please submit this as a Product Feedback or Idea (If it hasn't been added already) when you have a moment.

Let's hope improvements are coming later this year so it's more powerful and flexible.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

How do I create one conditional formatting rule for a specific range of rows/columns

Answers

Categories