Color scales in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to apply a 3-color scale conditional formatting in Excel.

Excel

Range("A1:A10").FormatConditions.AddColorScale([1])

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

A3

B2

C1

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 2 instead of 3 for a 3-color scale.

Using 4 which is not valid for color scales.

Excel uses 3 for a 3-color scale in conditional formatting.

2fill in blank

medium

Complete the code to set the minimum color to red in a 3-color scale.

Excel

With Range("B1:B10").FormatConditions(1).ColorScaleCriteria(1)
 .Type = xlConditionValueLowestValue
 .FormatColor.Color = [1]
End With

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

ARGB(0, 0, 255)

BRGB(0, 255, 0)

CRGB(255, 0, 0)

DRGB(255, 255, 0)

Attempts:

3 left

💡 Hint

Common Mistakes

Using green or blue RGB values instead of red.

Using yellow which is not red.

Red color in RGB is (255, 0, 0), which sets the minimum color to red.

3fill in blank

hard

Fix the error in setting the midpoint color to yellow in a 3-color scale.

Excel

With Range("C1:C10").FormatConditions(1).ColorScaleCriteria(2)
 .Type = xlConditionValuePercentile
 .Value = 50
 .FormatColor.Color = [1]
End With

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

ARGB(255, 255, 0)

BRGB(0, 0, 255)

CRGB(0, 255, 0)

DRGB(255, 0, 0)

Attempts:

3 left

💡 Hint

Common Mistakes

Using red or green instead of yellow.

Using blue which is not yellow.

Yellow color is RGB(255, 255, 0), which is correct for midpoint color.

4fill in blank

hard

Fill both blanks to set the maximum color to green and the type to highest value.

Excel

With Range("D1:D10").FormatConditions(1).ColorScaleCriteria([1])
 .Type = [2]
 .FormatColor.Color = RGB(0, 255, 0)
End With

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

A3

BxlConditionValueHighestValue

CxlConditionValueLowestValue

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using index 1 or 2 instead of 3 for maximum color.

Using lowest value type instead of highest.

The maximum color is the third color in the scale, so index 3. The type for maximum is xlConditionValueHighestValue.

5fill in blank

hard

Fill all three blanks to create a 3-color scale with red minimum, yellow midpoint, and green maximum colors.

Excel

With Range("E1:E10").FormatConditions.AddColorScale([1])
 With .ColorScaleCriteria([2])
  .Type = xlConditionValueLowestValue
  .FormatColor.Color = RGB(255, 0, 0)
 End With
 With .ColorScaleCriteria([3])
  .Type = xlConditionValuePercentile
  .Value = 50
  .FormatColor.Color = RGB(255, 255, 0)
 End With

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

A3

B1

C2

DxlConditionValueHighestValue

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong indices for color criteria.

Using wrong number of colors in AddColorScale.

Use 3 for the number of colors. The minimum color is index 1, midpoint is index 2.

Practice

1. What is the main purpose of using Color Scales in Excel's Conditional Formatting?

easy

A. To create charts automatically

B. To sort data alphabetically

C. To color cells based on their values to show data patterns

D. To lock cells from editing

Solution

1. Step 1: Understand Conditional Formatting

   Conditional Formatting changes cell appearance based on rules.

2. Step 2: Identify Color Scales function

   Color Scales color cells based on their numeric values to highlight patterns.

3. Final Answer:

   To color cells based on their values to show data patterns -> Option C

4. Quick Check:

   Color Scales = Highlight data patterns [OK]

Hint: Color scales show value differences with colors [OK]

Common Mistakes:

• Confusing color scales with sorting
• Thinking color scales create charts
• Assuming color scales lock cells

2. Which of the following is the correct way to apply a 3-color scale in Excel?

easy

A. Select cells -> Data tab -> Sort -> Color Scales

B. Select cells -> Insert tab -> Chart -> Color Scales

C. Select cells -> Review tab -> Protect Sheet -> Color Scales

D. Select cells -> Home tab -> Conditional Formatting -> Color Scales -> Choose 3-color scale

Solution

1. Step 1: Locate Conditional Formatting

   Conditional Formatting is under the Home tab in Excel.

2. Step 2: Apply Color Scales

   Within Conditional Formatting, choose Color Scales and pick a 3-color scale option.

3. Final Answer:

   Select cells -> Home tab -> Conditional Formatting -> Color Scales -> Choose 3-color scale -> Option D

4. Quick Check:

   Home -> Conditional Formatting -> Color Scales = Correct path [OK]

Hint: Use Home tab for Conditional Formatting [OK]

Common Mistakes:

• Looking for Color Scales in Insert or Data tabs
• Trying to apply Color Scales via sorting
• Confusing sheet protection with formatting

3. You apply a 2-color scale to a range of numbers from 10 to 100. What color will the cell with value 10 have if the scale is green (low) to red (high)?

medium

A. Green

B. Red

C. Yellow

D. No color

Solution

1. Step 1: Understand 2-color scale meaning

   In a green (low) to red (high) scale, the lowest value gets green color.

2. Step 2: Identify the lowest value color

   Value 10 is the lowest in the range, so it will be green.

3. Final Answer:

   Green -> Option A

4. Quick Check:

   Lowest value = green [OK]

Hint: Lowest value gets low color in scale [OK]

Common Mistakes:

• Assuming lowest value gets red
• Thinking middle colors appear for extremes
• Ignoring the color order in scale

4. You applied a 3-color scale but all cells show the same color. What is the most likely reason?

medium

A. You forgot to select the cells

B. All cells have the same value

C. Color scales only work on text

D. You applied a 2-color scale instead

Solution

1. Step 1: Check cell values

   If all cells have the same value, color scales cannot show gradient differences.

2. Step 2: Understand color scale behavior

   Color scales color based on value differences; no difference means one color.

3. Final Answer:

   All cells have the same value -> Option B

4. Quick Check:

   Same values = same color [OK]

Hint: Color scales need value differences [OK]

Common Mistakes:

• Thinking color scales work on text
• Not selecting cells before applying
• Confusing 2-color and 3-color scales

5. You want to highlight sales data where values below 500 are red, values between 500 and 1000 are yellow, and above 1000 are green. How do you set up a 3-color scale to achieve this?

hard

A. Set minimum to red at 0, midpoint yellow at 750, maximum green at 1500

B. Set minimum to green at 0, midpoint yellow at 750, maximum red at 1500

C. Set minimum to red at 500, midpoint yellow at 1000, maximum green at 1500

D. Set minimum to red at 0, midpoint yellow at 500, maximum green at 1000

Solution

1. Step 1: Define scale points for sales ranges

   Values below 500 should be red (low), between 500-1000 yellow (mid), above 1000 green (high).

2. Step 2: Choose scale values matching ranges

   Set minimum at 0 (red), midpoint at 750 (yellow between 500 and 1000), maximum at 1500 (green above 1000).

3. Final Answer:

   Set minimum to red at 0, midpoint yellow at 750, maximum green at 1500 -> Option A

4. Quick Check:

   Color scale points match value ranges [OK]

Hint: Match scale points to your value thresholds [OK]

Common Mistakes:

• Reversing color order
• Setting midpoint outside value range
• Using wrong min or max values