A heat map turns raw numbers into a visual signal. Instead of scanning rows of data looking for outliers or low values, the colors do the work for you. High performers show up green, problem areas show up red, and patterns in your dataset become obvious at a glance.
Google Sheets does not have a dedicated heat map button, but conditional formatting handles it in a few clicks. This guide covers four types of heat maps you can build: gradient color scale, single-color threshold, geographic, and calendar. Each one uses a different approach depending on what your data needs to show.
What Is a Heat Map in Google Sheets?
A heat map is a visualization layer applied to a spreadsheet that uses color to represent the value of each cell relative to the rest of the dataset. Cells with low values get one color (typically red), cells with high values get another (typically green), and everything in between gets a gradient shade that reflects where it falls in the range.
This makes heat maps especially useful for spotting trends, identifying outliers, and comparing performance across large datasets where reading individual numbers would take too long. Common use cases include:
- Student grade distributions across multiple tests
- Sales rep performance by region or month
- Website traffic by day of week
- Inventory levels across product lines
- Survey response patterns
Below is an example of a finished heat map in Google Sheets. Low values are shaded red and high values are shaded green, with a gradient applied across everything in between.
How to Create a Heat Map in Google Sheets: Quick Summary
Select your data, then go to Format > Conditional formatting > Add new rule > Color Scale. Choose your gradient colors and click Done. That is the core of it. The sections below walk through each variation in full detail.
| Heat Map Type | Best For | Method |
|---|---|---|
| Gradient (Color Scale) | Relative comparisons across a continuous range | Conditional formatting > Color Scale |
| Single Color Threshold | Fixed pass/fail or above/below criteria | Conditional formatting > Single Color |
| Geographic | Regional or country-level data distribution | Insert > Chart > Geo map |
| Calendar | Activity or value patterns over time | Conditional formatting + custom number format |
Gradient Heat Map in Google Sheets (Color Scale)
The gradient method is the most common approach. It applies a continuous color scale across your dataset so that every cell gets a background color that reflects its value relative to the rest. Use this when the relative difference between values matters, not just whether something is above or below a fixed threshold.
- Select the cells you want to apply the heat map to.
- Go to Format and click Conditional formatting.
- In the Conditional formatting pane on the right, click Add new rule.
- In the rule pane, click Color Scale.
- Click Preview to select color scale from the preset gradient options. The color on the left applies to the lowest values in your dataset and the color on the right applies to the highest. You can customize each color individually.
- Click Done. Google Sheets applies the gradient and colors every cell in your selected range.
Single-Color Heat Map in Google Sheets (Threshold-Based)
The gradient approach works well when relative differences matter. But sometimes you have fixed criteria: a student either passes or fails, a sales rep either hit quota or did not, inventory is either critically low or it is not. In those cases, a single-color threshold rule is cleaner.
Instead of a gradient across your full dataset, you define a specific cutoff and assign one background color to everything above it and another to everything below it. Multiple rules can stack, which lets you create distinct visual markers for different ranges within the same data.
Here is how to set up a two-rule threshold heat map using values below 100 (red) and above 400 (green) as an example:
- Select the cells you want to apply the heat map to.
- Go to Format and click Conditional formatting.
- In the pane on the right, click Add new rule.
- Click Single Color.
- In the Format cells if dropdown, select Greater than.
- Enter 400 in the value field.
- Under Formatting style, choose the color you want applied to cells above the threshold (green in this example).
- Click Done.
Now repeat the same steps, this time setting the condition to Less than 100 and choosing red as the formatting color. Every cell below 100 will turn red, every cell above 400 will turn green, and everything in between stays unformatted.
This heat map is dynamic. Change a value in any cell and the color updates automatically to reflect whether it still meets the rule conditions.
Important: When you copy and paste cells from elsewhere in the sheet into your heat map range, the conditional formatting rules are erased. To preserve the heat map when copying data in, right-click and choose Paste Special > Paste Values Only. This pastes the numbers without overwriting the formatting rules.
Advanced Color Scale Settings
The Color Scale option in conditional formatting includes advanced controls that give you precise control over how the gradient maps to your dataset. These are particularly useful when your data has a skewed distribution or when you want to anchor the midpoint at a specific value rather than letting Google Sheets calculate it automatically.
There are three control points:
- Minpoint — Sets what value gets the lowest color in your gradient. Options: Min value (default), Number, Percent, or Percentile. Setting this to a specific number is useful when your dataset contains outliers on the low end that would otherwise compress the rest of the gradient into a narrow range.
- Midpoint — Sets the neutral middle color of your gradient. Set to None by default. Adding a midpoint color (typically white or yellow) creates a three-color gradient, which works well for datasets where values above and below a central benchmark both need to stand out. Setting Midpoint to Percentile 50 anchors the middle color at the median of your dataset.
- Maxpoint — Sets what value gets the highest color in your gradient. Options: Max value (default), Number, Percent, or Percentile. Useful when high-end outliers in your dataset would otherwise pull the gradient so far that mid-range values all look similar.
Each control point also has its own color picker, so you can fully customize the gradient rather than relying on the preset options. For most datasets, the defaults work fine. Adjust these controls when the default gradient is not clearly showing the patterns in your data.
Note: if you are pulling data into your sheet via an API or an external data import, the conditional formatting rules apply dynamically as new values populate. You do not need to reapply them each time the data refreshes.
When to Use a Heat Map vs. Other Chart Types
Heat maps are not always the right tool. Here is a quick comparison to help you choose:
| Visualization | Best For | Use Heat Map Instead When |
|---|---|---|
| Bar chart | Comparing totals across categories | You have many categories and need to see density across the full grid |
| Line chart | Showing trends over time | You want to see trends across multiple variables simultaneously |
| Sparkline | Compact inline trend visualization per row | You need to compare relative values across rows and columns at once |
| Scatter plot | Showing correlation between two variables | You have a grid of values and want color to show magnitude instantly |
| Pivot table | Summarizing aggregated data | You want to add a visual layer on top of the summary numbers |
Geographic Heat Map in Google Sheets
A geographic heat map uses a geo chart to display your dataset on a map, with each country or region shaded based on its value. This is useful for visualizing sales by territory, survey responses by country, or any other dataset where location is a meaningful dimension.
Your data needs two columns: one with location names (country or region) and one with the numeric values you want to visualize. Google Sheets recognizes standard country names and many regional names automatically.
- Select your location and value data.
- Go to Insert > Chart.
- In the Chart editor, go to Chart type and select Geo chart.
- Go to the Customize tab and select Geo.
- Select the colors for your gradient. The min color applies to the lowest value in your dataset and the max color applies to the highest.
The result is a world map with each country shaded according to its value. Countries with low values appear in the min color and countries with high values appear in the max color, with a gradient applied across everything in between.
Calendar Heat Map in Google Sheets
A calendar heat map arranges your data in a calendar grid and applies a color scale to each day based on its value, similar to the contribution graph on GitHub. It is especially effective for showing activity patterns, daily sales figures, or any metric tracked over time.
The setup requires one extra step: you replace the date labels in your calendar with the actual numeric values, then hide those numbers using a custom number format so only the colors are visible.
- Set up your calendar grid and replace the date cells with the numeric values you want to visualize.
- Select all the value cells in your calendar.
- Go to Format > Conditional formatting.
- Click Add new rule.
- Click Color Scale and set the color gradient you want to use.
- Click Done. The calendar cells now have background colors reflecting their values.
- To hide the numbers and show only the colors, select all your value cells and go to Format > Number > Custom number format.
- Type three semicolons (
;;;) into the format field and click Apply. This tells Google Sheets to display nothing in the cell while keeping the value intact for the conditional formatting rule to read.
The result is a color-only calendar grid where the values drive the shading but remain invisible in the cells.
How to Remove or Edit a Heat Map in Google Sheets
To remove a heat map, select the range it covers, go to Format > Conditional formatting, and delete the relevant rules from the pane on the right by clicking the trash icon next to each one. This removes the color formatting without affecting your data.
To edit an existing rule (change the colors, adjust the threshold, or modify the range it applies to), open the same pane, click the rule you want to change, make your edits, and click Done.
Troubleshooting Google Sheets Heat Maps
Colors are not appearing after I set up the rule. Check that your selected range matches exactly where your data is. If the range in the conditional formatting rule does not overlap with your data cells, nothing will apply.
My color scale is not showing much variation. This usually means your dataset has outliers that are compressing the gradient. Open the Advanced settings in the Color Scale panel and set Minpoint or Maxpoint to a specific number that better reflects your typical data range, leaving the outliers to anchor the extremes.
The heat map disappeared after I pasted data. Pasting cells into a heat map range with standard paste overwrites the conditional formatting. Always use Paste Special > Paste Values Only to preserve your rules.
My geo chart is not recognizing location names. Google Sheets expects standard English country and region names. Check for spelling variations or abbreviations and correct them to match what Google Sheets recognizes.
The calendar heat map numbers are still visible. Make sure you applied the custom number format (;;;) to exactly the same range as your conditional formatting rule, not just part of it.
Frequently Asked Questions
How do I create a heat map in Google Sheets?
Select your data range, go to Format > Conditional formatting > Add new rule, then click Color Scale. Choose your gradient colors from the Preview options and click Done. Google Sheets applies a background color to each cell based on its value relative to the rest of the dataset.
Does Google Sheets have a built-in heat map template?
Google Sheets does not include a dedicated heat map template, but you can build one using conditional formatting in a few steps. Once set up, you can save your sheet as a reusable template by going to File > Make a copy.
What is the difference between a gradient heat map and a single-color heat map?
A gradient heat map uses a continuous color scale so every cell gets a shade that reflects exactly where its value falls in the dataset. A single-color heat map uses fixed threshold rules: cells above or below a specific value get one color, and everything else stays unformatted. Use gradient when relative differences matter, and single-color when you have fixed pass/fail or above/below criteria.
How do I remove a heat map from Google Sheets?
Select the range your heat map covers, go to Format > Conditional formatting, and click the trash icon next to the rule you want to delete. This removes the color formatting while leaving your data intact.
Why is my conditional formatting color scale not working?
The most common cause is a mismatch between the range in your conditional formatting rule and where your data actually is. Open the formatting pane and confirm the range is correct. If the range is right but the gradient looks flat, your dataset likely has outliers pulling the scale to extremes. Use the Minpoint and Maxpoint controls in Advanced settings to set fixed boundaries.
Can I apply a heat map to an entire row or column in Google Sheets?
Yes. When selecting your range before applying the conditional formatting rule, you can select full rows or columns. The color scale will apply across all cells in that selection based on the values present. You can also apply a single rule to multiple non-contiguous ranges by entering them manually in the range field of the formatting pane.
How do I make the numbers invisible in a calendar heat map?
Select the cells containing your values, go to Format > Number > Custom number format, and type three semicolons (;;;) into the format field. Click Apply. This hides the displayed value while keeping the underlying number intact so the conditional formatting rule continues to read it correctly.
Can I create a heat map in Google Sheets on mobile?
The Google Sheets mobile app supports conditional formatting, but the interface is more limited than the desktop version. You can apply a basic color scale rule on mobile, but the Advanced settings for Minpoint, Midpoint, and Maxpoint are easier to configure on desktop. For calendar and geographic heat maps, desktop is recommended.