Last Updated on January 9, 2024 by Jake Sheridan
What is conditional formatting in Google Sheets?
Conditional formatting in Google Sheets is a useful feature that allows you to apply specific formatting styles to cells based on certain criteria.
Using conditional formattin rules, you can change the appearance of cells, such as background color, text style, and color, depending on the data they contain or other conditions.
Conditional formatting is particularly useful in understanding your data because it provides a visual aid.
For instance, you can highlight cells that contain values above or below a certain threshold, making it easier to spot trends, outliers, or key data points.
It can also add color scales across a cell range, providing a clear visual indication of which cells in your range have high and low values.
8 Ways to use Conditional Formatting in Google Sheets
1. How to create a simple conditional formatting rule
Here’s a step-by-step guide on how to create a simple conditional formatting rule in Google Sheets:
Open your Google Sheets document. Select the range of cells you want to apply conditional formatting to.
For example, we’ll select cells B2 to B20 if we want to apply formatting to these cells.
Click on Format in the menu bar, then select Conditional formatting.
This opens the Conditional Format Rules sidebar on the right side of your screen.
In the Format cells if dropdown menu, select the condition that you want to apply.
For instance, you can choose “Greater than or equal to” if you want to format cells with values higher than a certain number.
Enter the value or formula for your condition in the field that appears next to the dropdown menu.
For example, if you selected “Greater than or equal to”, you might enter “100” to format cells with values greater than or equal to 100.
Next, choose the formatting style you want to apply under the “Formatting style” section.
You can change the text color, background color, and other formatting options.
For example, you might choose a green background for cells that meet your condition.
Click Done to proceed.
Your selected range of cells should now be highlighted based on the condition formatting rules we’ve applied.
In this case, cells with sales greater than or equal to 100 are highlighted in green.
We can also head to the Color scale tab to apply a color scale to a range.
In the Condition format rules panel, you can choose the colors to use for the minpoint, midpoint, and maxpoint.
Google Sheets will automatically calculate which colors scale to use based on the provided colors.
2. Format cells in Google Sheets by multiple conditions
You can apply multiple conditional formatting rules to the same range.
This will allow you to specify different formatting styles depending on the value of your data.
For example, we can add two conditional format rules on our dataset to highlight high-performing and low-performing products.
In the example above, we used conditional formatting on the same range to highlight products with over 100 monthly sales and products with less than 50 monthly sales.
To add more than one conditional formatting rule on a range, simply click the Add another rule option in the Conditional format rules panel.
3. Use custom formulas in conditional formatting rules
While Google Sheets allows you to choose from a variety of built-in conditions when creating conditional formatting rules, you may encounter situations which require more complex calculations.
Custom formulas for conditional formatting in Google Sheets add a powerful layer of functionality, enabling you to apply specific formatting rules based on complex conditions or calculations.
To use custom formulas in conditional formatting rules in Google Sheets, follow these steps:
Go to the Format menu and select Conditional formatting.
This will open the Conditional Format Rules panel on the right.
In the Format cells if dropdown, scroll down and select Custom formula is.
This option allows you to enter your own formula for the condition.
Enter your custom formula in the field provided.
For example, to highlight cells where the value is greater than the average of the values in the range B1:B20, you could use the formula =B1>AVERAGE($A$2:$B$20).
Note that you should always start your formula with an equal sign (=).
Your selected range should now be formatted as specified based on the custom formula provided.
4. Format cells by the text they contain
To use conditional formatting in Google Sheets to highlight cells that contain a specific word, follow these steps:
Open your Google Sheets document and select the range of cells where you want the conditional formatting to be applied.
In this simple example, we want to highlight cells that contain the word Red.
Click on Format in the menu bar and then select Conditional formatting.
This will open the Conditional Format Rules sidebar on the right side of your screen.
In the Format cells if dropdown menu in the sidebar, choose the option Text contains.
This option is specifically for highlighting cells based on text content.
In the box that appears beneath the Text contains option, type the word you want to trigger the formatting.
For example, if you want to highlight cells that contain the word “Red”, we’ll type “Red” in the box.
Below the condition, set the formatting style. You can change the cell’s background color, text color, and other formatting styles.
Choose a formatting style that will make the cells with the specified word stand out.
Click Done to apply the conditional formatting rule to the selected range.
In our example above, our conditional formatting rule highlights two cells containing the word “Red”.
5. Apply conditional formatting to entire rows
We can use a custom formula to apply conditional formatting to an entire row instead of just highlighting just the cell that we’re checking the condition of.
To make the conditional formatting apply to the entire row, ensure that the whole range is specified in the Apply to range section of the Conditional format rules panel.
The formula you enter depends on your specific condition.
For instance, if you want to format rows where the value in column C is greater than 15, use the formula: =$C2>15.
Note the use of a dollar sign ($) before the column letter C.
This locks the column so that the condition checks only column A for each row in the selected range.
In the example above, we used a custom formula to apply conditional formatting to an entire row while still only checking one column (column C).
6. Create Google Sheets conditional formatting based on another cell
We can use cell references in the conditional format rules to make it easy for users to change conditions.
For example, suppose you want to highlight cells in a range that are greater than some limit value.
Instead of typing the limit manually in the Format rules section, we can use an absolute cell reference to a cell in our spreadsheet.
This set up allows us to quickly change the limit to use in our condition.
In the example above, we were able to lower the limit and have the number of highlighted cells update automatically.
7. Add conditional formatting based on another cell text
Here’s how you can add conditional formatting based on another cell’s text.
Open the Google Sheets document you want to add conditional formatting to.
Identify the range you want to format based on another cell text.
In this example, we want to highlight cells in column B if they contain the text “Blue” which is indicated in cell F1.
Click Format > Conditional formatting to access the conditional format rules panel.
Select “Text contains” in the Format cells if… dropdown menu.
Provide an absolute cell reference to the cell containing the text you want to look for.
Click Done to apply the conditional formatting rule to the selected range.
Cells containing the text found in cell F1 should now be highlighted.
Changing the value in cell F1 will also automatically update which cells are highlighted.
8. Remove conditional formatting from Google Sheets
To remove conditional formatting from your Google Sheets document, follow these steps:
First, open your Google Sheets document and select the range of cells from which you want to remove conditional formatting.
If you want to remove formatting from the entire sheet, click on the corner button at the top left of the sheet to select everything.
Click on the Format menu in the top toolbar.
From the dropdown, select Conditional formatting.
This action will open the Conditional Format Rules sidebar on the right side of your screen.
Alternatively, you can right-click on the target cell and click Conditional formatting.
In the Conditional Format Rules sidebar, you’ll see a list of all the conditional formatting rules that are applied to the selected range or the entire sheet.
To remove a specific rule, hover over the rule you want to delete and click on the trash can icon that appears to the right of the rule.
This will immediately remove the selected conditional formatting rule.
The selected range should now be clear of any conditional formatting.
Hopefully this guide has given you an overview of conditional formatting
in Google Sheets.
What’s Next?Explore some of the other useful resources on Sheets for Marketers:
- Google Sheets Templates – A collection of over 200 Google Sheets templates for SEO, marketing, PPC and more.
- Google Sheets Tutorials – A growing collection of spreadsheet tutorials for doing (almost) everything in Google Sheets.
- Data Studio Templates – A collection of over 50 Google Data Studio templates for SEO reporting.