Bird
Raised Fist0
Excelspreadsheet~5 mins

Conditional formatting basics in Excel - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Recall & Review
beginner
What is conditional formatting in Excel?
Conditional formatting is a feature that changes the look of cells automatically based on rules you set, like coloring cells if they contain certain values.
Click to reveal answer
beginner
How do you apply conditional formatting to highlight cells greater than 100?
Select the cells, go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than, then enter 100 and choose a format.
Click to reveal answer
beginner
What types of formats can conditional formatting change?
It can change cell fill color, font color, font style (bold, italic), and add icons or data bars.
Click to reveal answer
intermediate
Can conditional formatting use formulas? If yes, why?
Yes, formulas let you create custom rules based on any condition you want, like comparing values in different cells.
Click to reveal answer
intermediate
What happens if multiple conditional formatting rules apply to the same cell?
Excel applies the rules in order. The first rule that matches can override others, but you can change rule order and stop if true.
Click to reveal answer
Which menu do you use to start conditional formatting in Excel?
AHome
BInsert
CData
DReview
What does conditional formatting do?
ACalculates sums
BSorts data alphabetically
CProtects cells from editing
DAutomatically changes cell appearance based on rules
Which of these can you NOT change with conditional formatting?
ACell fill color
BFont color
CCell formula
DFont style
To highlight cells less than 50, which rule type do you choose?
ALess Than
BGreater Than
CBetween
DEqual To
What is the benefit of using a formula in conditional formatting?
AIt speeds up Excel
BIt allows custom conditions beyond preset rules
CIt locks the cells
DIt changes the worksheet layout
Explain how to apply conditional formatting to highlight cells with values above a certain number.
Think about the steps from selecting cells to setting the rule.
You got /8 concepts.
    Describe how conditional formatting rules are applied when multiple rules affect the same cell.
    Consider how Excel decides which format to show.
    You got /5 concepts.

      Practice

      (1/5)
      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