Course: Advanced Excel
Topic: Combining Pivot Tables and Charts in Reports
Course Link: Advanced Excel Course
Demo Link: Register for a Course Demo
Website: JustAcademy
Microsoft Excel provides PivotTables and PivotCharts to help users summarize large datasets and present business information visually. A PivotTable organizes raw data into meaningful summaries, while a PivotChart displays those summaries through graphical representations.
Combining PivotTables and charts allows analysts to build reports that show sales trends, product performance, regional comparisons, revenue distribution, and business KPIs. Users can filter or rearrange the summarized information to investigate different aspects of the same dataset.
For example, a sales manager can use a PivotTable to summarize sales by region and a PivotChart to compare those regions visually. When the PivotTable is filtered, the associated PivotChart reflects the filtered results.
A PivotTable is an Excel feature that summarizes and analyzes data without requiring separate formulas for every category. It allows users to rearrange fields, group records, calculate totals, compare categories, and filter information.
| Component | Purpose | Example |
|---|---|---|
| Rows | Organizes categories vertically. | Region or Product |
| Columns | Displays categories horizontally. | Month or Sales Channel |
| Values | Calculates summaries of numerical fields. | Sum of Sales Amount |
| Filters | Restricts the report to selected criteria. | Year or Department |
A PivotChart is a chart associated with a PivotTable or its underlying data source. It presents summarized information visually and supports interactive analysis through compatible chart filters, PivotTable field changes, and slicers.
For example, a PivotTable may display total sales for five regions, while a PivotChart presents the same figures as vertical columns or horizontal bars.
| Chart Type | Best Use | Business Example |
|---|---|---|
| Column Chart | Comparing categories | Sales by region |
| Bar Chart | Ranking categories | Revenue by product |
| Line Chart | Showing trends over time | Monthly sales performance |
| Pie Chart | Showing simple part-to-whole relationships | Revenue contribution by a few categories |
| Area Chart | Showing changes in values over time | Revenue growth over several periods |
| Stacked Column Chart | Comparing totals and category contributions | Sales by region and product category |
Available chart types depend on the Excel version and PivotChart limitations. Some standard chart types, including scatter, stock, and bubble charts, are not supported as PivotCharts in many Excel versions.
| Feature | PivotTable | PivotChart |
|---|---|---|
| Presentation | Displays summarized information in rows and columns. | Displays summarized information graphically. |
| Main purpose | Detailed analysis and calculations. | Visual comparison and trend identification. |
| Interaction | Supports field arrangement, filtering, and grouping. | Supports visual filtering and linked report interaction. |
| Readability | Useful for reviewing exact numbers. | Useful for understanding patterns quickly. |
| Best application | Detailed summary reports. | Dashboards and visual reports. |
Accurate PivotTables and charts require a clean, consistent dataset. The source data should use a tabular structure with one header row and one record per row.
| Order ID | Date | Region | Product | Salesperson | Quantity | Sales Amount |
|---|---|---|---|---|---|---|
| ORD101 | 01-Jan-2026 | West | Laptop | Amit | 2 | 100000 |
| ORD102 | 03-Jan-2026 | North | Monitor | Priya | 3 | 36000 |
| ORD103 | 05-Jan-2026 | South | Keyboard | Rahul | 5 | 7500 |
| ORD104 | 08-Jan-2026 | West | Printer | Neha | 2 | 36000 |
| ORD105 | 12-Jan-2026 | North | Laptop | Amit | 1 | 50000 |
| ORD106 | 15-Jan-2026 | South | Monitor | Priya | 4 | 48000 |
Note: The dataset is illustrative and is intended for practice.
Using an Excel Table as the source helps the source range expand when new records are added. The PivotTable still needs to be refreshed before its summaries and associated charts reflect the new records.
To calculate total sales by region, arrange the fields as follows:
Excel creates a summary of sales for the West, North, and South regions. Format the values as currency and use a meaningful heading for the report.
| Report Requirement | Rows | Columns | Values |
|---|---|---|---|
| Sales by product | Product | None | Sum of Sales Amount |
| Sales by salesperson | Salesperson | None | Sum of Sales Amount |
| Monthly sales | Date grouped by Month | None | Sum of Sales Amount |
| Regional product analysis | Region | Product | Sum of Sales Amount |
| Quantity by product | Product | None | Sum of Quantity |
After summarizing data with a PivotTable, create a PivotChart to visualize the report. The chart helps readers recognize differences, patterns, and trends more quickly than a table alone.
If the PivotTable displays total sales by region, insert a clustered column chart or a horizontal bar chart. The chart makes it easy to identify the region generating the highest revenue.
When a field is rearranged in the associated PivotTable, the PivotChart generally reflects the updated field arrangement and summarized data.
A professional report often includes several PivotTables and charts because one visualization rarely answers every business question. Combining them helps users understand overall performance as well as the details behind it.
Multiple PivotTables can use the same source data. When compatible PivotTables share a PivotCache, they may also be able to share slicer controls. Confirm compatibility before designing the dashboard around shared filters.
| PivotTable Summary | Recommended Chart | Reason |
|---|---|---|
| Sales by region | Bar or Column Chart | Compares regional performance. |
| Sales by product | Sorted Bar Chart | Highlights top-performing products. |
| Monthly revenue | Line Chart | Shows changes over time. |
| Sales by product and region | Clustered or Stacked Column Chart | Compares categories across regions. |
| Revenue contribution | Bar or Doughnut Chart | Displays relative contribution. |
| Actual versus target | Clustered Column Chart | Shows the gap between actual and planned results. |
Use a chart that supports the business question. Avoid adding several charts that repeat the same information without providing additional insight.
A slicer is a visual filtering control that allows users to select specific categories with buttons. It can make reports easier to explore without requiring users to open traditional filter menus.
If a PivotTable does not appear in the Report Connections list, check whether it uses a compatible source and PivotCache. A slicer does not automatically control every PivotTable in a workbook.
A timeline is a visual date-filtering control for supported PivotTables. It allows users to select periods such as months, quarters, and years.
Timelines are particularly useful for monthly sales analysis, quarterly comparisons, annual performance reports, and seasonal trend analysis. The timeline will only work with supported PivotTables and valid date fields.
After combining PivotTables and charts, arrange the report so users can understand the most important findings quickly.
| Section | Recommended Content |
|---|---|
| Header | Report title, reporting period, and last refresh date. |
| Top section | Key metrics such as total sales, orders, and units sold. |
| Middle-left | Monthly sales trend chart. |
| Middle-right | Sales by region chart. |
| Bottom-left | Product performance chart. |
| Bottom-right | Salesperson performance chart. |
| Filter area | Region and product slicers and a date timeline. |
| Supporting area | Detailed PivotTables or a summary of important findings. |
PivotTables and PivotCharts may not automatically reflect changes to the underlying source data. Refreshing the report is an important maintenance step.
When a report uses a fixed source range, newly added records outside that range may not be included. An Excel Table is generally a convenient source because it expands as records are added, although the PivotTable still needs refreshing.
| Problem | Possible Cause | Solution |
|---|---|---|
| PivotChart does not show new data | The PivotTable has not been refreshed or its source range is incomplete. | Check the source range and refresh the PivotTable. |
| Values appear as Count instead of Sum | Numbers may be stored as text or the field is configured incorrectly. | Convert values to numbers and set the Values field to Sum. |
| Slicer does not filter all charts | Some PivotTables are not connected or use incompatible sources. | Check Report Connections and PivotCache compatibility. |
| Dates cannot be grouped | The date column contains text, blanks, or invalid dates. | Clean the date field and refresh the PivotTable. |
| Chart is difficult to read | Too many categories, labels, or colors are displayed. | Choose a more suitable chart and simplify the formatting. |
| PivotChart formatting changes after refresh | Some formatting, such as certain data labels or trendlines, may not be preserved. | Review formatting after refresh and test the behavior in the target Excel version. |
| Duplicate values inflate totals | Duplicate source records or an incorrect data model may be present. | Validate the source data and review the intended aggregation. |
Project Objective: Create an Excel report combining PivotTables and PivotCharts to analyze sales across regions, products, months, and salespeople.
The finished report should enable a manager to compare regional performance, identify high-performing products, monitor monthly sales, and evaluate salesperson results using a combination of summarized tables and interactive charts.
Answer: PivotTables summarize data, while PivotCharts visualize the summaries. Combining them makes reports more informative and easier to analyze.
Answer: A standard chart is generally linked to a worksheet range, while a PivotChart is based on a PivotTable's summarized data source and works with its interactive filtering and field arrangements.
Answer: Yes, if the PivotTables are compatible with the slicer's connections. The intended PivotTables must be selected in Report Connections or PivotTable Connections.
Answer: Excel Tables provide structured references, consistent headers, and automatic expansion when new records are added.
Answer: Create a PivotTable using Date in Rows and Sales Amount in Values. Group valid dates by month if necessary, then insert a line PivotChart.
Answer: The PivotTable may need refreshing, or the new records may fall outside the source range.
Answer: A timeline provides visual filtering by date and allows users to select time periods such as months, quarters, or years.
Answer: A bar or column chart is generally suitable because it makes differences between regions easy to compare.
Answer: Use descriptive titles, appropriate chart types, readable labels, consistent colors, clear number formats, and a logical layout.
Answer: Validate the source data, refresh PivotTables, test filters and slicers, verify totals, review chart formatting, and confirm that the reporting period is correct.
Combining PivotTables and charts in Excel is an important Advanced Excel skill for business reporting and data analysis. PivotTables organize raw data into useful summaries, while PivotCharts make comparisons, patterns, and trends easier to understand.
By adding appropriate charts, slicers, timelines, and a well-organized layout, users can develop interactive reports that support faster analysis and better business decisions. Always validate the source data, confirm the accuracy of the summaries, and test the report's interactive features before distributing it.
Explore the JustAcademy Advanced Excel Course to develop practical skills in PivotTables, PivotCharts, reporting, data analysis, and dashboard creation.
Interested in learning more? Register for a course demo to explore the course.
Visit the JustAcademy website for additional learning resources.
