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

What You Will Learn

• Understand what Slicers are and how they enhance data exploration
• Create Slicers for both Tables and Pivot Tables
• Customize Slicer styles, sizes, and column layouts
• Connect one Slicer to multiple Pivot Tables using Report Connections
• Use multi-select and clear filter features
• Build interactive dashboards with shared Slicers

What are Slicers?

A Slicer is a visual filter control — a floating panel with clickable buttons that filters a Table or Pivot Table. Instead of opening dropdowns and checking/unchecking items, users simply click a button to filter. It's faster, more intuitive, and looks professional in dashboards.

Analogy

Think of Slicers as the "filter buttons" on e-commerce websites like Myntra or Amazon. When you're shopping for shoes, you see clickable filters for Brand, Size, Color, and Price Range. Each click instantly updates the product listing. Slicers do the same thing for your Excel data.

Creating Slicers

For Pivot Tables

1. Click anywhere inside a Pivot Table
2. Go to PivotTable Analyze tab → Insert Slicer
3. Check the fields you want as Slicers (e.g., Category, State, Payment Method)
4. Click OK — Slicer panels appear on your worksheet

For Excel Tables

1. Click inside an Excel Table (created with Ctrl+T)
2. Go to Insert tab → Slicer
3. Check the fields → OK

Using Slicers

Slicer Customization

Slicer Styles

Click a Slicer → Slicer tab (on ribbon) → Slicer Styles gallery. Choose from Light, Dark, or custom color styles. You can also right-click a style → Modify to create your own.

Size and Columns

Resize the Slicer by dragging its corners. To change the number of button columns:

1. Click the Slicer
2. Go to Slicer tab → Buttons section
3. Set Columns = 2 or 3 for a wider layout
4. Adjust button height and width as needed

Report Connections — One Slicer, Multiple Pivot Tables

This is the most powerful Slicer feature. A single Slicer can control multiple Pivot Tables (and their Pivot Charts) simultaneously.

How to Connect

1. Right-click the Slicer → Report Connections (or PivotTable Connections)
2. A dialog shows all Pivot Tables in the workbook
3. Check all Pivot Tables you want this Slicer to control
4. Click OK

Table Slicers vs Pivot Table Slicers

Example 1: Flipkart Dashboard with Category Slicer

Setup: Create a Pivot Table from 1000-row sales data. Add a Slicer for "Category".

1. Create Pivot Table: Rows = City, Values = Sum of Amount
2. PivotTable Analyze → Insert Slicer → check "Category"
3. Click "Electronics" in the Slicer — Pivot Table instantly shows only Electronics revenue by city
4. Ctrl+click "Clothing" — now shows Electronics AND Clothing combined
5. Click Clear Filter icon — all categories shown again

Example 2: Multi-Slicer Dashboard

Setup: Create 3 Slicers: Category, State, Payment Method. All connected to same Pivot Table.

Click "Electronics" + "Maharashtra" + "UPI" → Pivot Table shows ONLY Electronics orders from Maharashtra paid via UPI. This cross-filtering is what makes dashboards powerful.

Example 3: Shared Slicer for 4 Pivot Tables

Scenario: BharatMart dashboard has 4 Pivot Tables on one sheet:

• PT1: Revenue by Category
• PT2: Monthly Trend
• PT3: Payment Distribution
• PT4: Top 10 Products

Create one "State" Slicer → right-click → Report Connections → check all 4 Pivot Tables. Now clicking "Karnataka" in the Slicer filters ALL four views to show only Karnataka data.

Example 4: Styled Slicer for Presentation

1. Insert a Category Slicer
2. Click Slicer → Slicer tab → choose "Dark Green" style
3. Set Columns = 3 (shows buttons in 3 columns)
4. Adjust button height to 30px for compact look
5. Position Slicer at the top of the dashboard sheet

Example 5: Table Slicer for Student Marks

Data: CBSE marks table with 200 students. Add a Slicer for "Stream" (Science/Commerce/Arts) and "Section" (A/B/C). Click "Science" + "Section A" to see only Science stream, Section A students.

Example 6: Multi-select Toggle

Click the multi-select icon (🔲) on the Slicer. Now each click toggles a button on/off without needing Ctrl. Click "UPI" → on. Click "Card" → both on. Click "UPI" again → off (only Card remains). This is great for quick exploration.

Exercises

Exercise 1

Create a Pivot Table (Revenue by City) and add a Slicer for "Category". Use the Slicer to find which city generates the most Electronics revenue.

Exercise 2

Add 3 Slicers (Category, State, Payment) to one Pivot Table. Use cross-filtering to answer: "How many Cash orders of Grocery items were placed in Gujarat?"

Exercise 3

Change a Slicer's style to "Dark Style 1" and set it to display in 4 columns. Resize to fit neatly beside the Pivot Table.

Exercise 4

Create 2 Pivot Tables from the same data source. Connect a single "Category" Slicer to both using Report Connections. Verify that clicking the Slicer filters both tables.

Exercise 5

Create a Table Slicer (not Pivot Table) for the CBSE marks data. Filter by Stream = "Commerce" and observe how the table rows are filtered.

Exercise 6

Use multi-select to select 3 states simultaneously. Take note of the Pivot Table total, then clear the filter and compare to the Grand Total.

Chapter 23 — Quiz

What You Will Learn

• Understand what Timelines are and how they differ from regular Slicers
• Create Timelines for date-based Pivot Tables
• Filter data by Days, Months, Quarters, and Years
• Customize Timeline styles and formatting
• Connect Timelines to multiple Pivot Tables

What is a Timeline?

A Timeline is a specialized visual filter designed exclusively for date fields in Pivot Tables. While you could use a regular Slicer for dates, Timelines provide a scrollable, zoomable date control that's far more intuitive for time-based filtering.

Timeline vs Date Slicer

Creating a Timeline

1. Click inside a Pivot Table that contains a date field
2. Go to PivotTable Analyze tab → Insert Timeline
3. Check the date field (e.g., "Order Date")
4. Click OK

The Timeline appears as a horizontal bar with month names. You can:

• Click a single month: Filter to that month only
• Click and drag: Select a range of months (e.g., Jan–Mar)
• Change zoom level: Click the dropdown at top-right — switch between DAYS, MONTHS, QUARTERS, YEARS
• Scroll: Use the scrollbar at bottom to navigate through time
• Clear filter: Click the 🚫 icon

Filtering by Time Period

By Months

Default view. Click "Mar" to filter March only. Click and drag from "Mar" to "Jun" to filter March through June. The Pivot Table updates instantly.

By Quarters

Switch to QUARTERS view. Click "Q1" for January–March. Drag Q1–Q2 for the first half of the year. This is ideal for quarterly business reviews.

By Years

Switch to YEARS view when you have multi-year data. Click "2023" to see all of 2023. Drag 2022–2024 for a 3-year range.

By Days

Switch to DAYS for granular daily analysis. Click a specific date. Useful for retail daily sales analysis or finding specific transaction dates.

Timeline Styles

Click the Timeline → Timeline tab on ribbon → Timeline Styles gallery. Styles include Light, Dark, and colored options. Match the style to your dashboard's color scheme.

Connecting to Multiple Pivot Tables

Just like Slicers, Timelines support Report Connections:

1. Right-click the Timeline → Report Connections
2. Check all Pivot Tables that should be filtered by this Timeline
3. Click OK

Now sliding the Timeline filters ALL connected Pivot Tables and their associated Pivot Charts simultaneously.

Solved Examples

Example 1: Monthly Sales Timeline

Data: 1000 orders from Jan–Dec 2024

1. Create Pivot Table: Rows = Category, Values = Sum of Amount
2. Insert Timeline for "Date"
3. Select MONTHS view → click "Jan" — see January-only revenue by category
4. Drag to select Jan–Mar — see Q1 revenue by category

Example 2: Quarterly Comparison

1. Switch Timeline to QUARTERS
2. Click Q1 → note the total revenue
3. Click Q2 → compare to Q1
4. This enables quick quarter-over-quarter analysis

Example 3: Timeline + Slicer Dashboard

Setup: 4 Pivot Charts on one sheet. One Timeline + two Slicers (Category, State). All connected via Report Connections.

User Flow: Select Q3 on Timeline → click "Electronics" Slicer → click "Karnataka" Slicer → all 4 charts show Electronics sales in Karnataka for Jul–Sep 2024.

Example 4: Year-over-Year Analysis

Data: 3 years of data (2022–2024)

1. Create Pivot Table: Rows = Category, Columns = Year
2. Insert Timeline → switch to YEARS view
3. Click 2023 to compare against 2022 data visible in the Pivot Table

Example 5: Daily Sales Spike Analysis

Scenario: Find which specific days had the highest sales during Diwali week (Oct 28 – Nov 3)

1. Switch Timeline to DAYS view
2. Drag to select Oct 28 – Nov 3
3. Pivot Table shows daily breakdown — identify the peak day

Exercises

Exercise 1

Create a Timeline for your sales data. Filter to show only Q2 2024 data. What is the total revenue for Q2?

Exercise 2

Switch the Timeline to DAYS view and find the single highest-revenue day in the dataset.

Exercise 3

Connect one Timeline to 2 Pivot Tables showing (1) Revenue by Category and (2) Order Count by Payment. Verify both update when you slide the Timeline.

Exercise 4

Change the Timeline style to a dark theme. Resize it to span the full width of your dashboard.

Exercise 5

Combine a Timeline with a "State" Slicer. Use them together to find: "Total revenue in Maharashtra for March 2024."

Exercise 6

Create a Pivot Chart (Line) showing monthly revenue. Add a Timeline. Select different month ranges and observe how the chart dynamically adjusts.

Chapter 24 — Quiz

What You Will Learn

• Import data from CSV and TXT files into Excel
• Connect to Microsoft Access databases
• Use Power Query's web connector to import data from websites
• Understand data connections and the Refresh mechanism
• Get an overview of connecting to SQL databases
• Handle common import issues: encoding, delimiters, data types

Why Import External Data?

In real businesses, data rarely lives in a single Excel file. It's spread across:

• CSV/TXT files: Exported from billing software, e-commerce platforms, POS systems (like Tally, Zoho, Shopify)
• Access databases: Used by small businesses for inventory, customer management
• Web sources: Stock prices from NSE/BSE, weather data, currency exchange rates
• SQL databases: Enterprise systems at companies like TCS, Infosys, Reliance
• Other Excel files: Monthly reports from different departments or branches

Importing from CSV Files

CSV (Comma-Separated Values) is the most common data exchange format. Every software — Tally, Zoho Books, Google Sheets, banking portals — can export CSV.

Method 1: Open Directly

1. File → Open → change file type filter to "Text Files (*.csv, *.txt)"
2. Select the CSV file → Open
3. Excel opens it directly. Problem: Some data may not be parsed correctly (dates, leading zeros in PIN codes).

Method 2: Using Get Data (Power Query) — Recommended

1. Go to Data tab → Get Data → From File → From Text/CSV
2. Select the CSV file
3. Preview window shows: File Origin (encoding), Delimiter, Data Type Detection
4. Click Transform Data to open Power Query Editor for cleaning, or Load to import directly

Importing from TXT Files

TXT files work similarly to CSV but may use different delimiters:

• Tab-delimited: Common in exports from legacy systems
• Fixed-width: Each column has a fixed character width — older bank statements use this
• Pipe-delimited: Some banking and government systems use | as separator

Steps

1. Data → Get Data → From File → From Text/CSV
2. Select the .txt file
3. In the preview, change Delimiter to Tab or Custom (enter |)
4. Verify data preview looks correct
5. Click Load or Transform Data

Importing from Access Database

Microsoft Access is used by many Indian small businesses for inventory, billing, and CRM. Excel can directly connect to Access databases (.accdb / .mdb).

Steps

1. Data tab → Get Data → From Database → From Microsoft Access Database
2. Browse and select the .accdb file
3. Navigator window shows all tables and queries in the database
4. Select the desired table(s)
5. Click Load to import or Transform Data to clean first

Importing from Web

Excel can pull data from websites — useful for stock prices, weather data, sports statistics, or any publicly available table.

Steps

1. Data → Get Data → From Other Sources → From Web
2. Enter the URL of the webpage containing the data table
3. Click OK — Power Query's Web Navigator identifies tables on the page
4. Select the desired table from the list
5. Click Transform Data or Load

Data Connections and Refreshing

When you import external data, Excel creates a data connection — a saved link to the original source. This means you can refresh the imported data to get the latest version.

Connecting to SQL Databases (Overview)

Enterprise companies use SQL databases (SQL Server, MySQL, PostgreSQL, Oracle) to store millions of records. Excel can connect to these:

1. Data → Get Data → From Database → From SQL Server Database
2. Enter the Server name and Database name
3. Choose authentication: Windows or SQL Server credentials
4. Navigator shows all tables and views
5. Select desired tables → Load or Transform

Important notes for SQL connections:

• You need the database server address and login credentials (get from your DBA/IT team)
• You may need to install an ODBC driver for specific databases (MySQL, PostgreSQL)
• For large datasets (millions of rows), consider importing only filtered data using a SQL query (Advanced options → SQL Statement)
• Data connections are stored in the workbook — be careful sharing files with embedded credentials

Solved Examples

Example 1: Import Tally Export (CSV)

Scenario: A CA firm receives monthly Tally exports as CSV files from 10 clients. Each file has: Voucher No, Date, Ledger Name, Debit Amount, Credit Amount.

1. Data → Get Data → From File → From Text/CSV
2. Select "client_march2024.csv"
3. Verify delimiter = Comma, encoding = UTF-8
4. Click Transform Data to fix date format (Tally exports dates as DD-MM-YYYY)
5. In Power Query: Select Date column → Transform → Change Type → Date
6. Click Close & Load

Example 2: Import Bank Statement (Pipe-Delimited TXT)

Scenario: SBI net banking exports transaction history as pipe-delimited .txt file.

1. Data → Get Data → From File → From Text/CSV
2. Select "SBI_Statement_Apr2024.txt"
3. Change Delimiter to "Custom" → enter "|"
4. Preview shows: Date | Description | Debit | Credit | Balance
5. Click Load

Example 3: Import from Access — School Database

Scenario: A school maintains student records in Access. Import the "Students" and "Marks" tables.

1. Data → Get Data → From Database → From Access
2. Select "SchoolDB.accdb"
3. Check "Students" and "Marks" tables
4. Load both → they appear as two separate tables in Excel

Example 4: Import NSE Stock Data from Web

1. Data → Get Data → From Other Sources → From Web
2. Enter URL with stock data tables
3. Select the table showing NIFTY 50 companies
4. Transform Data → clean column headers → Close & Load

Example 5: Auto-Refreshing Currency Rates

1. Import USD/INR exchange rate table from a website
2. Right-click the imported table → Connection Properties
3. Check "Refresh every 60 minutes"
4. Now every hour, Excel fetches the latest exchange rates

Exercises

Exercise 1

Create a CSV file with 50 rows of sample sales data in Notepad (comma-separated). Import it into Excel using Get Data. Verify all columns are correctly parsed.

Exercise 2

Create a tab-delimited .txt file with student marks. Import into Excel and change the delimiter to Tab.

Exercise 3

Import data from a web page that has an HTML table (e.g., Wikipedia's list of Indian states). Clean the data in Power Query before loading.

Exercise 4

After importing CSV data, modify the source CSV file (add 10 new rows). Use Refresh to update the imported data in Excel. Verify the new rows appear.

Exercise 5

Set up a data connection that refreshes automatically every 30 minutes. Check the connection properties to confirm.

Exercise 6

Import the same CSV file twice — once with "Load" and once with "Transform Data" → fix column types → "Close & Load". Compare the results.

Chapter 25 — Quiz

What You Will Learn

• Understand ETL (Extract, Transform, Load) and Power Query's role
• Navigate the Power Query Editor interface
• Perform common transformations: remove columns, rename, change types, split, merge columns
• Filter rows, remove duplicates, and replace values
• Merge queries (LEFT JOIN, INNER JOIN, etc.) to combine related tables
• Append queries (UNION) to stack tables with the same structure
• Group and aggregate data within Power Query
• Create custom columns using M formula language basics
• Choose loading destinations: worksheet, Data Model, or connection only
• Refresh Power Query data and understand the refresh pipeline

What is Power Query?

Power Query (officially called "Get & Transform Data") is Excel's built-in ETL (Extract, Transform, Load) engine. It allows you to:

• Extract: Import data from files (CSV, Excel, JSON), databases (Access, SQL Server), web, and more
• Transform: Clean, reshape, merge, filter, and calculate within a visual editor
• Load: Output the clean data to an Excel worksheet, the Data Model (for Power Pivot), or keep it as a connection only

Why Power Query Matters

Think of Power Query as a recipe for data cleaning. Every step you perform is recorded. When new data arrives (next month's CSV), you just click Refresh and every cleaning step is applied automatically to the new data. No manual work. No human error.

The Power Query Editor Interface

When you click Transform Data during any import, the Power Query Editor opens. It has these key areas:

Common Transformations

1. Remove Columns

Remove unnecessary columns from your data:

• Select column(s) → Right-click → Remove Columns
• Or: Home → Remove Columns → Remove Other Columns (keeps only selected columns)

2. Rename Columns

Double-click a column header → type new name → press Enter. Essential for standardizing column names across different source files.

3. Change Data Type

Click the data type icon (ABC, 123, 📅) next to the column name → select the correct type:

4. Split Columns

Split one column into multiple based on a delimiter or character count:

• Select column → Transform tab → Split Column → By Delimiter (comma, space, dash, etc.)
• Example: Split "Bengaluru, Karnataka" into City and State columns

5. Merge Columns

Combine two or more columns into one:

• Select multiple columns (Ctrl+Click) → Transform → Merge Columns
• Choose separator (space, comma, dash, custom)
• Example: Merge "First Name" + "Last Name" into "Full Name" with space separator

Filtering Rows

Click the dropdown arrow on any column header → filter options appear (same as Excel AutoFilter, but applied as a query step):

• Text filters: Equals, Contains, Begins with, Ends with
• Number filters: Greater than, Less than, Between
• Date filters: Before, After, Between, This month, Last year, etc.

Remove Duplicates

Select the column(s) that define uniqueness → Home → Remove Rows → Remove Duplicates.

Example: Remove duplicate customer entries based on "Customer ID" column — keeps the first occurrence, removes subsequent duplicates.

Replace Values

Select a column → Transform → Replace Values → enter "Value to Find" and "Replace With".

Examples:

• Replace "Blr" with "Bengaluru" in the City column
• Replace "NA" or "N/A" with blank (null) in Amount column
• Replace "M" with "Male" and "F" with "Female" in Gender column

Merging Queries (JOIN)

This is Power Query's equivalent of SQL JOINs — combine two tables based on a matching column.

Steps

1. In Power Query Editor: Home → Merge Queries
2. Select the primary query (e.g., Orders)
3. Select the secondary query (e.g., Customers)
4. Click the matching column in each (e.g., "Customer ID")
5. Choose the join type
6. Click OK → expand the merged column to select which fields to include

Example: Merge Orders with Customer Details

Appending Queries (UNION)

Stacks two or more tables with the same columns on top of each other — like SQL UNION.

Steps

1. Home → Append Queries
2. Choose: Two tables or Three or more tables
3. Select the tables to append
4. Click OK — rows from all tables are combined into one

Use Case: Monthly Reports

You have 12 CSV files (Jan.csv through Dec.csv), each with the same columns (Date, Product, Amount). Append all 12 into one combined dataset for annual analysis.

Grouping and Aggregation

Group rows by one or more columns and calculate aggregates — like SQL GROUP BY.

Steps

1. Select the column to group by (e.g., Category)
2. Transform → Group By
3. Set the aggregation: Sum of Amount, Count of Rows, Average of Price, etc.
4. You can add multiple aggregations in one step (click "Add Aggregation")

Example

Group sales data by City → calculate Total Revenue (Sum of Amount) and Order Count (Count of Rows):

Custom Columns with M Language

Power Query uses the M language (technically called "Power Query Formula Language") for its formulas. You can add calculated columns:

1. Add Column tab → Custom Column
2. Enter a column name and formula

Conditional Column (No-Code Alternative)

For simple if/else logic, use: Add Column → Conditional Column → fill in the conditions via the visual interface (no M code needed).

Loading Options

When you click Close & Load, you choose where to put the data:

To change: Close & Load dropdown → Close & Load To... → select destination.

Refreshing Power Query Data

Refreshing a Power Query re-runs ALL the recorded steps on the source data:

• Manual: Right-click the loaded table → Refresh, or Data → Refresh All
• Automatic: Connection Properties → set "Refresh every X minutes" or "Refresh on file open"
• Edit Steps: Right-click query in Queries & Connections pane → Edit → opens Power Query Editor

Example 1: Clean a Messy Sales CSV

Scenario: A Zomato restaurant partner receives a monthly sales CSV with issues: mixed date formats, "NA" in revenue column, extra spaces in restaurant names, and a useless "Serial Number" column.

Steps in Power Query:

1. Import: Data → Get Data → From Text/CSV → select file
2. Click "Transform Data" to open Power Query Editor
3. Remove column: Right-click "Serial Number" → Remove Column
4. Clean text: Select "Restaurant Name" → Transform → Trim (removes extra spaces)
5. Replace values: Select "Revenue" → Transform → Replace Values → find "NA", replace with blank
6. Change type: Click "Revenue" type icon → Decimal Number
7. Change type: Click "Date" type icon → Date (using locale: English India)
8. Filter: Click "Revenue" dropdown → Number Filters → Greater Than 0 (remove zero-revenue entries)
9. Close & Load

Result: Clean, analysis-ready data loaded to Excel. Next month, just refresh!

Example 2: Merge Orders + Customers (LEFT JOIN)

Scenario: Import an Orders CSV and a Customers CSV. Merge them on "Customer ID" to get customer names and cities alongside orders.

1. Import both files as separate queries (Get Data → From CSV, twice)
2. In Power Query Editor: Home → Merge Queries → Merge Queries as New
3. First table: Orders, column: Customer ID
4. Second table: Customers, column: Customer ID
5. Join kind: Left Outer
6. Click OK → Click expand icon on merged column → select Name, City, State
7. Close & Load

Example 3: Append 12 Monthly CSVs

Scenario: Combine January.csv through December.csv (same columns) into one annual dataset.

1. Best Method: Data → Get Data → From File → From Folder
2. Select the folder containing all 12 CSV files
3. Click "Combine & Transform Data" → Power Query combines all files
4. A "Source.Name" column is auto-added showing which file each row came from
5. Clean as needed → Close & Load

Example 4: Group By — City Revenue Summary

Source: 1000-row sales data

1. Import the data into Power Query
2. Transform → Group By
3. Group by: City
4. New column: "Total Revenue" — Sum of Amount
5. Add Aggregation: "Order Count" — Count of Rows
6. Add Aggregation: "Avg Order Value" — Average of Amount
7. Close & Load → get a clean summary table

Example 5: Custom Column — GST Calculation

1. In Power Query Editor: Add Column → Custom Column
2. Name: "GST Amount"
3. Formula: [Amount] * 0.18
4. Click OK → new column appears with 18% GST for each row
5. Add another custom column: "Total with GST" → [Amount] + [GST Amount]

Example 6: Conditional Column — Customer Segment

1. Add Column → Conditional Column
2. Column Name: "Segment"
3. If Amount ≥ 50000 → output "Premium"
4. Else if Amount ≥ 10000 → output "Standard"
5. Else → output "Budget"

Example 7: Split Column — Address Parsing

Problem: A column contains "HSR Layout, Bengaluru, Karnataka" — split into Area, City, State.

1. Select the Address column
2. Transform → Split Column → By Delimiter → Comma
3. Three new columns are created
4. Rename them: "Area", "City", "State"
5. Transform → Trim each column (remove leading spaces from ", Bengaluru")

Example 8: Unpivot Columns

Problem: Monthly data in wide format — one column per month. Need to convert to long format for Pivot Tables.

After Unpivot:

Steps: Select "Product" column → Transform → Unpivot Other Columns.

Example 9: Remove Duplicates with Multiple Key Columns

Problem: Order data has duplicate entries (same Order ID appearing twice due to system glitch).

1. Select "Order ID" column
2. Home → Remove Rows → Remove Duplicates
3. Keeps only the first occurrence of each Order ID

Example 10: Replace Inconsistent City Names

Problem: The City column has variations: "Bangalore", "Bengaluru", "BENGALURU", "Blr" — all mean the same city.

1. First: Transform → Capitalize Each Word (standardize case)
2. Then: Transform → Replace Values → "Bangalore" → "Bengaluru"
3. Replace Values → "Blr" → "Bengaluru"
4. Now all entries are consistently "Bengaluru"

Practice Exercises

Exercise 1

Import a CSV file into Power Query. Remove the first 2 columns and the last column. Change the "Date" column type to Date. Close & Load.

Exercise 2

Import a dataset with "Full Name" column. Split it into "First Name" and "Last Name" by space delimiter.

Exercise 3

Import an Orders table and a Products table. Merge them on "Product ID" using Left Outer join. Expand to get "Product Name" and "Unit Price".

Exercise 4

Create 3 CSV files with the same columns. Use "From Folder" to import and combine all 3. Add a custom column showing which file each row came from.

Exercise 5

Use Group By to summarize sales by Category: Total Amount, Count of Orders, Average Amount. Load the result to a new worksheet.

Exercise 6

Add a Conditional Column: if Quantity > 10, label as "Bulk"; if Quantity > 3, label as "Normal"; else "Single".

Exercise 7

Import data with inconsistent entries: "UPI", "upi", "Upi", "Google Pay UPI". Use Replace Values and Capitalize Each Word to standardize to "UPI" and "Google Pay UPI".

Exercise 8

Import a wide-format table with months as columns. Unpivot to convert to long format suitable for Pivot Tables.

Exercise 9

Use "From Folder" to import 4 monthly CSV files. In Power Query, filter out rows where Amount = 0 or Amount is null. Close & Load.

Exercise 10

Create a custom M column: if [Payment] = "EMI" then [Amount] * 1.12 else [Amount] (add 12% interest for EMI orders). Name it "Effective Amount".

Chapter 26 — Quiz