Color scales in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of Color Scale Based on Values

You apply a 3-color scale conditional formatting in Google Sheets to cells A1:A5 with these values: 10, 20, 30, 40, 50.

The scale uses red for the minimum, yellow for the midpoint, and green for the maximum.

What color will cell A3 (value 30) display?

A Yellow

B Red

C Green

D Orange

💡 Hint

The midpoint color is applied to the middle value in the range.

With a 3-color scale from red (min) to yellow (mid) to green (max), the middle value (30) gets the midpoint color, yellow.

❓ Function Choice

intermediate

Choosing the Right Function for Color Scale Thresholds

You want to set a 2-color scale in Google Sheets where the color changes at the average value of a range.

Which function should you use to calculate the threshold for the color scale?

A AVERAGE(range)

B SUM(range)

C MAX(range)

D COUNT(range)

💡 Hint

The average gives the middle value of the data.

AVERAGE(range) calculates the mean, which is the correct threshold for a color scale midpoint.

🎯 Scenario

advanced

Applying Color Scale to Highlight Top 10% Values

You have sales data in cells B2:B100. You want to apply a color scale that highlights the top 10% highest sales in green and the rest in red.

Which conditional formatting setting will achieve this?

A Set color scale with min as red, max as green, and midpoint set to 'Percentile' 10%

B Set color scale with min as red, max as green, and min set to 'Percentile' 90%

C Set color scale with min as green, max as red, and max set to 'Percentile' 10%

D Set color scale with min as red, max as green, and max set to 'Percentile' 90%

💡 Hint

The top 10% means values above the 90th percentile.

Setting max to the 90th percentile with green highlights the top 10% in green, and min as red colors the lower values red.

❓ data_analysis

advanced

Interpreting Color Scale Results on Mixed Data

You apply a 3-color scale to a range with values: -100, -50, 0, 50, 100.

The scale uses blue for minimum, white for midpoint, and red for maximum.

What color will the cell with value 0 display?

A Blue

B White

C Red

D Purple

💡 Hint

The midpoint color applies to the middle value between min and max.

0 is the midpoint between -100 (min) and 100 (max), so it gets the midpoint color white.

🧠 Conceptual

expert

Understanding Color Scale Behavior with Non-Numeric Data

You apply a 2-color scale conditional formatting to a column containing numbers and some text values.

What happens to the cells with text values?

A Text cells cause an error and stop the formatting

B Text cells are colored with the minimum color of the scale

C Text cells are not colored by the scale and remain unchanged

D Text cells are colored with the maximum color of the scale

💡 Hint

Color scales apply only to numeric values.

Cells with text are ignored by color scales and keep their original formatting.