Conditional formatting in Google Sheets is a feature that automatically changes the appearance of cells based on specific rules or conditions you define. By applying formatting styles, such as font properties, cell background colors, or data highlighting, you can visually emphasize certain values or patterns in your spreadsheet.
This powerful tool enables you to make your data more visually appealing and easier to interpret, helping you spot trends, outliers, and important information effortlessly. Conditional formatting helps you transform your data visualization and analysis, turning raw numbers into actionable insights with ease.
In this article, you’ll learn 9 different ways on how you can use conditional formatting in Google Sheets for a variety of purposes.
How to use conditional formatting in Google Sheets to apply a color scale
Cost: $0
Time: 1 minute
Color scale, when applied with conditional formatting in Google Sheets, is useful for representing data distribution and identifying patterns at a glance. It can help you to gain valuable insights without having to examine individual values. It’s particularly valuable when you're dealing with numerical information or large datasets, because the color gradients applied to the cells will help you quickly spot low and high values, variations and trends.
You can use color scales in any scenario where visualizing the relative magnitude of data points helps you better understand and make decisions like data analysis, performance evaluations and financial reports.
Here are the steps to follow to apply a color scale using conditional formatting in Google Sheets:
Step 1: Select the cells
Select the entire range of cells that you want to apply the conditional formatting to, by selecting the first cell and dragging through the rest.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format”, then choose “conditional formatting” to open the Conditional format rules sidebar on the right side of your screen. If you have other rules set up already, you'll need to click on "add another rule."
Step 3: Select “Color scale”
On the Conditional formatting rules sidebar, click on “Color scale.”
Step 4: Confirm the data range
Under “Apply to range,” make sure you have all the cells that you want to highlight. If not, use the grid icon to select the correct ones as shown in the image below.
Step 5: Set your conditional formatting rules
Under “Minpoint” in “Format rules,” click on the dropdown menu to select how you want your formatting rules:
- Min value: This refers to the smallest value within a specified range of cells and is used to set up conditional formatting to highlight cells that contain the smallest value in a range, making it stand out visually.
- Number: This refers to a data type that represents numerical values. This can include whole numbers (integers) or decimal numbers (floating-point numbers).
- Percent: A percent is a way to express a portion of a whole as a fraction out of 100. Conditional formatting can be applied to percentages to visually emphasize cells with specific percentage ranges, for example, highlighting all cells with percentages above 80%.
- Percentile: A percentile is a statistical measure that indicates the value below which a given percentage of observations in a group of observations fall. For example, the 75th percentile is the value below which 75% of the data points fall.
Repeat the process to “Maxpoint.”
Step 6: Choose a color
If you choose not to use the default color, you can customize your colors in the “maxpoint” and “minpoint” sections.
Step 7: Click on “Done”
At the bottom of the conditional formatting rules, click on “Done” to apply the conditional formatting rules.
How to Apply Conditional Formatting Based on Numerical Values
Cost: $0
Time: 1 minute
Conditional formatting in Google Sheets allows you to automatically format cells based on their numerical values, so that you can quickly identify and visually emphasize specific data points that meet a particular condition. This can help you identify underperforming metrics, pinpoint areas of concern and help you identify items that require urgent action or further review without extensive manual analysis. That way, you can focus on relevant information based on the highlighted patterns within your Google Sheets document.
Here are the steps to follow to apply to highlight values less in Google Sheets:
Step 1: Select dataset
Select the entire range of cells that you want to apply the conditional formatting to.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format,” then scroll down and choose “Conditional formatting” to open the Conditional format rules sidebar on the right side of your screen. If you have other rules set up already, you’ll need to click "Add another rule."
Step 3: Confirm range
Under “Apply to range,” confirm that you have all the cells that you want to format. If not, use the grid icon to select the correct ones.
Step 4: Set your conditional formatting rules
In the Conditional format rules sidebar, select "Greater than," "Less than," "Equal to," "Between," etc., based on the condition you want to apply. Next, enter the numerical value for the condition.
For example, if you choose "Less than" and enter "50," the formatting will be applied to cells with values less than 50.
Step 5: Format style
In the Conditional formatting rules sidebar click on “Formatting style” to customize the conditional formatting to your preference.
Click on “Done”
At the bottom of the Conditional formatting rules sidebar, click on “Done” to apply the conditional formatting rules.
How to use conditional formatting in Google Sheets to highlight rows alternatively
Cost: $0
Time: 1 minute
When dealing with large datasets, it can become challenging to keep track of rows, especially when scrolling through the sheet. Highlighting rows alternatively with different colors provides a clear visual separation between each row, which improves readability. Additionally, highlighting rows alternatively, also known as “zebra lines,” can help you reduce eye strain and give your sheets a more professional, aesthetic appeal.
Here are the steps to follow to use conditional formatting to highlight rows alternatively:
Step 1: Select the cells
In your spreadsheet, select the cells that you want to apply the conditional formatting to. If that’s the entire sheet, you can click on the square between the column letters and row numbers at the top-left corner to select the entire sheet.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format”, then scroll down and choose “Conditional formatting” to open the Conditional format rules sidebar on the right side of your screen. If you have other rules set up already, click on "Add another rule."
Step 3: Confirm the range
Under “Apply to range”, confirm that you have all the cells that you want to format. If not, use the grid icon to select the correct ones.
Step 4: Set your conditional formatting rules
In the Conditional format rules sidebar, under “Format cells if…” in the Format rules section, click on the dropdown menu and select “Custom formula is.”
In the new field that appears, enter this formula to highlight odd-numbered rows: =ISODD(ROW()). Alternatively, if you prefer highlighting even numbered rows, enter this formula: =ISEVEN(ROW()).
The ISEVEN(ROW()) or ISODD(ROW()) formulas check whether the row number is even or odd. Rows with even or odd row numbers, depending on which formula you choose to apply, will return TRUE, and Google Sheets will apply the formatting style you selected. This way, every other row in the selected range will be highlighted with the chosen formatting.
Step 5: Format style
In the Conditional formatting rules sidebar, under Formatting style, you can customize how the rows will be highlighted according to your preference.
Step 6: Click on “Done”
At the bottom of the Conditional formatting rules sidebar, click on “Done” to apply the conditional formatting rules.
How to use conditional formatting in Google Sheets to highlight blank cells
Cost: $0
Time: 1 minute
It is easy to scan and identify blank cells when your dataset is small. But when you have hundreds of records, using conditional formatting is the best way to instantly highlight all the blank cells in your dataset.
Here are the steps you can follow to use conditional formatting to highlight blank cells in Google Sheets:
Step 1: Select the cells
Select all the cells you want to apply conditional formatting to and highlight them all.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format,” then scroll down and choose “Conditional formatting.”
Step 3: Confirm range
Under the Apply to range section, ensure you have all the cells that you have to highlight. If not, use the grid icon to select the correct ones.
Step 4: Set your conditional formatting rules
Under the Format rules section, click on the drop-down arrow and select “is empty.”
Step 5: Format style
In the “formatting style” section, you have the option of choosing default formats. Because the cells are empty, you can only choose your desired fill color.
Step 6: Click on “Done”
At the bottom of the Conditional formatting rules sidebar, click on “Done” to apply the conditional formatting.
How to use conditional formatting in Google Sheets to highlight errors
Cost: $0
Time: 1 minute
If you use a variety of formulas or import the data from text files or Excel, it’s possible that some of the cells in your dataset could have an error value. It is important to fix these error values to better use your data for further calculations or analysis.
Here are the steps to follow to use conditional formatting in Google Sheets to highlight errors:
Step 1: Select the cells
Select all the cells you want to apply conditional formatting to and highlight them all. An easy way to do this is by selecting the first cell and dragging through the rest.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format,”, then choose “Conditional formatting” to open the Conditional format rules sidebar on the right side of your screen. If you have conditional format rules already set up, you’ll need to click on "Add another rule."
Step 3: Confirm range
Under the “Apply to range” section, ensure you have all the cells that you want to highlight. If not, use the grid icon to select the correct ones.
Step 4: Set your conditional formatting rules
In the Conditional formatting rules sidebar, under Format rules, click on the drop-down arrow and select "Custom formula is." In the box that appears, enter the following formula: =ISERROR(C1). Note that you need to replace the cell in parentheses with the cell where your data begins.
Step 5: Format style
In the Formatting style section, you have the option of going with the default style or choosing your desired fill color, text color, style and more.
Step 6: Click on “Done”
At the bottom of the Conditional format rules sidebar, click on “Done” to instantly apply the conditional formatting.
How to use conditional formatting in Google Sheets to highlight cells with a searched string
Cost: $0
Time: 2 minutes
When working with large datasets or complex spreadsheets, using conditional formatting in Google Sheets to highlight cells that contain a searched string allows you to quickly pinpoint and focus on specific information. It is particularly useful for error checking and data analysis because it streamlines the identification of specific data points that match the search criteria.
By using conditional formatting for this purpose, you can visually highlight cells containing the searched string, making it easier to filter, review, and draw insights from the data by ensuring you don't miss critical information in your spreadsheet.
Here are the steps to follow to use conditional formatting in Google Sheets to highlight cells that contain a searched string:
Step 1: Select the cells
Select the entire range of cells that you want to apply the conditional formatting to, by selecting the first cell and dragging your mouse as you select the rest.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format”, then choose “Conditional formatting” to open the Conditional format rules sidebar on the right side of your screen. If you have other rules set up already, you"ll need to click "Add another rule."
Step 3: Confirm range
Under the Apply to range section, ensure you have all the cells that you have to highlight. If not, use the grid icon to select the correct ones.
Step 4: Set your conditional formatting rules
In the Conditional format rules sidebar, under Format rules, select "Custom formula is" from the dropdown menu.
In the box that appears, enter the following formula: =AND(NOT(ISBLANK($C$2)),ISNUMBER(SEARCH($C$2,A2))). Here’s what this formula means:
- NOT(ISBLANK($C$2)) checks if cell C2 is not blank. It ensures that the search string in C2 is specified; otherwise, the conditional formatting won"t be applied;
- ISNUMBER(SEARCH($C$2,A2)) performs the search within each cell in the selected range (e.g., A2, B2, C2, etc.) to see if the value in C2 is found within the cell. The SEARCH function returns the position of the search string in the cell or an error if not found. The ISNUMBER function then checks if the result is a number (i.e., the search string is found), and if so, it returns TRUE;
- AND(...) combines the two conditions mentioned above. The conditional formatting will only be applied if both conditions are met, meaning cell C2 is not blank and the search string is found in the cell.
Step 5: Format style
In the Formatting style section, choose the formatting style you want to apply, by picking a background color, a font color, a font style, and more.
Step 6: Click on “Done”
At the bottom of the Conditional formatting rules sidebar, click on “Done” to highlight cells that contain the searched string.
How to use conditional formatting in Google Sheets to highlight duplicates
Cost: $0
Time: 3 minutes
Using conditional formatting in Google Sheets to highlight duplicates aids in data analysis, error checking, data validation and data cleansing, because it allows you to visually identify duplicate entries in a dataset.
Here are the steps to follow to use conditional formatting to highlight duplicate data points:
Step 1: Select the cells
Select the entire range of cells to which you want to apply the conditional formatting by selecting the first cell and dragging your mouse as you select the rest.
Step 2: Open the Conditional format rules sidebar
In the top menu bar, click on “Format,” then choose “Conditional formatting” to open the Conditional format rules sidebar on the right side of your screen. Click on "Add another rule" if you already have other conditional format rules set up.
Step 3: Confirm range
Under “Apply to range,” ensure you have all the cells that you have to highlight. If not, use the grid icon to select the correct ones. You can do this easily by selecting the first cell and dragging your mouse as you select the rest.
Step 4: Set your conditional formatting rules
In the Conditional format rules sidebar, under “Format cells if…” click on the drop-down menu and select “Custom formula is.”
In the box that appears, enter this formula to detect duplicates: =countif($A$2:$A, A2)>1. Here’s what this formula does:
- The countif function checks how many times the value in the current cell (A2 in this case) appears in the entire range (A2:A). If the count is greater than 1, it means the value is duplicated, and the formula returns TRUE, triggering the conditional formatting for that cell;
- The use of absolute reference ($A$2:$A) ensures that the range A2:A remains constant when the conditional formatting is applied to other cells in the selected range. The relative reference (A2) will adjust automatically for each cell within the range
Step 5: Format style
In the Formatting style section, choose the formatting style you want to apply to the duplicate cells.
Step 6: Click on “Done”
At the bottom of the Conditional format rules sidebar, click on “Done” to use the conditional formatting to detect duplicate data in your Google Sheet.
How to use conditional formatting in Google Sheets based on time-related conditions
Cost: $0
Time: 1 minute
You use conditional formatting based on time. related conditions, in order to highlight certain cells. It’s applicable to schedules, time monitoring data, event reminders and other time-related data visualization.
In this tutorial, for the purpose of exemplifying, we will apply conditional formatting to highlight the time between 3:00 p.m. and 8:00 p.m.
Step 1: Select the cells
Select the entire range of cells to which you want to apply the conditional formatting by selecting the first cell and dragging your mouse as you select the rest.
Step 2: Open the Conditional format rules sidebar
On your Google Sheets, select the range of cells you want to apply conditional formatting. Then click on "Format" on the top of your screen and click on "Conditional formatting” on the drop-down menu.
Step 3: Set your conditional formatting rules
In the Conditional format rules menu on the right side of your screen, click on "format cells" and select "is between" from the dropdown menu.
For example, if you want to highlight the time between 9:00 a.m. and 2:00 p.m., you’d write in one box “09:00 a.m.” and “02:00 p.m.” in the other.
Step 4: Click on “Done”
Click on "Done" to apply the conditional formatting to your sheet.
How to remove conditional formatting in Google Sheets
Sometimes, you may want to clear your conditional formatting in your spreadsheet. This may be necessary because you have received more data or you want to start your spreadsheet from scratch. Here is how you do it:
Step 1: Select cells with conditional formatting
Select the cells with conditional formatting by clicking on the first cell and dragging your mouse to select the entire range.
Step 2: Open the Conditional format rules sidebar
On the top menu, click on “Format” and then click on “Conditional formatting” on the dropdown menu.
Step 3: Delete the conditional formatting
On the right side, hover your cursor beside each conditional formatting to see the delete button. Click here to delete any conditional rule you want.
What is Softr
Softr is the easiest way to turn your data into powerful business apps—no code required. Connect to your spreadsheet or database, customize layout and logic, and share with your team or clients.
Join 700,000+ users worldwide, building client portals, internal tools, CRMs, dashboards, project management systems, inventory management apps, and more—all without code.
Join 700,000+ users worldwide, building client portals, internal tools, CRMs, dashboards, project management systems, inventory management apps, and more—all without code.
Get started free
Silvia Gituto
Categories
