Lesson 5.1 – Interactive Dashboards using Pivot Tables & Charts
Lesson Overview
Pivot Tables and Pivot Charts are foundational tools for building interactive dashboards. They allow dynamic summarization, filtering, and visualization of large datasets efficiently.
1. Pivot Tables
1.1 What is a Pivot Table?
-
A Pivot Table summarizes data from a large dataset quickly.
-
Can group, filter, and calculate totals with drag-and-drop simplicity.
1.2 Steps to Create a Pivot Table
-
Select your dataset.
-
Go to Insert → Pivot Table.
-
Choose New Worksheet or Existing Worksheet for placement.
-
Drag fields into Rows, Columns, Values, and Filters.
1.3 Example
-
Dataset: Sales data with Product, Region, Sales Amount
-
Pivot Table: Total Sales by Region and Product
2. Pivot Charts
2.1 What is a Pivot Chart?
-
A Pivot Chart is a graphical representation of Pivot Table data.
-
Automatically updates when the Pivot Table changes.
2.2 Steps to Create a Pivot Chart
-
Select Pivot Table → Go to Insert → Pivot Chart
-
Choose chart type (Column, Line, Pie, etc.)
-
Customize chart with titles, colors, and labels
2.3 Tips
-
Use filter buttons on charts for interactivity
-
Combine multiple charts for comprehensive dashboards
3. Slicers & Timelines
3.1 Slicers
-
Add buttons to filter Pivot Table/Chart data visually
-
Steps: Pivot Table → Insert Slicer → Select field → Click OK
3.2 Timelines
-
Filter date-based data interactively
-
Steps: Pivot Table → Insert Timeline → Select date field → Click OK
4. Real-Life Examples
-
Sales Dashboard: Filter by month, region, or product
-
HR Dashboard: Track employee attendance and department-wise headcount
-
Finance Dashboard: Compare revenue and expenses across quarters
5. Practice Questions
-
Create a Pivot Table showing total sales by region.
-
Insert a Pivot Chart based on the Pivot Table.
-
Add a slicer to filter data by product category.
-
Insert a timeline to filter sales data by month.
-
Explain how Pivot Tables help in interactive dashboards.