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

Practice

1. What does conditional formatting do in Excel?

easy

A. Changes the appearance of cells based on rules you set

B. Deletes cells that contain errors

C. Automatically sorts data in a column

D. Creates a backup copy of your spreadsheet

Solution

1. Step 1: Understand the purpose of conditional formatting

   Conditional formatting is used to change how cells look based on conditions or rules you define.

2. Step 2: Compare options to the definition

   Only Changes the appearance of cells based on rules you set describes changing cell appearance based on rules, which matches conditional formatting.

3. Final Answer:

   Changes the appearance of cells based on rules you set -> Option A

4. Quick Check:

   Conditional formatting changes cell appearance [OK]

Hint: Think: formatting changes when conditions are met [OK]

Common Mistakes:

• Confusing conditional formatting with sorting
• Thinking it deletes or moves data
• Believing it creates backups automatically

2. Which of these is the correct way to start creating a conditional formatting rule in Excel?

easy

A. Select cells, then go to Home > Conditional Formatting > New Rule

B. Right-click a cell and choose Insert > Conditional Formatting

C. Use the formula bar to type =CONDITIONAL()

D. Click File > Save As > Conditional Formatting

Solution

1. Step 1: Recall how to access conditional formatting

   In Excel, conditional formatting rules are created from the Home tab under Conditional Formatting.

2. Step 2: Match the correct menu path

   Select cells, then go to Home > Conditional Formatting > New Rule correctly shows selecting cells, then Home > Conditional Formatting > New Rule.

3. Final Answer:

   Select cells, then go to Home > Conditional Formatting > New Rule -> Option A

4. Quick Check:

   Home tab > Conditional Formatting > New Rule [OK]

Hint: Look under Home tab for Conditional Formatting options [OK]

Common Mistakes:

• Trying to insert conditional formatting from right-click menu
• Typing a formula in the formula bar to start formatting
• Confusing file saving with formatting

3. You apply a conditional formatting rule to highlight cells greater than 100 in range A1:A5. The values are: 90, 150, 100, 200, 80. Which cells will be highlighted?

medium

A. A1, A3, and A5

B. A2 and A4

C. A3 and A5

D. All cells

Solution

1. Step 1: Identify which values are greater than 100

   Values: A1=90, A2=150, A3=100, A4=200, A5=80. Only 150 and 200 are greater than 100.

2. Step 2: Match cells with values greater than 100

   Cells A2 and A4 have values greater than 100, so these will be highlighted.

3. Final Answer:

   A2 and A4 -> Option B

4. Quick Check:

   Highlight cells > 100 = A2, A4 [OK]

Hint: Check which numbers are strictly greater than 100 [OK]

Common Mistakes:

• Including cells equal to 100
• Highlighting cells less than 100
• Highlighting all cells regardless of value

4. You want to highlight cells in B1:B5 that contain the word "Complete". You create a rule with formula =$B$1="Complete" but no cells get highlighted. What is the likely problem?

medium

A. The formula should be =EXACT(B1,"Complete") to match case

B. The formula should be =B1=="Complete" with double equals

C. The formula should use relative reference without $ signs

D. The formula should be =B1="complete" with lowercase c

Solution

1. Step 1: Understand relative references in conditional formatting

   When applying a formula rule to a range, the formula should use relative references (no $) so it adjusts per cell.

2. Step 2: Identify the problem with the formula

   If the formula uses absolute reference like $B$1, it only checks one cell, so others won't highlight.

3. Final Answer:

   The formula should use relative reference without $ signs -> Option C

4. Quick Check:

   Use relative references in conditional formatting formulas [OK]

Hint: Avoid $ in formula references for conditional formatting [OK]

Common Mistakes:

• Using double equals (==) which is invalid in Excel
• Ignoring case sensitivity issues
• Using absolute references ($) incorrectly

5. You want to highlight all rows in A2:D10 where the value in column C is less than 50. Which formula should you use in conditional formatting applied to A2:D10?

hard

A. =$C$2<50

B. =C2<50

C. =C$2<50

D. =$C2<50

Solution

1. Step 1: Understand how to apply conditional formatting to entire rows

   To highlight entire rows based on a column value, fix the column with $ and keep the row relative.

2. Step 2: Analyze each formula option

   =$C2<50 uses =$C2<50, which fixes column C but allows row to change per row, correctly checking each row's column C value.

3. Final Answer:

   =$C2<50 -> Option D

4. Quick Check:

   Fix column, relative row for row-based conditional formatting [OK]

Hint: Use $ before column letter to fix column in formula [OK]

Common Mistakes:

• Not fixing the column, so wrong cells checked
• Fixing both column and row, so only one cell checked
• Fixing row instead of column, causing wrong behavior