Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Pivot Table Exercises

Practise creating PivotTables, summarizing business data, grouping dates, filtering records, calculating totals, comparing categories, and preparing meaningful reports using practical Excel exercises.

1. Introduction to PivotTables

A PivotTable is an Excel feature that summarizes and analyses large datasets without requiring a separate formula for every group. It can organize records by categories, calculate totals, count records, compare values, and display results in a compact report.

For example, a sales dataset may contain hundreds of orders from multiple regions and products. Instead of manually adding the sales for each region, a PivotTable can summarize the data by region and calculate total sales automatically.

PivotTables are useful for sales analysis, employee reporting, attendance summaries, expense analysis, inventory reports, and management dashboards.

Remember: A PivotTable summarizes the source records. When the source data changes, refresh the PivotTable to update its results.
How a PivotTable Works
Source Data Product Region Sales Individual records PivotTable Fields Rows: Region Values: Sum of Sales Summary Report North: ₹88,000 South: ₹60,000 West: ₹1,50,000 Grouped results Source Records → Field Arrangement → Summary

Conceptual illustration of PivotTable summarization.

2. Prepare the Source Dataset

Create a worksheet named SalesData. Enter the following records with the headers in the first row. Each row represents one sales order.

Order ID Date Region Product Quantity Sales (₹) Cost (₹)
ORD001 01-Oct-2026 West Laptop 2 1,00,000 70,000
ORD002 02-Oct-2026 South Mobile 3 60,000 42,000
ORD003 03-Oct-2026 North Tablet 4 48,000 32,000
ORD004 04-Oct-2026 West Laptop 1 50,000 35,000
ORD005 05-Oct-2026 North Mobile 2 40,000 28,000
Data preparation: Use one header row, keep column names unique, and avoid merged cells within the source dataset. Store dates as actual Excel dates and sales and cost as numeric values.

3. How to Create a PivotTable

Follow these steps in Excel:

1
Select the Source Data

Click inside the dataset or select the complete range, including the column headings.


2
Insert a PivotTable

Select Insert → PivotTable. Confirm the source range and choose where to place the PivotTable.


3
Choose the Fields

Drag fields into Rows, Columns, Values, or Filters depending on the report requirement.


4
Configure the Values

Choose Sum, Count, Average, or another suitable summary calculation.


5
Format and Verify

Format numbers as currency, check the Grand Total, and compare the results with the source records.

4. Understanding PivotTable Areas

The four main PivotTable areas control how information is arranged and summarized.

Area Purpose Example Field
Rows Displays categories vertically. Region or Product
Columns Displays categories horizontally. Product or Month
Values Calculates totals, counts, averages, and other summaries. Sales or Quantity
Filters Filters the entire PivotTable report. Region or Date
PivotTable Field Arrangement
Rows Region Product Categories listed vertically Columns Product Month Categories listed horizontally Values Sum of Sales Average of Sales Numeric calculations Filters Region Date Filter the complete report Choose field placements according to the report requirement.

5. Exercise 1: Region-Wise Sales Analysis

The first exercise calculates total sales for each region. It is useful for comparing business performance across geographical areas.

PivotTable Field Setup
  • Rows: Region
  • Values: Sales, summarized by Sum
Expected Result
Region Total Sales (₹)
North 88,000
South 60,000
West 1,50,000
Grand Total 2,98,000

The West region has the highest total sales in this sample dataset. The PivotTable groups records by region and adds the sales values for each group.

Verification: North = ₹48,000 + ₹40,000 = ₹88,000. South = ₹60,000. West = ₹1,00,000 + ₹50,000 = ₹1,50,000.

6. Exercise 2: Product-Wise Sales Analysis

A product-wise PivotTable helps identify which products contribute most to total sales.

PivotTable Field Setup
  • Rows: Product
  • Values: Sales, summarized by Sum
Product Total Sales (₹)
Laptop 1,50,000
Mobile 1,00,000
Tablet 48,000
Grand Total 2,98,000

Laptop sales are highest in this dataset, followed by Mobile and Tablet. Sorting the PivotTable by total sales in descending order can make the ranking easier to interpret.

7. Exercise 3: Region and Product Comparison

Placing a field in both Rows and Columns helps compare categories across another dimension. For example, compare product sales across regions.

PivotTable Field Setup
  • Rows: Region
  • Columns: Product
  • Values: Sum of Sales
Region Laptop (₹) Mobile (₹) Tablet (₹) Total (₹)
North 0 40,000 48,000 88,000
South 0 60,000 0 60,000
West 1,50,000 0 0 1,50,000
Grand Total 1,50,000 1,00,000 48,000 2,98,000

The table shows how each product contributes to the sales of a region. In an actual PivotTable, combinations with no records may appear blank rather than as zero, depending on the layout and settings.

8. Exercise 4: Count Orders and Calculate Average Sales

PivotTables can summarize values using functions other than Sum. The Value Field Settings menu allows you to change the calculation to Count, Average, Max, Min, and other supported summary types.

Count Orders by Region
  • Rows: Region
  • Values: Order ID, summarized by Count
Region Order Count
North 2
South 1
West 2
Grand Total 5
Calculate Average Sales by Product
  • Rows: Product
  • Values: Sales, summarized by Average
Product Average Sales per Order (₹)
Laptop 75,000
Mobile 50,000
Tablet 48,000
Important distinction: Sum of Sales represents total revenue, while Average of Sales represents the average sales value per source record in the group.

9. Exercise 5: Filtering PivotTable Results

Filters and slicers allow users to focus on selected records without modifying the original dataset.

Using a Report Filter
  1. Click inside the PivotTable.
  2. Drag Region into the Filters area.
  3. Open the filter dropdown above the report.
  4. Select West to display only the West region's results.
  5. Clear the filter to display all regions again.
Using a Slicer
  1. Select the PivotTable.
  2. Open the PivotTable Analyze tab.
  3. Select Insert Slicer.
  4. Choose Region or Product.
  5. Click a slicer button to filter the report.
Filtering a PivotTable
Region Filter (All Regions) West North South Filtered PivotTable Region: West Product Sales Laptop ₹1,50,000 Total ₹1,50,000 Only matching source records are summarized.
Note: A slicer filters the PivotTable it is connected to. To control multiple PivotTables, use Report Connections where supported and ensure the reports share a compatible PivotCache or data model.

10. Exercise 6: Grouping Dates by Month

When a source dataset contains valid Excel dates, a PivotTable can group dates by month, quarter, or year, depending on the data and grouping options.

Steps
  1. Place Date in the Rows area.
  2. Place Sales in the Values area.
  3. Right-click a date value in the PivotTable.
  4. Select Group.
  5. Choose Months and, if needed, Years.
  6. Confirm the grouping and review the summarized values.

Date grouping is especially useful for monthly and yearly sales reports. If the Group option is unavailable, check for text dates, blank or invalid date values, or source and PivotTable restrictions.

11. Refreshing a PivotTable

A PivotTable may not automatically include new records added outside its original source range. Refreshing updates the report using its current source data.

Refresh Steps
  1. Add or update records in the source dataset.
  2. Click inside the PivotTable.
  3. Select PivotTable Analyze → Refresh, or right-click and choose Refresh.
  4. Check the updated totals and categories.
Best practice: Convert a growing source dataset into an Excel Table before creating the PivotTable. This makes it easier for the source range to expand when new rows are added. Refresh the PivotTable after adding records.

12. Common PivotTable Mistakes

Problem Possible Cause Solution
Values show Count instead of Sum Excel may be treating source values as text or the field contains nonnumeric entries. Check and correct the source data, then refresh.
New records are missing The PivotTable source range may not include the new rows. Update the source range or use an Excel Table.
Dates cannot be grouped Dates may be stored as text or include invalid values. Correct date values and refresh.
Duplicate-looking categories Inconsistent spaces, spelling, or text formatting. Clean and standardize source values.
Unexpected Grand Total Incorrect source records, duplicate rows, or unsuitable aggregation. Verify the source data and value-field settings.
Filter does not affect another report PivotTables may not share a compatible cache or connection. Check Report Connections and the source model.

13. Practice Assignments

Use the sample SalesData worksheet to complete these tasks. Create a separate worksheet for your PivotTable reports.

  1. Create a PivotTable showing total sales by region.
  2. Create a PivotTable showing total sales by product.
  3. Compare product sales across regions using Rows and Columns.
  4. Count the number of orders for each region.
  5. Calculate average sales per product.
  6. Sort products from highest to lowest total sales.
  7. Filter the report to display only West region orders.
  8. Insert a slicer for Region and test its filtering behaviour.
  9. Group valid dates by month in a dataset covering multiple months.
  10. Add a new source record, refresh the PivotTable, and verify the updated totals.
Expected outcome: You should be able to prepare regional, product-wise, cross-category, and date-based summaries, then verify each report against the underlying data.

14. Important Points

  • Use clean source data with unique column headings.
  • Select Rows, Columns, Values, and Filters based on the report requirement.
  • Confirm whether numeric values should use Sum, Count, or Average.
  • Format currency and date values for readability.
  • Refresh PivotTables after source data changes.
  • Use slicers and filters to explore selected categories.
  • Check the source range when new records are not included.
  • Verify Grand Totals and category summaries against the source data.

15. Summary

In this module, you learned how to create PivotTables, organize fields into Rows, Columns, Values, and Filters, and summarize business data using Sum, Count, and Average.

You also practised regional and product-wise analysis, cross-category comparisons, filtering, date grouping, refreshing reports, and troubleshooting common problems.

These skills help transform detailed source records into structured reports and provide a foundation for interactive Excel dashboards and automated reporting solutions.