Smartsheet: I hate color (so hide them)

re: “I hate color”. No, not me.

And I don’t mean complicated Conditional Formatting Rules like this:

although I have thoughts on that too.

But I did hear something very close to that sentiment when talking to a customer recently. And like most comments from customers, it got me thinking. (Note to self: That’s why they pay you)

So first let’s talk about Colors in Smartsheet.

Smartsheet Formatting

Smartsheet allows cell level formatting.

This can be by Column, Rows, a single cell or a group of cells.

Last formatting ‘wins’. The column formats above were entered first, then the rows, the cell and groups of cells.

Smartsheet Conditional Formatting

Smartsheet also allows Conditional Formatting. You can read more here.

Here, I have added three Conditional Formatting Rules. If you refer back to that first image, people can get carried away here.

Removing Conditional Formatting Temporarily

Each Rule can be disabled to turn it off without deleting it.

But what if you have more than a few Rules? Even more pressing, what if you want to do this OFTEN?

Well, you can. But only at the expense of removing all colors too.

Removing All Colors Temporarily

First, I added a new column named [Set Sheet Color]. I used a Dropdown List to select between “No Color” and “Color”. I’ll use Row 1 to select my choice. The rest of the column is filled with a formula to reproduce that choice (=[Set Sheet Color]$1).

Next, I create a new Rule to set the background color  White and the text Black. Note that “No Background” and “Automatic” for the options do not achieve what we want as this is considered by Smartsheet to be “no choice” and is rejected.

The Rule is placed first* in the list. At the bottom of the Conditional Formatting dialog box, Smartsheet reminds us why:

Note: higher rules take priority of lower rules.

When [Set Sheet Color] is set to “No Color”, that Rules will set our choices and in effect, all other formatting, conditional or otherwise, are removed.

But only temporarily. Setting the [Set Sheet Color] to something besides “No Color” will ‘restore’ them (though they were never deleted, only overridden).

Removing Cell Colors Temporarily (but keeping Conditional Formats)

Because of order of the Rules, moving the new Rule to the bottom of the list has the effect of turning off all colors not associated with a Conditional Format Rule.

Now when [Set Sheet Color] is changed back to “No Color”, all cells with color formatting are set back to Black text on White background, unless they were affected by a previous Rule.

Have Cake and Eat It Too (Colors Seen and Unseen)

Let’s have both.

I changed the Dropdown List options to “No Color (All Conditions)” and “No Color (Normal)”

and then modified the Conditional Formatting Rules to account for this change.

(Yes, I could have still used “No Color” for the first rule).

And now the three options look like this:

   

Further Improvements and Closing Thoughts

I would much prefer if this feature was available directly in Smartsheet. I hope some day it is is. But I somehow doubt it.

This may be something that I add to some of my design sheets going forward. This is very likely when trying to bring a sense of order to sheets built with lots of Conditional Format Rules (I try to avoid that because they don’t transfer easily from Sheet A to Sheet B).

Which bring me to my last point

*first — I added the first “No Color” Rule at the top. Using the order of operation for Rules, this could be used to expand from an prioritization set of Rules — I want to see the “Red” Rule but not the “Green” Rule. I can only see one definite use case for that, but there are likely others.

If you like this post, please “Like” it.

If you are new to Smartsheet and want to check it out, click here. If you have questions about this post, add them below. If you have questions about something else in Smartsheet, post on the Community. I occasionally stop by to answer them and so do a lot of other talented people.