Color scales in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick visual way to see which products have the highest and lowest sales in the last quarter.

📊 Data: You have a table listing products and their total sales for the last quarter.

🎯 Deliverable: Create a color scale in the sales column that colors the highest sales in green and the lowest sales in red, with a gradient for values in between.

Sample Data

Product	Sales
Apples	1200
Bananas	850
Cherries	400
Dates	950
Elderberries	300
Figs	700
Grapes	1100
Honeydew	650

1

Step 1: Select the sales data cells from B2 to B9.

No formula needed; just select cells B2:B9.

Expected Result

Cells B2 to B9 are highlighted for formatting.

2

Step 2: Open the Format menu and choose Conditional formatting.

No formula; use menu navigation: Format > Conditional formatting.

Expected Result

Conditional format rules panel opens on the right.

3

Step 3: In the Conditional format rules panel, select the Color scale tab.

No formula; select Color scale tab.

Expected Result

Color scale options appear for customization.

4

Step 4: Set the Minpoint color to red, Midpoint color to yellow, and Maxpoint color to green.

Minpoint: Number, Value: 300, Color: Red (#ff0000) Midpoint: Number, Value: 750, Color: Yellow (#ffff00) Maxpoint: Number, Value: 1200, Color: Green (#00ff00)

Expected Result

Sales cells color from red (lowest) through yellow (middle) to green (highest).

5

Step 5: Click Done to apply the color scale formatting.

No formula; click Done button.

Expected Result

Sales column cells show a gradient color scale from red to green based on sales values.

Final Result

Product     Sales
-------------------
Apples     ██████████ (green)
Bananas    ██████ (yellow-green)
Cherries   ███ (orange-red)
Dates      ███████ (yellow)
Elderberries██ (red)
Figs       █████ (yellow-orange)
Grapes     █████████ (green-yellow)
Honeydew   ████ (orange)

✓Apples and Grapes have the highest sales, shown in green.

✓Elderberries and Cherries have the lowest sales, shown in red.

✓Other products have sales in between, shown with gradient colors from red to green.

Bonus Challenge

Add a second color scale to highlight products with sales above 1000 in blue.

Show Hint

Use a custom formula in conditional formatting like =B2>1000 and set the fill color to blue.