How to Use Conditional Formatting in Google Sheets

Google sheets are a great way to collect and present related data of various types. Apart from that you can also take the data from certain rows and columns of your spreadsheet and derive information out of them. For that, you can use Conditional formatting about which I have described in this guide.

As the name suggests, you have to put forth a certain condition upon which information can be derived from a specific set of data from the Google Sheets.

Conditional Formatting in Google Sheets

You have to select a range of data from the spreadsheet. Then you have to place an IF condition. Upon fulfilling the condition the resulting cells from the spreadsheet will be highlighted.

Steps to Use Conditional Formatting

open a new Google Sheets spreadsheet or an existing one
Set a range by dragging your mouse across cells, columns, and rows of the datasheet
Invoke the conditional formatting toolbar by clicking Format > Conditional formatting

The above step is the ideal method when the amount of data in the spreadsheet is not too big.

Formatting with Cells Empty/Non-Empty

This formatting is clearly dependent upon whether data is present in the cells or not. Accordingly, the cells will be highlighted and as per the cell color you have specified.

Conditional Formatting Based on Text

Here are the various clauses based on the text input of the user that devices what portion of data will be highlighted

Text stats with
Text end with
Text is exactly
Text contains
The text does not contain

Let me show you steps that actually are the same for any text-based clause you specify for the Google sheet you are using.

select the range of data
Click Format > Conditional Formatting
Conditional format rules dialog box will open
Go to Format Cells if
Select the type of text formatting
Let’s say you select if Text Contains
Then specify what exactly the text is for example a word or a phrase that repeats a few times in the spreadsheet
So, as per your specification Text Contains, the cells or the columns(specify the range) consisting of the particular word will be highlighted

In the same way you can set any text-based condition and retrieve the resulting information from the spreadsheet.

Highlighting the Whole Row of A Spreadsheet

In this type of formatting, when you specify an IF clause, instead of a particular cell the whole row gets highlighted with the default cell color.

The formatting condition will be “Custom Formula is” followed by the specified value.

select range of spreadsheet
Go to Format > Conditional Formatting
Set Format Cells if to Custom Formula is
Now, if you want to highlight the rows consisting of a certain value, then use the following format
=$specify cell of a particular column <” enter the value “>

For example, you want to highlight the rows that consist of a specific word. You want to highlight the cells from column A of the spreadsheet having the word BMW.

You have to mention a formula like this.

=$A2=“BMW”

Let me explain this one by one.

= means the formula is initiated
$A means the Formula will look within column A
A2 is the sample data in column A
BMW is the data value that means the rows containing the word BMW will be highlighted

Using Numbers for Conditional Formatting

You have to specify the condition based on

greater than
Less than
Greater than equal to
Less than equal to
Is equal to
Is not equal to
Is between
Is not between

Here are the steps,

select the range of data
Format > Conditional Formatting
Click Add new rule
Under Format Cells, if specify a number based clause
Then set a numerical value
Click Done

Depending upon the range of data you have selected the resulting information will be highlighted by coloring the appropriate cells in the default or custom color that you have set.

Using Colors for Conditional Formatting in Google Sheets

When you set color-specific conditions, the resulting cells in the spreadsheet will be highlighted in the default color. On the other hand, the other cells that are not qualifying the IF condition will be highlighted as well in a different color.

Conditional Formatting in Google Sheets Using Dates

In this formatting type, you have to use the date as a parameter in the IF condition, to highlight or retrieve a certain range of data from the Google Sheets.

There are three types of Formatting based on Date

Date is
date is before
Date is after

Now, for the steps for setting the formatting conditions

open the spreadsheet
Select the range
Go to Format > Conditional Formatting
Under Format Cells if, set any of the three Date based clauses I have mentioned above
Like every other formatting set the value of the Date parameter such as Today, yesterday, after a specific date or month, and even year

Accordingly, the resulting cells of the spreadsheet will be highlighted. Remember that for finding using the date the spreadsheet must have Date elements or date as data values present in it.

So, these are the various types of conditional formatting that you can use in your Google Sheets spreadsheet to retrieve numerous specific chunks of information from a collective data table.