Why rules-based formatting highlights patterns in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to highlight cells greater than 50.

Excel

=A1 [1] 50

Drag options to blanks, or click blank then click option'

A>

B=

C<

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>'

Using '=' which only matches 50 exactly

The formula =A1>50 highlights cells with values greater than 50.

2fill in blank

medium

Complete the formula to highlight cells equal to the text "Done".

Excel

=A1=[1]

Drag options to blanks, or click blank then click option'

A'Done'

BDone

Cdone

D"Done"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around text

Using single quotes instead of double quotes

Text values in formulas must be in double quotes, so "Done" is correct.

3fill in blank

hard

Fix the error in the formula to highlight cells with values less than or equal to 100.

Excel

=A1 [1] 100

Drag options to blanks, or click blank then click option'

A<

B=>

C<=

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=>' which is not valid

Using '==' which is not valid in Excel formulas

The correct operator for less than or equal is <=. The option '=>' is invalid in Excel.

4fill in blank

hard

Fill both blanks to create a formula that highlights cells with values between 10 and 20 inclusive.

Excel

=AND(A1 [1] 10, A1 [2] 20)

Drag options to blanks, or click blank then click option'

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '<='

Using '>' instead of '>='

The formula uses >= to check if A1 is at least 10, and <= to check if A1 is at most 20.

5fill in blank

hard

Fill all three blanks to create a formula that highlights cells containing text starting with "A" and having length greater than 3.

Excel

=AND(LEFT(A1,1)=[1], LEN(A1) [2] [3])

Drag options to blanks, or click blank then click option'

A"A"

B>

C3

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around "A"

Using '<' instead of '>' for length comparison

The formula checks if the first letter is "A" and the length is greater than 3.

Practice

1. What is the main purpose of rules-based formatting in Excel?

easy

A. To change cell colors based on specific conditions

B. To add formulas to cells automatically

C. To sort data alphabetically

D. To protect cells from editing

Solution

1. Step 1: Understand rules-based formatting

   Rules-based formatting changes how cells look based on rules you set.

2. Step 2: Identify the main effect

   The main effect is changing cell colors or styles to highlight data patterns.

3. Final Answer:

   To change cell colors based on specific conditions -> Option A

4. Quick Check:

   Rules-based formatting = change cell appearance [OK]

Hint: Think: formatting changes appearance by rules [OK]

Common Mistakes:

• Confusing formatting with sorting
• Thinking it adds formulas
• Assuming it protects cells

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

easy

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

B. Right-click cell and choose Insert Formula

C. Use Data > Sort to create a rule

D. Go to Review > Protect Sheet

Solution

1. Step 1: Recall how to create conditional formatting

   You start by selecting cells, then use the Home tab's Conditional Formatting menu.

2. Step 2: Identify the correct menu path

   The correct path is Home > Conditional Formatting > New Rule.

3. Final Answer:

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

4. Quick Check:

   New rule path = Home > Conditional Formatting > New Rule [OK]

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

Common Mistakes:

• Trying to insert formulas from right-click menu
• Using Data > Sort instead of formatting
• Confusing with sheet protection options

3. Given cells A1 to A5 contain values: 10, 20, 30, 40, 50. You apply a rule: Format cells greater than 25 with red fill. Which cells will be highlighted?

medium

A. A1, A2

B. A1, A3, A5

C. All cells

D. A3, A4, A5

Solution

1. Step 1: Identify values greater than 25

   Values are 10, 20, 30, 40, 50. Values greater than 25 are 30, 40, 50.

2. Step 2: Match cells with those values

   Cells A3, A4, and A5 contain 30, 40, and 50 respectively.

3. Final Answer:

   A3, A4, A5 -> Option D

4. Quick Check:

   Values > 25 = A3, A4, A5 [OK]

Hint: Check which values meet the condition > 25 [OK]

Common Mistakes:

• Including values equal to 25
• Highlighting wrong cells by position
• Assuming all cells highlight

4. You set a conditional formatting rule with formula =A1>100 but no cells get highlighted even though some values are above 100. What is the likely problem?

medium

A. The formula should use relative reference like =A$1>100

B. The formula should be =A1>=100 to include 100

C. The rule was applied to the wrong cell range

D. Conditional formatting does not work with formulas

Solution

1. Step 1: Check formula correctness

   The formula =A1>100 is valid and should highlight cells with values above 100.

2. Step 2: Consider rule application range

   If no cells highlight, likely the rule was applied to a range that does not include those cells.

3. Final Answer:

   The rule was applied to the wrong cell range -> Option C

4. Quick Check:

   Wrong range = no highlight [OK]

Hint: Check if rule covers the correct cells [OK]

Common Mistakes:

• Thinking formula syntax is wrong
• Assuming conditional formatting ignores formulas
• Confusing relative and absolute references here

5. You want to highlight cells in column B that are above the average of the entire column B. Which conditional formatting formula should you use for the range B1:B10?

hard

A. =B1>AVERAGE(B1:B10)

B. =B1>AVERAGE($B$1:$B$10)

C. =B1>AVERAGE(B$1:B$10)

D. =B1>AVERAGE(B1:B$10)

Solution

1. Step 1: Understand the need for fixed range in average

   The average should always calculate over the fixed range B1:B10, so use absolute references $B$1:$B$10.

2. Step 2: Use relative reference for cell comparison

   The cell reference B1 should be relative so it adjusts for each cell in the range.

3. Final Answer:

   =B1>AVERAGE($B$1:$B$10) -> Option B

4. Quick Check:

   Fixed average range + relative cell = correct formula [OK]

Hint: Use $ to fix average range, keep cell relative [OK]

Common Mistakes:

• Not fixing average range with $ signs
• Fixing cell reference instead of range
• Using inconsistent mixed references