We always try to make our data appealing, presentable and to draw attention to some important parts of our data by highlighting cells and applying various formatting styles of fonts and colors. When we want to make this formatting happen automatically based on some rules and conditions then it is called Conditional Formatting.
Conditional formatting in Google sheets is very easy and user-friendly feature where we can use built-in rules and conditions and can apply custom formulas to make it happen. Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number.
Please follow these steps to apply conditional formatting in Google Sheets.
- On your computer, open a spreadsheet in Google Sheets.
- Select the cells you want to apply format rules to.
- Click Format > Conditional formatting. A toolbar will open to the right.
- Create a rule.
- Single color: Under “Format cells if,” choose the condition that you want to trigger the rule. Under “Formatting style, choose what the cell will look like when conditions are met.
- Color scale: Under “Preview,” select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow.
- Click Done.
Here, we will discuss various rules and conditions to apply conditional formatting in Google Sheets. Suppose we have a sales data set of Salespersons with their earnings, their ranking in respective divisions with respect to their earnings, and rewards points. Using this dataset we will apply conditional formatting rules under the following conditions.
Google sheets conditional formatting rule with one condition
Using the above data set, we can conditionally highlight all salespersons under conditional formatting rule who meet the condition of a specified sales figure. Suppose we want to highlight those salespersons whose earnings are greater than equal to $2,000, irrespective of their divisions.
In order to do that, just select the data range D3:D32, Click Format > Conditional formatting. A toolbar will open to the right. Click on the list of rules under “Format Cells if….” and select Greater than or equal to and then insert 2000 in Value or Formula window. Choose the Formatting Style as per your requirement and press DONE.
This will conditionally format by highlight all those figures with chosen formatting style that meet the condition of having earning figures of $2,000 or greater.
We can also set up conditional formatting by using Color Scale in the conditional formatting rules sidebar with a custom color range instead of just Single color. We can set up color combinations for Minimum, MidPoint and Max Point in the selected data range.
In order to do that, select the data range D3:D32, Click Format > Conditional formatting. A toolbar will open to the right. Click on Color Scale, under preview choose the Formatting Style combination for Min value, MidPoint and Max Point as per your requirement and press DONE as shown below.
Google sheets conditional format rules with multiple conditions
By using the single color, you can add multiple rules to narrow down your target values to highlight which meet given conditions. You can set your formatting style for each rule set and can specify low and high range values to be highlighted as per your requirement. For example, you want to conditionally format the values which have earning figure of;
- Greater than and equal to $2,000
- Less than and equal to $1,000
To do this, we will add two rules with above-given conditions and format them with separate formatting styles as shown below.
As you can see we have created two separate rules for two different conditions and have highlighted the cells conditionally where they meet given conditions.
Google sheets conditional format rules with the custom formula
Using custom formula rule we can conditionally format the rows or cells where conditional logic returns TRUE in the formula. For example, we want to conditionally highlight those salespersons that are ranked highest in terms of their earning in their respective divisions. To make it easier for you, we have already ranked each salesperson in terms of their earnings in their respective divisions in the column with header RANK in Division.
To do that, we will select the data range, and in conditional formatting rule set select Custom Formula and enter your formula to check the condition or logic. As we want to conditional highlight those salespersons who have the highest rank in their division by applying following custom formula in conditional formatting rule.
As we want to check each cell of column E whether it is equal to rank 1 or not, we will freeze the column E by inserting $ sing with column reference and will keep the row reference free to move in selected range.
This formula will check each value in column E against provided condition where rank is 1, and will highlight the row with selected formatting style where this formula will return TRUE as you can see in above image.
We can also use this custom formula to check whether the rank of each salesperson in the respective division is equal to 1 by using this formula;
You can see we have removed the columns “RANK in DIVISION” and ”REWARDS”, but still we can calculate each salesperson rank in respective division using the above custom formula and can check which salespersons have highest ranks in their respective divisions and can highlight those records. You can see the results of both custom formulas are the same.
Conditional formatting based on another cell value
Instead of inserting hardcoded value as a condition in formatting rule, we can just use reference of another cell value as criteria to highlight the targeted rows or cells. We can easily edit or change the criteria value in that cell to modify formatting condition based on another cell value. You don’t have to edit the condition again and again in formatting rule. Like, you want to highlight rows where salespersons earnings are greater than equal to $2,000 as we did previously above. So instead of using this value in condition, we will put this value in another cell and we will use that cell reference as our condition as shown below.
As you can see we have fixed the conditional value in a cell reference and put that cell reference in the custom formula to conditionally format the rows. Now we can easily change the conditional value in that cell and conditional formatting will be updated based on that cell value, like instead of $2,000we can change it to $2,500 and conditional formatting will take effect accordingly.
Conditional formatting based on another cell Text value
If you want to use conditional value as text and want to highlight your targeted rows or cells based on another cell text value, then you can do that easily by following the above rule as a condition. Like instead of using earning figures you can place division name as the condition in another cell and based on that you can highlight all the rows or cells which belong to that division.
Then your formatting condition would be as follows in the formula;
You can see formula is the same but its condition is based on text value in another cell and this formula would perform a check on column C instead of column D as shown in below image.