Conditional Formatting - One rule on multiple columns

Wouter Hijzen

Hello,

To make our Smartsheets more legible when used for input requests, we use conditional formatting. 

For example, if we have a table used for percentage-splits with 12 columns and 100 rows we generally pre-populate these inputs where we can with formulas. If the formulas show 0%, it becomes messy fast. To clean it up, we use conditional formatting to say "if value is 0, then white text" (on white background this becomes invisible). Currently, setting this up is extremely time consuming when building new models as you have to set up a rule for conditional formatting for each column and select to which column to apply it. 

The standard option to apply formatting to entire row is there - is it possible to add as a standard "to entire column"? Also, it is possible to apply a conditional formatting to multiple columns but not to apply one rule to multiple columns. Being able to apply one rule to multiple columns and set application to "to entire column" would make conditional formatting much easier/more versatile.

Thanks!

Wouter

Shaine Greenwood

Hi Wouter,

Conditional formatting is based on making changes on the row level when the conditions are met. 

You can apply a conditional formatting rule that will change the format of an entire row. You can also select individual columns to apply a format to in the span of a single conditional format rule by clicking entire row and selecting the individual columns, making it such that you don't have to create a new rule for every column.

Buster K

Hello Shaine,

As I attempt what you suggested i'm seeing the condition laid out across the row, not down the column.  My data is formatted in columns.

Is there a way to select multiple columns and apply a single conditional format for multiple columns down the columns?

Thank you,

Buster

Amber Benbow

Hi,

I am also experiencing this issue. I have a spreadsheet which is column based and I need to apply 7 conditional formatting across 26 columns (so the clone rule isn't what I am looking for). In Excel, I would have these rules set up and then define the cells they span...

Can you please advise on how I can duplicate this in Smartsheets?

Genevieve P.

Hi @Amber Benbow

Would you be able to share a screen capture of your sheet (but block out any sensitive data), explaining what formatting you want changed?

You should be able to set up a simple rule, such as the first column is not blank, then select the other 26 columns you want to have the certain formatting applied to (by following Shaine's comment above). This would only need to be one rule set up for all 16 columns.

Our Help Center has more information on conditional formatting (see here).

You could also apply formatting once to an entire column, such as colouring the background of the cells, by clicking on the column name and highlighting it if that would be easier (see here).

Let me know if you're having trouble with any of this and I'm happy to help!

Cheers,

Genevieve

Iain Elliott

Hi Genevieve,

The functionality people are looking to achieve is that in Excel you can build a conditional formatting rule and choose the cells (both rows and columns) that the single rule is applied to. In Smartsheet, the rules are applied to a row based on the value found in a specific column as Shaine explained, but this means when migrating an excel spreadsheet into Smartsheet that you can't create like for like formatting without manually creating lots of independent rules per column.

Is there a way to be able to apply a single rule across multiple columns?

Cheers

Iain

Genevieve P.

Hi @Iain Elliott thank you for clarifying!

It sounds like you're looking to format the columns without a condition then (for example, you want the first three columns to all have a blue background, versus only giving them a blue background if a certain Status is selected), is that correct?

If so, you can apply formatting to multiple columns at one time by selecting/highlighting them all. Click on a column name at the top of the sheet to highlight the full column, then use shift-click to select multiple in a row. Once they're all highlighted, you can adjust the formatting from the toolbar menu at the top and that will apply to all of those columns/cells without needing any criteria.

If the columns are spaced out and there are other columns between, you are correct in saying you would need to create multiple independent rules per-column... but one way to get around this would be to drag the columns next to each other, apply the formatting, then drag the columns back to their original location.

Smartsheet functionality does differ from Excel, so here's more information on formatting in general that may help clarify (click here).

Hope this helps!

Genevieve

Sydni

Can you clarify how to apply the same conditional formatting rule to multiple columns at once? I'm working on a compliance tracker with 40 columns, each of which represents a distinct requirement. In any of those 40 columns, when the cell says "incomplete," I want it to be highlighted as a red cell. Right now, I can't figure out how to apply the same formatting rule (Make the cell red if it says "incomplete") to multiple columns except by duplicating the formatting rule and then changing the trigger and formatting columns.

Attached is a picture of how I currently know I can definitely format each "incomplete" cell to show up as red, but for the trigger at the start of each rule, can I select multiple columns at once? I've tried CTRL+click and Shift+click to no avail.

Genevieve P.

Hi @Sydni

Currently the way to do this is to use the duplicate function and then adjust the column the rule is referencing, as you've been doing. There isn't a way to apply a rule across an entire sheet in the way you and Ian are describing - please provide this feedback to our Product team so they can hear about your use-case through this form, here!

Thank you,

Genevieve

Umesh Shah

I am looking for this fucntionality as well, so it looks like there is no way to apply a conditional format based on column entries to multiple columns. I instead i have to copy the rule multiple times over for each column.

Not very elegant.

Andre Munoz

Good morning everyone!

@Genevieve P I'm a fan of using SmartSheet so far, and use it for inventory work, training and experience matrices, and some personal organization. This functionality would really help. Is there any update on proceeding to implement this functionality?

Respectfully,

Andre

Genevieve P.

Hi @Andre Munoz

I don't have an update on this functionality, but please add your voice to this request by filling in this form, here. Thank you!

Genevieve

MMiles

Hello Genevieve, has there been any update to this? I need to use a conditional format to highlight any blank data cells in 25 columns for 4 different regions and the only time that the clone rule will save me is having to set the format. I have to manually change each and every condition and column to format for over 100 conditional formatted columns that I need.

I think it would be a wise investment for Smartsheet to allow multiple column selections when setting the condition for anything using the same criteria across multiple columns, this does not seem like it would be too hard of an implementation for Smartsheet to do since it already lets you select multiple columns to format based on setting the condition, the only thing needed would be a check box to indicate only format cells in the selected columns based on the criteria.

Genevieve P.

Hi @MMiles

Your screen captures are very helpful, thank you! Since this currently isn't possible in Smartsheet, I have submitted your feedback to the Product Team with a link to this Community post to show your example/feedback of how you would see this being implemented.

Thanks,

Genevieve

dcwdcwdcw

I think this is a simple use case... I want to highlight the Finish cell when the date is in the past and the % Complete is less than 100%.

Genevieve P.

Hi @dcwdcwdcw

No problem! You can do that by setting two conditions in your rule, like so:

Click the arrow on the left to add a second condition. Keep in mind that percent columns (if they have % formatting applied) are set in decimals, so you'll want to check that the cell is less than 1 instead of less than 100. See: Conditional Formatting

Cheers!

Genevieve