Conditional formatting lets you format cells so that their appearance changes dynamically according to the value they contain, or to values in other cells. There are many possible applications of conditional formatting, including uses such as:
- Highlight cells above some threshold (For example, using bold text for all transactions over $2000)
- Format cells so their color varies with their value (For example, applying a more intense red background as the amount over $2000 increases)
- Dynamically format cells based on the content of other cells (For example, highlighting the address of properties whose "time on market" field is > 90 days)
You can even format cells based on their value with respect to that of other cells. For example, you could format a range of cells based on their value compared to the median value of the range:
In this example, cells in each row are formatted according to how the value in their age field compares to the median value of all the ages.
Rows whose age is above the median have red text, and those below the median have a red background. Two of the rows have a value for age that matches the median age; these cells receive no special formatting. (For the source code that creates this conditional formatting, see the Example below.)
Conditional formatting rules
Conditional formatting is expressed using formatting rules. Each spreadsheet stores a list of these rules, and applies them in the same order as they appear in the list. The Sheets API lets you add, update, and delete these formatting rules.
Each Rule specifies a target range, type of rule, conditions for triggering the rule, and formatting to apply.
Target range — This can be a single cell, a range of cells, or multiple ranges.
Type of rule — There are two categories of rules:
- Boolean rules apply a format only if specific criteria are met.
- Gradient rules calculate the background color of a cell, based on the value of the cell.
The conditions that are evaluated, as well as the formats that you can apply, are different for each of these types of rule, as detailed in the following sections.
Boolean rules
Boolean rules define whether or not to apply a specific format, based on a condition that evaluates to True or False. A boolean rule takes the form:
{
"condition": {
object(BooleanCondition)
},
"format": {
object(CellFormat)
},
}
The condition can use one of the built-in condition types, or it can use a custom formula for more complex evaluations.
Built-in rules let you apply formatting according to numeric thresholds, text comparison, or whether a cell is populated. These are always evaluated with respect to the target cell.
Custom formula is a special condition type that lets you apply formatting according to an arbitrary expression, which also allows evaluation of any cell, not just the target cell.
To define the formatting applied by a boolean rule, you use a subset of the CellFormat type to define:
- Whether or not the text in the cell is bold, italic, or strikethrough
- The color of the text in the cell
- The background color of the cell
Gradient rules
Gradient rules define a range of colors that correspond to a range of values. A gradient rule takes the form:
{
"minpoint": {
object(InterpolationPoint)
},
"midpoint": {
object(InterpolationPoint)
},
"maxpoint": {
object(InterpolationPoint)
},
}
Each InterpolationPoint defines a color and its corresponding value. The set of three points thus defines a color gradient.
Managing conditional formatting rules
To create, modify, or delete conditional formatting rules, use the BatchUpdate method with the appropriate request type:
Add rules to the list using the AddConditionalFormatRuleRequest
Replace or Reorder rules in the list using the UpdateConditionalFormatRuleRequest
Delete rules using the DeleteConditionalFormatRuleRequest
Example
The following example shows how to create the conditional formatting shown in the screenshot at the beginning of this page. For additional examples, see the conditional formatting samples page.