Color Scale Conditional Formatting in Google Sheets

Color scale conditional formatting in Google Sheets applies a gradient of colors across a range based on each cell’s value. Low values get one color, high values get another, and everything in between blends proportionally. The result is a visual heat map that makes patterns, outliers, and trends visible without reading a single number.

In This Guide

• How Color Scale Works
• Step-by-Step: Apply a Color Scale
• Two-Color vs Three-Color Scales
• Custom Min, Max, and Midpoint Values
• Build a Heat Map Across Rows and Columns
• Tips
• Related Google Sheets Tutorials
• FAQ

How Color Scale Works

Standard conditional formatting applies a single fill color when a condition is true. Color scale works differently. It maps every numeric value in the range to a position on a gradient. The lowest value gets the min color, the highest gets the max color, and every value in between gets a proportional blend.

Google Sheets calculates the position using this logic:

Value Position	Color Applied
At or below minimum	Min color (e.g., green)
At midpoint (if set)	Midpoint color (e.g., yellow)
At or above maximum	Max color (e.g., red)
Between min and max	Blended shade

Cells with text, blanks, or errors are skipped. Only numeric values participate in the gradient.

Step-by-Step: Apply a Color Scale

We’ll use the monthly sales summary to color-code performance across product categories.

Step 1: Select the Range and Open Conditional Formatting

Highlight the numeric cells you want to format. For the monthly summary, select B2:E13 (the sales values, not the headers or the Month column).

Go to Format > Conditional formatting.

Step 2: Switch to the Color Scale Tab

At the top of the Conditional format rules panel, you see two tabs: Single color and Color scale. Click Color scale.

The panel switches to show min, midpoint, and max color options instead of the standard condition rules.

Step 3: Choose a Color Scheme and Click Done

Pick a preset gradient from the Preview dropdown, or set custom colors:

• Minpoint: green (low values)
• Midpoint: yellow (middle values)
• Maxpoint: red (high values)

Click Done. The gradient applies across the entire range. Lower sales figures shade green, middle values turn yellow, and top performers glow red.

Two-Color vs Three-Color Scales

Google Sheets supports two gradient types:

Two-Color Scale

Uses a min color and a max color with a smooth blend between them. Best for simple high/low comparisons.

Setting	Example
Min	White
Max	Dark blue

The result: light cells have low values, dark cells have high values. Two-color scales work well when you only need to answer “how much?” without distinguishing a middle range.

Three-Color Scale

Adds a midpoint color between min and max. This creates two distinct gradients: min-to-mid and mid-to-max.

Setting	Example
Min	Green
Midpoint	Yellow
Max	Red

Three-color scales reveal more nuance. Green-yellow-red is a classic “traffic light” pattern where green means good, yellow means watch, and red means act.

Custom Min, Max, and Midpoint Values

By default, Google Sheets sets the min and max to the actual lowest and highest values in your range. You can override this with fixed values for more control.

Setting Custom Boundaries

In the Color scale tab, each point (min, midpoint, max) has a type dropdown:

Type	Behavior
Min value / Max value	Uses the actual min or max from the range
Number	Uses a fixed number you enter
Percent	Uses a percentage of the range (0-100)
Percentile	Uses a statistical percentile of the values

When to Use Custom Values

Fixed benchmarks: If $10,000 is your monthly target, set the midpoint to 10000. Everything below gets the low color, everything above gets the high color, regardless of the actual data range.

Consistent scales across sheets: If you compare two departments, set both color scales to the same fixed min and max. Otherwise each range gets its own gradient, and the same shade of green could mean $5,000 in one sheet and $50,000 in another.

Outlier handling: One extreme value can compress the rest of the gradient into a narrow band. Set the max to a reasonable ceiling (like the 95th percentile) to spread colors more evenly across the typical range.

Build a Heat Map Across Rows and Columns

A heat map applies color scale to a grid of numbers so you can spot patterns in both directions. The monthly sales summary is a natural fit: months run down the rows, product categories run across the columns.

Apply the Color Scale to the Full Grid

Select B2:D13 (all product categories, all months). Apply a single color scale rule. Google Sheets evaluates every cell in the range together, so the gradient spans the full grid.

Now you can see two things at once:

• Across columns: which category sells more in each month
• Down rows: how each category trends over time

The darkest cells jump out immediately. No scanning row by row.

Compare by Row vs by Grid

One color scale across the entire grid compares all values against each other. If product categories have very different scales (Optics averaging $16,000 vs Laboratory averaging $10,000), the Laboratory column may look uniformly light.

To compare each row or column against itself, apply separate color scale rules:

1. Select one column (B2:B13)
2. Apply a color scale
3. Repeat for each column

Now each column’s gradient runs from its own min to its own max. This answers “which month was best for this category?” rather than “which cell is the highest overall.”

Tips

1. Start with presets, then customize. The built-in green-yellow-red and white-to-blue gradients work for most use cases. Only set custom values when the default gradient doesn’t tell the story you need.

2. Match the color direction to the meaning. Green-to-red implies good-to-bad. If higher values are better (revenue, scores), use green for max. If higher values are worse (expenses, errors), use red for max.

3. Combine color scale with charts. Color scale shows patterns in raw data. A line chart or bar chart built from the same data tells the story in a presentation. Use both.

4. Color scale rules stack. You can have a color scale rule and a single-color rule on the same range. The single-color rule takes priority for cells that match its condition. Use this to flag specific thresholds (like cells below zero) while keeping the gradient for everything else.

Related Google Sheets Tutorials

• Conditional Formatting in Google Sheets - Full guide to all formatting rule types, including single color and custom formulas
• Custom Formula in Conditional Formatting - Write formula-based rules for more complex conditions
• Format Cells Based on Another Cell - Apply formatting using values from a different column
• Google Sheets Charts: The Complete Guide - Visualize the same data with bar charts, line charts, and more

Frequently Asked Questions

How do I add a color scale in Google Sheets?

Select the range, go to Format > Conditional formatting, and switch to the Color scale tab. Choose a color scheme from the presets or set custom min and max colors. Click Done. The gradient applies automatically based on each cell’s value.

What is the difference between color scale and single color conditional formatting?

Single color formatting applies one fill color to cells that meet a condition (like greater than 100). Color scale applies a gradient across all cells based on their values. Low values get one color, high values get another, and everything in between blends proportionally.

Can I use a color scale on text values in Google Sheets?

No. Color scale only works on numeric values. Cells containing text, blanks, or errors are skipped and get no color. If your numbers are stored as text, convert them first using the VALUE function or Format > Number.

How do I create a heat map in Google Sheets?

A heat map is a color scale applied to a grid of numbers. Select the entire data range (not the headers), apply a color scale rule with green for low and red for high (or any gradient), and the cells shade automatically. This works well for comparing values across rows and columns.

Can I set custom midpoint values for a color scale?

Yes. In the Color scale tab, set the midpoint type to Number, Percent, or Percentile and enter a value. The gradient transitions from min color to midpoint color to max color. This gives you a three-color scale for more precise shading.