Conditional Formatting in Google Sheets| Quick Guide

Conditional Formatting in Google Sheets| Quick Guide
Reading Time: 4 minutes
Conditional Formatting in Google Sheets

Get started with Conditional Formatting in Google Sheets

Need for Conditional Formatting In Google Sheets

When you’re working with huge tables, all the data can be very overwhelming. Hence we have graphs and charts to help us visualize our data. But we can also visualize our data with colors. It is very effective in keeping track of the magnitudes of the data visually. We will have a basic look at Conditional Formatting in Google Sheets. You can check this article which talks about the advantages Conditional Formatting provides you with.

Basic Use of Conditional Formatting in Google Sheets

Let’s imagine you are a teacher and you have a huge list of students in front of you which looks something like this.

Sample table
Figure 1: Sample table

Let’s say you want to know in a quick look, regarding which students are scoring below 75. It is easily achievable using conditional formatting. Just select the column (Marks in this case) then go to Format-> Conditional Formatting. A sidebar like such should open up.

Conditional Formatting Sidebar
Figure 2: Conditional Formatting Sidebar

Let’s first have a look at the single color tab only. The first field is “Apply to range”, as the name suggests this is the range for which you want to make changes.

In the second field, “Format cells if” we get a selection of various conditions such as less than, greater than, starts with as well as option to define a custom formula.

Format Rule Options in Conditional Formatting
Figure 3: Format Rule Options

Let us choose less than and enter the value 75 so we make clear that we want to change color for values below 75. After that, choose a color, in this case, we would choose red and hit done.

Resultant Table
Figure 4: Result

Color Scaling in Conditional Formatting

You can also color scale your data. For example. The students with better marks will have marks in a darker green shade and students with lower grades will be redder.

To do so let’s select the column again, open the conditional formating window and choose Color Scale tab.

Color Scaling Panel
Figure 5: Color Scaling Panel

This is the Color Scale window. We have multiple options here- let’s go through them one by one.

  • Min point- You basically choose the value and color for your minpoint here. You can choose the value in many ways for eg. min value, percent, percentile. Here we will choose minvalue which means we choose the minimum point as a reference for the value hence our minimum point would have 100% intensity of this shade. For our current example, let’s choose the color red.
  • Mid point- It is the same as minpoint except for the apparent fact that we have to choose the value and color for the mid-point here. For our current example, we will keep this as null
  • Max point- The color and reference value for the max point we will be choosing, all the values exceeding the max point(the value that you set here)  will have the same color. For our example, we will choose green here.

After these changes, our sidebar would look something like this.

Selected option in Color Scaling Panel
Figure 6: Selected option in Color Scaling Panel

And our table would look something like this 

Resultant Table
Figure 7: Resultant Table

You can see our highest value(91) is painted green while our lowest value(47) has the pure shade of red.

Conclusion

In this article, we saw the basic use case for Conditional Formatting in Google Sheets. If you’re working with a large set of data, using color formatting it becomes very easier for us to identify data patterns and quickly identify the magnitude of values using colors. You can also overlay multiple formatting conditions on top of each other as well.

See Also

How to create Heat Maps: Understand how to create Heat Maps using Conditional Formatting in Google Sheets

Numbers of Days, Months and Years between two dates: Number of Days, Months and Years between two dates