Conditional formatting basics in Excel - Step-by-Step Guide

Introduction

Conditional formatting helps you highlight cells automatically based on their values. It makes it easy to spot important data like high sales or overdue tasks without scanning every number.

When you want to highlight sales above a target to see top performers quickly

When you need to mark dates that are past today to track overdue items

When you want to color-code grades to see who passed or failed at a glance

When you want to flag duplicate entries in a list to avoid errors

When you want to visually separate high, medium, and low values in a report

Steps

Step 1: Select the cells you want to format

- Worksheet grid

The selected cells are highlighted with a border

Step 2: Click Conditional Formatting

- Home tab, Styles group

A menu with formatting options appears

Step 3: Choose a rule type like 'Highlight Cells Rules' or 'Top/Bottom Rules'

- Conditional Formatting menu

A submenu with specific conditions appears

Step 4: Select a condition, for example 'Greater Than...'

- Conditional Formatting submenu

A dialog box opens to enter the value and choose a format

Step 5: Type the value to compare, e.g., 100, and pick a formatting style like light red fill

- Dialog box

Preview shows how cells greater than 100 will look

Step 6: Click OK

- Dialog box

Cells meeting the condition are highlighted with the chosen format

Before vs After

Before

A list of sales numbers from 50 to 200 with no colors

After

Cells with sales above 100 are filled with light red fill and dark red text

Settings Reference

Rule Type

📍 Conditional Formatting menu on Home tab

Choose the kind of condition to apply for formatting

Default: No rule applied

Condition Value

📍 Dialog box after selecting a rule

Set the value or criteria that triggers the formatting

Default: Empty

Format Style

📍 Dialog box after entering condition

Choose how the cells will look when the condition is true

Default: Light Red Fill with Dark Red Text

Common Mistakes

Selecting the wrong cells before applying conditional formatting

The formatting will apply to the wrong data and not highlight what you want

Always select the exact cells you want to format before starting

Typing the condition value incorrectly, like using text when numbers are needed

The rule will not work because the condition does not match the data type

Match the condition value type to the data type in the cells

Applying too many rules on the same cells without managing rule order

Rules can overlap and cause confusing or unexpected formatting

Use Manage Rules to check and order rules properly

Summary

Conditional formatting highlights cells automatically based on rules you set

It helps you quickly see important data like high values or duplicates

Always select the right cells and set correct conditions for best results