Follow these step-by-step instructions to create a conditional formatting rule.
Conditional formatting allows you to create rules on your sheet, whereby the formatting of individual cells or entire rows will update when certain criteria are met.
Learn how to use conditional formatting to change the look of cells in your sheet when they meet certain conditions. For example, you can have all unfinished tasks that are due within the next month highlighted in yellow, or turn any item that goes over budget red.
Apply Formatting Automatically with Conditional Formatting Rules
Conditional formatting rules apply formatting automatically to rows or cells based on the values they contain.
In this article:
NOTE: The conditional formatting feature won't change cell values. To change cell values based on criteria, you'll want to use a formula. For more information about how to create formulas, see Create and Edit Formulas in Smartsheet.
To create or modify conditional formatting rules, you must be the sheet Owner or have Admin-level sharing access to the sheet.
Create a Conditional Formatting Rule
- Click Conditional Formatting on the toolbar to display the Conditional Formatting window.
- Click Add New Rule to create a new, blank conditional formatting rule.
Set the Conditions for the Rule
- Click <set condition> to display the Set Condition window.
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.
- The available criteria will vary depending on the column type. For example, for a Checkbox column, you can apply formatting based on whether 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).
- Smartsheet looks at the numbers in a column formatted for percentage (for example, a % Complete column) as values between 0 and 1. Use decimal values (examples: 0.25 for 25%, 0.5 for 50%) to get the correct comparison, as opposed to whole numbers.
- To further customize your rule (for example, if you want to specify that a Comments column contains the value “urgent”), click define custom criteria.
- To display formatting on rows that fall outside of the condition, select the checkbox to Apply format when condition is NOT met.
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.
Set the Format for the Rule
- From the Conditional Formatting window, 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 certain selected columns in the row. When the rule is set up to apply formatting to multiple columns, you'll see an ellipsis (...) in the rule description.
NOTE: If multiple rules have the same condition and formatting, they will be consolidated into a single rule.
- Click OK to close the Conditional Formatting window.
|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.|
Note that if your aim is to alter the cell or row background color, click the Background Color
Add a Condition
- Click Conditional Formatting in the toolbar to display the Conditional Formatting window.
- Click the drop-down arrow to the left of the rule in the Conditional Formatting window to view additional options.
- To add more conditions to a rule, select 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 use the above steps to 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.
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.
Clone a Rule
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.
To establish the order in which rules are executed, you can rearrange them. To rearrange a rule, clicking it 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 ToJoe 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.
Delete or Disable a Conditional Formatting Rule
- Click Conditional Formatting in the toolbar.
The Conditional Formatting window appears listing all existing rules.
- Choose one of the following:
- To temporarily turn off a conditional formatting rule (for example if you think 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.