Color scales in Google Sheets - Step-by-Step Guide

Introduction

Color scales help you see patterns in numbers by coloring cells based on their values. This makes it easy to spot high, low, or middle numbers quickly without reading each cell.

When you want to highlight sales figures from lowest to highest in a report.

When you need to quickly see which students scored the highest or lowest on a test.

When comparing monthly expenses to find which months cost more or less.

When tracking project progress and you want to see which tasks are nearly done or just started.

When analyzing survey results to spot trends in responses visually.

Steps

Step 1: Select

- the range of cells with numbers you want to color

The cells you want to format are highlighted

Step 2: Click

- Format menu at the top

A dropdown menu appears

Step 3: Choose

- Conditional formatting from the dropdown

The Conditional format rules pane opens on the right

Step 4: Under Format rules, click

- the dropdown and select Color scale

Color scale options appear below

Step 5: Pick

- a preset color scale or customize colors for Min, Mid, and Max values

Cells in the selected range change colors based on their values

Step 6: Click

- Done button at the bottom of the pane

The color scale is applied and the pane closes

Before vs After

Before

A column of numbers from 10 to 100 with no colors

After

The same column shows a gradient from green (lowest numbers) to red (highest numbers), making it easy to see which values are small or large

Settings Reference

Format rules

📍 Conditional format rules pane

Choose between coloring cells one color or using a gradient based on values

Default: Single color

Minpoint, Midpoint, Maxpoint colors

📍 Color scale section in Conditional format rules pane

Set colors that represent the lowest, middle, and highest values in the range

Default: Green (min), Yellow (mid), Red (max) for default scale

Midpoint type

📍 Color scale section

Define how the middle value is calculated for the color gradient

Default: Percent

Common Mistakes

Selecting cells with text or empty cells before applying color scales

Color scales only work on numbers, so text or empty cells won't get colored and may cause confusion

Select only the cells containing numbers before applying the color scale

Not adjusting the midpoint color or value when data is skewed

The default midpoint may not represent your data well, causing misleading colors

Customize the midpoint type and value to better fit your data distribution

Summary

Color scales visually highlight numbers by coloring cells from low to high values.

Use the Format > Conditional formatting > Color scale to apply this feature.

Remember to select only numeric cells and adjust midpoint settings for best results.