Data validation rules in Excel - Dashboard Guide

Dashboard Mode - Data validation rules

Dashboard Goal

This dashboard helps you understand and apply data validation rules in Excel to control what users can enter in cells. It shows examples of different validation types and how they restrict input.

Sample Data

Item	Quantity	Category	Price	Order Date
Apples	10	Fruit	1.20	2024-06-01
Bananas	5	Fruit	0.80	2024-06-02
Carrots	7	Vegetable	0.50	2024-06-03
Detergent	3	Cleaning	3.00	2024-06-04
Eggs	12	Dairy	2.50	2024-06-05

Dashboard Components

• KPI Card: Total Quantity
  Formula: =SUM(B2:B6)
  Shows total quantity of all items (result: 37)
• Data Validation Example 1: Quantity (Whole Number)
  Rule: Allow only whole numbers between 1 and 20
  Applied to cells B2:B6
  Effect: Prevents entering decimals or numbers outside 1-20
• Data Validation Example 2: Category (List)
  Rule: Allow only these categories: Fruit, Vegetable, Cleaning, Dairy
  Applied to cells C2:C6
  Effect: User must pick from dropdown list
• Data Validation Example 3: Price (Decimal)
  Rule: Allow decimal numbers greater than 0 and less than or equal to 10
  Applied to cells D2:D6
  Effect: Prevents negative or too large prices
• Data Validation Example 4: Order Date (Date)
  Rule: Allow dates from 2024-06-01 to 2024-06-30
  Applied to cells E2:E6
  Effect: Only dates in June 2024 allowed

Dashboard Layout

+----------------------+-----------------------------+
| Total Quantity: 37    | Data Validation Rules Table  |
| (KPI Card)           |                             |
+----------------------+-----------------------------+
| Quantity Validation  | Category Validation (List)  |
| (Whole Number 1-20)  |                             |
+----------------------+-----------------------------+
| Price Validation     | Order Date Validation       |
| (Decimal >0 & <=10)  | (Date in June 2024)         |
+----------------------+-----------------------------+

Interactivity

When users try to enter data in the Quantity, Category, Price, or Order Date columns, the data validation rules immediately check the input. If the input does not follow the rule, Excel shows an error message and prevents invalid entry. This keeps the data clean and consistent.

Self Check

Add a filter to show only items in the 'Fruit' category. Which components update?

• The data table will show only rows with Category = Fruit (Apples and Bananas).
• The Total Quantity KPI card will update to show the sum of quantities for these filtered rows (10 + 5 = 15).
• Data validation rules remain the same and continue to restrict input in their columns.

Key Result

Dashboard demonstrating how to apply and use data validation rules in Excel to control input types and values.