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.
How a PivotTable Works
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 |
3. How to Create a PivotTable
Follow these steps in Excel:
Select the Source Data
Click inside the dataset or select the complete range, including the column headings.
Insert a PivotTable
Select Insert → PivotTable. Confirm the source range and choose where to place the PivotTable.
Choose the Fields
Drag fields into Rows, Columns, Values, or Filters depending on the report requirement.
Configure the Values
Choose Sum, Count, Average, or another suitable summary calculation.
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
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.
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 |
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
- Click inside the PivotTable.
- Drag Region into the Filters area.
- Open the filter dropdown above the report.
- Select West to display only the West region's results.
- Clear the filter to display all regions again.
Using a Slicer
- Select the PivotTable.
- Open the PivotTable Analyze tab.
- Select Insert Slicer.
- Choose Region or Product.
- Click a slicer button to filter the report.
Filtering a PivotTable
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
- Place Date in the Rows area.
- Place Sales in the Values area.
- Right-click a date value in the PivotTable.
- Select Group.
- Choose Months and, if needed, Years.
- 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
- Add or update records in the source dataset.
- Click inside the PivotTable.
- Select PivotTable Analyze → Refresh, or right-click and choose Refresh.
- Check the updated totals and categories.
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.
- Create a PivotTable showing total sales by region.
- Create a PivotTable showing total sales by product.
- Compare product sales across regions using Rows and Columns.
- Count the number of orders for each region.
- Calculate average sales per product.
- Sort products from highest to lowest total sales.
- Filter the report to display only West region orders.
- Insert a slicer for Region and test its filtering behaviour.
- Group valid dates by month in a dataset covering multiple months.
- Add a new source record, refresh the PivotTable, and verify the updated totals.
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.