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.
Also Read
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.
If you want to apply conditional formatting for a wider range of cells (implicating a big spreadsheet)
- then you have to specify the formatting style that will highlight the resulting cells/rows/column differentiating it from the rest of the data.
- It will help bring clarity while presenting information or formatting as per a specific IF condition
- Go to Conditional Format Rules
- Under formatting style, select Default from the drop-down list.
Note: You can also create a custom styling by setting text to be bold/italicized/underlined. You can set the font color and cell background color.
Different Types of Conditional Formatting
Let’s take a look at the various instances of conditional formatting.
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.