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.
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.
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:
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.
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:
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:
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.
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.
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.
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:
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:
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:
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.
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:
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:
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:
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:
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:
Absolutely. Just click the icons below the color band below Formatting style.
Each icon does the following, from left to right:
Through some testing, it is possible to do the following:
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:
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.
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.
Step 1: Click Format in the main toolbar.
Step 2: Select Conditional formatting.
Step 3: Click any cell that has conditional formatting.
If you enjoyed this article, you might also like our article on how to import in Google Sheets the Binance API or our article on how to set up Google Sheets conditional formatting based on another cell.
If you want a copy of our Google Sheets email template, we also suggest checking out our detailed guide.
