Google Sheets conditional formatting cell reference

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

Google Sheets conditional formatting cell reference

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:

Screenshot of formatting to highlight values above/below median

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 Only use the formatting features listed above. Although the CellFormat type can express other formattingincluding underlining, alignment, or bordersattempting to use these in a conditional formatting rule results in a 400 invalid request error.

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.

Apps Script  sheets/api/spreadsheet_snippets.gs   View on GitHub   /** * conditional formatting * @param {string} spreadsheetId spreadsheet ID * @returns {*} spreadsheet */ Snippets.prototype.conditionalFormatting = (spreadsheetId) => { try { let myRange = Sheets.newGridRange(); myRange.sheetId = 0; myRange.startRowIndex = 0; myRange.endRowIndex = 11; myRange.startColumnIndex = 0; myRange.endColumnIndex = 4; // Request 1 let rule1ConditionalValue = Sheets.newConditionValue(); rule1ConditionalValue.userEnteredValue = '=GT($D2,median($D$2:$D$11))'; let rule1ConditionFormat = Sheets.newCellFormat(); rule1ConditionFormat.textFormat = Sheets.newTextFormat(); rule1ConditionFormat.textFormat.foregroundColor = Sheets.newColor(); rule1ConditionFormat.textFormat.foregroundColor.red = 0.8; let rule1Condition = Sheets.newBooleanCondition(); rule1Condition.type = 'CUSTOM_FORMULA'; rule1Condition.values = [rule1ConditionalValue]; let rule1BooleanRule = Sheets.newBooleanRule(); rule1BooleanRule.condition = rule1Condition; rule1BooleanRule.format = rule1ConditionFormat; let rule1 = Sheets.newConditionalFormatRule(); rule1.ranges = [myRange]; rule1.booleanRule = rule1BooleanRule; let request1 = Sheets.newRequest(); let addConditionalFormatRuleRequest1 = Sheets.newAddConditionalFormatRuleRequest(); addConditionalFormatRuleRequest1.rule = rule1; addConditionalFormatRuleRequest1.index = 0; request1.addConditionalFormatRule = addConditionalFormatRuleRequest1; // Request 2 let rule2ConditionalValue = Sheets.newConditionValue(); rule2ConditionalValue.userEnteredValue = '=LT($D2,median($D$2:$D$11))'; let rule2ConditionFormat = Sheets.newCellFormat(); rule2ConditionFormat.textFormat = Sheets.newTextFormat(); rule2ConditionFormat.textFormat.foregroundColor = Sheets.newColor(); rule2ConditionFormat.textFormat.foregroundColor.red = 1; rule2ConditionFormat.textFormat.foregroundColor.green = 0.4; rule2ConditionFormat.textFormat.foregroundColor.blue = 0.4; let rule2Condition = Sheets.newBooleanCondition(); rule2Condition.type = 'CUSTOM_FORMULA'; rule2Condition.values = [rule1ConditionalValue]; let rule2BooleanRule = Sheets.newBooleanRule(); rule2BooleanRule.condition = rule2Condition; rule2BooleanRule.format = rule2ConditionFormat; let rule2 = Sheets.newConditionalFormatRule(); rule2.ranges = [myRange]; rule2.booleanRule = rule2BooleanRule; let request2 = Sheets.newRequest(); let addConditionalFormatRuleRequest2 = Sheets.newAddConditionalFormatRuleRequest(); addConditionalFormatRuleRequest2.rule = rule2; addConditionalFormatRuleRequest2.index = 0; request2.addConditionalFormatRule = addConditionalFormatRuleRequest2; // Batch send the requests const requests = [request1, request2]; let batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest(); batchUpdate.requests = requests; const response = Sheets.Spreadsheets.batchUpdate(batchUpdate, spreadsheetId); return response; } catch (err) { // TODO (developer) - Handle exception Logger.log('Failed with error %s', err.message); } };

Java  sheets/snippets/src/main/java/SpreadsheetSnippets.java   View on GitHub   List<GridRange> ranges = Collections.singletonList(new GridRange() .setSheetId(0) .setStartRowIndex(1) .setEndRowIndex(11) .setStartColumnIndex(0) .setEndColumnIndex(4) ); List<Request> requests = Arrays.asList( new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=GT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setTextFormat( new TextFormat().setForegroundColor( new Color().setRed(0.8f)) )) ) ) .setIndex(0) ), new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest() .setRule(new ConditionalFormatRule() .setRanges(ranges) .setBooleanRule(new BooleanRule() .setCondition(new BooleanCondition() .setType("CUSTOM_FORMULA") .setValues(Collections.singletonList( new ConditionValue().setUserEnteredValue( "=LT($D2,median($D$2:$D$11))") )) ) .setFormat(new CellFormat().setBackgroundColor( new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f) )) ) ) .setIndex(0) ) ); BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, body) .execute(); System.out.printf("%d cells updated.", result.getReplies().size());

JavaScript  sheets/snippets/snippets.js   View on GitHub   var myRange = { sheetId: 0, startRowIndex: 1, endRowIndex: 11, startColumnIndex: 0, endColumnIndex: 4, }; var requests = [{ addConditionalFormatRule: { rule: { ranges: [ myRange ], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [ { userEnteredValue: '=GT($D2,median($D$2:$D$11))' } ] }, format: { textFormat: { foregroundColor: { red: 0.8 } } } } }, index: 0 } }, { addConditionalFormatRule: { rule: { ranges: [ myRange ], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [ { userEnteredValue: '=LT($D2,median($D$2:$D$11))' } ] }, format: { backgroundColor: { red: 1, green: 0.4, blue: 0.4 } } } }, index: 0 } }]; var body = { requests }; gapi.client.sheets.spreadsheets.batchUpdate({ spreadsheetId: spreadsheetId, resource: body }).then((response) => { var result = response.result; console.log(`${result.replies.length} cells updated.`); });

Node.js  sheets/snippets/snippets.js   View on GitHub   const myRange = { sheetId: 0, startRowIndex: 1, endRowIndex: 11, startColumnIndex: 0, endColumnIndex: 4, }; const requests = [{ addConditionalFormatRule: { rule: { ranges: [myRange], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [{userEnteredValue: '=GT($D2,median($D$2:$D$11))'}], }, format: { textFormat: {foregroundColor: {red: 0.8}}, }, }, }, index: 0, }, }, { addConditionalFormatRule: { rule: { ranges: [myRange], booleanRule: { condition: { type: 'CUSTOM_FORMULA', values: [{userEnteredValue: '=LT($D2,median($D$2:$D$11))'}], }, format: { backgroundColor: {red: 1, green: 0.4, blue: 0.4}, }, }, }, index: 0, }, }]; const requestBody = { requests, }; this.sheetsService.spreadsheets.batchUpdate({ spreadsheetId, requestBody, }, (err, response) => { if (err) { // Handle error. console.log(err); } else { console.log(`${response.replies.length} cells updated.`); } });

PHP  sheets/snippets/src/SpreadsheetSnippets.php   View on GitHub   $myRange = [ 'sheetId' => 0, 'startRowIndex' => 1, 'endRowIndex' => 11, 'startColumnIndex' => 0, 'endColumnIndex' => 4, ]; $requests = [ new Google_Service_Sheets_Request([ 'addConditionalFormatRule' => [ 'rule' => [ 'ranges' => [ $myRange ], 'booleanRule' => [ 'condition' => [ 'type' => 'CUSTOM_FORMULA', 'values' => [ [ 'userEnteredValue' => '=GT($D2,median($D$2:$D$11))' ] ] ], 'format' => [ 'textFormat' => [ 'foregroundColor' => [ 'red' => 0.8 ] ] ] ] ], 'index' => 0 ] ]), new Google_Service_Sheets_Request([ 'addConditionalFormatRule' => [ 'rule' => [ 'ranges' => [ $myRange ], 'booleanRule' => [ 'condition' => [ 'type' => 'CUSTOM_FORMULA', 'values' => [ [ 'userEnteredValue' => '=LT($D2,median($D$2:$D$11))' ] ] ], 'format' => [ 'backgroundColor' => [ 'red' => 1, 'green' => 0.4, 'blue' => 0.4 ] ] ] ], 'index' => 0 ] ]) ]; $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([ 'requests' => $requests ]); $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest); printf("%d cells updated.", count($response->getReplies())); return $response;

Python  sheets/snippets/spreadsheet_snippets.py   View on GitHub   my_range = { 'sheetId': 0, 'startRowIndex': 1, 'endRowIndex': 11, 'startColumnIndex': 0, 'endColumnIndex': 4, } requests = [{ 'addConditionalFormatRule': { 'rule': { 'ranges': [my_range], 'booleanRule': { 'condition': { 'type': 'CUSTOM_FORMULA', 'values': [{ 'userEnteredValue': '=GT($D2,median($D$2:$D$11))' }] }, 'format': { 'textFormat': { 'foregroundColor': {'red': 0.8} } } } }, 'index': 0 } }, { 'addConditionalFormatRule': { 'rule': { 'ranges': [my_range], 'booleanRule': { 'condition': { 'type': 'CUSTOM_FORMULA', 'values': [{ 'userEnteredValue': '=LT($D2,median($D$2:$D$11))' }] }, 'format': { 'backgroundColor': { 'red': 1, 'green': 0.4, 'blue': 0.4 } } } }, 'index': 0 } }] body = { 'requests': requests } response = service.spreadsheets() \ .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute() print('{0} cells updated.'.format(len(response.get('replies'))))

Ruby  sheets/snippets/lib/spreadsheet_snippets.rb   View on GitHub   my_range = { sheet_id:           0, start_row_index:    1, end_row_index:      11, start_column_index: 0, end_column_index:   4 } requests = [{ add_conditional_format_rule: { rule:  { ranges:       [my_range], boolean_rule: { condition: { type:   'CUSTOM_FORMULA', values: [{ user_entered_value: '=GT($D2,median($D$2:$D$11))' }] }, format:    { text_format: { foreground_color: { red: 0.8 } } } } }, index: 0 } }, { add_conditional_format_rule: { rule:  { ranges:       [my_range], boolean_rule: { condition: { type:   'CUSTOM_FORMULA', values: [{ user_entered_value: '=LT($D2,median($D$2:$D$11))' }] }, format:    { background_color: { red: 1, green: 0.4, blue: 0.4 } } } }, index: 0 } }] body = { requests: requests } batch_update = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new batch_update.requests = requests result = service.batch_update_spreadsheet(spreadsheet_id, batch_update) puts "#{result.replies.length} cells updated."

Video liên quan