How to Use Conditional Formatting in Google Sheets [Complete Guide]

In this article we will learn about what is Conditional Formatting and how we can use them in Google Sheets with 10 real life use cases.

What is Google Sheets?

Certain formatting style is applied to certain types of data in any spreadsheet program in order to make the data more visible as well as more visually appealing. In the same way, Google Sheets which is an online spreadsheet program, also allow different formatting styles options. It is up to the user to find out which one is more suitable for the data being dealt with. Common formatting styles include Bold, Italic, Underline, Strikethrough, Text Color, Background Color, etc. We can apply the mentioned and a lot other types of Formatting Styles for cells in Google Sheets

What is Conditional Formatting in Google Sheets?

Conditional Formatting is a feature provided by Google Sheets for the ease of user. When dealing with hundreds and thousands of data and values, it is a very difficult task to format all the data based upon different rules manually. For example, it is possible that you want to highlight the successful candidates for job application using Green Highlight, or failed students in Red Highlight, or it is also possible that you would like to format the cells of a record based upon a certain checkbox, or format a cell based upon other cell’s value or simply find minimum and maximum in a large data range. You can do the above mentioned and a lot more tasks using Conditional Formatting in Google Sheets.

As the name suggests, conditional formatting applies specific formatting style to certain cells which satisfy the condition provided in Conditional Formatting Rules. This condition decides if the cell will have certain formatting style or not. We adjust the conditional formatting rules and conditions to the requirements and nature of our problem and let the Google Sheets automatically apply the formatting style after checking the condition. Rather than going through each set of records by hand and waste plenty of our time, we can simply use Conditional Formatting in Google Sheets. The basic purpose of Conditional Formatting is to automate the manualized work for applying Formatting Styles.

Why we use Conditional Formatting in Google Sheets?

Rather than going through each record one by one, we use Conditional Formatting in Google Sheets to make our sheets more presentable and accurate in terms of appearance. We always decide the Formatting Style upon the nature of the problem. No matter what formatting style is required by the problem, we can apply it using the Conditional Formatting function provided by Google Sheets.

Use Cases

Suppose we have a problem, where we have to authenticate the candidacy of applicants based upon the fact whether they have received COVID Vaccine doses and highlight eligible candidates in Green and ineligible candidates in Red so that we would not have to check the Vaccine information about each candidate, we can just apply Conditional Formatting and the issue will be solved in a few seconds rather than going through each entry separately to know eligibility. We can solve this problem by using method for Conditional Formatting if box is checked Google Sheets.

Another problem could be to apply conditional formatting based on another column cell values. This type of problems need a thorough analysis on the other column and based on that column’s values, we decide if the current column needs to Formatting Style or not. One problem could be to highlight the products based on their value of sales so business can decide which products should be stocked more and which should not be stocked more often. We can solve it using Google Sheets Conditional Formatting based on another column.

Since the purpose of Google Sheets is to automate the manual work and that of Conditional Formatting is to automate it even further. Then, in some cases it is also possible that apparently the formulae in Google Sheet is not working properly and we would like to highlight the cells having a formula so we can look at the cells containing formula to check any mistake in formulae and correct it. In this case, we can use the method for Google Sheets Conditional Formatting if cell contains formula.

Google Sheets facilitate a user much more than discussed above through Conditional Formatting which will further be discussed below.

How to use Conditional Formatting in Google Sheets?

Before going through other uses of Conditional Formatting in Google Sheets, let us first discuss the method for using it. Here we will discuss the general method for How to apply it in google sheets.

Steps to apply Conditional Formatting in Google Sheets (here we find duplicate cells) is as below:

Step 1:

Select the cell(s) where you wish to apply Conditional Formatting either by dragging the mouse over it or typing the cell numbers in Top Left Corner Cell Name box as:

Step 2:

Now apply Conditional Formatting using one of the methods below:

Method 1: Right Click anywhere on the selected cells. A sub-menu will appear. Choose “View more cell actions” and then Conditional Formatting as shown below:

Right Click -> View More Cell Actions -> Conditional Formatting

Method 2: From the Format toolbar, choose Conditional Formatting as shown below:

Format -> Conditional Formatting

As we click on Conditional Formatting, Conditional Formatting Rules Box will appear as:

Step 3:

Set the condition that is required to activate the conditional formatting.

Here, let us highlight the cells which are duplicates of each other. So, we apply the condition using a Custom Formula as:

Step 4:

Now set the Format for Conditional Formatting. We can set our required format in Formatting Style section in Conditional Format Rule Box as:

Step 5:

Click Done to submit the conditional formatting.

This is the general method for applying conditional formatting. The Condition, Formula and Formatting Style can be customized to meet the requirements of the problem.

Use Cases of Conditional Formatting in Google Sheets

Let us discuss several cases for when to use conditional formatting.

Case 1: Google Sheets Conditional Formatting Based on Another Column

Suppose you have a worksheet containing products and their sales for the past month. And you have to highlight the product names which has less than 10 sales in red to limit their stock and highlight the products having more than 100 sales to buy more of their stock.

We can apply the conditional formatting in order to conditionally apply Formatting Style as:

Click here for more details on how to apply google sheets conditional formatting based on another column.

How to Use Google Sheets Conditional Formatting Based on Another Column

Case 2: Google Sheets Conditional Formatting If Cell Contains Formula

We may have a Google Sheet in which students result is entered and processed.

We can see that the student’s percentage exceeds 100% which is not possible by-book. So, we need to out the anomaly which is causing the wrong percentage and for that we need to find the cells containing formula and hence the sheet can be corrected.

Applying conditional formatting would give the result as:

By applying conditional formatting, we only need to check the highlighted cells for their formulae and sheets can be corrected.

Click here for more details on how to apply google sheets conditional formatting if cell contains formula.

How to Apply Google Sheets Conditional Formatting If Cell Contains Formula

Case 3: Conditional Formatting Based on Another Cell Google Sheets

We might need to use Conditional Formatting based on Another Cell in Google Sheets for some specific problems such as highlight if the current month’s average sales are less or greater than previous month’s average sales. In this example, we have certain oils in stock and their per liter as well as average sale in liter for this month and also average sales for the previous month as:

Average sales (liter) is calculated through the use of formula and also previous month’s Average Sales (liter) is also available. Our task is to apply Conditional Formatting such that: Average Sales of the current month is highlighted Green if it is greater than Previous Month’s Average Sales and Average Sales of the current month is highlighted Red if it is less than Previous Month’s Average Sales.

Applying conditional formatting would give result as:

Click here for more details on how to apply conditional formatting based on another cell google sheet.

Conditional Formatting based on Another Cell in Google Sheets

Case 4: Conditional Formatting If Box is Checked Google Sheets

Suppose we have the following sheet consisting of the data of some Travelling Agency. They wish to consider only vaccinated candidates and ignore/postpone travelling for the rest of candidates. They want to filter out the traveling ID of candidates with highlighting First Name in green so that they can contact those candidates for further details of travel.

Applying conditional formatting would lead to:

Click here for more details on how to apply conditional formatting if box is checked google sheets.

How to Apply Conditional Formatting IF Box is Checked in Google Sheets

Case 5: Conditional Formatting Duplicates Google Sheets

Let us take an example of Response List upon a Skin Product. We have to find out the cells containing duplicate values and later exclude those responses from response list as they are fake responses.

Applying conditional formatting would lead to:

Now, it is completely our choice to ignore the fake responses with dark red highlight.

Click here for more details on how to apply conditional formatting duplicates google sheets.

How to Apply Conditional Formatting to Find Duplicates in Google Sheets

Case 6: Google Sheets Conditional Formatting Text Color

It is very useful in scenarios where we have to assign different text colors to cells based on their content.

One simple scenario can be to assign text colors based on the status of deliveries:

• Green for successful deliveries
• Yellow for pending deliveries
• Red for cancelled deliveries

Here we are working on the data of a courier service and list of its delivery’s details are mentioned. We are supposed to assign green text color to the status if delivery is successful, yellow if pending and red if the delivery is cancelled so that the data is more arranged and clearly visible at a glance without actually reading the status of the delivery.

Delivery list is as:

Applying conditional formatting would lead to:

Click here for more details on how to apply google sheets conditional formatting text color.

How to Change Text Color Using Conditional Formatting in Google Sheets

Case 7: Google Sheets Conditional Formatting Strikethrough

We may have a Google Sheet where information of employees is kept. Talking about employees’ information, we know that there may be thousands of employees in a company and the record may exceed thousands, so we have to strikethrough the Employee ID of those employees who have left or retired from the company so that when we look at the data, we know at a glance which employees are currently working in the company.

Applying conditional formatting would lead to:

Click here for more details on how to apply google sheets conditional formatting strikethrough.

How to Apply Conditional Formatting Strikethrough in Google Sheets

Case 8: Conditional Formatting Lowest and Highest Value in Row Google Sheets

We may have a scenario where we have the products information of sales for various months of sales and we need to find out the maximum and minimum month of sales for better evaluation of products sales per time. We can achieve this using method of Conditional Formatting Lowest and Highest Value in row Google Sheets.

We will highlight the lowest sales value in Red Bold Text and maximum sales value in Green Bold Text for better visual.

Suppose we have the following Google Sheet:

Applying conditional formatting would lead to:

Click here for more details on how to apply conditional formatting lowest and highest value in row google sheets.

How to Apply Conditional Formatting Lowest and Highest Value in Google Sheets

Case 9: Google Sheets Copy Conditional Formatting

In some cases, we may need to copy conditional formatting from one cell to another.

You must first have a conditional formatting to copy it to other cells. We use the following conditional formatting here as per our requirements (highlight too fewer sales).

We only have conditional formatting applied on first cell of Sales.

Copying conditional formatting to rest of the cells of sales would lead to:

Click here for more details on how to apply google sheets copy conditional formatting.

How to Copy Conditional Formatting in Google Sheets (2 Methods)

Case 10: Google Sheets Conditional Formatting Multiple Columns

Sometimes the requirement of the problem may be to highlight certain cells within multiple columns based upon a condition. For example, a teacher may need to highlight cells having no marks in red.

Let us say we have the following worksheet and we need to highlight the empty cells.

Also we need to make sure that extra cells in the columns, here Row 12 and ahead, do not highlight as they do not have student allocation in them. So we will use specific conditional formatting alongside using the formulae that makes the Conditional Formatting ignore the cells having no student allocated in it.

Applying conditional formatting would lead to:

Click here for more details on how to apply google sheets conditional formatting multiple columns.

How to Use Google Sheets Conditional Formatting Multiple Columns

Notes

Conclusion

Google Sheets Conditional Formatting allows us to find certain cells which satisfy the condition or formula which enables us to find certain special cells in our sheets sooner and save up a lot of time.

In this article, easy step-by-step general method for applying conditional formatting as well as some basic uses and methods have been described. Any queries about them are most welcomed. Feel free to comment below for any questions or suggest a topic you want us to describe next.

Thanks for Reading!