Google Sheets is always the first choice: to house tons of data or export any report, create a to-do list, or manage works. The simple rows and columns make it easy to work with a large data set in one go.
While Google Sheets has a range of functions to create and organize data, there is another feature that makes Spreadsheets extremely powerful - Conditional Formatting.
Conditional Formatting in Google Sheets lets you add rules to highlight a cell, an entire row, or an entire column when it meets the set criteria. This helps in understanding the data on the sheet at a glance. Here we will take you through the steps to add conditional formatting to Google Sheets and the different types of rules that you can use.
How to Apply Conditional Formatting in Google Sheets?
Google Sheets conditional formatting follows the If-Then rule. The color or style of the cells changes based on the rules that you have set for that cell or that row or column.
To apply conditional formatting, you need to mention:
- Range: The cell or cells where you want to apply the formatting.
- IF clause: The criteria which will trigger the formatting.
- Style: The change that will reflect in the cells once the IF clause is met.
For instance, you can set conditional format rules in Google Sheets like IF cell A2 has text, THEN change background color to yellow. Here:
- Range is A2
- IF clause is there will be a text in A2 cell
- Style is the yellow color
Now, let’s see how to apply conditional formatting in Google Sheets for more than a single cell. To start, open a blank Google Sheets if you want to enter data first and then start working on it, or open the Google Sheets where you already have your data.
Go to Format option and scroll down to find Conditional Formatting option.
You will see two options:
- Single color: Option to use one color to highlight specific cells when criteria are met
- Color scale: Option to create a color range to highlight the progression of values like ascending or descending, and so on.
We will look into using color scale a little later.
For demo purposes, we will proceed with the default color.
- In Apply to Range, we will mention only the A2 cell number.
- Under format rules, we will add a rule that is empty.
- As mentioned, we will keep the default color (you can change it as per your choice).
- Click on Done to save your format rule.
You will instantly see the A2 cell turned into the default color.
The Format cells if clause has a long list of options that you can choose from. You can apply formatting conditions for texts, dates, numbers, as well as add custom conditional formatting in Google sheets to suit your needs.
Applying Formatting Rules for Non-empty Cells
You can apply conditional formatting rules for a cell that contains data. It can be number, text, date, or any other detail. To set a rule for a cell that is not empty, go to Format > Conditional Formatting > select format cell if it is not empty. Here we will apply this rule for the entire range of data on our sheet: A1 to F14.
Now if you look, all the cells that are empty are not highlighted. Here we have used the default color. If you want, you can change it to any other color. Google Sheets will show you 6 color variations. However, you can always pick a color of your choice. You will notice a Custom Format option as well. This option lets you style your texts. You can bold, italic or strikethrough your texts. You can also change the font of your texts.
Now, let’s look into the other formatting rule available in Google Sheets conditional formatting.
Example 1: Conditional formatting with texts
A text-based rule changes the text that matches the rule you set. You can create rules for:
- Text contains
- Text does not contain
- Text starts with
- Text ends with
- Text is exactly
Let’s say you want to highlight people working at a specific location like Bangalore.
Go to Format > Conditional Formatting > Enter cell range - here its C1:C14 > Under Format cell if select text contains > in the value or formula box, enter Bangalore > Done. In the custom format option, you can also choose to Bold the text or make it Italic for further segregation.
You will see all the cells containing Bangalore are highlighted.
Note: Here we have specified the cell range from C1 to C14. This means the formatting rule is applicable to only this range of cells. If you are adding any new information from C15, the rule doesn't apply.
To ensure that your formatting rule applies to the entire column, make sure to select the entire column first and select Conditional Formatting from the Column format option [Look for the small drop-down arrow after selecting the column]. This ensures that the formatting is applied across the entire column and continues to apply even when you add new rows.
Example 2: Conditional formatting with numbers
You can set up triggers for number-based conditional formatting in Google Sheets. With numbers, you can set up rules for:
- Greater than
- Greater than or equal to
- Less than
- Less than or equal to
- Is equal to
- Is not equal to
- Is between
- Is not between
Let’s try with Greater than trigger. To apply this rule, go to Format > Conditional Formatting > Select cell range - here we will enter A5 to E14 > Under Format cells if select Greater than > in the value box, enter your choice of value. Here it is 5790 > Select color code or go with default > Done.
You will see all numbers greater than 5790 are now highlighted in the sheet.
Note: Google Sheets can read all types of number entries - from currencies to percentile to hours.
Example 3: Conditional formatting using dates
To apply date-based rules, you need to maintain a consistency in how you enter the date (same applies for currency or percentile or any other number type). To do this, select the column where you have added dates. Go to Format > Number > Date. If you want to change it to any other format, scroll down to More Formats and select More date and time formats. Once done, now you can add formatting rules.
There are three types of rules that you can use to apply date-based formatting in Google Sheets-
- Date is
- Date is before
- Date is after
This encompasses dates in any format you write. Here we have in dd/mm/yyyy format.
To apply a date-based rule, go to Format > Conditional Formatting > Select cell range - here we will enter A5 to F14 > Under Format cells if select Date is.
You will get options:
- In the past week
- In the past month
- In the past year
- Exact date
Let’s select Exact date. In the value box, enter the date in the format that you have set for your sheet. Dates matching your criteria will get highlighted.
Using Custom Formula to apply conditional formatting
Custom formula lets you add specific formulas to apply conditional formatting based on other cell values or to an entire range.
For instance, when using text-based rules, cells that contained the mentioned text “Bangalore” were highlighted. With Custom Formula, you can highlight an entire row in which the text “Bangalore” is present.
To do this, select the entire range from A5:F14. Go to Format > Conditional Formatting > select Custom Formula > enter custom formula =$C5=”Bangalore” > Done.
You will see all the rows in which Bangalore is present in column C is now highlighted.
How did this work?
In this custom formula =$C5="Bangalore" :
- = indicates starting of the formula
- $ gives Google Sheets indication to lock column C and search only in Column C. Otherwise, it will look into all the columns that have texts. Here you specifically tell Google Sheets to start looking from cell C5 and only in column C. If you add $ before 5, then Google Sheets will look into column C and row 5 only.
- "Bangalore" indicates the text to search for.
To highlight rows that do not contain the mentioned value (here Bangalore), you can use custom formula =$C5<>"Bangalore". You can use custom formulas for dates and numbers as well.
Using Color Scale to Apply Conditional Formatting
Until now, we used a single color to highlight cell style changes against formatting rules. The color scale option lets you showcase a progression in numbers, either in ascending or descending order (as you set it). To use color scale, select your data range.
Here let's do it for column D and E.
- Select columns D and E or manually enter cell range as D1:E1000 (since this sheet currently has 1000 rows)
- Select Color Scale
- Select default color (which is currently auto-applied in the sheet below) or pick a custom color grade
- The main part: Enter the minimum value, midpoint value, and maximum value to create a gradation. You can choose it to be a number, percentile or
- Here you can change color code for your sheet
After adding the values and selecting your choice of colors, you will see the gradation applied on your sheet. The color codes are applied based on the range you have set.
These are the basics of using conditional formatting in Google Sheets. It is recommended to apply a condition to an entire column so that the rules are automatically applied when new data is added.
Now that you know its basics play around with data in Google Sheets and make it look more insightful for you and your team.