Icon sets in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to apply icon sets to the range A1:A10 using conditional formatting.

Excel

Select the range A1:A10, then go to Conditional Formatting > Icon Sets > [1].

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

AData Bars

BColor Scales

C3 Arrows

DTop/Bottom Rules

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'Data Bars' or 'Color Scales' instead of an icon set.

Trying to apply icon sets outside Conditional Formatting.

The '3 Arrows' icon set is a common choice for showing values increasing or decreasing in a range.

2fill in blank

medium

Complete the formula to use icon sets with a custom rule to show a green check icon if the value in B2 is greater than 80.

Excel

Select B2 > Conditional Formatting > Icon Sets > More Rules > Format all cells based on their values > Icon Style: [1] > Show icon only: unchecked > Set the green check icon to appear when value is greater than 80.

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

A3 Symbols (Circled)

B3 Arrows

C4 Ratings

D5 Quarters

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting an icon set without a check mark.

Forgetting to uncheck 'Show icon only' to see values and icons.

The '3 Symbols (Circled)' icon set includes a green check icon suitable for indicating values above a threshold.

3fill in blank

hard

Fix the error in the formula to apply icon sets to range C1:C10 where the icons should be based on percentiles.

Excel

Select C1:C10 > Conditional Formatting > Icon Sets > More Rules > Change 'Type' for each icon to [1] > Set values to 0, 50, 90.

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

APercent

BPercentile

CNumber

DFormula

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Percent' instead of 'Percentile' which changes the meaning.

Leaving 'Type' as 'Number' which uses absolute values.

To base icon sets on percentiles, the 'Type' must be set to 'Percentile' for each icon threshold.

4fill in blank

hard

Fill both blanks to create a formula that applies a 3-icon set to range D1:D20, showing only icons, and sets the middle icon to appear when values are between 40 and 70.

Excel

Select D1:D20 > Conditional Formatting > Icon Sets > More Rules > Icon Style: [1] > Show Icon Only: [2] > Set middle icon value to 40 and upper icon value to 70.

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

A3 Traffic Lights (Unrimmed)

B3 Arrows (Colored)

CTRUE

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing an icon set without clear color distinctions.

Setting 'Show Icon Only' to FALSE when only icons are desired.

The '3 Traffic Lights (Unrimmed)' icon set is suitable for showing ranges with colors. Setting 'Show Icon Only' to TRUE hides the cell values, showing only icons.

5fill in blank

hard

Fill all three blanks to create a formula that applies a 5-icon set to range E1:E15, shows icons and values, and sets the lowest icon to appear below 20, the middle icon at 50, and the highest icon above 80.

Excel

Select E1:E15 > Conditional Formatting > Icon Sets > More Rules > Icon Style: [1] > Show Icon Only: [2] > Set icon values to 20, 50, 80 with types [3].

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

A5 Boxes

BFALSE

CNumber

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'Show Icon Only' as TRUE which hides values.

Using 'Percent' or 'Percentile' instead of 'Number' for types.

The '5 Boxes' icon set provides five distinct icons. Setting 'Show Icon Only' to FALSE shows both icons and values. Using 'Number' type means the icon thresholds are based on actual numbers.