AI Reading

Quick summary of this article

Preparing the AI summary…

PivotTables and PivotCharts with Practical Examples

PivotTables are among Excel’s most powerful reporting tools. They summarize thousands of transaction rows without requiring complex formulas. Users can reorganize reports by dragging fields, apply filters and calculate totals, averages, percentages and rankings.

In this chapter, you will learn how to create and format PivotTables, group dates and numbers, change Value Field Settings, use Show Values As calculations, create calculated fields, add slicers and timelines and design interactive PivotCharts.

Learning Objectives

After completing this chapter, you should be able to:

Prepare a clean PivotTable source dataset.
Create a PivotTable from an Excel Table.
Understand Rows, Columns, Values and Filters.
Summarize data using SUM, COUNT and AVERAGE.
Group dates by months, quarters and years.
Group numerical values into ranges.
Display percentages, differences and running totals.
Create calculated fields.
Add slicers and timelines.
Connect one slicer to multiple PivotTables.
Create interactive PivotCharts.
Build a management sales-summary report.

1. What Is a PivotTable?

A PivotTable is an interactive summary report created from a structured dataset. It allows you to rearrange and analyze information without changing the original data.

Questions a PivotTable Can Answer

What are the total sales by region?
Which salesperson generated the highest revenue?
Which products are selling the most?
How many orders were completed or cancelled?
What is the average order value?
How are sales changing month by month?
What percentage of total sales comes from each region?
Which customers or products contribute the most revenue?

2. PivotTable Source Data Requirements

A PivotTable is only as reliable as its source data. Prepare the dataset carefully before building the report.

Source Data Rules

Every column must have a unique heading.
Avoid blank column headings.
Avoid completely blank rows and columns.
Store one record per row.
Store one type of information per column.
Do not merge cells inside the source data.
Use consistent data types.
Store dates as valid Excel dates.
Store numerical values as numbers, not text.
Avoid manual subtotal and grand-total rows.
Convert the source range into an Excel Table.

3. Create the Sales Dataset

Create a worksheet named Sales_Data and enter the following sample records:

OrderID	OrderDate	Salesperson	Region	Product	Category	Quantity	UnitPrice	TotalSales	Cost	Status
ORD-1001	05-Jan-2026	Rahul	East	Laptop	Computer	2	45000	90000	76000	Completed
ORD-1002	12-Jan-2026	Priya	North	Monitor	Computer	4	12000	48000	39000	Completed
ORD-1003	22-Jan-2026	Imran	West	Keyboard	Accessories	10	1500	15000	9500	Pending
ORD-1004	08-Feb-2026	Neha	South	Printer	Office Equipment	3	18500	55500	43000	Completed
ORD-1005	14-Feb-2026	Rahul	East	Mouse	Accessories	15	800	12000	7500	Completed
ORD-1006	27-Feb-2026	Priya	North	Laptop	Computer	1	45000	45000	38000	Pending
ORD-1007	03-Mar-2026	Imran	West	Monitor	Computer	5	12000	60000	49000	Completed
ORD-1008	17-Mar-2026	Neha	South	Keyboard	Accessories	8	1500	12000	7600	Cancelled
ORD-1009	06-Apr-2026	Rahul	East	Printer	Office Equipment	2	18500	37000	29000	Completed
ORD-1010	19-Apr-2026	Priya	North	Mouse	Accessories	20	800	16000	10000	Completed
ORD-1011	09-May-2026	Imran	West	Laptop	Computer	3	45000	135000	114000	Completed
ORD-1012	21-May-2026	Neha	South	Monitor	Computer	6	12000	72000	59000	Pending

Add more records for meaningful analysis. Convert the complete range into an Excel Table and name it SalesData.

4. Creating Your First PivotTable

Click any cell inside the SalesData table.
Go to Insert → PivotTable.
Confirm the Table/Range.
Select New Worksheet.
Click OK.
Rename the new worksheet Sales_Pivot.

Excel displays a blank PivotTable area and the PivotTable Fields panel.

5. Understanding the PivotTable Field Areas

Area	Purpose	Example fields
Rows	Creates categories vertically	Region, Product, Salesperson
Columns	Creates categories horizontally	Status, Year, Quarter
Values	Performs calculations	Sum of Sales, Count of Orders
Filters	Filters the complete report	Category, Region, Status

6. Create a Region-Wise Sales Report

Field Arrangement


Rows: Region
Values: TotalSales

Excel calculates the total sales for every region.

Rename the Value Heading

Right-click any number in the Values area.
Select Value Field Settings.
Enter Total Sales in Custom Name.
Click Number Format.
Apply Currency or Accounting format.

7. Add Multiple Row Fields

Region and Product Report


Rows:
1. Region
2. Product

Values:
Sum of TotalSales

Products appear inside their respective regions. You can expand or collapse the groups using the plus and minus buttons.

Change the Row Hierarchy

Drag Product above Region to display regions inside each product. PivotTables allow reports to be reorganized without editing the source data.

8. Add a Column Field

Sales by Region and Status


Rows: Region
Columns: Status
Values: Sum of TotalSales

The report shows Completed, Pending and Cancelled sales in separate columns.

Sales by Product and Region


Rows: Product
Columns: Region
Values: Sum of TotalSales

9. Add a Report Filter

Filter by Category


Rows: Region
Values: Sum of TotalSales
Filters: Category

Use the filter above the PivotTable to display Computer, Accessories or Office Equipment data.

Add More Than One Filter


Filters:
1. Category
2. Status
3. Salesperson

10. Understanding Value Field Settings

Excel automatically selects a calculation based on the source field. Numerical fields usually use SUM, while text fields commonly use COUNT.

Available Summary Calculations

Sum
Count
Average
Max
Min
Product
Count Numbers
Standard Deviation
Variance

Change Sum to Average

Right-click a TotalSales value.
Select Summarize Values By.
Select Average.

Display Total and Average Together

Drag TotalSales into the Values area twice. Configure one field as Sum and the other as Average.


Values:
1. Sum of TotalSales
2. Average of TotalSales

11. Count Orders in a PivotTable

Count Order IDs by Region


Rows: Region
Values: Count of OrderID

Because OrderID contains text, Excel normally summarizes it using Count.

Count Orders by Status


Rows: Status
Values: Count of OrderID

12. Distinct Count in a PivotTable

A normal Count includes every record. Distinct Count counts unique values, such as unique customers or unique products.

Create a PivotTable with Distinct Count

Insert a new PivotTable.
Select Add this data to the Data Model.
Add Customer ID or Product to the Values area.
Open Value Field Settings.
Select Distinct Count.

Distinct Count may not appear unless the source was added to the Data Model.

13. Sorting PivotTable Values

Sort Regions by Sales

Right-click a value in the Total Sales column.
Select Sort.
Select Largest to Smallest.

Sort Labels Alphabetically

Right-click a Region label and select Sort A to Z.

14. Applying Value Filters

Show Regions with Sales Greater Than ₹100,000

Open the Region Row Labels filter.
Select Value Filters → Greater Than.
Select Sum of TotalSales.
Enter 100000.
Click OK.

Display the Top Five Products

Open the Product Row Labels filter.
Select Value Filters → Top 10.
Change 10 to 5.
Select Sum of TotalSales.
Click OK.

15. Grouping Dates in a PivotTable

Valid Excel dates can be grouped into months, quarters and years.

Create a Monthly Sales Report

Drag OrderDate to Rows.
Drag TotalSales to Values.
Right-click any date in the PivotTable.
Select Group.
Select Months.
Click OK.

Group by Years and Months

Select both Years and Months in the Grouping dialog.


Rows:
1. Years
2. Months

Values:
Sum of TotalSales

Group by Quarters

Select Years and Quarters to compare quarterly performance across years.

Why Date Grouping May Fail

The source contains blank dates.
One or more dates are stored as text.
The date column contains an error.
The source range includes invalid records.

16. Grouping Numerical Values

Numerical fields can be grouped into ranges such as sales bands or age groups.

Create Sales Bands

Drag TotalSales to Rows.
Drag OrderID to Values and summarize by Count.
Right-click a sales value.
Select Group.
Set Starting At to 0.
Set Ending At to an appropriate maximum.
Set By to 25000.

Example Result


0–24,999
25,000–49,999
50,000–74,999
75,000–99,999
100,000–124,999

17. Understanding Show Values As

Show Values As changes how a summarized value is displayed without changing the underlying calculation.

Common Show Values As Options

% of Grand Total
% of Column Total
% of Row Total
% of Parent Row Total
Difference From
% Difference From
Running Total In
% Running Total In
Rank Largest to Smallest
Rank Smallest to Largest

18. Display Percentage of Grand Total

Region Contribution Percentage

Drag TotalSales to Values twice.
Keep the first field as Sum of TotalSales.
Open Value Field Settings for the second field.
Select Show Values As.
Select % of Grand Total.
Rename it Sales Contribution %.

Example Layout


Rows: Region

Values:
1. Total Sales
2. Sales Contribution %

19. Display Percentage of Row or Column Total

Region and Product Contribution


Rows: Region
Columns: Product
Values: Sum of TotalSales

Use % of Row Total to show each product’s contribution within a region.

Use % of Column Total to show each region’s contribution to a product.

20. Calculate Difference from Previous Month

Place grouped Months in Rows.
Add TotalSales to Values twice.
Keep the first field as Total Sales.
For the second field, select Show Values As → Difference From.
Select Months as the Base Field.
Select Previous as the Base Item.

Calculate Percentage Change

Select % Difference From instead of Difference From.

21. Create a Running Total

Cumulative Monthly Sales

Place Months in Rows.
Add TotalSales to Values twice.
Open the second value field.
Select Show Values As → Running Total In.
Select Month as the Base Field.

The result shows accumulated sales from the first month to the current month.

22. Rank Salespeople

Rank from Highest to Lowest Sales

Place Salesperson in Rows.
Add TotalSales to Values twice.
Keep the first field as Total Sales.
For the second field, select Show Values As → Rank Largest to Smallest.
Select Salesperson as the Base Field.

23. Creating a Calculated Field

A calculated field performs a calculation using other source fields within a traditional PivotTable.

Create a Profit Calculated Field

Click inside the PivotTable.
Go to PivotTable Analyze.
Select Fields, Items & Sets → Calculated Field.
Enter Profit as the name.
Enter the following formula:

=TotalSales-Cost

Click Add and then OK.

Create a Profit Percentage

=(TotalSales-Cost)/TotalSales

Format the result as a percentage.

Important Limitation

Traditional PivotTable calculated fields operate on aggregated field values and may not produce the expected result for every row-level calculation. For more advanced models, use Power Pivot measures and DAX.

24. PivotTable Design and Report Layout

Compact Form

Displays multiple row fields in one column. It saves space but is less suitable for exporting a flat report.

Outline Form

Displays each row field in a separate column with hierarchical indentation.

Tabular Form

Displays each row field in a separate column. It is usually the best format for exporting and further analysis.

Change the Report Layout

Click inside the PivotTable.
Go to Design → Report Layout.
Select Compact, Outline or Tabular Form.

Repeat Item Labels

Use Design → Report Layout → Repeat All Item Labels to repeat category names on every row.

25. Managing Subtotals and Grand Totals

Subtotals

Go to Design → Subtotals.
Select whether subtotals appear at the top, bottom or not at all.

Grand Totals

Go to Design → Grand Totals.
Select totals for rows, columns, both or neither.

Remove unnecessary subtotals when they make the report difficult to read.

26. Displaying Zero or Blank Values

Right-click inside the PivotTable.
Select PivotTable Options.
Open the Layout & Format tab.
Use For empty cells show.
Enter 0 or another required display value.

27. Refreshing a PivotTable

A PivotTable does not always update automatically when the source data changes.

Refresh One PivotTable

Right-click inside the PivotTable.
Select Refresh.

Refresh All Reports

Go to Data → Refresh All.

Refresh When the Workbook Opens

Open PivotTable Options.
Select the Data tab.
Enable Refresh data when opening the file.

28. Changing the Data Source

If the source is a normal fixed range, new records may not be included.

Click inside the PivotTable.
Go to PivotTable Analyze → Change Data Source.
Select the correct range or table.
Click OK.
Refresh the PivotTable.

Using an Excel Table as the source avoids most fixed-range problems because the table expands automatically.

29. Adding a Slicer

A slicer is a visual filter containing clickable buttons.

Add Region and Status Slicers

Click inside the PivotTable.
Go to PivotTable Analyze → Insert Slicer.
Select Region and Status.
Click OK.

Using a Slicer

Click one button to apply a filter.
Use Ctrl to select multiple items.
Use the Multi-Select button for several selections.
Use the Clear Filter button to remove the slicer filter.

30. Formatting a Slicer

Choose a consistent slicer style.
Change the number of columns.
Resize buttons for readability.
Align multiple slicers.
Use short and meaningful captions.
Avoid using too many slicers on one report.

31. Connecting One Slicer to Multiple PivotTables

A single slicer can control multiple PivotTables that share the same Pivot Cache or Data Model.

Select the slicer.
Go to the Slicer tab.
Select Report Connections or PivotTable Connections.
Select the PivotTables that should respond.
Click OK.

If a PivotTable does not appear in the connection list, it may have been created from a different source or Pivot Cache.

32. Adding a Timeline

A Timeline is a visual date filter designed for PivotTables.

Insert a Timeline

Click inside the PivotTable.
Go to PivotTable Analyze → Insert Timeline.
Select OrderDate.
Click OK.

Timeline Levels

Years
Quarters
Months
Days

Users can drag across the Timeline to select a date period.

33. Creating a PivotChart

Click inside the PivotTable.
Go to PivotTable Analyze → PivotChart.
Select the required chart type.
Click OK.

Recommended PivotChart Types

Analysis	Recommended chart
Sales by region	Clustered Column or Bar
Monthly sales trend	Line Chart
Product comparison	Bar Chart
Completed vs Pending orders	Column or Doughnut Chart
Sales and profit	Combo Chart

34. Create a Monthly Sales PivotChart

PivotTable Fields


Rows: OrderDate grouped by Months
Values: Sum of TotalSales

Chart Steps

Insert a Line PivotChart.
Change the title to Monthly Sales Trend.
Apply a professional colour.
Format the vertical axis as currency.
Remove unnecessary field buttons.
Add a Timeline for OrderDate.

35. Hiding PivotChart Field Buttons

Select the PivotChart.
Go to PivotChart Analyze.
Select Field Buttons → Hide All.

Slicers and Timelines can provide a cleaner filtering experience.

36. Create a PivotTable Dashboard

Create separate PivotTables for the following KPIs and charts:

Suggested KPI Cards

Total Sales
Total Profit
Total Orders
Average Order Value
Completed Orders
Unique Products

Suggested PivotCharts

Monthly Sales Trend
Sales by Region
Top Five Products
Sales by Salesperson
Orders by Status

Suggested Filters

Region slicer
Category slicer
Salesperson slicer
Status slicer
Order Date timeline

37. Extracting a PivotTable Value with GETPIVOTDATA

GETPIVOTDATA retrieves summarized data from a PivotTable. Excel may create it automatically when you reference a PivotTable value.

Example

=GETPIVOTDATA(
"Total Sales",
$A$3,
"Region",
"East"
)

Dynamic Region Example

Suppose the selected region is stored in B2:

=GETPIVOTDATA(
"Total Sales",
$A$3,
"Region",
B2
)

The second argument must reference a cell inside the PivotTable.

38. Handling Deleted Source Items

Old field items may remain visible in PivotTable filters after they have been deleted from the source data.

Open PivotTable Options.
Select the Data tab.
Set Number of items to retain per field to None.
Refresh the PivotTable.

39. Common PivotTable Problems

PivotTable Shows Count Instead of Sum

The source column may contain text, blanks or errors. Clean the column and ensure every sales value is numerical.

New Rows Are Missing

The source may be a fixed range. Convert it into an Excel Table or change the data source.

Dates Cannot Be Grouped

The date column may contain blanks, text or errors. Correct the source data and refresh the PivotTable.

Slicer Does Not Control Every PivotTable

The PivotTables may use different sources or Pivot Caches. Create them from the same source or Data Model.

Number Formatting Disappears

Apply formatting through Value Field Settings rather than directly formatting individual cells.

Calculated Field Gives Unexpected Results

Traditional calculated fields use aggregated data. Use a source-data calculation or a DAX measure when row-level logic is required.

40. PivotTable Best Practices

Use an Excel Table as the source.
Use clear and unique column headings.
Remove blank rows and invalid values.
Refresh reports after changing source data.
Apply number formats through Value Field Settings.
Rename value fields clearly.
Use Tabular Form for export-friendly reports.
Remove unnecessary subtotals and grand totals.
Use slicers only for important dimensions.
Connect slicers to all relevant PivotTables.
Choose charts appropriate for the analysis.
Avoid excessive colours and effects.
Keep supporting PivotTables on a separate worksheet.
Use Power Pivot for advanced multi-table calculations.

41. Practical Assignment: Interactive Sales Dashboard

Build an interactive management sales dashboard using at least 500 transaction records.

Required Worksheets

Sales_Data
Pivot_Reports
Dashboard

Required PivotTables

Sales by Region
Sales by Product
Sales by Salesperson
Monthly Sales Trend
Orders by Status
Top Five Products
Sales and Profit by Category

Required Calculations

Total Sales
Total Cost
Total Profit
Average Order Value
Order Count
Sales Contribution Percentage
Month-over-Month Change
Running Total Sales
Salesperson Rank

Dashboard Requirements

Create at least four KPI cards.
Create a monthly Line PivotChart.
Create a regional Column PivotChart.
Create a Top Five Products Bar Chart.
Create an Orders by Status chart.
Add Region, Category and Salesperson slicers.
Add an Order Date timeline.
Connect filters to every relevant PivotTable.
Apply consistent number formats.
Use a clean professional dashboard layout.
Test every slicer and timeline selection.
Refresh the complete report after adding new data.

42. Practice Exercises

Exercise 1: Employee Salary Summary

Create a PivotTable showing total, average, highest and lowest salary by Department.

Exercise 2: Student Performance Report

Summarize average marks by Class and Subject. Add a Gender slicer and an Admission Year timeline.

Exercise 3: Inventory Report

Create a PivotTable showing stock quantity and inventory value by Category and Product.

Exercise 4: Invoice Ageing Summary

Summarize outstanding amounts by Customer and Ageing Group. Display each group as a percentage of total outstanding value.

Exercise 5: Salesperson Ranking

Create a PivotTable that displays Total Sales and Rank Largest to Smallest for every salesperson.

43. Chapter Quiz

What is the main purpose of a PivotTable?
Why should source data have unique column headings?
What is the purpose of the Rows area?
What is the purpose of the Values area?
How can dates be grouped by month and year?
What is the difference between Summarize Values By and Show Values As?
How can a percentage of grand total be displayed?
What is the purpose of a calculated field?
What is the difference between a slicer and a timeline?
How can one slicer control multiple PivotTables?
Why might Distinct Count be unavailable?
Why should a PivotTable be refreshed?

44. Frequently Asked Questions

Does a PivotTable change the original data?

No. It summarizes and displays source data without changing the original records.

Why is Excel counting my sales instead of adding them?

The sales column probably contains text, blanks or errors. Convert every valid sales value to a number and refresh the PivotTable.

Does a PivotTable update automatically?

Not always. Use Refresh or Refresh All. You can also enable refresh when opening the workbook.

Can one slicer control multiple PivotTables?

Yes, when the PivotTables share the same source, Pivot Cache or Data Model. Use Report Connections to select them.

What is the difference between a PivotChart and a normal chart?

A PivotChart is connected to a PivotTable and responds to PivotTable filters, slicers and timelines.

Should calculated fields be used for every PivotTable calculation?

No. Traditional calculated fields have limitations. Use source-data formulas or Power Pivot DAX measures for advanced calculations.

Conclusion

PivotTables convert large datasets into flexible management reports. Fields can be placed into Rows, Columns, Values and Filters to analyze sales, orders, employees, inventory and financial information without writing complex formulas.

Date grouping, numerical grouping, Show Values As calculations, slicers, timelines and PivotCharts make the reports more interactive. By connecting several PivotTables to shared filters, you can build a professional Excel dashboard.

In the next chapter, we will learn Power Query, including importing, cleaning, transforming, appending, merging and refreshing data from Excel, CSV and folder sources.

Advanced Excel Chapter 9: PivotTables and PivotCharts