Why rules-based formatting highlights patterns in Excel - Dashboard Impact

Dashboard Mode - Why rules-based formatting highlights patterns

Dashboard Goal

Help users quickly see sales trends and patterns by highlighting high and low sales values using rules-based formatting.

Sample Data

Region	Month	Sales
North	Jan	120
North	Feb	150
North	Mar	90
South	Jan	200
South	Feb	180
South	Mar	220
East	Jan	130
East	Feb	170

Dashboard Components

• KPI Card - Total Sales: Shows total sales for all regions and months.
  Formula: =SUM(C2:C9)
  Result: 1260
• Table with Rules-Based Formatting: The sales column (C2:C9) is conditionally formatted:
  • Cells with sales >= 200 are highlighted green (high sales).
  • Cells with sales < 100 are highlighted red (low sales).
  This helps spot strong and weak sales quickly.
• Average Sales per Region: Shows average sales for each region using formulas:
  North: =AVERAGEIFS(C2:C9,A2:A9,"North") -> 120
  South: =AVERAGEIFS(C2:C9,A2:A9,"South") -> 200
  East: =AVERAGEIFS(C2:C9,A2:A9,"East") -> 150

Dashboard Layout

+----------------------+-----------------------+
|      Total Sales      |  Average Sales by Reg  |
|       (KPI Card)      |       (Table)          |
+----------------------+-----------------------+
|        Sales Data Table with Rules-Based Formatting        |
+------------------------------------------------------------+

Interactivity

Adding a filter for Region will update the Total Sales KPI, the Average Sales by Region table, and the Sales Data Table to show only data for the selected region. The rules-based formatting will then highlight high and low sales within that filtered data, helping users see patterns specific to that region.

Self Check

If you add a filter for Region = South, which components update and how?

• Total Sales KPI: Updates to sum only South region sales (200 + 180 + 220 = 600).
• Average Sales by Region: Shows only South region average (200).
• Sales Data Table: Shows only South rows with sales values highlighted by rules-based formatting (green for sales ≥ 200, red for sales < 100, none in this case).

Key Result

Dashboard highlights sales patterns using rules-based formatting and shows total and average sales by region.