How to Apply a Color Scale Based on Values in Google Sheets

How to Apply a Color Scale Based on Values in Google Sheets

Learning how to apply a color scale based on values in Google Sheets is useful to apply color gradients on different ranges of values

Applying color scale in Google Sheets helps to complement the existing data inserted. It also helps with a better visualization by utilizing colors to present different values for users. 

Something to take note of is that color scale can only be applied to numbers, including date and time. If the cell contains numbers together with text, it would not be able to use the color scale feature in Google Sheets. 

However, do not fear as you can always extract the numbers into another cell by using the REGEXTRACT or REGEXREPLACE functions. You can then apply a color scale to the new cells. 

 

Let’s take an example: 

Imagine you have to present the whole year’s sales revenue to the board of directors of your company. Instead of showing a boring colorless Google Sheet tab, you can easily apply a color scale to show the least and most revenue earned within the year.

How to Apply a Color Scale Based on Values in Google Sheets

By using the color scale feature in Google Sheets, we can know that October has the highest revenue, and June immediately has the lowest revenue during the year.

 

A Real Life Example of Applying a Color Scale Based on Value in Google Sheets

In this example, you are an elementary teacher assistant. The final exam has recently ended.

Your headteacher sent you a raw Google Sheets file filled with exam results for all students in Grade 2. You are instructed to arrange the data and clean it up to visualize better which students need more help. 

We can use the color scale feature in Google Sheets to highlight the students with exam results that are less ideal.

How to Apply a Color Scale Based on Values in Google Sheets

After applying the color scale based on the student’s exam results for all five subjects, we can clearly see which student needs more support on which subject.

You may make a copy of the spreadsheet using the link I have attached below. 

 

 

How to Apply a Color Scale Based on Values in Google Sheets 

Example 1

  1. First, we select the range that we want to apply the color scale on. In this example, it would be C4:C13.

How to Apply a Color Scale Based on Values in Google Sheets

 

  1. Then, you select Format, then click Conditional formatting

How to Apply a Color Scale Based on Values in Google Sheets

 

  1. Once you clicked Conditional formatting, a Conditional format rules pop-up on the side would appear. 

How to Apply a Color Scale Based on Values in Google Sheets

 

  1. For the color scale, there are presets to choose from. In this example, we chose the red to white combo to show the lowest mark in red, which signifies help needed. You can also customize the color scale based on your preference.

How to Apply a Color Scale Based on Values in Google Sheets

 

  1. As you can see in the preview, the max point would be red, and min point would be white. We would not need to insert a midpoint as we are not concerned with that at the moment.

How to Apply a Color Scale Based on Values in Google Sheets

 

  1. Once you press Done, your worksheet would look like this.

Example 1 Step 6

 

  1. Just do the same for the remaining subjects, and you will achieve a worksheet like this.

Example 1 Step 7

 

  1. Let us say you would like to know the students who scored more than 80 marks for each subject. We can simply set Maxpoint to ‘100’ and Minpoint to ‘80’. We also change the Minpoint to White and Maxpoint to Red.

Example 1 Step 8

 

  1. Once we are done with the new inputs, our worksheet will show only apply color scale on those who scored 80 and more. The brightest red being the highest mark among the group for that subject.

Example 1 Step 9

 

Example 2

We can also apply the color scale to dates. 

Suppose that you are the team leader of a team of three for a marketing service company. You created a shared Google Sheets with them to track everyone’s tasks and deadlines. 

However, since there are many deadlines and tasks, it makes it hard to track what is urgent to be completed.

You can apply the color scale feature to the datelines to categorize them by urgency. Red color would be the datelines closest to you, and green would be datelines furthest from you.

How to Apply a Color Scale Based on Values in Google Sheets

Let us do this together step by step! 🤗

  1. Select the range that we would like to apply color scale on. In this instance, it is A4:A8.

Example 2 Step 2

 

  1. Select Format, then select Conditional formatting. Once the Conditional format rule pop-up appears on the right side of your screen, select Color scale

Example 2 Step 3

 

  1. In the preview section, select the color scale from green to yellow to red

Example 2 Step 3

 

  1. Now we need to tailor the color to turn from red to yellow to green instead as we would like to show datelines closest to us in red. 

 

  1. The earliest date would be interpreted as the minpoint, and the date furthest away would be interpreted as the maxpoint. So, let us select Red for minpoint and Green for maxpoint. Midpoint would remain as yellow. 

Example 2 Step 5

 

  1. Once you press Done, the worksheet would look like this.

Example 2 Step 6

 

By doing this, your team is now able to view what is the utmost urgent task to deliver, and you can remind each other as well! 

There you go! We have presented to you several ways to apply the color scale based on values in Google Sheets to improve the appearance as well as incorporate visualization. 

If you are not familiar with the conditional formatting feature on Google Sheets, go ahead and check out our tutorial on how to apply it

Besides applying a color scale, conditional formatting helps you identify a cell or range of cells with values that are interesting. It helps emphasize unusual values in a given data value that are higher or lower than a given threshold.

Don’t be afraid to try out different format rules and customizations to tailor the conditional formatting to your preferences! 

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like