Formula-based rules in Excel - Dashboard Guide

Dashboard Mode - Formula-based rules

Dashboard Goal

Track monthly sales performance and highlight if sales meet or exceed targets using formula-based rules.

Sample Data

Month	Sales	Target
January	1200	1000
February	950	1000
March	1100	1000
April	1300	1200
May	1250	1200
June	1150	1200

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(B2:B7)
  Shows total sales from January to June.
• KPI Card: Total Target
  Formula: =SUM(C2:C7)
  Shows total target sales for the same period.
• Table: Sales vs Target with Status
  Formula for Status in D2:
  =IF(B2>=C2, "Met", "Below")
  Drag down to D7.
  Shows if monthly sales met or fell below the target.
• Chart: Sales and Target Comparison
  Bar chart comparing sales and targets for each month.

Dashboard Layout

+----------------------+----------------------+
|   Total Sales (KPI)  |   Total Target (KPI) |
+----------------------+----------------------+
|                                      Chart |
|                              Sales vs Target |
|                                      Table |
+---------------------------------------------+

Interactivity

Add a filter for months (e.g., select January to March). When applied:

• Total Sales and Total Target KPIs update to sum only selected months.
• Sales vs Target table shows only selected months with updated status.
• Chart updates to display data for selected months only.

Self Check

If you add a filter to show only months where sales met or exceeded targets, which rows remain in the table?

Answer: Rows for January, March, April, and May remain because their sales are equal or above targets.

Key Result

Dashboard shows monthly sales vs targets with status and total KPIs using formula-based rules.