The sheet owner and collaborators with Admin access to the sheet can create, delete, and disable conditional formatting rules. Conditional formatting rules are used to automatically apply formatting to cells based on the values they contain.
NOTE: The Conditional Formatting feature applies formatting only: that is, it won't change cell values. To automatically change cell values based on criteria, you'll want to use a formula. More on Using Formulas in Smartsheet can be found here.
Jump to video demonstration: Conditional Formatting
Check out our Support Tip in the Smartsheet Blog: Using Conditional Formatting and Formulas Together
Creating Conditional Formatting Rules
- Click the Conditional Formatting button in the left toolbar to display the Conditional Formatting form.
- Click Add New Rule to create a new, blank conditional formatting rule.
- Click <set condition> to display the Set Condition form.
The condition you set determines what will trigger the formatting. For example, if you'd like to apply a background color to all rows assigned to Joe, select the column named Assigned To in the left pane and the criteria Joe in the right pane.
As you set a condition, keep the following in mind:
- The available criteria will vary depending on the column type. For example, for a checkbox column, you can apply formatting based on whether or not the box is checked. For a date column, you can apply formatting based on the date being "in the past" or "in the next [x] days" (among other options).
- (Optional) Click define custom criteria to customize your rule (e.g., if the Comments column contains “urgent”).
- (Optional) Select the checkbox to Apply format when condition is NOT met to display formatting on rows that fall outside of the condition. In the example above, selecting the checkbox would apply formatting to rows NOT assigned to Joe.
- When you're finished setting the condition, click OK.
- From the Conditional Formatting form, click this format to choose the formatting to apply if the condition is met.
The formatting options will appear within the window.
By default, the formatting is applied to the entire row. If needed, click entire row to limit the formatting such that it's only applied to one selected field in the row.
- Click OK to close the Conditional Formatting form.
|Use this option||To do this|
|Font and Font Size||Set the font type (Arial, Tahoma, Times, Verdana or Auto) and size|
|B, I, U,
||Apply bold, italics, underscore, and/or
|(Background Color)||Apply a background color to cells|
|(Font Color)||Apply a color to the font|
|Task Bar Color||
For rows that meet the condition, apply a color to the card border in Card View or the Gantt and Calendar bars in Gantt View or Calendar View. A color will be applied to a card, Gantt bar, or calendar bar only if you've applied the rule to the entire row.
Add a Condition or Clone a Rule
Click the drop-down arrow to the left of the rule in the Conditional Formatting form to view additional options.
Add multiple conditions to a rule by selecting Add Condition (AND). This creates an “and” operator in the rule, indicating that multiple conditions must be met for the format to be applied. For example, you could create a rule with the following two conditions:
If Assigned To is ‘Joe’ and Status is ‘In Progress’ then apply this format to the entire row.
TIP: This rule formats all of Joe's tasks that are currently in progress. The boxes to the right of the rule display a preview of how the formatting will look. The left box shows that the text will be grey, italicized and have a strike-through applied. The right box shows that the color blue will appear on Gantt and calendar bars of rows that meet the criteria.
Rearrange rules to establish your order of priority by clicking on a rule and dragging it up or down in the list. Rules are applied in order from top to bottom. If rules conflict, the rule that is higher in the list takes priority. For example, if your first rule formats the row red when Status is At Risk and the second rule formats the row green when it's Assigned to Joe, any row that has both an At Risk Status and is Assigned To Joe will turn red because the first rule takes priority. Note that rules are additive rather than exclusive, so if one rule turns the text red and another turns the cell background red, both rules will still be applied.
Cloning is useful for replicating an existing complex rule for use in a similar new rule. To clone a rule, click the drop-down arrow to the left of the rule and select Clone Rule.
Deleting or Disabling Conditional Formatting Rules
- Click the Conditional Formatting icon in the left toolbar. The Conditional Formatting form appears listing all existing rules.
- To turn off a conditional formatting rule you may want to use later, disable it by clicking the drop-down arrow to the left of the rule and selecting Disable Rule. (Select Enable Rule to enable it later.)
- To delete the rule entirely, click the drop-down arrow next to the rule and select Delete Rule.