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.

Practice

1. What is the main purpose of notification rules in Google Sheets?

easy

A. To protect cells from editing

B. To automatically format cells based on values

C. To create charts from data

D. To send email alerts when changes happen in the sheet

Solution

1. Step 1: Understand notification rules function

   Notification rules are designed to alert users by email when changes occur in a Google Sheet.

2. Step 2: Compare options with this function

   Only To send email alerts when changes happen in the sheet describes sending email alerts on changes, matching the purpose of notification rules.

3. Final Answer:

   To send email alerts when changes happen in the sheet -> Option D

4. Quick Check:

   Notification rules = Email alerts on changes [OK]

Hint: Notification rules alert you by email on sheet changes [OK]

Common Mistakes:

• Confusing notification rules with formatting or protection features
• Thinking notification rules create charts
• Assuming notification rules prevent editing

2. Where do you find the option to set notification rules in Google Sheets?

easy

A. Under the 'Format' menu

B. Under the 'Data' menu

C. Under the 'Tools' menu

D. Under the 'Insert' menu

Solution

1. Step 1: Recall menu location for notification rules

   Notification rules are set up from the 'Tools' menu in Google Sheets.

2. Step 2: Verify other menu options

   'Data', 'Format', and 'Insert' menus do not contain notification rules settings.

3. Final Answer:

   Under the 'Tools' menu -> Option C

4. Quick Check:

   Notification rules location = Tools menu [OK]

Hint: Find notification rules under Tools menu [OK]

Common Mistakes:

• Looking under Data menu instead of Tools
• Confusing with Format or Insert menus
• Not checking the Tools menu at all

3. If you set a notification rule to email you 'right away' when any change is made, what happens?

medium

A. You get a daily summary email only

B. You get an email immediately after each change

C. You get no emails until you open the sheet

D. You get emails only when you manually refresh

Solution

1. Step 1: Understand 'right away' notification setting

   This setting sends an email immediately after any change is made in the sheet.

2. Step 2: Compare with other options

   Daily summary, no emails until you open the sheet, and emails only on manual refresh describe delayed or conditional emails, which do not match 'right away' behavior.

3. Final Answer:

   You get an email immediately after each change -> Option B

4. Quick Check:

   'Right away' = Immediate email alert [OK]

Hint: 'Right away' means immediate email after change [OK]

Common Mistakes:

• Confusing 'right away' with daily summaries
• Thinking emails require opening the sheet
• Assuming manual refresh triggers emails

4. You set a notification rule but never receive emails. What is a likely reason?

medium

A. You did not save the notification rule after creating it

B. You set the notification to 'right away' but no changes were made

C. Your email address is not linked to the Google account

D. Notification rules only work on weekends

Solution

1. Step 1: Check if notification rule was saved

   If you create a rule but do not save it, no emails will be sent.

2. Step 2: Evaluate other options

   You set the notification to 'right away' but no changes were made is possible but less likely if changes were made; Your email address is not linked to the Google account is incorrect because email is linked to Google account; Notification rules only work on weekends is false as notifications work any day.

3. Final Answer:

   You did not save the notification rule after creating it -> Option A

4. Quick Check:

   Unsaved rule = No emails sent [OK]

Hint: Always save notification rules after setting them [OK]

Common Mistakes:

• Assuming notifications work without saving
• Believing notifications only work on weekends
• Not checking if changes were made

5. You want to get notified only once a day about any changes in your shared Google Sheet. How do you set this up?

hard

A. Create a notification rule set to email 'daily digest' for all changes

B. Create a notification rule set to email 'right away' but only for your edits

C. Create a notification rule set to email 'right away' for all changes

D. Create a notification rule set to email 'weekly summary' for all changes

Solution

1. Step 1: Identify notification frequency needed

   You want one email per day summarizing all changes, so 'daily digest' is the correct frequency.

2. Step 2: Match option with this frequency

   Create a notification rule set to email 'daily digest' for all changes matches 'daily digest' for all changes; other options either notify immediately or less frequently.

3. Final Answer:

   Create a notification rule set to email 'daily digest' for all changes -> Option A

4. Quick Check:

   Daily digest = One email per day [OK]

Hint: Choose 'daily digest' to get one email per day [OK]

Common Mistakes:

• Choosing 'right away' instead of daily digest
• Selecting weekly summary when daily is needed
• Limiting notifications to own edits only