Managing multiple rules in Excel - Dashboard Guide

Dashboard Mode - Managing multiple rules

Goal

See how to use multiple rules to highlight sales performance and stock status in a product list.

Sample Data

Product	Sales	Stock
Apples	150	30
Bananas	80	5
Cherries	200	0
Dates	50	20
Elderberries	120	15
Figs	90	0
Grapes	170	25

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(B2:B8)
  Shows total sales of all products.
• KPI Card: Products Out of Stock
  Formula: =COUNTIF(C2:C8, 0)
  Counts how many products have zero stock.
• Table with Conditional Formatting Rules
  Rules applied to Sales and Stock columns:
  1. Sales >= 150: Fill green (good sales)
  2. Sales < 100: Fill red (low sales)
  3. Stock = 0: Fill orange (out of stock)
  4. Stock <= 10 and > 0: Fill yellow (low stock)
  These rules help quickly see product status.

Dashboard Layout

+----------------------+-------------------------+
| Total Sales: 860     | Products Out of Stock: 2 |
+----------------------+-------------------------+
| Product     | Sales | Stock                      |
|-----------------------------------------------|
| Apples      | 150   | 30                        |
| Bananas     | 80    | 5                         |
| Cherries    | 200   | 0                         |
| Dates       | 50    | 20                        |
| Elderberries| 120   | 15                        |
| Figs        | 90    | 0                         |
| Grapes      | 170   | 25                        |
+-----------------------------------------------+

Interactivity

Add a filter for Sales >= 100. When applied, the table and KPI cards update to show only products with sales 100 or more. The total sales and out of stock count recalculate accordingly.

Self Check

If you add a filter to show only products with Stock <= 10, which components update and how?

• The table shows only products with stock 10 or less.
• Total Sales KPI updates to sum sales of filtered products.
• Products Out of Stock KPI updates to count only filtered products with zero stock.

Key Result

Dashboard shows total sales, out of stock count, and highlights sales and stock status with multiple rules.