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.

Practice

1. What is the main purpose of using Color scales in Google Sheets?

easy

A. To visually highlight data patterns by coloring cells based on their values

B. To sort data alphabetically

C. To create charts automatically

D. To lock cells from editing

Solution

1. Step 1: Understand what color scales do

   Color scales apply colors to cells depending on their values, making patterns easy to see.

2. Step 2: Compare options with this purpose

   Only To visually highlight data patterns by coloring cells based on their values describes coloring cells based on values to highlight patterns.

3. Final Answer:

   To visually highlight data patterns by coloring cells based on their values -> Option A

4. Quick Check:

   Color scales = Highlight data patterns [OK]

Hint: Color scales color cells by value to show patterns fast [OK]

Common Mistakes:

• Confusing color scales with sorting or charting
• Thinking color scales lock cells
• Assuming color scales create formulas

2. Which menu path correctly applies a color scale in Google Sheets?

easy

A. Tools > Protect sheet > Color scale

B. Data > Sort range > Color scale

C. Insert > Chart > Color scale

D. Format > Conditional formatting > Color scale

Solution

1. Step 1: Recall where color scales are applied

   Color scales are set under Format menu, inside Conditional formatting.

2. Step 2: Match the correct menu path

   Format > Conditional formatting > Color scale shows the correct path: Format > Conditional formatting > Color scale.

3. Final Answer:

   Format > Conditional formatting > Color scale -> Option D

4. Quick Check:

   Color scale path = Format > Conditional formatting [OK]

Hint: Color scales are under Format > Conditional formatting [OK]

Common Mistakes:

• Looking for color scales under Data or Insert menus
• Confusing color scales with sorting or chart options
• Trying to find color scales in Tools menu

3. You apply a color scale with blue for low values, white for middle, and red for high values on a range with numbers 10, 50, 90. Which cell will be colored red?

medium

A. Cell with 90

B. Cell with 50

C. Cell with 10

D. Cell with 50 and 90

Solution

1. Step 1: Understand color scale color assignments

   Blue is for low values, white for middle, red for high values.

2. Step 2: Identify the highest value in the range

   The highest number is 90, so it gets the red color.

3. Final Answer:

   Cell with 90 -> Option A

4. Quick Check:

   Highest value = red color [OK]

Hint: Highest value gets the high color in color scales [OK]

Common Mistakes:

• Choosing middle value for red color
• Assuming multiple cells get the same high color
• Mixing up low and high colors

4. You tried to apply a color scale but all cells show the same color. What is the most likely reason?

medium

A. Color scale only works on text, not numbers

B. You forgot to select the cells before applying

C. All cells have the exact same value

D. You applied a filter instead of color scale

Solution

1. Step 1: Understand how color scales assign colors

   Color scales color cells based on value differences; if all values are the same, colors are identical.

2. Step 2: Analyze the given options

   All cells have the exact same value explains why all cells have the same color: identical values.

3. Final Answer:

   All cells have the exact same value -> Option C

4. Quick Check:

   Same values = same color [OK]

Hint: Color scales need different values to show color differences [OK]

Common Mistakes:

• Thinking color scales work on text
• Not selecting cells before applying color scale
• Confusing filters with color scales

5. You want to highlight sales data from 0 to 1000 with a green to yellow to red color scale. Which setup correctly assigns colors for low, midpoint, and high values?

hard

A. Low: Red, Midpoint: Yellow, High: Green

B. Low: Green, Midpoint: Yellow, High: Red

C. Low: Yellow, Midpoint: Green, High: Red

D. Low: Red, Midpoint: Green, High: Yellow

Solution

1. Step 1: Understand color meanings for data ranges

   Green usually means good/low risk, yellow is middle, red means high/warning.

2. Step 2: Match colors to sales values from low to high

   Low sales get green, middle sales yellow, high sales red to show increasing alert.

3. Final Answer:

   Low: Green, Midpoint: Yellow, High: Red -> Option B

4. Quick Check:

   Green low, yellow mid, red high = correct scale [OK]

Hint: Green means low, red means high in color scales [OK]

Common Mistakes:

• Reversing red and green colors
• Assigning yellow to low values
• Mixing up midpoint colors