How To

How to do Conditional Formatting in Google Sheets

In Google Sheets, conditional formatting can be used to highlight or format cells according to their contents. So, how to do conditional formatting in Google Sheets?

If you have 50 students’ exam results for one subject, for instance, you may immediately identify the students who received less than a 35 on the test.

Similar to this, you can highlight cells based on their value by using a variety of these criteria, as we shall learn later in this article.

A large number of cutting-edge features that assist in making sense of a mountain of data are available in Google Sheets. One of them is conditional formatting, which is straightforward but effective.

It helps create a colorful and visually appealing dataset from drab rows and columns of black text on white backdrops. In addition to saving time, this improves the readability and significance of the data.

Learn everything there is to know about Google Sheets Conditional Formatting by following along with this tutorial.

What is Google Sheets сonditional formatting?

Based on the rules you specify, Google Sheets conditional formatting allows you to automatically modify the font attributes of a particular cell, row, column, and even the cell’s background color.

In other words, this tool makes your data stand out by utilizing the power of visualization. Specific values are highlighted by cell coloring, making complex tables easier to view and comprehend.

Conditional formatting makes data more aesthetically pleasing and hence more readable by humans.

When you can apply conditional formatting in Google Sheets

Practically any workflow can make use of conditional formatting to visualize information, including data patterns, problem areas, positive news, and even inaccurate or defective data. There may not be another Google Sheets product with such a broad range of uses.

  • Sales managers utilize conditional formatting to quickly glean insights from voluminous sales data.
  • No problem, accountants use conditional formatting to show any negative values in red in their profit/loss computations.
  • When project managers attempt to understand how their resources are being used, conditional formatting might once more be of use.

It is also an excellent tool for tracking objectives because it provides visual cues for how well one is doing in relation to particular criteria.

Not to mention the numerous daily and monthly reports that are common in large firms. If you’re busy and they’re not properly formatted, they can be a tremendous hassle because they don’t immediately make sense.

Therefore, conditional formatting can be useful whenever you want to add some life to your Google Sheets tables.

How to Access Conditional Formatting in Google Sheets

By selecting Format Conditional Formatting, you can access conditional formatting.

By doing so, you can set the rules in the “Conditional format rules” box that appears on the right.

When using conditional formatting in Google Sheets, you now have two options at your disposal:

  • Individual Color
  • Color Scale

When you wish to highlight all of the cells based on the value, you can use the single-color option.

When to Use Color Scale Conditional Formatting

When you additionally wish to visually display the variation between the values in the cells, “Color scale” can be employed. A score of 34, for instance, would be a lighter shade of red, whereas a score of 10 would be a darker hue.

How to do Conditional Formatting in Google Sheets

Highlight Scores less than 35

Assume you want to quickly identify the cells where the score is less than 35 in the data set as shown below.

How to do it:

  • Choose the cells (B2:B16 in this case).
  • Select Conditional Formatting under Format.
  • Make sure “Single color” is selected in the Conditional formatting pane.
  • Verify the range that ‘Apply to range’ relates to. If not, you may modify it right here.
  • Choose Less than from the “Format cells if” drop-down menu
  • Directly beneath the drop-down menu, a new field would show up. Put 35 there.
  • Choose the format type. You can make your own or pick from the pre-made options. Let me choose red in this instance.
  • Select Done.
  • This would immediately turn the color red and emphasize every cell with a score below 35.

Create a Heatmap Using the Scores

Let’s explore how to make a heat map in Google Sheets using Conditional Formatting utilizing the same data set as in the example before.

On the basis of the values in the cells, a heat map would display a gradient of colors

The steps to make the heat map using the exam results are as follows:

  • Decide which data collection will be used to generate the heat map.
  • Select Conditional Formatting under Format.
  • Choose “Color scale” from the Conditional formatting rules box.
  • Verify the range that ‘Apply to range’ relates to. If not, you may modify it right here.
  • Choose the desired gradient from the Preview drop-down. Keep in mind that the gradient’s leftmost color is applied to the lower value numbers, while the gradient’s highest side color is applied to the higher value numbers.
  • Here, you can also see a live preview of the gradient when picking it.
  • Click done.

By doing this, a heat map will be produced with a gradient that depends on the value of each cell.

Google Sheets Custom Formula Conditional Formatting to Highlight All Instances of Duplicate Data Points or conditional formatting duplicates Google Sheets

In Google Sheets, you may utilize the conditional formatting Google Sheets duplicates feature’s custom function to indicate duplicate data points.

The steps listed below can be used to highlight all instances of duplicate occurrence:

  • Choosing the data set.
  • Select Conditional Formatting under Format.
  • Select “Single Color” from the Conditional Format rules menu.
  • Verify the range that ‘Apply to range’ relates to. If not, you may modify it right here.
  • Choose “Custom formula is” from the drop-down menu under “Format cells if.”
  • Fill it out with the formula =COUNTIF($A$2:$A$11,A1)>1.
  • Decide on a format.
  • Select OK.

This will draw attention to any instances of duplicate values.

The fact that this is now dynamic means that it will automatically indicate the cells with duplicate values if you modify the dataset.

Highlight Alternate Rows (creating Zebra Lines)

When printing reports, it is common practice to highlight alternative rows.

It makes the report easier to read and provides it with a more polished appearance.
Although you can do this by applying the format manually, conditional formatting can make it simple and quick.

Here are the methods to use conditional formatting in Google Sheets to highlight certain cells:

  • Choosing the dataset.
  • Select Conditional Formatting under Format.
  • Choose “Single color” from the Conditional Format rules menu.
  • Make that the range that Apply to refers to is the right range. If not, you may modify it right here.
  • Choose the “Custom formula is” option for conditional formatting under the “Format cells if” drop-down.
  • Fill it out with the following formula: =MOD(ROW(),2).
  • Decide on a format.
  • Select OK.
  • This would draw attention to different rows within the chosen range.

The ROW() function provides the row number of a specified cell. The residue is then returned by the MOD() function after dividing by 2. The condition is TRUE and the conditional format is used when the remainder is 1 (if the row numbers are odd).

Use the following formula to draw attention to the rows that have even numbers: =MOD(ROW(),2).

Highlight Blank Cells

The dataset’s empty cells can be highlighted using the custom formula option. While it is simple to scan and find blank cells when the data collection is small, conditional formatting is the best option when there are hundreds of entries.

The steps to highlight every blank cell in this data collection are listed below.

  • Choosing the dataset.
  • Select Conditional Formatting under Format.
  • Choose “Single color” from the Conditional Format rules menu.
  • Verify the range that ‘Apply to range’ relates to. If not, you may modify it right here.
  • Choose “Custom formula is” from the drop-down menu under “Format cells if.”
  • Put the following equation there: =ISBLANK (A2)
  • Decide on a format.
  • Select OK.

This would immediately highlight all of the data set’s blank cells.

Highlight Errors

There is a chance that some of the cells in your data set have an invalid value if you imported the data from Excel or Text files or if you employed numerous calculations.

If you intend to use this data for additional research or calculations, it is crucial to handle these incorrect values.

The steps to highlight all the incorrect cells are listed below.

  • Choosing the dataset.
  • Select Conditional Formatting under Format.
  • Choose “Single color” from the Conditional Format rules menu.
  • Verify the range that ‘Apply to range’ relates to. If not, you may modify it right here.
  • Choose “Custom formula is” from the drop-down menu under “Format cells if.”
  • Type the formula =ISERROR into that field (A2)
  • Decide on a format.
  • Select OK.

This would immediately show all the cells that contain errors.

Conditional Formatting Sheets to Highlight Cells That Contain the Searched String

In Google Sheets, conditional formatting can be used to build a database that is searchable.
As soon as we enter a name in cell C2 and press Enter, the name is highlighted throughout the data set. If you only type part of a name, all the cells that contain that string will be highlighted. For instance, if you type “P” in cell C2 and press the enter key, Pat and Paul’s names will be highlighted.

The steps to make this are as follows:

  • Choosing the dataset.
  • Select Conditional Formatting under Format.
  • Choose “Single color” from the Conditional Format rules menu.
  • Verify the range that ‘Apply to range’ relates to. If not, you may modify it right here.
  • Choose “Custom formula is” from the drop-down menu under “Format cells if.”
  • Put in the formula shown below: =AND(NOT(ISBLANK($C$2)),ISNUMBER(SEARCH($C$2,A2)))
  • Decide on a format.
  • Select OK.

Now, anytime you type a string in cell C2 and press Enter, all the cells that contain that string are immediately highlighted.

How it functions

In this instance, the formula consists of two parts:

  • Cell C2 is examined to see if it is empty using the NOT(ISBLANK($C$2)) component of the formula. Nothing is highlighted if it is empty, indicating that this condition is false. If you don’t use this section, when C2 is empty, all the cells in the dataset will be highlighted.
  • It determines whether the string in cell C2 corresponds to the strings in the cells in the data range by using the search function SEARCH($C$2,A2). The SEARCH function would return a number if the string was located. In this situation, ISNUMBER would then return TRUE.

Conditional Formatting Google Sheets Entire Row

Simply do the following to conditionally format a full row:

  • Choose the row heading.
  • To format, go to Format. formatting with conditions
  • Establish your conditional formatting guidelines.

Conclusion

A spreadsheet can be the ideal tool for playing with your data and extracting meaning from it, regardless of the type of project you’re working on or the type of data you’re working with. You can make your data visually interesting and add logic to reports to make them intuitive and reader-friendly by using conditional formatting in Google Sheets. Therefore, as you continue to study and use this fantastic function, you’ll undoubtedly discover dozens of applications for conditional formatting.

People May Ask

Q- What Is Google Sheets Conditional Formatting? / What Does Google Sheets Conditional Formatting Mean?

A- In Google Sheets, conditional formatting automatically formats cells in accordance with the specifications that the user specifies.

Q- How Do I Use Google Sheets’ Conditional Formatting?

  • To improve the scannability of the data in your spreadsheets, employ conditional formatting. Applying conditional formatting is made simple by:
  • To apply conditional formatting, highlight the cells, then choose Format > formatting with conditions
    Establish your conditional formatting guidelines.

Q- How Does Google Sheets Conditionally Change Cell Color?

A- Change the fill color in the Conditional formatting menu’s Formatting style section.

Q- In Google Sheets, How Do I See Conditional Formatting Rules?

A- Go to Format > Conditional formatting to view all the conditional formatting rules at once. Next, click any already-existing rules to reveal the cells to which they apply.

Q- How Can I Modify the Colors and Rules for Conditional Formatting?

  • Then choose Formatting. formatting with conditions
  • To change an existing rule, click on it.
  • As if you were creating a new rule, modify the existing one.
  • Use the fill color option to change the colors.

Related Articles

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
Back to top button
0
Would love your thoughts, please comment.x
()
x
Mail Icon