How to Use Color Scale in Google Sheets (Step-by-Step Guide)

The Conditional Formatting feature of Google Sheets can provide a great way to make your data visually stand out. For example, you can use it to highlight important cells in your data that satisfy a given condition.

You can also use it to apply a two- or three-color scale to your data to help visualize the extent of similarity or difference between values. For example, a color scale based on value can be used to display a heatmap, with lower values represented in lighter shades and higher values represented in darker shades.

In this tutorial, we will discuss how to use color scale in Google Sheets, along with some simple examples. We will use color scales to:

  • Create a basic heatmap of scores
  • To display the percentage completed for a task (ideal for Gantt Charts)

How to Apply a Color Scale in Google Sheets to a Given List of Numbers

Let’s say you have the following scores for a group of 6 people:

 list of scores

If you want to format the scores using a color scale, here are the steps that you need to follow:

  • Select the range of cells that you want to format. In our example, we will select cells B2:B7.
  • From the Format menu, select Conditional Formatting.
select conditional formatting
  • You should now see the Conditional format rules sidebar on the right side of the window.
Conditional format rules sidebar
  • Click on the Color scale tab.
color scale tab
  • Under ‘Apply to range,’ you can see the range of cells to which the color scale will be applied. If you need to correct this range, you can go ahead and correct it.
Apply to range
  • Under Format rules, you should see a preview of your color scale. This is the default color scale provided by Google Sheets.
default color scale google sheets
  • If you are happy with this, you can finish setting up by clicking on Done at the bottom of the sidebar.

The default color scale in Google Sheets uses the darkest shade of green to represent the minimum value, and a white shade to represent the maximum value. The default color scale here does not have a Midpoint specification. However, you are free to adjust these specifications according to your requirement.

Customizing the Color Scale

If you prefer to customize the color scale in Step 6 with your own colors, simply select the color scale bar under Preview (as shown below).

 select the color scale bar

You should see some preset color scales that you can choose from.

preset color scales

However, if you want full control over the colors in your scale, click on the ‘Custom color scale’ button at the bottom of the dropdown.

Custom color scale

Select the colors that you prefer and click Done at the bottom of the Conditional format rules sidebar.

You can also specify different rules for selecting the color shade from your scale, given a value. Just below the Preview of the color scale, you will notice three categories:

  • Minpoint
  • Midpoint
  • Maxpoint
minpoint, midpoint, maxpoint

These categories help you specify the exact shades you want at the start, middle, and end of your color spectrum. For each point, there’s a dropdown list that lets you select either a number, percent, or percentile to correspond to a given color.

For example, choosing the ‘percent’ option lets you specify what percent of the total for a given value should the given color be applied. You can type the corresponding value into the input box to the right of the dropdown list. Values closer to this percent will have colors that are nearby in the color spectrum.

percent option

To customize the color at each point (Minpoint, Midpoint, and Maxpoint), click on the color button on the right for each point. You should see a color palette from which you can select a color.

color palette

If you want a particular color, you can click ‘Custom’ and use the slider and shade tool to get your specific color.

Custom color

If you know the hexadecimal code for your color, you can enter it into the ‘Hex’ input box, and click OK.

hex input box

Once you are done setting up your color scale rule, click on Done.

color scale in google sheets

Related reading: How to Sort by Color in Google Sheets

Example Applications of the Color Scale in Google Sheets

Let us look at some examples to understand further how you can apply the color scale to your data in some use-cases:

Creating a Basic Heat Map of Scores in Google Sheets

A very common use of the Google Sheets color scale is creating heat maps. A heat map is a visualization technique that uses colors to represent the magnitude of values in a range. The variation in the color scale may be by hue or intensity to give the viewer a visual cue.

Let’s say you have the following student scores and want to create a heatmap so viewers can see at a glance which students have scored higher and which students need more guidance:

 student scores

To create the heat map, follow the steps to apply the color scale to the cells B2:B7 (as shown in the first section of this tutorial). At step 6, select your choice of colors for the Minpoint and Maxpoint. Change the Midpoint to ‘Percentile’ and enter a value of 50 in the input box next to it. Select your choice of color for the values at the center of the color scale.

An example setting has been shown in the screenshot below:

example setting

Once you click on ‘Done’, here’s how your heatmap should look if you had used the same setting as the one shown above:

google sheets heatmap

Tip: You can learn more about creating heatmaps from our tutorial: Creating a Heat Map in Google Sheets.

Displaying the Percentage Completed for a Task in Google Sheets

You can also use different shades of color to represent what percentage of a task has been completed. These color scales can be useful in building Gantt charts, so you can visually show how much of a given project an employee has finished.

For example, say you have the following list of employees with the percentage of work completed shown in column B:

list of employees with the percentage of work completed

To apply a percentage completed color scale to column B, follow the color scale steps as shown in the first section of this tutorial. At step 6, change the Minpoint, Midpoint, and Maxpoint to ‘Percent’ and enter values 0, 50, and 100 in the input boxes for each point in that order.  Select your choice of color for the values at the center of the color scale as well.

An example setting has been shown in the screenshot below:

example setting

Here’s how the table should look after you’ve applied the above setting to it:

 custom color scale

Conclusion

This tutorial showed you how to apply Conditional formatting with a Color scale in Google Sheets. We also showed you some example applications where the color scale can come in handy. We hope this tutorial was helpful for you and easy to follow.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.