Color scales in Excel - Dashboard Guide

Dashboard Mode - Color scales

Dashboard Goal

This dashboard helps you quickly see which sales amounts are low, medium, or high by using color scales. It answers the question: "Which sales values stand out as small, average, or large?" This makes it easy to spot trends and focus on important numbers.

Sample Data

Product	Sales
Apples	120
Bananas	300
Cherries	180
Dates	90
Elderberries	250
Figs	60
Grapes	200

Dashboard Components

• Sales Table with Color Scale: The sales amounts in column B (cells B2:B8) have a color scale applied. Low sales numbers show in red, medium in yellow, and high in green. This uses Excel's built-in conditional formatting color scale feature.
• Average Sales KPI: Cell D2 shows the average sales calculated by the formula =SUBTOTAL(1,B2:B8). This gives a quick number to compare individual sales against.
• Max Sales KPI: Cell D3 shows the highest sales value with =SUBTOTAL(4,B2:B8). This highlights the top performer.
• Min Sales KPI: Cell D4 shows the lowest sales value with =SUBTOTAL(5,B2:B8). This points out the smallest sales.

Dashboard Layout

+----------------------+-----------------+
|      Sales Table     |  KPI Summary    |
|  (Products & Sales)  |  Avg: [D2]      |
|  (B2:B8 with colors) |  Max: [D3]      |
|                      |  Min: [D4]      |
+----------------------+-----------------+

Interactivity

You can add a filter (like a dropdown or slicer) to select specific products or sales ranges. When you filter the products, the color scale updates to show colors only for the visible sales values. The average, max, and min KPIs also update to reflect the filtered data. This helps you focus on parts of the data you want to analyze.

Self Check

If you add a filter to show only products with sales above 150, which components update?

• The sales table colors update to show only the filtered sales with the color scale adjusted to the new range.
• The average sales KPI recalculates to the average of the filtered sales.
• The max and min sales KPIs update to the highest and lowest sales in the filtered list.

Key Result

Dashboard showing sales data with color scales and KPIs for average, max, and min sales.