Conditional Formatting in Google Sheets ( 6 Useful Examples )

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : May 14, 2021 | Review Date : May 16, 2021 | Last Update : May 29, 2021
Google Sheet, Spread Sheet
How to use Conditional Formatting in Google Sheets

If you work with large data set, you must validate formatting conditions many times. Google doc’s development team identify the value of this matter, and they introduce a conditional formatting feature. conditional formatting in google sheets is a superset of IF Function. It contains pre-defined conditions statements that can automatically apply for preset styles based on the data argument. Also, you can implement custom conditional as well.

 To access conditional formatting in google sheets, 

  1. Open a spreadsheet in Google Sheets.
  2. Select cell range which you need to format
  3. On the top of navigation click Format (middle of Insert and Data navigation link) > Select Conditional formatting.
  4. Then create rules using formatting criteria (Single color or color scale)
  5. Click Done Button
About This Article
In this article, we cover basic to advance usage of conditional formatting in google sheets with practical examples and feature of formatting rule, as well

Conditional Formatting VS IF Function in google sheets

I hope you already know that IF statements work with the TRUE and FALSE returns. 

In conditional formatting in google sheets, it only executes the TRUE result to the defined preset style argument in the spreadsheet (If the condition statement is TRUE, THEN return this preset style in the cell). You must define a separate condition (+ add another rule) rule for an additional argument (IF FALSE or IF ELSE) and the return always needs to be TRUE in condition. 

 If you are already familiar with IF Functional in google sheets, it deals with multiple arguments in a single statement (If the condition statement is TRUE, do this, else do this), and you can deal with many advances (nested argument) operation. I’m extremely comfortable with Native IF statements rather than conditional formatting because it enables execute complete arguments in a single query.

Feature of conditional formatting in google sheets

Before we move to conditional formatting functionality, let’s check what are the features and can format based on a specific condition.

Feature of conditional formatting in google sheets

There are two types of color formatting options in the dialog box. Single color and color scale

  1. This is a range field that represents which cell range you need to apply that conditional formatting. 
  2. This condition criteria dropdown. It contains a set of pre-defined argument options listed based on values, texts, and dates. You can also apply a custom formula if not exit in the pre-defined argument list.
  3. This is the conditions formatting argument text field. You can insert values or formulas such as logical operators (AND / OR). make sure that, it only emits TRUE 
  4. This is a formatting style that can be used to change text or background
  5. This Done and Cancel button 
  6. If you need to add another conditional rule. you can click and generate.

About example

Let's take a student exam mark spreadsheet., In this spreadsheet contain 3 data row ( Student Name, Gender, Number of attempts, Exam Mark, Practical Mark, Total Mark ) total mark calculate using exam mark and particle mark then divide by 2 ( exam mark + particle mark / 2 ) & additionally it has row header called Filter Result. It merges 2 cells using H & I column. Its use for highlight cells which given condition.

Download Saample Sheet

To Import the example datasheet into your spreadsheet,

  1. Open a spreadsheet in Google Sheets.
  2. On the top of navigation click File > Import (import datasheet)

Let’s check how doing following example with this sample student mark sheets.

Example 01

Change  cell color using the value in conditional formatting

In the above student mark data sheet, 
If the Total Mark greater than the 75 Student mark, the Total mark cell needs to be the green background color.

Change color based on the value using conditional formatting in google sheets

  1. On the top of navigation click Format (middle of Insert and Data navigation link) > Select Conditional formatting.
  2. Select the cells you want to apply format rules to.  base on the example sheet I insert the F2:F14 call range. (Manually ignore A1 header row range).
  3. Formatting (If condition) criteria dropdown containing a set of pre-defined options. Select greater than option
  4. Then in the formatting argument text field can apply which value of the formula needs to combine with the conditions formatting. Base on the example enters 75 (value).
  5. In the formatting style section, select background color as green (color bucket icon)
  6. Then click the Done Button.

Throughout the examples, we cover how to implement conditional formatting with value and highlight.

Example 02

Use Color Scale on conditional formatting in google sheets

The color scale will differ in intensity based on the value in the cell range, make sure that Custom formulas will not be supported in color scale formatting in Google Sheets.

In the above student mark data sheet, 
Highlight Student Total mark value using a color scale.

Apply color scale n to given cell range using conditional formatting in google sheets

  1. On the top of navigation click Format (middle of Insert and Data navigation link) > Select Conditional formatting > then selected color scale tab.
  2. Select the cells you want to apply format rules to.  base on the example sheet I insert the F2:F14 call range. (Manual ignore A1 header row range).
  3. The select color scale you need to present on the cell range color scale present left (min value) to the right (max value)
  4. In midpoint execute your min value to start color, you can also change that value range color using a color bucket icon in midpoint execute your middle color range and maxpoint top value range of color scale. Make sure that your color scale will excuse left to right (left midpoint right max point). 
  5. Then click the Done Button.

Custom formula with conditional formatting in google sheets.

Now you all know that conditional formatting is built with a set of predefined If Function (conditional statement). but sometimes you cannot build a rule using existing conditional may be it will not suit your requirement. Instead of that, you can use a custom formula, which helps to build your custom formulas in conditional formatting in google sheets.

custom formulas also type of If Function, but it can be carryout your formula which means you don’t want to start with =IF.  Make sure that all custom formulas start with an equal sign (=). It can combine conditional formatting rules. As same as other custom formulas only return TRUE.

Example 03

Custom conditional formatting based on another cell value ( highlight row based on cell value )

In the above student mark data sheet, 
Need to highlight the entire student row (as green color) who get total mark greater than 75
  1. On the top of navigation click Format (middle of Insert and Data navigation link) > Select Conditional formatting > then selected single color.
  2. Select the cells you want to apply format rules to.  base on the example sheet I insert the F2:F14 call range.
  3. Then select Custom formula is an option from formatting dropdown to apply the custom formula
  4. Next to apply the custom formula, insert the below statement into the formatting argument text field. =$F2>75 custom formula is in conditional formatting in google sheets
  5. In the formatting style section, select the background color as green (color bucket icon)Then click the Done Button.
  6. Then click the Done Button.custom conditional formatting based on another cell value ( highlight row based on cell value )

Example 04

Logical Operation (AND, OR ) in Conditional Formatting in Google Sheets.

If you already know about the IF Function in google sheets, logical operators use to connect multiple operations in a single Is statement. Its advantage is you can reduce the number of nested If operations. you already know that conditional operation also pre-defined If function, but you cannot operate with the nested conditional rule. So, this logical operation highly benefits for overcome that kind of situation.

Still don’t you have any idea about how to handle IF Function & logical operation (AND, OR, or NOT) In google sheets, we highly recommend reading our Right Way to Use Google Sheets IF Function (With 6 Helpful Tips )  Article,

In the above student mark data sheet,
highlight the entire student row (as green color) who get total mark greater than 75 & number of attempt 1 & 2 time face for the exam.
  1. On the top of navigation click Format (middle of Insert and Data navigation link) > Select Conditional formatting > then selected single color.
  2. Select the cells you want to apply format rules to.  base on the example sheet I insert the F2:F14 call range.
  3. Then select Custom formula is an option from formatting dropdown to apply the custom formula
  4. Next to apply the custom formula, insert the below statement into the formatting argument text field.  =AND($F2>75, OR ($C2 = 1, $C2 = 2))Logical Operation (AND, OR ) in Conditional Formatting in Google Sheets
  5. In the formatting style section, select background color as green (color bucket icon)
  6. Then click the Done Button.

Logical Operation (AND, OR ) in Conditional Formatting in Google Sheets

 
 

Conditional formatting based on another cell value in google sheets

In most cases, data sheets use for analysis data using base on a given value and then formatting these cells based on the given condition. it helps to analyze the gravity of the datasheet. check box and dropdown value condition often use to operate primary value usage for conditional formatting in google sheets.
In the above student mark data sheet,
filter data using checkbox interactive status handle.in student mark sheet Contain the Filter Result column that field build merge with H & I columns.
Example 05
If checked I:2 cell checkbox (>75) , then highlight rows (with green color) student who got greater than 75 Total mark. Conditional formatting based on another cell value in google sheets
  1. On the top of navigation click Format ( middle of Insert and Data navigation link ) > Select Conditional formatting > then selected single color.
  2. Select the cells you want to apply format rules to.  base on the example sheet I insert the F2:F14 call range.
  3. Then select Custom formula is an option from formatting dropdown to apply the custom formula.
  4. Next to apply the custom formula, insert the below statement into the formatting argument text field.  =AND ($I$2, $F2 > 75) condition expression - Conditional formatting based on another cell value in google sheets
    1. In the formatting style section, select background color as green (color bucket icon)
  5. Then click the Done Button.

Example 06
If checked I:3 cell checkbox (< 75) then highlights the rows (with red color) the student who got less than 75 Total mark.Conditional formatting based on another cell value in google sheets

  1. On the top of navigation click Format ( middle of Insert and Data navigation link ) > Select Conditional formatting > then selected single color.
  2. Select the cells you want to apply format rules to.  base on the example sheet I insert the F2:F14 call range.
  3. Then select Custom formula is an option from formatting dropdown to apply the custom formula
  4. Next to apply the custom formula, insert the below statement into the formatting argument text field.    =AND ($I$2, $F2 < 75) condition expression - Conditional formatting based on another cell value in google sheets
  5. In the formatting style section, select background color as green (color bucket icon)
  6. Then click the Done Button.

Still don’t you have any idea about checkbox usage in google sheets,  we highly recommend reading our How to add checkbox in google sheets ( 7 Useful Example )  Article.

Conclusion

Google sheets, rapidly growing every day, conditional formatting in google sheets one of the amazing feature which google developer team introduced for the community. you can with complex formulas, various logical operators, and also for data analysis. Make sure that the conditional formula only returns a True value and that reduces your query time due to a lot of predefined arguments.

Table of article :