Dashboards & Interactivity
What You’ll Learn:
-
How to build an interactive dashboard
-
Use slicers to filter pivot tables and charts dynamically
-
Apply dynamic named ranges to auto-update your charts
-
Combine charts and filters to make data exploration intuitive
Prerequisites:
-
Understanding of pivot tables and charts
-
A base dataset with multiple rows and dimensions (e.g., sales, product, region, date)
Step 1: Sample Dataset – Sales Report
Paste this data into your sheet:
| Date | Salesperson | Region | Product | Units Sold | Revenue (₹) |
|---|---|---|---|---|---|
| 2025-07-01 | Aman | East | T-Shirt | 10 | 2000 |
| 2025-07-01 | Meera | West | Jacket | 5 | 4000 |
| 2025-07-02 | Aman | East | Jeans | 8 | 3200 |
| 2025-07-02 | Meera | West | Shoes | 6 | 4500 |
| 2025-07-03 | Ravi | North | T-Shirt | 7 | 1400 |
| 2025-07-03 | Aman | East | Jacket | 3 | 2400 |
| 2025-07-04 | Ravi | North | Shoes | 5 | 3750 |
| 2025-07-04 | Meera | West | Jeans | 4 | 1600 |
Step 2: Create Pivot Tables
-
Select the dataset → Go to Insert > Pivot Table
-
Choose New Sheet
-
Add Rows:
Salesperson -
Add Values:
Revenue(Summarize by: SUM) -
Create another pivot table for:
-
Rows:
Product -
Columns:
Region -
Values:
Units Sold
-
Step 3: Insert Charts from Pivot Tables
-
Select the pivot table range
-
Click Insert > Chart
-
Choose appropriate chart type:
-
Bar chart for revenue by salesperson
-
Column chart for product-wise region sales
-
Step 4: Add Slicers for Interactivity
A slicer allows you to filter pivot tables and charts from one central filter.
Steps:
-
Click on your pivot table or chart
-
Go to Data > Add a slicer
-
Set slicer column (e.g.,
RegionorSalesperson) -
Position the slicer near your dashboard area
-
When clicked, slicer filters all linked tables/charts
Note: Make sure slicers are connected to the correct pivot tables (you can check using the slicer settings gear icon)
Step 5: Create Dynamic Named Ranges (Optional – Advanced)
If your dataset is growing over time, use a dynamic named range so pivot tables auto-update.
-
Select Data > Named ranges
-
Name:
SalesData -
Formula:
=QUERY(Sheet1!A:F, "select * where A is not null", 1)
-
Use
SalesDatain your pivot table and chart sources
Step 6: Organize Your Dashboard
-
Rename sheet:
Dashboard -
Move charts to the top half
-
Place slicers on the left or top-right
-
Add summary metrics using formulas:
-
Total Revenue:
=SUM(F2:F100) -
Top Salesperson: Use
SORT&INDEX
-
-
Format charts with clear titles and legends
Mini Project: Interactive Sales Dashboard
Objective: Build a live dashboard where a user can:
-
Select region or salesperson using slicers
-
View pivot-based summaries
-
See visual insights in charts
