0
0
Google Sheetsspreadsheet~8 mins

Data validation rules in Google Sheets - Dashboard Guide

Choose your learning style9 modes available
Dashboard Mode - Data validation rules
Dashboard Goal

Help users enter correct data by showing how to use data validation rules in Google Sheets. This dashboard guides you to set rules that limit input to specific types like numbers, dates, or dropdown lists.

Sample Data
ItemQuantityOrder DateStatus
Apples102024-06-01Pending
Bananas52024-06-03Shipped
Oranges82024-06-02Pending
Grapes122024-06-05Cancelled
Mangoes72024-06-04Shipped
Dashboard Components
  • KPI Card: Total Orders
    Formula: =COUNTA(A2:A6)
    Shows total number of orders (5).
  • KPI Card: Valid Quantity Entries
    Formula: =COUNTIF(B2:B6, ">=1")
    Counts quantities that are 1 or more (5 valid entries).
  • KPI Card: Valid Order Dates
    Formula: =COUNT(C2:C6) (Note: This counts numbers, so better use =COUNTIF(C2:C6, ">="&DATE(2024,6,1)) - COUNTIF(C2:C6, ">"&DATE(2024,6,30)) to count dates between June 1 and June 30, 2024.)
    Counts valid dates in the range.
  • Data Validation Rule Example 1: Quantity must be whole number >=1
    Rule: Set data validation on Quantity column (B2:B6) to allow only whole numbers greater than or equal to 1.
  • Data Validation Rule Example 2: Order Date must be a date between 2024-06-01 and 2024-06-30
    Rule: Set data validation on Order Date column (C2:C6) to allow dates between June 1 and June 30, 2024.
  • Data Validation Rule Example 3: Status must be from dropdown list
    Rule: Set data validation on Status column (D2:D6) to allow only these values: Pending, Shipped, Cancelled.
Dashboard Layout
+----------------------+----------------------+----------------------+
|    Total Orders       |  Valid Quantity      |  Valid Order Dates    |
|       (KPI)           |      (KPI)           |        (KPI)          |
+----------------------+----------------------+----------------------+
|                      Data Table with sample orders                    |
|                      (Items, Quantity, Order Date, Status)           |
+-----------------------------------------------------------------------+
|  Data Validation Rules Examples (Quantity, Date, Status columns)      |
+-----------------------------------------------------------------------+
Interactivity

When users enter data in Quantity, Order Date, or Status columns, the data validation rules check the input immediately. If input is invalid, a warning or rejection message appears. The KPI cards update automatically to reflect only valid entries.

Self Check

Add a filter to show only orders with Status = "Pending". Which components update?

  • The Data Table shows only rows with Status "Pending".
  • The KPI cards for Total Orders, Valid Quantity Entries, and Valid Order Dates update to count only the filtered rows.
Key Result
Dashboard demonstrating how to apply and monitor data validation rules for Quantity, Order Date, and Status columns in Google Sheets.