Color scales in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Applying a 3-color scale to highlight sales performance

You have a list of monthly sales figures in column B (B2:B13). You want to apply a 3-color scale where the lowest sales are red, the midpoint is yellow, and the highest sales are green. Which of the following steps correctly applies this color scale in Excel?

A Select B2:B13, go to Home > Conditional Formatting > Color Scales > Red - Yellow - Green Color Scale

B Select B2:B13, go to Home > Conditional Formatting > Icon Sets > 3 Arrows (Colored)

C Select B2:B13, go to Home > Conditional Formatting > Data Bars > Gradient Fill

D Select B2:B13, go to Home > Conditional Formatting > Color Scales > Green - Yellow - Red Color Scale

💡 Hint

Think about the order of colors from low to high values.

The correct 3-color scale for lowest to highest values with red, yellow, and green is the 'Red - Yellow - Green Color Scale'. Option A applies this correctly. Option A reverses the colors. Options B and C use different conditional formatting types.

📊 Formula Result

intermediate

Result of applying a 2-color scale with custom colors

You apply a 2-color scale to a range of numbers from 1 to 100, setting the minimum color to blue and the maximum color to orange. What color will a cell with the value 50 approximately show?

A Pure orange

B A color halfway between blue and orange, like a purple-brown shade

C Pure blue

D White

💡 Hint

Think about how color scales blend colors between minimum and maximum values.

A 2-color scale blends colors between the minimum and maximum. Since 50 is halfway between 1 and 100, the color will be a mix between blue and orange, resulting in a brownish or purple-brown shade.

❓ Function Choice

advanced

Choosing the correct formula to set a midpoint for a 3-color scale

You want to apply a 3-color scale to a range of numbers where the midpoint color corresponds to the average of the range. Which formula should you use in the 'Midpoint' setting of the color scale?

A Use the formula =AVERAGE(B2:B20)

B Use the formula =MEDIAN(B2:B20)

C Use the formula =MAX(B2:B20)/2

D Use the formula =MIN(B2:B20)

💡 Hint

The midpoint should represent the average value of the data.

The midpoint in a 3-color scale is best set to the average value to balance colors around the center. Using =AVERAGE(B2:B20) calculates this correctly. Median or half the max value may not represent the true center, and minimum is the lowest value, not midpoint.

❓ data_analysis

advanced

Interpreting color scale results in a sales report

You applied a 3-color scale to a sales column with red for low, yellow for midpoint, and green for high. You notice some cells with sales of 500 are yellow, while others with 480 are green. What is the most likely reason?

A The color scale midpoint is set incorrectly, causing inconsistent colors

B The cells with 480 have manual green fill applied, overriding the scale

C The data range includes hidden rows affecting the scale calculation

D The color scale uses percentiles, so 480 might be above the 50th percentile and 500 below it

💡 Hint

Think about how Excel calculates color scales by default.

By default, Excel color scales use percentiles, not exact values. So a value of 480 could be in a higher percentile than 500 if the data distribution is uneven, causing 480 to appear greener than 500.

🧠 Conceptual

expert

Understanding limitations of color scales with non-numeric data

You try to apply a 3-color scale to a column containing dates and text values mixed together. What will happen when you apply the color scale?

A Excel will produce an error and refuse to apply the color scale

B Excel will convert text to zero and color all text cells with the minimum color

C Only the numeric date values will be colored; text cells will not get color formatting

D All cells will be colored randomly regardless of content

💡 Hint

Consider how Excel treats text in numeric conditional formatting.

Color scales work only on numeric values. Dates are stored as numbers, so they get colored. Text cells are ignored and remain uncolored.