Conditional formatting is a tool that allows you to format cells in a way that you can easily differentiate between different types of data you may have in your spreadsheet.
This tool is located in Format >> Conditional Formatting.
You can format cells using a single color, or a color scale.
Each of them is divided into multiple format rules.
Single color
This type of formatting uses a single color if the condition is met, otherwise the cells stay transparent.
You can use different formatting styles, such as bold, italics, underline, strike-through, text color, and fill color.
If you click on the Default name, Sheets will show you several options that you can choose.
Format cells conditions
These rules can be divided into four categories:
- String
- Date
- Numbers
- Custom formula
Let’s create a simple rule for a table.
This example shows how the following rules work.
Color scale
These condition rules can be applied to numbers. In this case, we chose white to a green color scale and all the other options stay the same:
The bigger the number the more intensive the color.
In color scale formatting, three points can be specified:
Minpoint
The minimal value that Sheets takes into consideration. In our case, it’s 19.
Let’s compare this to different minpoints: 0 and 32.
When a minpoint is lower than the differences between values are milder. When the minpoint is higher then the values lower than midpoint are not taken into consideration in conditional formatting.
Midpoint
This point represents the middle color. Click the number under midpoint to see what is the middle number in our example.
In this case, it’s 32.
These are the differences between the following midpoints: 12, 32, and 52.
Maxpoint
The maximum value is taken into consideration. It’s 60 in this example. Let’s try a few different values: 40, 60, 80.
Selecting ranges
There are a few ways to select different ranges.
If you want to select a cell in a single column, you can just drag them. In the same way, you can select multiple adjacent columns.
Applying formatting to non-adjacent columns
The question is, how you can apply the same conditional formatting for non-adjacent ranges.
There are three ways you can do it in Google Sheets:
- Create separate formatting for each column
- Add another range.
When you are inside the Conditional format rules, click Select data range.
Next, add another range.
Drag another column you want. Click OK. Now there are two ranges to which formatting is applied: C2:C1 and E2:E11.
- Using the Ctrl key.
The last one is the handiest. Before applying the conditional formatting to cells, select the first range, and then, while holding the Ctrl key, the second one.
Custom formula
One of the formatting cells conditions for a single color is Custom formula.
This option allows you to create your specified condition where the formatting will be applied.
In this case, we are going to display apply formatting only to odd numbers. Let’s use it the ISODD function for this.
Select numbers and add a conditional rule:
|
1 |
=ISODD(C2:C11) |
Click Done.
Let’s see how our example looks like.
Only the odd numbers are filled with color and the even ones are left without any fill. With custom formulas, you can create much more complicated formulas consisting of many functions.
