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.