How to Create and Format a Heatmap in Google Sheets Using Conditional Formatting Colour Scales

When presenting dense numerical spreadsheets—such as monthly sales revenue, student examination scores, website server response latencies, or employee attendance rates—scanning long columns of raw figures makes it difficult to spot trends or anomalies quickly. A Heatmap transforms complex numerical data into an intuitive visual gradient, assigning colour shades according to relative values so high performers, bottlenecks, and outliers stand out instantly.

In Google Sheets, you can build dynamic, automated heatmaps without writing complex scripts or inserting external add-ons by leveraging Conditional Formatting Colour Scales. Here is a step-by-step tutorial on how to set up, customise, and optimise heatmaps in Google Sheets.

What Is a Heatmap in Google Sheets?

A heatmap applies a continuous two-colour or three-colour gradient across a selected range of numerical cells:

• Relative Scaling: Cells are coloured dynamically relative to the lowest, median, and highest numerical values within the highlighted range.
• Automated Updates: As numbers change or new rows are added, Google Sheets recalibrates gradient intensities in real time.
• Zero Formula Maintenance: Operates entirely through native rendering rules without altering your underlying cell formulas or mathematical totals.

How to Create a Heatmap in Google Sheets

Setting up your initial colour scale heatmap takes less than two minutes:

1. Open your spreadsheet in Google Sheets.
2. Click and drag to select the numerical dataset you wish to format (e.g., B2:F20). Avoid including header labels or summary total rows, as extreme totals will distort your gradient distribution.
3. Click Format in the top menu bar.
4. Select Conditional formatting from the dropdown menu. A sidebar panel titled Conditional format rules opens on the right.
5. At the top of the sidebar, switch from the Single colour tab to the Colour scale tab.
6. Google Sheets instantly applies a default Green-to-White gradient across your selected cells.

Customising Heatmap Colour Palettes

Different datasets call for different chromatic metaphors. You can customise your gradient scale to match standard corporate palettes:

1. In the Conditional format rules sidebar, click the Preview palette dropdown box under Colour scale.
2. Choose from several popular preset gradients:
   • Green to Red: Traditional for metrics where high numbers are positive (sales revenue, profit margins, satisfaction scores).
   • Red to Green: Ideal for metrics where lower values represent superior performance (customer support tickets, server latency, error rates).
   • Single Hue Gradients: Subtle shades (such as Light Blue to Dark Blue) that look clean, modern, and accessible for monochrome printing.
3. To configure custom brand colours, click the individual Minpoint, Midpoint, and Maxpoint colour buckets to pick exact hex codes.
4. Click Done at the bottom of the sidebar to lock in your heatmap formatting.

Scale Point	Default Setting	Custom Value Options
Minpoint	Min value in range	Exact number, percentile (e.g., 10th), or custom formula
Midpoint	None (2-colour scale)	Percentile (50th), static median baseline, or neutral white
Maxpoint	Max value in range	Target quota ceiling, 90th percentile, or fixed benchmark

Creating a “Values-Hidden” Visual Heatmap

If you want your heatmap to serve purely as an executive graphic matrix where numbers do not clutter the visual presentation:

1. Highlight your heatmap cell range.
2. Click Format > Number > Custom currency / Custom number format.
3. In the format field, enter three consecutive semicolons: ;;;
4. Click Apply.
5. Google Sheets conceals the visible text while preserving all underlying numbers for calculations and gradient shading, leaving behind a clean visual graphic matrix.

Best Practices to Ensure Readability

To keep your Google Sheets heatmaps professional and accessible:

• Exclude Summary Totals: Always omit SUM() and AVERAGE() rows from the formatting range so extreme aggregates do not skew the median gradient.
• Consider Colour Accessibility: Red-green palettes can be challenging for colour-blind colleagues. Opt for blue-to-orange or single-hue monochromatic ramps for public dashboards.
• Limit Applied Ranges: Applying colour scales across thousands of rows can slow browser rendering; scope your rules tightly to the necessary tables.