Managing rule priority in Google Sheets - Dashboard Guide

Dashboard Mode - Managing rule priority

Goal

Understand how to manage rule priority in conditional formatting to highlight sales data based on multiple conditions.

Sample Data

Product	Region	Sales
Apples	East	120
Bananas	West	80
Cherries	East	150
Dates	West	200
Elderberries	East	90
Figs	West	110

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(C2:C7)
  Shows total sales across all products and regions.
• Conditional Formatting Rules:
  1. Rule 1 (High Sales): Format cells in Sales column with sales >= 150 with green fill.
  2. Rule 2 (Medium Sales): Format cells with sales between 100 and 149 with yellow fill.
  3. Rule 3 (Low Sales): Format cells with sales < 100 with red fill.
  
  Rule Priority: Rule 1 has highest priority, then Rule 2, then Rule 3. This means if a sale is 150 or more, it gets green fill even if it also meets other rules.
• Filtered Table: Shows data filtered by Region using a dropdown filter in cell E1.
  Formula for filtered sales total:
  =SUM(FILTER(C2:C7, B2:B7=E1))
  This updates total sales based on selected region.

Dashboard Layout

+----------------------+--------------------+
| Total Sales (KPI)    | Region Filter (E1) |
+----------------------+--------------------+
|                      Filtered Sales Total      |
+-----------------------------------------------+
|                Sales Data Table                 |
+-----------------------------------------------+

Interactivity

The dropdown in cell E1 lets you select a region (East or West). When you pick a region:

• The filtered sales total updates to show sales only for that region.
• The sales data table remains visible but you can add a filter view to show only that region if desired.
• Conditional formatting colors remain based on sales values regardless of region.

Self Check

If you set the region filter in E1 to "East", which components update?

• The filtered sales total updates to sum only East region sales.
• The total sales KPI does NOT change (it shows all sales).
• The conditional formatting colors in the sales column remain based on sales values.

Key Result

Dashboard shows total sales, sales colored by priority rules, and filtered sales by region.