Creating Pivot Charts from PivotTable Data

Course: Advanced Excel

Topic: Creating Pivot Charts from PivotTable Data

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Official Website: JustAcademy

1. Introduction to Pivot Charts

A PivotChart is an interactive chart in Microsoft Excel that visually represents summarized data from a PivotTable or an associated data source. It helps users understand business information through charts instead of examining large tables of numbers.

PivotCharts are especially useful when working with large datasets containing sales transactions, expenses, employee performance, inventory, customer orders, and financial records. They allow users to explore trends, compare categories, identify patterns, and filter information dynamically.

For example, a business may use a PivotTable to summarize monthly sales by region and then create a PivotChart to compare regional performance. When users filter the report to a specific region or product, the chart updates to reflect the selected information.

Why PivotCharts Are Important

• Convert summarized data into visual representations.
• Make comparisons between categories easier.
• Identify trends and patterns in business performance.
• Support interactive filtering and exploration.
• Improve management reports and dashboards.
• Reduce the effort required to interpret large tables.
• Help communicate business insights to stakeholders.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the relationship between PivotTables and PivotCharts.
• Create a PivotChart from an existing PivotTable.
• Select suitable chart types for different business requirements.
• Configure chart fields and data series.
• Apply filters, slicers, and timelines where supported.
• Customize chart titles, legends, labels, and formatting.
• Refresh PivotCharts when source data changes.
• Troubleshoot common PivotChart problems.
• Create an interactive business reporting dashboard.

3. Understanding the Relationship Between PivotTables and PivotCharts

A PivotTable summarizes source data into meaningful categories, while a PivotChart presents that summary visually. Both are connected, allowing users to explore information from different perspectives.

A PivotChart is linked to its associated PivotTable and source data. Changes to the field layout or filters can affect the chart and its related summary. Read Microsoft's PivotTable and PivotChart overview.

4. Preparing Data for a PivotChart

Before creating a PivotChart, prepare the source data carefully. Clean, structured data helps Excel generate accurate summaries and charts.

Example Sales Dataset

Data Preparation Guidelines

• Use a single header row with clear, unique column names.
• Keep each type of information in a separate column.
• Remove unnecessary blank rows and columns.
• Ensure sales amounts are numeric values.
• Use consistent date formats for date-based analysis.
• Check for missing values and duplicate records.
• Convert the dataset into an Excel Table using Ctrl+T when appropriate.

5. Creating a PivotTable Before Building a PivotChart

A PivotTable provides the summarized data that will be visualized in the chart.

Steps to Create a PivotTable

1. Select any cell in the source dataset.
2. Go to the Insert tab.
3. Select PivotTable.
4. Verify the source range or Excel Table name.
5. Choose New Worksheet or Existing Worksheet.
6. Click OK.
7. Arrange the fields in the PivotTable Fields pane.

Example Field Arrangement

• Rows: Month
• Columns: Region
• Values: Sum of Sales Amount
• Filters: Product

This arrangement summarizes sales by month and region while allowing users to filter the report by product.

6. Creating a PivotChart from an Existing PivotTable

Excel allows users to create a PivotChart directly from an existing PivotTable. The exact ribbon labels may vary slightly by Excel version and platform.

Step-by-Step Procedure

1. Open the workbook containing the PivotTable.
2. Click any cell inside the PivotTable.
3. Navigate to the Insert tab or the PivotTable Analyze tab, depending on the Excel version.
4. Select PivotChart if the command is available.
5. Choose a suitable chart type, such as Column, Bar, Line, or Pie.
6. Click OK to insert the chart.
7. Position and resize the chart as needed.
8. Review the chart categories, series, values, and legend.
9. Update the chart title and formatting.

The resulting chart is connected to the PivotTable. You can change the field arrangement or filters to analyze different aspects of the same dataset.

Alternative: In Excel versions or platforms where a direct PivotChart command is unavailable, create a PivotTable first, select it, and insert a supported chart through the Insert tab. The available chart types and features depend on the Excel version. Microsoft's PivotChart creation guide provides platform-specific instructions.

7. Choosing the Right PivotChart Type

The correct chart type depends on the purpose of the report and the nature of the data.

Some chart types, such as scatter, stock, and bubble charts, are not supported as PivotCharts in standard Excel implementations. If a specialized chart is required, consider creating a regular chart from a separate summary range.

8. Creating a Column PivotChart

A column PivotChart is useful for comparing values across categories. It is commonly used for sales reports, revenue summaries, regional performance, and expense analysis.

Steps

1. Create a PivotTable summarizing sales by month.
2. Select a cell inside the PivotTable.
3. Insert a PivotChart.
4. Choose a Clustered Column chart.
5. Set the chart title to Monthly Sales Performance.
6. Ensure months appear in chronological order.
7. Format the vertical axis as currency or the appropriate reporting unit.
8. Review the chart to identify high-performing and low-performing periods.

A column chart makes it easy to identify which months generated the highest sales and which months require additional investigation.

9. Creating a Line PivotChart for Trend Analysis

A line PivotChart is suitable for displaying trends over time. It is especially effective when a report contains daily, weekly, monthly, quarterly, or yearly summaries.

Steps

1. Create a PivotTable using a date or month field.
2. Add the required numeric measure to Values.
3. Insert a PivotChart and choose a Line chart.
4. Place the time period along the horizontal axis.
5. Use separate series to compare regions or product categories.
6. Add a descriptive title and suitable axis labels.
7. Check for missing periods and confirm chronological order.

For example, a line chart comparing monthly sales across several regions can help identify seasonal patterns and changes in business performance.

10. Creating a PivotChart for Regional Comparison

Regional comparison helps managers evaluate the performance of different geographical areas.

Suggested PivotTable Layout

• Rows: Region
• Columns: Product
• Values: Sum of Sales Amount

Insert a clustered bar or column PivotChart to compare products across regions.

Benefits

• Identify the strongest-performing region.
• Compare product demand across locations.
• Highlight regions requiring additional sales support.
• Evaluate whether sales are concentrated in a small number of regions.

11. Using the PivotChart Field List

The PivotChart Field List controls which fields appear in the chart and how they are organized. It provides a convenient way to change the chart's analysis without rebuilding it from scratch.

How to Change the Chart Fields

1. Select the PivotChart.
2. Open the PivotChart Field List if it is not already visible.
3. Move fields between the available areas.
4. Add or remove fields according to the reporting objective.
5. Review the chart after every major field change.

Changing fields in the chart also changes the layout of the associated PivotTable, so check both when working with a shared report.

12. Filtering PivotCharts

Filtering helps users focus on selected data without creating separate charts for every category.

Common Filtering Options

• Product Filter: Display data for a selected product.
• Region Filter: Analyze performance in a specific region.
• Date Filter: Focus on a particular period.
• Category Filter: Compare selected business categories.
• Value Filters: Focus on items meeting specified numerical conditions in the associated PivotTable.

Steps to Filter a PivotChart

1. Select the PivotChart.
2. Locate the chart's field buttons or filter pane, depending on the Excel version.
3. Open the filter for the required field.
4. Select the values to display.
5. Apply the filter.
6. Confirm that the chart and associated PivotTable show the expected results.

13. Using Slicers with PivotCharts

A slicer is a visual filtering control that lets users filter PivotTables and connected PivotCharts by clicking buttons. Slicers make dashboards easier to use because users do not need to navigate traditional drop-down filters.

Steps to Insert a Slicer

1. Select the PivotTable or its associated PivotChart.
2. Open the PivotTable Analyze or equivalent ribbon tab.
3. Select Insert Slicer.
4. Choose the fields for which interactive filters are needed.
5. Click OK.
6. Position the slicer near the chart.
7. Click slicer buttons to filter the report.

Example

A sales dashboard may contain slicers for Region, Product, and Year. Selecting North and Laptop filters the connected report to display laptop sales in the North region for the selected period.

If multiple PivotTables or PivotCharts need to respond to the same slicer, use Report Connections or PivotTable Connections where supported. The reports generally need compatible data sources and shared PivotTable caches or an appropriate common Data Model.

14. Using Timelines for Date-Based PivotCharts

A timeline is a visual date filter for supported PivotTables. It allows users to filter records by periods such as years, quarters, months, or days.

Steps to Insert a Timeline

1. Ensure the source data contains a valid date field.
2. Select a cell inside the PivotTable.
3. Open the PivotTable Analyze tab.
4. Select Insert Timeline, where available.
5. Choose the date field.
6. Click OK.
7. Select the desired period and adjust the timeline level.
8. Review the associated PivotChart.

Timelines are useful for analyzing seasonal sales, monthly expenses, quarterly revenue, and year-over-year performance. They require a suitable date field and supported PivotTable configuration.

15. Formatting and Customizing PivotCharts

Formatting improves readability and helps users focus on the most important information.

Important Formatting Elements

• Chart Title: Explain what the chart measures.
• Axis Titles: Identify categories and measurement units.
• Legend: Explain the meaning of different series.
• Data Labels: Display values when they improve interpretation.
• Number Formats: Use currency, percentages, or units consistently.
• Chart Style: Select a clean, professional layout.
• Chart Size: Ensure labels remain readable.
• Background: Avoid distracting effects and excessive decoration.

Example of a Clear Chart Title

Instead of using a generic title such as Sales, use a descriptive title such as Monthly Sales by Region – 2026.

Use meaningful labels and avoid displaying too many data labels when the chart already contains many categories. Formatting should make the chart easier to interpret rather than more decorative.

16. Refreshing PivotCharts When Data Changes

PivotCharts depend on their associated PivotTable and source data. When new transactions are added or existing values are updated, the report may need to be refreshed.

Steps to Refresh

1. Update the underlying source data.
2. Check that the source range includes the new records.
3. Select the associated PivotTable.
4. Choose Refresh or Refresh All from the Data or PivotTable ribbon options.
5. Review the PivotTable totals.
6. Check that the PivotChart reflects the updated results.

Using an Excel Table as the source makes it easier to include newly added records during refresh. Refreshing does not automatically fix a source range that excludes new data when a fixed range is used.

17. PivotCharts vs. Regular Charts

Choose a PivotChart when interactive filtering and changing field arrangements are important. Choose a regular chart when a specialized chart type or more direct control over individual plotted series is required.

18. Common Problems and Troubleshooting

19. Best Practices for Professional PivotCharts

• Start with clean and well-structured source data.
• Build a PivotTable with a clear analytical purpose before creating the chart.
• Select a chart type that matches the comparison or trend being analyzed.
• Use concise titles and descriptive axis labels.
• Keep colors consistent across related charts.
• Use slicers only for meaningful filtering options.
• Avoid unnecessary 3D effects and excessive decoration.
• Keep the number of displayed series manageable.
• Verify numerical summaries against the source data.
• Refresh the PivotTable after relevant source-data changes.
• Test all filters and slicers before sharing the report.
• Use readable fonts and accessible color combinations.

20. Practical Project: Interactive Sales Dashboard

Project Title: Creating a Regional Sales Dashboard with PivotCharts

Project Objective

Create an interactive Excel dashboard that summarizes sales by month, region, and product. The dashboard should help management compare performance and identify important trends.

Sample Dataset

Project Tasks

1. Enter the sample dataset into Excel.
2. Convert the data into an Excel Table.
3. Create a PivotTable showing sales by month and region.
4. Create a column PivotChart for monthly sales comparisons.
5. Create a line PivotChart to show the monthly trend.
6. Create a bar PivotChart to compare regional sales.
7. Add a slicer for Product.
8. Add another slicer for Region if appropriate.
9. Place the charts and filters on a dedicated dashboard worksheet.
10. Format chart titles, axes, legends, and currency values.
11. Refresh the PivotTables and test the filters.
12. Verify that the dashboard communicates accurate and useful insights.

Expected Outcome

The completed dashboard should provide an interactive visual summary of sales performance. Users should be able to compare regions, explore products, and understand monthly sales trends using the connected PivotCharts and filters.

21. Practice Exercises

1. Create a PivotChart showing total sales by product.
2. Build a column chart comparing monthly revenue.
3. Create a line chart displaying sales trends over several months.
4. Use a bar chart to compare sales across regions.
5. Insert a slicer to filter a PivotChart by product category.
6. Create a date-based PivotChart and filter it using a timeline where supported.
7. Change the field arrangement and observe how the chart updates.
8. Refresh the PivotTable after adding new transactions.
9. Format a PivotChart for inclusion in a management presentation.
10. Build a dashboard containing multiple PivotCharts and compatible slicers.

22. Interview Questions and Answers

Q1. What is a PivotChart in Excel?

Answer: A PivotChart is an interactive chart connected to a PivotTable or its underlying data source. It visually represents summarized information.

Q2. What is the relationship between a PivotTable and a PivotChart?

Answer: A PivotTable summarizes data, while a PivotChart visualizes it. Changes to the associated field arrangement and filters can affect both.

Q3. How do you create a PivotChart from an existing PivotTable?

Answer: Select a cell in the PivotTable, choose the PivotChart command where available, select a chart type, and confirm the insertion.

Q4. Which chart is suitable for displaying monthly sales trends?

Answer: A line chart is generally suitable for displaying trends over time, while a column chart is useful for comparing individual months.

Q5. What is a slicer?

Answer: A slicer is a visual filtering control that allows users to filter PivotTables and connected PivotCharts by clicking buttons.

Q6. What is a timeline in Excel?

Answer: A timeline is a visual date-filtering control that lets users filter supported PivotTables by periods such as years, quarters, months, and days.

Q7. Why is a PivotChart not showing new data?

Answer: The PivotTable may need to be refreshed, or the source range may not include the new records.

Q8. Can all chart types be used as PivotCharts?

Answer: No. Some chart types, including scatter, stock, and bubble charts in standard Excel implementations, are not supported as PivotCharts.

Q9. What is the difference between a PivotChart and a regular chart?

Answer: A PivotChart is connected to a PivotTable and supports interactive field-based analysis. A regular chart is linked directly to a worksheet range or table.

Q10. How can PivotCharts be used in business dashboards?

Answer: PivotCharts can display sales trends, financial summaries, regional comparisons, and KPI performance. Slicers and timelines make the dashboard interactive.

23. Quick Revision Checklist

• Understand the relationship between PivotTables and PivotCharts.
• Prepare clean, structured source data.
• Create a PivotTable and insert a supported chart.
• Select an appropriate chart type for the analysis.
• Configure fields, categories, series, and values.
• Use filters, slicers, and timelines where supported.
• Customize chart titles, labels, legends, and number formats.
• Refresh the report when source data changes.
• Troubleshoot missing data, sorting, and connection issues.
• Validate the dashboard before sharing it with stakeholders.

24. Conclusion

Creating PivotCharts from PivotTable data is an important Advanced Excel skill for turning numerical summaries into clear, interactive visual reports. PivotCharts make it easier to compare categories, monitor trends, and communicate business insights.

By combining PivotTables, suitable chart types, slicers, timelines, and consistent formatting, Excel users can build dashboards that support efficient business analysis and decision-making.

Learn more through the Advanced Excel Course at JustAcademy, register for a course demo, or visit the JustAcademy official website.