Color scales in Excel - 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 are selling best and which are selling poorly this month.

📊 Data: You have a table with product names and their sales numbers for the current month.

🎯 Deliverable: Create a sales report where the sales numbers are colored with a color scale to highlight high and low sales.

Sample Data

Product	Sales
Apples	120
Bananas	75
Cherries	200
Dates	50
Elderberries	180
Figs	90
Grapes	160
Honeydew	130

1

Step 1: Select the sales numbers in the Sales column (cells B2 to B9).

No formula needed; just select cells B2:B9.

Expected Result

Cells B2 to B9 are highlighted for formatting.

2

Step 2: Apply a color scale to the selected sales numbers to show low sales in red and high sales in green.

Go to Home tab > Conditional Formatting > Color Scales > Select the Green - Yellow - Red color scale (green for high, red for low).

Expected Result

Sales numbers are colored from red (lowest sales) to green (highest sales) with yellow in the middle.

3

Step 3: Verify that the highest sales number (200 for Cherries) is green and the lowest sales number (50 for Dates) is red.

No formula; visually check the colors.

Expected Result

Cherries sales cell is green, Dates sales cell is red, others are colored accordingly.

Final Result

Product     Sales
Apples      120 (yellow-green)
Bananas     75  (orange-red)
Cherries    200 (green)
Dates       50  (red)
Elderberries180 (yellow-green)
Figs        90  (orange)
Grapes      160 (yellow-green)
Honeydew    130 (yellow-green)

✓Cherries have the highest sales, clearly shown in green.

✓Dates have the lowest sales, clearly shown in red.

✓Color scale helps quickly identify sales performance visually.

Bonus Challenge

Add a second color scale to the Product column to highlight products starting with letters A-M in blue shades and N-Z in purple shades.

Show Hint

Use a custom conditional formatting rule with a formula like =LEFT(A2,1)<="M" and apply a blue color scale; then add another rule for letters N-Z with a purple scale.