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)
Table of Contents
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:
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.
- You should now see the Conditional format rules sidebar on the right side of the window.
- Click on the 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.
- Under Format rules, you should see a preview of your color scale. This is the default color scale provided by 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).
You should see some preset color scales that you can choose from.
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.
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
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.
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.
If you want a particular color, you can click ‘Custom’ and use the slider and shade tool to get your specific color.
If you know the hexadecimal code for your color, you can enter it into the ‘Hex’ input box, and click OK.
Once you are done setting up your color scale rule, click on Done.
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:
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:
Once you click on ‘Done’, here’s how your heatmap should look if you had used the same setting as the one shown above:
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:
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:
Here’s how the table should look after you’ve applied the above setting to it:
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.
