How to Use Conditional Formatting in Google Sheets
Conditional formatting in Google Sheets is a powerful tool that allows you to highlight sections of your spreadsheet based on certain criteria set by you.
Using conditional formatting, you can change a cell’s background color or the style of the cell’s text, based on your desired pre-sets. This makes it easy to emphasize and visualize important data.
In this post, we will show you how simple it is to use conditional formatting in Google Sheets. We’ll walk through the process of highlighting cells with certain conditions and discuss when each type of condition should be used.
Conditional formatting in Google Spreadsheets
- Create/open the Google Sheet by going to http://sheets.google.com
- Select the cells or the range you want to apply format rules to.
- Click Format → Conditional formatting
- On the toolbar on your right, go to the drop-down and select a rule.
- You can select a rule-based on your requirement or you can click on Custom formula is and type in a formula manually
- Next, under Formatting style, select a text style, color or background color to be used when the conditions are met.
- Click Done.
For example, I would like to highlight all the cells in this data range with values over $1000.
- Under formatting rules, I will select Greater than from the drop-down, and type in 1000 in the field below it.
- I picked a purple color from the grid, so all the values greater than $1000 can be highlighted in purple.
How to copy conditional formatting in Google Sheets
In order to copy conditional formatting from one data range to another in the same Google Sheet, follow the following steps:
- Select the data range that has the conditional formatting rules applied to it.
- Right-click and then click on Copy (or use the keyboard shortcut Control + C)
- Select the data range of cells where you want to paste the copied conditional formatting
- Right-click on any of the selected cells
- Click on Paste Special, then click on ‘Paste format only’ option (or use the keyboard shortcut CONTROL + ALT + V)
- As specified by the condition set in the first data range, all values greater than or equal to $25000 have been highlighted in the second data range as well.
Conditional formatting based on another cell in Google Sheets
In the previous section, we learned how to highlight/format cells, based on the conditions set within the selected range. However, it is also possible to set conditional formatting in the selected range, based on the criteria of another range in the sheet.
- Open your Google Sheet and select the data range.
- Click Format → Conditional formatting
- On the toolbar on your right, go to the drop-down and select Custom Formula is
As an example, I would like to highlight the sales made by Michelle
- My data range is D2:D18
- My custom formula will be =B2=”Michelle”
- Pick a formatting style
- Click Done
- All the sales made by Michelle will be highlighted
As another example, I would like to see which sales rep made a sale over $2000
- My data range is B2:B18
- My custom formula will be =D2>2000
- Pick a formatting style
- Click Done
- All the sales reps with a unit sale of over $2000 will be highlighted
In another example, the custom formula can be tied to a condition cell, whereby all values lesser than the conditional value need to be highlighted
- Put a value in a cell as a condition i.e. $1000
- My data range is D2:D18
- My custom formula will be =D2<$F$2 (The $ signs ensure the cell is locked in the formula)
- Pick a formatting style
- Click Done
- All the sales less than $1000 will be highlighted
If I change the value in the condition cell, my highlighted values will change dynamically, without having to change the conditional formatting rules
Conditional formatting based on multiple conditions
Multiple conditions can be applied to the same data. However, it is important to note that the order of these conditions is important, as the conditions are applied in the order they are set.
For example, using my data range, I want to highlight all unit sales over $2000
- My data range is D2:D18
- My custom formula will be =D2>2000 OR pick Greater than from the Formula rules dropdown.
- Pick a formatting style
- Click Done
- All unit sales over $2000 will be highlighted
To continue with this example, I want to use the same data range and highlight all unit sales over $1000 in a different color
- Scroll down, click on Add another rule
- My data range is D2:D18
- My custom formula will be =D2>1000 OR pick Greater than from the Formula rules dropdown.
- Change formatting style from the previous condition
- Click Done
- All unit sales over $1000 will be highlighted
Using the Custom formula, multiple conditions can be applied to data ranges, however, the order of conditions is of utmost importance.
Click your data range, then click on Format → Conditional Formatting to see all the formatting rules applied to it on the sidebar.
Using the three vertical dots next to the rule, you can drag and drop the order of rules to change the sequence.
Relevant Formulas
Logical Function IF
Conditional formatting on Google Sheets is based on the embedded IF function:
Logical Function IF
You can use this formula to devise several custom formulae. If true, the value will be highlighted. If false, as below, it will do nothing:
= IF(logical_expression, value_if_true, value_if_false)
Logical Function AND
As an expansion of the IF function, this function highlights values that hold true for multiple logical expressions:
= AND(logical_expression1, [logical_expression2, ...])
Logical Function OR
Again, as an expansion of the IF function, at least one argument must be TRUE for the cell to be highlighted in conditional formatting
OR(logical_expression1, [logical_expression2, ...])
As proficiency develops with using logical functions to implement in your custom formatting, you may find it helpful to combine these functions to create more analytical and impactful datasheets.
References
