Pivot Tables
What You’ll Learn:
-
What pivot tables are and when to use them
-
How to summarize large data sets with just a few clicks
-
How to group, filter, and aggregate data
-
How to create a simple sales summary project using pivot tables
Step 1: What is a Pivot Table?
A pivot table is a tool that allows you to summarize, analyze, explore, and present large datasets. It rearranges and groups your raw data to help extract meaningful patterns.
Step 2: Sample Dataset – Sales Log
Enter the following data into a 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 |
Step 3: Insert a Pivot Table
Steps:
-
Select the entire table (A1:F7)
-
Go to Insert > Pivot table
-
In the dialog box, choose:
-
Create in new sheet
-
Click Create
-
Now the Pivot Table Editor will open on the right.
Step 4: Build a Summary Table – Total Revenue by Salesperson
Steps in Pivot Table Editor:
-
Rows → Add Salesperson
-
Values → Add Revenue
-
Set Summarize by: SUM
-
You now have a pivot table showing each salesperson’s total revenue.
Step 5: Add More Analysis – Revenue by Product and Region
Try the following setup:
-
Rows → Region
-
Columns → Product
-
Values → Revenue (SUM)
This creates a matrix view of revenue per product in each region.
Step 6: Apply Filters (Optional)
To focus on specific data:
-
In the Pivot Table Editor, add Filter → Salesperson
-
Filter to show only “Aman” or others
Mini Project: Build a Sales Dashboard
Using the same dataset:
-
Create 3 pivot tables:
-
Revenue by Salesperson
-
Units Sold by Product
-
Revenue by Region
-
-
Apply filters to compare data over time
-
Format the pivot table with currency formatting for ₹ values
Bonus:
-
Create bar charts from the pivot table outputs
