Using Google Sheets’ conditional formatting feature, we can alter the color of a cell-based on its value. We’ll show you how to create both single color and multi-color heatmaps.
Conditional formatting heatmaps are a great way to show you a spike in sales or a dip in a student’s grades. This allows us to pinpoint problems and fix them asap.
Here’s how to create a heatmap in Google Sheets:
- Select the area you would like to apply the heatmap to. This can be a column, row, or two-dimensional array.
- Right-click/two-finger tap to open the options menu and click “conditional formatting†OR click “Format†then “Conditional Formatting†in the toolbar
- After the Conditional Formatting menu opens up, select “Color Scale.†Now we have our Heatmap!
Multi-Color Heatmap
Multi-color heatmaps make it even easier to spot data that’s higher or lower than the usual range by adding eye-catching colors to your maximum and minimum values.
After following the steps above (select the area and apply conditional formatting), make sure to click the “color scale†column.
We can head over to “Max Value,†and click the paint bucket color selector icon to choose a color. I chose Green for the max value and Red for the min value.
When we look at the heatmap we can see which days brought us the most ice cream sales. But, on Thursday of Week 2 we had 42 sales which is way more than any of the other days, pretty much drowning out the other variation.
Digging Deeper (Advanced Settings)
To show greater detail, we can adjust the min and max points. Let’s say that we need to sell exactly 5 ice cream cones to break even. I would want to place any numbers below 5 in red (since I’m losing money).
Changing the midpoint
Locate the “midpoint†section in the Color Scale tab and change the option to “Number†and set it to 5 (or whatever your break-even price is). Now we can see which days we were losing the most money, however, we can’t see many variations in the green cells. To do this, let’s exclude outliers from our heatmap
Excluding Outliers in your Heatmap
If we usually have high sales at 10 ice cream cones per week, most of our data will be below 10, so there is no reason to “dilute†the scale with an outlier such as 42.
Let’s set the “Maxpoint†to a number, then make it 10.
Now all numbers above 10 will be this exact dark green, allowing us to see more detail where it is needed.
The best thing about this conditional formatting heatmap is that it’s dynamic meaning it will change whenever you enter new data.
Applications
Now let’s see how we can use heatmaps in real-world situations.
We can use heatmaps to see which students scored well and who performed badly on each exam. This allows educators to better serve their students.
First, we’ll select our grades, then apply conditional formatting (shown above).
Make sure you reorder the conditional formatting rules so that this one takes precedence over the one already there or else you’ll end up with this.
To reorder the conditional formatting rules just head over to the conditional formatting bar. Hover over a rule, then hold down when three vertical dots appear. Simply drag it above the previous rule.
However, our current spreadsheet shows us the highest and lowest numbers. To show the highest numbers of just one test, we need to select each column individually.
To do this open up our conditional Formatting rule, and click on the grid icon next to the range input field. While holding the “CTRL†or “CMD†key select the first cell and drag your mouse pointer down to the end of the column like this:
Now click done “add another rule.†This automatically saves and duplicates the current rule, so you can simply repeat the process for each column.
Final Result
Now we can see how students are performing with a simple Google Sheets heatmap.
Taking Heatmaps to the Next Level
Way back in seventh grade science, we were tasked with monitoring plants after we altered a variable in their environment. Our group decided to add sponges and paper towels (spowels) to see if the plant would last longer in a simulated drought.
Keeping neat data is important, and I forced my group to use a spreadsheet instead of a table (like everyone else). And lets just say, spreadsheets are very powerful.
Now that I’m looking at it, I probably should’ve put a label stating that we were measuring plant height in cm (it was seventh grade).
But we were able to use conditional formatting to track the growth of each plant, but the coolest part was tracking the plant’s color.
We wanted to track the color of the plant to track it’s health, so we set up conditional formatting so the exact colors (recorded in PMS) of each plant were shown in each cell.
I’ll just say that our final presentation was pretty cool. Remember, spreadsheets are always better than paper data tables.