What Is Conditional Formatting With Formulas In Google Sheets?
Conditional Formatting with Formulas In Google Sheets helps users to highlight the cells based on the condition or criteria given by the user using formulas and the received output.
The Google Sheets Conditional Formatting with Formulas takes formulas as set rules for the dataset and then highlights the results of the formula.
For example, we have the data table below with Pass or Fail results of five students. We will apply the Conditional formatting using a formula.
Select cells B2:B6 and apply the conditional formatting rule by entering the custom formula =$B2=“Pass”. We will get the below output.
We can observe that the cells with value as “Pass” are highlighted.
Table of contents
Key Takeaways
- Conditional Formatting with Formulas is a feature mainly used for highlighting the logical results of the set formulas based on the criterias. We can set simple formulas like greater or less than any values, or use the MOD() function.
- It provides a clear distinction between the highlighted cells and the others making the data easy to comprehend and keep track of, while working on a large dataset.
- Using this feature, we can highlight alternate rows or columns, starting from the first or the second row or column using the MOD() function along with the ROW() and the COLUMN() functions, respectively.
How To Use Conditional Formatting With Formulas In Google Sheets?
We can use Google Sheets Conditional Formatting with Formulas in a few methods, namely:
- Highlight Cells which has Values greater than, Less than or equal to a number.
- Highlight One Cell Based on Another Cell.
- Highlight All the Empty or blank Cells in the Range.
- Use AND Function to Highlight Cells.
- Use OR Function to Highlight Cells.
- Use COUNTIF Function to Highlight Cells.
- Highlight Every Alternative Row.
- Highlight Every Alternative Column.
Examples
We will consider some examples for Conditional Formatting with Formulas using the above-mentioned methods.
Example #1 – Highlight Cells which has Values Less than 300
Consider the data table below that consists of three employee sales for three days. We will highlight the cell values less than 300.
The steps to use formula to highlight the cell values that are less than 300 are as follows:
Step 1: Choose cell C1:C10 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula = =C1<300 in the “Value or formula” field, and select the color, here blue, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output. Column C cells whose values are less than 300 are highlighted, as shown below.
Example #2 – Highlight One Cell Based on Another Cell
The following data consists of a list of smartphones and their quarterly sales. We will format cell A3 based on the cell value of B6, if it is a number.
The steps to use a formula to format a cell based on another cell value if it is a number are as follows:
Step 1: Choose cell A3 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =ISNUMBER(B6) in the “Value or formula” field, and select the color, here light magenta, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.Cell A3 is formatted because the other cell value, i.e., cell B6, is a number.
Example #3 – Highlight All the Empty Cells in the Range
The table below lists the top smartphone models in the US in column A and their sales from January to March. However, the data appears incomplete and we must highlight the empty cells.
The steps to use a formula to apply the Conditional Formatting on blank cells are as follows:
Step 1: First, choose the cell range A1:D7 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =ISBLANK(A1) in the “Value or formula” field, and select the color, here cyan, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.The cells in the dataset that are empty or blank are highlighted.
Example #4 – Use AND Function to Highlight Cells
Consider the below table listing the students’ scores in columns, A and B, respectively. We will test multiple conditions such as, whether the students have scored greater than or equal to 60 or not, also if the score is less than 80 and then highlight the values.
The steps to use the AND formula to apply the Conditional Formatting feature are as follows:
Step 1: First, choose the cell range B2:B6 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =AND(B2>=60,B2<80) in the “Value or formula” field, and select the color, here yellow, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.
The AND function has found that cells B2, B3 and B5 satisfy both the conditions.
Example #5 – Use OR Function to Highlight Cells
Consider the below data with some random city names. We will use the OR function to highlight the cells that fulfills the set criteria highlight the city names “Bangalore” and “Mysore”
The steps to highlight the city names “Bangalore” & “Mysore” using the OR function are as follows:
Step 1: First, choose the cell range A1:D8 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =OR(A1=”Bangalore”,A1=”Mysore”) in the “Value or formula” field, and select the color, here magenta, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output. All the “Bangalore” and “Mysore” cells in the dataset are highlighted.
Example #6 – Use COUNTIF Function to Highlight Cells =countif($A$1:$D$10,A1)>5
We have the raw data of some city names entered multiple times in the dataset given below. For our reference, we have created a, table in columns F and G, to keep track of the number of times the city names appear in the dataset. We will format the cities that appear more than 5 times in the dataset.
The steps to highlight the city names “Bangalore” & “Mysore” using the OR function are as follows:
Step 1: First, choose the cell range A1:D8 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. There, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =countif($A$1:$D$10,A1)>5 in the “Value or formula” field, and select the color, here orange, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.We see that the“Bangalore” and “Mumbai” cells in the dataset are highlighted as they appear more than 5 times in the dataset,
Example #7 – Highlight Every Alternative Row
The following data consists of products order details such as, order date, customer names who ordered the products and the delivery status. We will format every alternate row using the ROW() and the MOD() functions with the conditional formatting feature.
The steps to highlight alternate rows are as follows:
Step 1: Choose cells A1:E11 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =MOD(ROW(),2) in the “Value or formula” field, and select the color, here light blue, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.The alternative rows are highlighted.
Example #8 Highlight Every Alternative Column
Consider the same data as in example 7. Just as we highlighted every alternative row, we will highlight every alternate column, using the COLUMN() and the MOD() functions with the conditional formatting feature.
The following data consists of products order details such as, order date, customer names who ordered the products and the delivery status.
The steps to highlight alternate columns are as follows:
Step 1: Choose cells A1:E11 – select the “Format” tab – click the “Conditional formatting” option. The “Conditional format rules” pane appears on the right side. Here, click the “Add another rule” option, as shown below.
Step 2: We see the “Single Color” and the “Color scale” tabs. Select the “Single color” tab, then,
select the “Custom formula is” option from the “Format cells if…” drop-down, enter the formula =MOD(COLUMN(),2) in the “Value or formula” field, and select the color, here light red, from the “Formatting style”, as shown below.
Step 3: Finally, click the “Done” option to get the below output.The alternate columns are highlighted.
However, if we want the columns to be highlighted from the second column change the formula to =mod(column()+1,2) by adding +1 after the column function in the formula. Then, the new output will be, as shown below.
Important Things To Note
- Conditional Formatting accepts only logical formulas with either “TRUE” or “FALSE” results.
- A preview of Conditional Formatting is just an indication of how formatting looks.
- While applying the formulas, ensure to select the right dataset. Sometimes selecting the entire dataset will work, while in some cases we might have to select only a small data or a small cell range in the dataset.
Frequently Asked Questions (FAQs)
We can clear the Conditional Formatting in Google Sheets in a couple of ways, namely,
Delete the set rule –
Let us consider example 1’s output image, shown below.
Hover around the set rule in the “Conditional format rules” pane, we will get the “Remove rule” option. Click it, as shown below.
Then, the dataset becomes as how it was at first, as shown below.
However, if we have multiple rules set, we can just clear the rules that are not required. In the same way as learnt, hover around the set rules and delete the rule that is not required, as shown in the images below.
We can use the above learnt options to clear single or multiple rules at once.
At times, we must clear all the rules set for a dataset, such as formatting styles or conditional formatting, then, we can proceed with the following steps,
Select the entire worksheet or just the dataset – go to the “Format” tab – click the “Clear formatting” option, as shown below.
The Google Sheets conditional formatting with formulas may not work for the following reasons, namely:
a) The formatted dataset has been modified or updated and did not get refreshed to display the updated data.
b) We have not set the conditional formatting rules or the set rules are deleted.
c) Also, since Google Sheets are online, it might not work due to network issues.
We have another alternate way to apply the “Conditional formatting” as follows:
First, select the dataset – right-click on the selected dataset – scroll to the end of the list and click the “View more cell actions” right arrow – select the “Conditional formatting” option, as shown below.
Download Template
This article must help understand Conditional Formatting with Formulas In Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Conditional Formatting With Formulas In Google Sheets. We learn how to use Formatting With Formulas, examples, work template. You can learn more from the following articles –
Leave a Reply