Solution for extreme amount of conditional formattting

Mike S.

Hello!!!

I have a scenario that has an extreme amount of conditional formatting requirements. if completed, the sheet will have over 10,000 conditional formatting rules. While hear there are not limits to the amount of rules, at 1,800 the conditional formatting dialogue box really doesn't want to open any more. Has anyone found a solution for this?

I really hate to have to introduce a new tool to solve this. I was thinking about Smartsheet's excel data connector. I haven't used it since they introduced graphs and charts on dashboards, but maybe its an option. The only issues that ultimately it is a report that will collect all of the data from about 300 different sheets that would all need conditional formatting. Does the excel data connector work with reports?

Other ideas?

Thanks!!!

Tim C

I have to ask, why would your single sheet need 10,000 conditional formatting rules? I've just never ran into someone with over 150. Sheet performance just slows down too much and from an admin perspective would be impossible to maintain. Could you go into a little more detail into your sheet structure. And/Or a screenshot?

Yes you can use a report as the source for the live data connector.

Mike S.

@Tim C I accidentaly posted this issue twice. Here is the answer I gave on the other post about why. Good to know the data connector can pull from a report.

As for the quantity of formatting, the sheet (and resulting report) is a roadmap view of specific schedule items and each item gets its own color format. So on the final report, all jobs are listed (will be around 300). Then up to 30 items from each project are shown on that projects line according to the date. The columns represent a single day and there is 1 years worth of view on the report. So each of the 365 columns could have any of the 30 items on different rows. So on the base sheet, the conditional formatting for each item needs to live in each column. Then the report pulls them all together. I can't share the current view that lives in excel, but if I can find a way to show what I am talkin about, I will post it.

Tim C

Hmm that's really unique. Typically in Smartsheet (forgive me I'm not sure how familiar you are with the platform), we would have each day as a row instead of a column. Smartsheet can't switch columns and rows like Excel can, so it wouldn't be a 1 to 1 transfer. In Smartsheet I would recommend against that many columns unless you absolutely have to. I see the usability of the sheet go way down and performance starts to suffer. Do you or your company have an account team at Smartsheet? There are resources you could possibly pull to take a look. Or another community member if you could provide screenshots. Wish I could be more helpful!

Mike S.

Thank you @Tim C . I've created a sheet or two in Smartsheet. ;). Data starts vertical, transposes to horizontal. That all works fine. Trying to recreate a clients existing experience. Vertical solves the formatting issue but creates different problems in trying to pull data from different sheets. I appreciate the ideas.

Tim C

After connecting with you on LinkedIn I see now you're an expert, sorry for the previous message! After digging a bit more I don't see a workaround unless the client is okay with changing their process. Here is a post that might be worth looking at. I was looking at. You also may be a able to do it through the Smartsheet API. But I've never tried myself.

Mike S.

@Tim C, Ha! No worries. Glad to be able to connect with you and collaborate! Thanks for the info, I'll check it out.