Part VI: Pivot Tables & Reporting

What You Will Learn

• Understand what a Pivot Table is and why it is the most powerful feature in Excel
• Create Pivot Tables from structured datasets using Insert → PivotTable
• Master the four Pivot Table areas: Filters, Rows, Columns, and Values
• Apply Value Field Settings: Sum, Count, Average, Min, Max, Product
• Group data by date (Month/Quarter/Year) and by number ranges
• Create Calculated Fields and Calculated Items for custom calculations
• Sort, filter, and format Pivot Tables professionally
• Refresh data and change the data source dynamically
• Use Show Values As for % of Grand Total, Running Total, Rank, and more
• Apply PivotTable Styles and switch between Compact, Outline, and Tabular layouts

What is a Pivot Table?

A Pivot Table is an interactive data summarization tool that allows you to reorganize, group, filter, and calculate large datasets without writing a single formula. Think of it as a magical table that can "pivot" (rotate) your data to view it from any angle.

Real-World Analogy

Imagine you run a chain of 50 Chai Point outlets across India. Every day, each outlet generates a transaction log: customer name, drink ordered, size, price, payment method, time, and outlet city. After one month, you have 1,50,000 rows of data. You want to know:

• Which city generates the most revenue?
• What is the average order value for each drink type?
• How does UPI usage compare to cash across different cities?

A Pivot Table answers ALL of these questions by simply dragging fields into different areas. No formulas. No filtering. No manual counting.

Creating a Pivot Table

Step 1: Prepare Your Data

Before creating a Pivot Table, your data must be in tabular format — each column has a header, each row represents one record, and there are no blank rows or merged cells.

Step 2: Insert a PivotTable

1. Click any cell inside your data range
2. Go to Insert tab → click PivotTable
3. In the dialog box, Excel automatically selects the entire data range
4. Choose the location: New Worksheet (recommended) or Existing Worksheet
5. Click OK

Step 3: The PivotTable Field List

After inserting, you see an empty Pivot Table on the left and the PivotTable Fields pane on the right with four areas:

Value Field Settings

When you drag a numeric field to the Values area, Excel defaults to Sum. But you can change this to any of these aggregation functions:

To change the Value Field Setting: Right-click any value in the Pivot Table → Value Field Settings → select the desired function.

Grouping Data

Grouping by Date

One of the most powerful features: if your data has dates, you can automatically group them by Months, Quarters, or Years.

1. Drag the Date field to Rows
2. Excel may auto-group dates. If not, right-click any date in the Pivot Table
3. Select Group
4. Choose: Days, Months, Quarters, Years (you can select multiple)
5. Click OK

Grouping by Number Ranges

You can group numeric values into ranges. For example, grouping order amounts into slabs of ₹5,000:

1. Right-click a numeric field in Rows → Group
2. Set Starting at: 0, Ending at: 100000, By: 5000
3. This creates groups: 0–4999, 5000–9999, 10000–14999, etc.

Calculated Fields

A Calculated Field lets you create new columns within the Pivot Table using formulas based on existing fields — without modifying the source data.

Sorting and Filtering in Pivot Tables

Pivot Tables have their own sorting and filtering mechanisms:

• Row Label Filters: Click the dropdown arrow next to Row Labels to filter specific categories
• Value Filters: Filter rows where Sum of Amount > ₹50,000 (Top 10 filters, Greater Than, Between, etc.)
• Label Filters: Filter by text — "begins with", "contains", etc.
• Sort A-Z / Z-A: Sort rows alphabetically or by value (largest to smallest)
• Manual Sort: Drag items to reorder them manually

Refreshing and Changing Data Source

Pivot Tables do not update automatically when source data changes. You must:

• Refresh: Right-click the Pivot Table → Refresh, or press Alt+F5
• Refresh All: Data tab → Refresh All (refreshes all Pivot Tables in the workbook)
• Change Data Source: PivotTable Analyze → Change Data Source → select new range

PivotTable Styles and Layouts

Layout Options

To change: PivotTable Analyze → Design tab → Report Layout → choose the layout.

Show Values As

This transforms how values are displayed without changing the underlying calculation:

Complete Walkthrough: Building a Sales Analysis Pivot Table

Step 1: Open Your Dataset

Open the file containing 1000 rows of sales data. Verify it has headers in Row 1 and no blank rows.

Step 2: Convert to Table (Recommended)

1. Click any cell in the data
2. Press Ctrl+T
3. Confirm "My table has headers" is checked
4. Click OK. Your data now has a striped table format.

Step 3: Insert PivotTable

1. With cursor inside the table, go to Insert → PivotTable
2. Source: "Table1" (auto-detected)
3. Location: New Worksheet
4. Click OK

Step 4: Build "Revenue by Category" View

1. Drag Category to Rows
2. Drag Amount to Values (auto-sums)
3. Result: Each category with its total revenue

Step 5: Add "State" as Columns

1. Drag State to Columns
2. Now you see a cross-tabulation: Category × State with Sum of Amount at each intersection

Step 6: Add Filter by Payment Method

1. Drag Payment to Filters
2. A dropdown appears above the Pivot Table
3. Select "UPI" to see only UPI transactions

Step 7: Change to Average

1. Right-click any value → Value Field Settings
2. Choose Average
3. Now see the average order value per Category × State

Step 8: Group Dates by Quarter

1. Add Date to Rows (above Category)
2. Right-click a date → Group
3. Select Months and Quarters
4. Click OK — dates are now grouped into Q1, Q2, Q3, Q4 with months within each

Step 9: Apply Show Values As — % of Grand Total

1. Right-click any value → Show Values As → % of Grand Total
2. All values now show as percentages — instantly see each category's contribution

Step 10: Apply a PivotTable Style

1. Click inside the Pivot Table
2. Go to Design tab → PivotTable Styles
3. Choose a medium or dark style for professional reporting

Example 1: Zomato Order Analysis

Scenario: Zomato's Bengaluru operations team has 50,000 order records for March 2024. They need to analyze: total revenue by restaurant type, average order value by area, and peak ordering hours.

Pivot Table Setup:

• Rows: Type (restaurant category)
• Columns: (none initially)
• Values: Sum of Amount, Count of Order ID, Average of Rating
• Filter: Area

Result:

Example 2: CBSE Class 12 Results Analysis

Scenario: A school principal has 800 student records for Class 12 board exams and wants to analyze: average marks by subject and stream, pass/fail rates, and top-performing sections.

Pivot Table 1 — Average Marks by Stream and Subject:

• Rows: Stream, then Subject (nested)
• Values: Average of Marks

Pivot Table 2 — Pass/Fail Count by Section:

• Rows: Section
• Columns: Status (Pass/Fail)
• Values: Count of Roll No

Example 3: TCS Employee Salary Analysis

Scenario: TCS HR department analyzes salary data of 5000 employees across departments and locations to prepare the annual compensation report.

Pivot Table Setup:

• Rows: Department
• Columns: Location
• Values: Average of Monthly Salary
• Filter: Grade

Calculated Field: Annual CTC = Monthly Salary * 12 * 1.4 (including benefits)

Grouping: Group Experience into ranges: 0-2, 3-5, 6-10, 11-15, 16+ years to see salary trends by experience level.

Example 4: GST Collection by State (Government Data)

Scenario: Analyze monthly GST collection data across Indian states to identify top contributors and seasonal patterns.

Pivot: Rows = State, Columns = Month (grouped by Quarter), Values = Sum of Total. Use Show Values As → % of Column Total to see each state's share per quarter.

Example 5: Reliance Jio Recharge Analysis

Scenario: Jio's analytics team analyzes recharge data from 10 lakh subscribers to understand plan preference by region.

• Rows: Plan Type (Prepaid/Postpaid), Plan Name
• Columns: Region (North/South/East/West)
• Values: Count of Subscribers, Sum of Recharge Amount
• Show Values As: Running Total to see cumulative subscriber growth by month

Exercise Set — Pivot Tables

Exercise 1: Basic Pivot Table

Using the Flipkart sales dataset (1000 rows), create a Pivot Table showing total revenue by Category. Sort from highest to lowest revenue.

Answer: Drag Category → Rows, Amount → Values (Sum). Right-click any amount → Sort → Largest to Smallest.

Exercise 2: Cross-Tabulation

Create a Pivot Table showing Count of Orders where Rows = State, Columns = Payment Method.

Answer: Drag State → Rows, Payment → Columns, Order ID → Values. Change to Count (right-click → Value Field Settings → Count).

Exercise 3: Grouping Dates

Show monthly revenue trend for the year 2024. Group the Date field by Months and Years.

Answer: Drag Date → Rows, Amount → Values. Right-click date → Group → select Months and Years.

Exercise 4: Calculated Field

Create a Calculated Field called "GST" that equals Amount * 0.18. Show total Amount and GST side by side for each Category.

Answer: PivotTable Analyze → Calculated Field → Name: "GST", Formula: =Amount*0.18 → OK.

Exercise 5: Show Values As

Show each Category's revenue as a % of Grand Total. Which category has the highest share?

Answer: Right-click value → Show Values As → % of Grand Total. Electronics typically shows 55-60%.

Exercise 6: Multiple Values

Create a Pivot Table showing Category in Rows with three value columns: Sum of Amount, Average of Amount, and Count of Order ID.

Answer: Drag Amount to Values twice, Order ID to Values once. Change second Amount to Average via Value Field Settings. Rename column headers by clicking and typing.

Exercise 7: Top 5 Filter

Show only the Top 5 cities by total revenue using Value Filters.

Answer: Click Row Labels dropdown → Value Filters → Top 10 → change to Top 5 Items by Sum of Amount.

Exercise 8: Tabular Layout

Convert the Pivot Table to Tabular Form and repeat all item labels. Copy the result to a new sheet.

Answer: Design → Report Layout → Show in Tabular Form. Design → Report Layout → Repeat All Item Labels. Select all, Ctrl+C, new sheet, Ctrl+V.

Exercise 9: Number Grouping

Group order amounts into slabs: 0-999, 1000-4999, 5000-9999, 10000-49999, 50000+. Show count of orders in each slab.

Answer: Drag Amount to Rows. Right-click → Group → Starting: 0, Ending: 100000, By: custom ranges. Alternatively, create a helper column with IF formulas for exact custom slabs.

Exercise 10: Running Total

Show cumulative monthly revenue using Running Total. In which month does cumulative revenue cross ₹25,00,000?

Answer: Group dates by month. Right-click value → Show Values As → Running Total In → Base Field: Date. Read the month where value exceeds 25,00,000.

Chapter 21 — Quiz

What You Will Learn

• Understand the relationship between Pivot Tables and Pivot Charts
• Create Pivot Charts from existing Pivot Tables and from scratch
• Choose the right chart type for your data analysis needs
• Use interactive filtering directly within Pivot Charts
• Format and customize Pivot Charts for professional presentations
• Combine Pivot Charts with Slicers for interactive dashboards

What is a Pivot Chart?

A Pivot Chart is a graphical representation of a Pivot Table. It is linked to a Pivot Table — when you filter, sort, or rearrange the Pivot Table, the Pivot Chart updates automatically, and vice versa. Unlike regular charts, Pivot Charts have built-in filter buttons that allow interactive exploration of data.

Key Differences: Regular Chart vs Pivot Chart

Creating a Pivot Chart

Method 1: From an Existing Pivot Table

1. Click anywhere inside an existing Pivot Table
2. Go to PivotTable Analyze tab → PivotChart
3. Choose a chart type (Column, Bar, Line, Pie, etc.)
4. Click OK

Method 2: From Scratch (No Existing Pivot Table)

1. Click any cell in your data range
2. Go to Insert tab → PivotChart (in the Charts group, look for the PivotChart dropdown)
3. Excel creates both a Pivot Table and Pivot Chart simultaneously
4. Arrange fields in the PivotChart Fields pane

Choosing the Right Chart Type

Interactive Filtering in Pivot Charts

Pivot Charts have field buttons directly on the chart area:

• Axis Fields button: Filter which categories appear on the axis (e.g., show only Electronics and Clothing)
• Legend Fields button: Filter the series in the legend (e.g., show only UPI and Card payments)
• Value Filter button: Filter by value criteria
• Report Filter: If your Pivot Table has a Filter area, it appears as a dropdown on the chart

To hide field buttons (for clean presentation): Click chart → PivotChart Analyze → Field Buttons → Hide All

Formatting Pivot Charts

Pivot Charts support all standard chart formatting options:

• Chart Title: Click and type a descriptive title
• Data Labels: Right-click data series → Add Data Labels → format position and number format
• Colors: Click chart → Design tab → Change Colors to choose a professional palette
• Axis formatting: Right-click axis → Format Axis → set number format (₹ #,##0), min/max bounds
• Chart Style: Design tab → Chart Styles gallery for pre-built looks
• Legend position: Click legend → drag or right-click → Format Legend → position (Top, Bottom, Right)

Pivot Chart + Slicer Combination

The most powerful interactive dashboard technique is combining Pivot Charts with Slicers (Chapter 23). When a user clicks a Slicer button (e.g., selecting "Mumbai"), all connected Pivot Charts update simultaneously to show only Mumbai data. This creates a truly interactive dashboard without any VBA or macros.

Example 1: Revenue by Category — Clustered Column Chart

Data: Flipkart sales dataset, 1000 rows

Pivot Table: Rows = Category, Values = Sum of Amount

Chart Type: Clustered Column

1. Create Pivot Table with Category in Rows, Amount in Values
2. Click PivotTable Analyze → PivotChart → Column → Clustered Column
3. Add data labels: Right-click bars → Add Data Labels
4. Format: Change colors to a green palette, add chart title "Revenue by Category — FY 2024"

Example 2: Monthly Trend — Line Chart

Pivot Table: Rows = Date (grouped by Month), Values = Sum of Amount

Chart Type: Line with Markers

This instantly reveals seasonal patterns — diwali months (Oct-Nov) show spikes, January shows post-holiday dip.

Example 3: Payment Method Distribution — Doughnut Chart

Pivot Table: Rows = Payment Method, Values = Count of Orders

Chart Type: Doughnut

Example 4: State-wise Comparison — Horizontal Bar Chart

Pivot Table: Rows = State, Values = Sum of Amount (sorted largest to smallest)

Chart Type: Bar Chart (horizontal) — ideal for 15+ state names that would crowd a column chart

Example 5: Revenue + Orders Combo Chart

Pivot Table: Rows = Month, Values = Sum of Amount AND Count of Orders

Chart Type: Combo — Column for Revenue (primary axis), Line for Order Count (secondary axis)

1. Create Pivot Table with two value fields
2. Insert PivotChart → select Combo
3. Set Sum of Amount as Clustered Column (primary axis)
4. Set Count of Orders as Line (secondary axis)
5. This reveals if revenue growth is from more orders or higher order values

Example 6: Category Contribution — Stacked Column

Pivot Table: Rows = Quarter, Columns = Category, Values = Sum of Amount

Chart Type: 100% Stacked Column — shows each category's share per quarter

Example 7: Top 5 Products — Filtered Bar Chart

Pivot Table: Rows = Product, Values = Sum of Quantity (Value Filter: Top 5)

Chart Type: Bar Chart with contrasting colors for each product

Exercises

Exercise 1

Create a Pivot Chart (Line) showing monthly revenue trend. Add a trendline. What is the overall trend — increasing or decreasing?

Answer: Right-click the line → Add Trendline → Linear. If slope is positive, revenue is increasing.

Exercise 2

Create a Pie chart showing category-wise revenue share. Explode the largest slice.

Answer: Click the largest slice once (selects series), click again (selects single point), drag outward to explode.

Exercise 3

Create a Combo chart: Monthly Revenue (Column) + Average Order Value (Line on secondary axis).

Answer: Insert PivotChart → Combo. Set Sum of Amount as Clustered Column, Average of Amount as Line on Secondary Axis.

Exercise 4

Create a Stacked Bar showing payment method breakdown by state. Which state has the highest UPI adoption?

Answer: Rows = State, Columns = Payment, Values = Count. Insert Stacked Bar. The state with the largest UPI segment has highest UPI adoption.

Exercise 5

Create two Pivot Charts on the same sheet: (1) Revenue by Category and (2) Order Count by Month. Format both with consistent colors.

Answer: Create two Pivot Tables from the same data. Insert a chart from each. Move and resize both charts on one sheet. Use Design → Change Colors to apply the same color palette.

Exercise 6

Hide all field buttons from a Pivot Chart and add a custom chart title. Save as an image (right-click → Save as Picture).

Answer: Click chart → PivotChart Analyze → Field Buttons → Hide All. Click chart title, type custom title. Right-click chart border → Save as Picture → PNG.

Exercise 7

Create a Pivot Chart, then change the underlying Pivot Table's aggregation from Sum to Average. Observe how the chart updates automatically.

Exercise 8

Use the Axis field button on a Pivot Chart to show only the top 3 categories. Then clear the filter to show all.

Chapter 22 — Quiz