In this article, we will learn how to use color scale in google sheets. The color scale is used to display your data with a group of colors and each value has a color based on its group for example a data having 1 to 5 numbers, now a color scale will be added to this data so the data will be divided into 3 groups 4-5 are from maximum group, 1-2 from the minimum group, and 3 is from the mid group. So, the colors will be the lightest for the max values and will be darker for the min value and the average color will be for the mid value.
Now, this is just a concept not a constant. You can change the colors, of course, you can change the intensity of the colors based on numbers, percentages, or percentiles. what I have described above is the default working of a color scale when directly applied to data. But we have a lot of customization options to customize the color scale, change colors, and change the spectrum of the color variations based on data regarding numbers, percent, or percentile. We can also change the max, min, and mid values for it.
Why use Color Scale in Google Sheets
The color scale is one of the best ways to display the statistical data and let the user quickly pick up the max, min, and midpoints of the data at a glance. Color scale defines the overall variation of the data and also helps to quickly figure out the data direction. The color scale is a feature inside conditional formatting in google sheets, where we have some pre-defined color scales to use, we can also make our color scales here in sheets. To learn a color scale, we should know the basics of conditional formatting in google sheets.
And after learning color scale we will be able to create fantastic heat maps in google sheets, and we will talk about that later. So I hope you have got an idea of why you need to learn how to use color scale in google sheets.
How to use Color Scale in Google Sheets
Here we will learn step by step, we will start with how to use color scales in google sheets for the very first time, then we will move and will customize the color scales, then will create our color scales, and similarly, we will go ahead and implement some practical examples to learn color scale with all its attributes.
How a Color Scale Looks like on a Sorted data
A color scale based on conditional formatting can be applied to numeric data only, it cannot be applied to other data types such as strings, and characters. They are only numbers, and they can be floating points numbers, percentages, or percentiles as well
Let’s say you have a dataset in which you have some students from standard 1 to 10. Here for the standard column, you can apply a conditional formatting color scale based on numbers.
Let’s have a look at this
Well, it looks like a very good pattern.
How a Color Scale Looks on Unsorted data
The color scale looks good on sorted data but does not look very good on an unsorted list
See the same data set unsorted
Don’t worry, it’s not all about color scales, there are tons of things we can do using color scales let’s see how to add a color scale practically on a dataset
How to use Color Scale in Google Sheets – Adding a Color Scale
In this section, let’s add a color scale a sample data.
For this you need to follow the below steps simply
Select the data (not header)
Go to Format > Conditional formatting
Click on the Colour Scale tab
In the below Format rules section, click on the scale to select any pre-defined color scale
Click on Custom Colour scale to make your color scale
To define your color scale, you need to define two colors for min and max value
By default, mid value is locked, but you can unlock and define it by choosing any unit other from the dropdown (use according to your data)
Now define a color for the midpoint
You can see all your five colors in the preview section (three colors are given by you and the other two are picked by sheets between that defined range of color)
Click Done to save the changes and apply this to your data
This is how you can use color scale in google sheets.
How to use Color Scale in Google Sheets – Color Scale based on Percentages
In this section, we will learn how to use color scale in google sheets based on a percentage of the values. We can make students percentages for a batch of students using a simple color scale.
For this example, I have a simple data set in which I have some students’ names and their percentage marks.
Select the data column (excluding header)
Go to Format > Conditional Formatting
Select percentage as the unit from min, mid and max dropdowns.
Select colors for a min, mid, and max color pickers
Click on done and you’re done, your data is color scales as percentages.
This is how you can use color scales for percentage values.
How to use Color Scale in Google Sheets – Creating a Heatmap
In this section, we will learn how to create a heat map using a color scale in google sheets. A heat map is a visual representation of data based on color where lighter color shows one data point and the intensity of the colors shows the other data point and the average colors are meant to be the center or midpoint of the data. We can assume it is like 1-10 where 1 will be the darkest 5-6 will be average and 10 will have the lowest intensity. In this way, we can easily where our values are smaller and where we need to work to increase or decrease the value.
For this example, I have a sample data set of the sales of the last three years and I will use three data points to describe their color of them, and all between values will take nearing color or low or high intensity/hue of that color.
Select columns data, go to Format > Conditional formatting
Select the color scale tab and define colors for all three points
Click on done. You have seen how easily we can see the values and can analyze the data to predict the values very quickly
This is how we can use color scale in google sheets to create a heat map. Now heatmaps are not limited to these, we may have some very big data sets and some very complex heatmaps since this tutorial is mainly based on the color scale, so we will learn heatmaps in detail in some other tutorial in the future. For now, I hope you have got a basic idea about the heat maps.
Download/Copy Google Sheets Workbook
Useful Notes on How to use Color Scale in Google Sheets
- Color scales can be used for data analysis, a very common technique is a heatmap that shows the data as min, mid or max points with your defined colors for each point
- Color scales are applied on numeric data only because the units are number, percentage, and percentile, you cannot evaluate a text or string or any other data type using color scale formatting rules.
- Color scales are inside conditional formatting but work differently because there is no formula option there are only colors options for numeric values
Frequently Asked Questions
How to use color scale in google sheets?
Color scales are found inside the conditional formatting “color scale” tab, it is very different from the normal conditional formatting rules we use to evaluate our data (any kind of data). Using color scales, we can only work with numbers, the values are automatically detected and assigned colors lighter to the maximum value, but we can change it we can assign a lighter color to the minimum value and can increase the intensity of the color as the value go towards maximum. This is how to use color scale in google sheets to make excellent visual heatmaps to understand the data values at a glance.
Wrapping up how to use color scale in google sheets. We have learned what are color scales, where they can be found in google sheets, and how they work. We have discussed the limitations of color scales and now we know that color scales work with only integer data types numbers, floats, percentages, and percentiles. We saw how we apply a color scale on a numeric data column, we have also discussed that we have three controlling points of our data they are minimum point, midpoint, and maximum point, we learned how to change colors for each point of our data and reverse any pre-defined color scale according to our preferred colors and their intensity. That’s all from how to use color scale in google sheets.
I hope you find this article helpful; I will see you very soon with another helpful tutorial till then, take care. Thank you for reading, keep learning with Office Demy.