Notification rules in Google Sheets - Dashboard Guide

Dashboard Mode - Notification rules

Goal

Track sales data and get notified automatically when sales exceed targets or when new data is added.

Sample Data

Date	Salesperson	Region	Sales Amount	Target
2024-06-01	Alice	East	1200	1000
2024-06-02	Bob	West	950	1000
2024-06-03	Charlie	East	1100	1000
2024-06-04	Diana	South	1300	1200
2024-06-05	Eva	West	1050	1000

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUBTOTAL(109,D2:D6)
  Shows total sales amount from all salespeople.
• KPI Card: Number of Sales Above Target
  Formula: =COUNTIF(D2:D6, ">" & E2:E6) (entered as array formula or use =SUMPRODUCT(--(SUBTOTAL(103,OFFSET(D2:D6,ROW(D2:D6)-ROW(D2),0,1))=1),--(D2:D6>E2:E6))))
  Counts how many sales exceeded their targets.
• Table: Sales Status
  Formula in new column F2: =IF(D2>E2, "Above Target", "Below Target") copied down
  Shows if each sale is above or below target.
• Notification Rule Setup
  Use Google Sheets notification rules:
  - Notify when changes are made
  - Notify when a user submits a form (if data is form-based)
  - Notify when a specific cell or range changes (e.g., sales amount column)
  This is set up via Google Sheets menu: Tools > Notification rules.

Dashboard Layout

+----------------------+-----------------------------+
| Total Sales          | Number of Sales Above Target |
|      5600            |             4               |
+----------------------+-----------------------------+
| Sales Data Table (with Status column)           |
| Date | Salesperson | Region | Sales | Target | Status |
|--------------------------------------------------|
| 2024-06-01 | Alice  | East   | 1200  | 1000  | Above Target |
| 2024-06-02 | Bob    | West   | 950   | 1000  | Below Target |
| 2024-06-03 | Charlie| East   | 1100  | 1000  | Above Target |
| 2024-06-04 | Diana  | South  | 1300  | 1200  | Above Target |
| 2024-06-05 | Eva    | West   | 1050  | 1000  | Above Target |
+--------------------------------------------------+
| Notification Rules Setup Instructions             |
+--------------------------------------------------+

Interactivity

Filters can be added to the table to view sales by region or salesperson. When filtered, the KPI cards update automatically to reflect totals and counts for the filtered data.

Notification rules alert users by email when data changes, such as new sales added or sales amounts updated.

Self Check

If you add a filter to show only the East region, which components update?

• The Total Sales KPI updates to sum only East region sales (1200 + 1100 = 2300).
• The Number of Sales Above Target updates to count only East region sales above target (2).
• The Sales Status table shows only East region rows.
• Notification rules remain active and notify on any changes regardless of filter.

Key Result

Dashboard shows total sales, count of sales above target, sales status per entry, and explains notification rules for data changes.