Color scales in Google Sheets - Dashboard Guide

Dashboard Mode - Color scales

Dashboard Goal

Show how sales performance varies across products using color scales to highlight low to high values.

Sample Data

Product	Sales Q1	Sales Q2	Sales Q3
Apples	120	150	130
Bananas	80	90	100
Cherries	200	210	190
Dates	50	60	55
Elderberries	170	160	180

Dashboard Components

• Sales Table with Color Scales: The sales data table uses conditional formatting with a 3-color scale from red (lowest sales) to yellow (middle) to green (highest sales) applied to the Sales Q1, Q2, and Q3 columns. This visually shows which quarters had low or high sales per product.
• Total Sales per Product: In column E, a formula sums sales across Q1 to Q3 for each product:
  =SUM(B2:D2)
  This total column also uses a 2-color scale from light orange (low total) to dark orange (high total).
• Average Sales per Quarter: Below the table, a row calculates average sales for each quarter:
  =SUBTOTAL(101,B2:B6) for Q1, similarly for Q2 and Q3.
  This row uses a 3-color scale to highlight which quarter had higher average sales.

Dashboard Layout

+---------------------------------------+
|           Sales Table with Color Scales           |
|  Product | Q1 | Q2 | Q3 | Total Sales  |
|---------------------------------------|
| Apples   |    |    |    |             |
| Bananas  |    |    |    |             |
| Cherries |    |    |    |             |
| Dates    |    |    |    |             |
| Elderberries |    |    |    |             |
|---------------------------------------|
| Average  |    |    |    |             |
+---------------------------------------+

Interactivity

Users can apply filters on the Product column to show only selected products. When filtered, the average sales row updates dynamically to reflect the sales range of the visible data only. The color scales provide consistent highlights based on the full dataset. This helps users focus on specific products and see their relative sales performance with color highlights.

Self Check

If you add a filter to show only "Apples" and "Cherries", which components update?

• The sales table updates to show only those two products.
• The Average Sales row recalculates based on the filtered sales values, so values adjust to the new averages.
• The Total Sales column and Average Sales row update to reflect only the visible products.

Key Result

Sales data table with color scales highlights low to high sales per product and quarter, with totals and averages dynamically updating on filters.