Color scales in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to apply a 3-color scale conditional formatting rule in Google Sheets.

Google Sheets

ApplyConditionalFormat(range, {colorScale: {minpoint: {type: 'min'}, midpoint: {type: 'percentile', value: 50}, maxpoint: {type: 'max'}, colors: ['red', [1], 'green']}})

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

Apurple

Bblue

Corange

Dyellow

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a color that does not visually represent a middle value well.

Using a color not supported by Google Sheets color scale.

The middle color in a 3-color scale is often yellow to show mid values between red (low) and green (high).

2fill in blank

medium

Complete the code to set the minimum point type to 'number' with value 0 in a color scale.

Google Sheets

ApplyConditionalFormat(range, {colorScale: {minpoint: {type: [1], value: 0}, midpoint: {type: 'percentile', value: 50}, maxpoint: {type: 'max'}, colors: ['red', 'yellow', 'green']}})

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

Anumber

Bmin

Cpercentile

Dmax

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'min' instead of 'number' which does not allow specifying a value.

Confusing 'percentile' with 'number'.

Setting minpoint type to 'number' allows specifying an exact numeric value for the minimum color scale point.

3fill in blank

hard

Fix the error in the color scale code by choosing the correct color format for the maxpoint color.

Google Sheets

ApplyConditionalFormat(range, {colorScale: {minpoint: {type: 'min'}, midpoint: {type: 'percentile', value: 50}, maxpoint: {type: 'max'}, colors: ['red', 'yellow', [1]]}})

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

Argb(0,255,0)

Bgreen

C#00FF00

Dgreenish

Attempts:

3 left

💡 Hint

Common Mistakes

Using non-standard color names like 'greenish'.

Using color names that may not be recognized in Google Sheets.

Google Sheets color scales accept hex color codes like '#00FF00' for green. Named colors like 'green' may not always work.

4fill in blank

hard

Fill both blanks to create a 2-color scale from blue to white.

Google Sheets

ApplyConditionalFormat(range, {colorScale: {minpoint: {type: [1], maxpoint: {type: [2], colors: ['blue', 'white']}})

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

Amin

Bmax

Cnumber

Dpercentile

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'number' or 'percentile' without specifying values.

Swapping min and max types.

For a 2-color scale, minpoint type is 'min' and maxpoint type is 'max' to cover the full range.

5fill in blank

hard

Fill all three blanks to create a 3-color scale with min as 0, midpoint at 50 percentile, and max as 100.

Google Sheets

ApplyConditionalFormat(range, {colorScale: {minpoint: {type: [1], value: 0}, midpoint: {type: [2], value: 50}, maxpoint: {type: [3], value: 100}, colors: ['red', 'yellow', 'green']}})

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

Anumber

Bpercentile

Dmax

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'max' type for maxpoint with a value specified.

Using 'number' type for midpoint instead of 'percentile'.

Minpoint and maxpoint use 'number' type with values 0 and 100. Midpoint uses 'percentile' type with value 50.

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