Conditional Formatting Google Sheets: The Ultimate 2023 Guide
In this article you will learn how to use the conditional formatting feature in Google Sheets. You will learn what conditional formatting is, when you should use one, how to use one, and see it in action through examples. You will also get some FAQs about conditional formatting answered.
What is conditional formatting in Google Sheets?
Conditional formatting in Google Sheets allows you to set changes to the format of cells depending on their values. These changes usually include changing the fill color of the cells but may also include changing the text color or the text format of the stored values in the cell. They are all used to highlight the values that fulfill a certain set condition.
Should you use conditional formatting in Google Sheets?
You will find conditional formatting useful when you need to scan the hard numbers and highlight those that are relevant for your analysis. Some of the examples include the following:
- Sales: highlight sales that exceeded the expected range
- Marketing: highlight users that completed the conversion funnel from awareness to sales
- Project management: highlight items that are already completed
In designing dashboards in Google Sheets, you still need to include tables of most important data for a given purpose. You should add conditional formatting to these tables so that you will be quickly alerted to values that break the pattern, as they will be quickly highlighted whenever the table is refreshed.
How to use conditional formatting in Google Sheets?
To use conditional formatting in your Google Sheets, you need to access the conditional formatting sidebar, select the range, and then apply the formatting you want to be applied when a condition or a set of conditions is met. There are two ways of loading the conditional formatting sidebar and selecting the range:
Method 1: Load conditional formatting sidebar then select the range
This method works best when you are applying conditional formatting to a large range that is difficult to manually select.
Step 1: Click Format.
Step 2: Select Conditional formatting.
The Conditional formatting sidebar will appear on the right side.
Screencap:
Step 3: Click the small box icon on the right side of the textbox.
Step 4: The pop-up box Select a data range will appear. Select the cells or type the range inside the box, then click OK.
Screencap:
Method 2: Select the range then load the sidebar
This method works best when the range fits in your screen.
Step 1: Select the range of cells where you want to apply conditional formatting.
Step 2: Click Format, then select Conditional formatting.
Screencap:
We will use both of these methods in the examples below.
When to Use Color Scale Conditional Formatting
Google Sheets offers single color and color scale for conditional formatting. Single color conditional formatting is selected by default. You can, however, select color scale by clicking the Color scale tab:
Color scale conditional formatting is used when you want to highlight the values as part of a range. It works by setting a color scale and assigning values to it depending on the range of values in the selected cells:
You can choose the color scale from a preset list or customize your own:
We will learn how to use color scale conditional formatting in Example 2 below.
How to Conditional Format in Google Sheets – Examples
To get a grasp of the wide variety of uses for conditional formatting that you can get from Google Sheets, check the examples below. Note that these start with the Conditional format rules sidebar already loaded and the range selected, using one of the two methods highlighted in the previous section.
Example 1: Highlight Scores less than 35
Step 1: Click the dropdown box below Format rules label.
Step 2: Select Less than.
Step 3: A textbox below it will appear. Type 35.
Step 4: Click the bar below Formatting style label.
Step 5: Select the preset red shading.
Step 6: Click Done.
Screencap:
Example 2: Create a Heatmap Using the Scores
Google Sheets has its own color scale feature that automatically creates a color scale depending on the values in the range. Here are the steps:
Step 1: In the Conditional format rules sidebar, select Color scale tab.
Step 2: Select data range.
Step 3: Click the colored box under Format rules.
Step 4: A selection of preset color scales will appear. Select what fits your purpose best.
Step 5: Click Done.
Screencap:
Example 3: Google Sheets Custom Formula Conditional Formatting to Highlight All Instances of Duplicate Data Points
Step 1: Click the dropdown box below Format rules.
Step 2: Select Custom formula is. A textbox will appear below it.
Step 3: Insert the following formula:
=COUNTIF(range,firstcell)>1
Where
range is the range you select
firstcell is the reference to the first cell you select
If you are fine with the default color, you can click Done. Else, here are the next steps in replacing the colors:
Step 5: Click the color bar below Formatting style.
Step 6: Select another color from the preset or click Custom format.
Step 7: Click Done.
Screencap:
Example 4: Highlight Alternate Rows (creating Zebra Lines)
Step 1: Click the dropdown box below Format rules.
Step 2: Select Custom formula is. A textbox will appear below it.
Step 3: Add the following formula:
=MOD(ROW(),2)=1
Step 4: Click Done.
Example 5: Highlight Blank Cells
Step 1: Click the dropdown box below Format rules.
Step 2: Select Is empty.
Step 3: Click the color bar below Formatting style.
Step 4: Select the preset red box.
Step 5: Click Done.
Screencap:
Example 6: Highlight Errors
Step 1: Click the dropdown box below Format rules.
Step 2: Select Custom formula is. A textbox will appear below it.
Step 3: Enter the following formula:
=ISERROR(firstcell)
Where firstcell is the reference to the first cell in the column.
Step 4: Change the color shade by clicking the color bar below Formatting style.
Step 5: Select either yellow or red preset.
Step 6: Click Done.
Screencap:
Example 7: Conditional Formatting Sheets to Highlight Cells That Contain the Searched String
Step 1: Click the dropdown box below Format rules.
Step 2: Select Custom formula is. A textbox will appear below it.
Step 3: Enter the following formula:
=AND(NOT(ISBLANK(search_key)),ISNUMBER(SEARCH(search_key,firstcell)))
Where
search_key is the cell where you want to enter the search key
firstcell is the reference to the first cell in the column
Step 4: Click Done.
Screencap:
You can see the mini search engine in action:
Example 8: Conditional Formatting to Highlight Rows That Contain the Searched String
You can improve Example 7 to highlight the entire row instead when the search string appears in the sheet. Make sure to select the entire range, then follow the steps:
Step 1: Click the dropdown box below Format rules.
Step 2: Select Custom formula is. A textbox will appear below it.
Step 3: Enter the following formula:
=AND(NOT(ISBLANK(search_key)),ISNUMBER(SEARCH(search_key,firstcell)))
Where
search_key is the cell where you want to enter the search key
firstcell is the reference to the first cell in the column. It should be formatted like:
$A2
Where the dollar sign is placed before the reference.
Step 4: Click Done.
Screencap:
Sample of the improved search engine in action:
Example 9: Conditional Formatting Google Sheets Entire Row
There are two ways of doing this:
Method 1: Highlight the row before loading the sidebar
Method 2: Setting the range to include the selected row. The format goes as:
3:3
You can see it in action below:
FAQs about Google Sheets Conditional Formatting
Can I customize Google Sheets conditional formatting?
Absolutely. Just click the icons below the color band below Formatting style.
Each icon does the following, from left to right:
- Bold
- Italics
- Underline
- Strikethrough
- Text color
- Fill color
Through some testing, it is possible to do the following:
- Set the fill color to white, boldface the text and add strikethrough;
- Hide the values with black fill;
- Set a different color scheme:
Can I customize the color scale format?
Yes! You can customize the color for the minpoint, the midpoint, and the maxpoint.
By default, minpoint is fixed for the minimum value of the range while the maxpoint is fixed for the maximum value of the range. Click the fill color icon and select the color you want:
Additionally, midpoint is not enabled by default. Enabling it will allow you to set three color grade scheme:
How can I set specific values for the color range format?
Step 1: Click the dropdown box below the value you want to set.
Step 2: Select Number.
Step 3: The textbox on the right will be available for input. Just input the value for each point.
Step 4: Click Done.
Can I add two or more conditional formatting rules in the same range?
Yes. Just make sure that the rules do not clash with each other when at least two of them are fulfilled by the value stored in a cell.
How can I check existing conditional formatting rules in a sheet?
Step 1: Click Format in the main toolbar.
Step 2: Select Conditional formatting.
Step 3: Click any cell that has conditional formatting.
