Conditional formatting basics in Google Sheets - Step-by-Step Guide

Introduction

Conditional formatting helps you change how cells look based on their values. It makes important data stand out automatically, like highlighting high sales or overdue tasks.

When you want to highlight all sales above $1000 in your sales report

When you need to mark dates that are past today’s date in a task list

When you want to color-code grades to see which students scored below 60

When you want to quickly spot duplicate entries in a list

When you want to show cells with empty values in a different color

Steps

Step 1: Select the cells you want to format

- Spreadsheet grid

The selected cells are highlighted with a blue border

Step 2: Click Format

- Top menu bar

A dropdown menu appears

Step 3: Select Conditional formatting

- Format dropdown menu

The Conditional format rules pane opens on the right side

Step 4: Under Format cells if, choose a condition like 'Greater than'

- Conditional format rules pane

The condition input box appears below

Step 5: Type the value to compare, for example, 1000

- Value input box under the condition

The rule is set to highlight cells greater than 1000

Step 6: Click the Fill color icon and pick a highlight color

- Formatting style section in the pane

The preview shows cells with the chosen color

Step 7: Click Done

- Bottom of the Conditional format rules pane

The selected cells with values over 1000 are highlighted with the chosen color

Before vs After

Before

A list of sales numbers from 500 to 2000 with no colors

After

All sales numbers above 1000 are highlighted in green

Settings Reference

Format cells if

📍 Conditional format rules pane

Choose the condition that triggers the formatting

Default: Cell is not empty

Formatting style

📍 Conditional format rules pane

Set how the cells look when the condition is true

Default: No formatting

Common Mistakes

Selecting the wrong cells before applying conditional formatting

The formatting will apply to the wrong area and not highlight the intended data

Always select the exact cells you want to format before opening the conditional formatting pane

Using a condition that does not match the data type, like 'Greater than' on text

The rule will not work because numbers and text are treated differently

Choose conditions that fit the data type, for example, 'Text contains' for text cells

Not clicking Done after setting the rule

The rule will not be saved or applied

Always click Done to save and apply your conditional formatting rule

Summary

Conditional formatting changes cell appearance based on rules you set.

It helps highlight important data automatically, like high numbers or dates.

Remember to select the right cells and click Done to apply your rules.