Welcome to your next lesson in the Advanced Excel module. In our last session, you mastered pivot tables, learning how to transform vast amounts of raw data into structured, insightful summaries. This is a crucial skill for any analyst, but summarizing data is only half the battle. The other half is communicating your findings effectively.
Today, we will build directly on your new pivot table skills by learning how to visualize those summaries with Pivot Charts. While a pivot table organizes numbers for analysis, a pivot chart turns those numbers into a compelling visual story, making it easier to spot trends, comparisons, and outliers. For a data or business analyst, being able to quickly create dynamic, interactive visuals is essential for exploring data and presenting findings to stakeholders. By the end of this lesson, you will be able to build and manipulate pivot charts to bring your summarized data to life.
1. The Power of a Pivot Chart: More Than a Pretty Picture
A pivot chart is not just a regular chart. Its defining feature is its direct link to a pivot table. The two are intrinsically connected. Any change you make to the pivot table—like adding a field, applying a filter, or rearranging data—is instantly reflected in the pivot chart, and vice versa. This creates a powerful, interactive tool for data exploration.
Think of it this way: a standard Excel chart is like a static photograph of your data. A pivot chart, on the other hand, is like a live video feed. It dynamically responds as you "pivot" the underlying data, allowing you to explore different angles of your analysis in real-time.
To see this concept in action, the following video introduces a common business scenario and demonstrates how a pivot table and pivot chart work together to solve a problem.
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
Watch the "Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)" video by Excel Campus - Jon.
Pay close attention to the introductory segment from the beginning of the tutorial. The instructor shows the end product—an interactive dashboard—and explains how pivot tables and pivot charts form the foundation for it. This will give you a clear sense of the "why" before we dive into the "how".
2. Creating Your First Pivot Chart
There are two primary ways to create a pivot chart, and the method you choose often depends on whether you're starting from scratch or already have a pivot table built.
Method 1: Creating a Pivot Chart and Pivot Table Simultaneously
If you are starting with your raw source data, you can create both the pivot table and the pivot chart in one step. This is the quickest way to start visualizing your data.
How to Make Pivot Chart in Excel
Watch the video "How to Make Pivot Chart in Excel" from Kevin Stratvert.
Watch the first three minutes of the video, covering the introduction, inserting a chart, and building the initial view. Introduction: The instructor explains what pivot charts are and the importance of having well-organized data, reinforcing concepts from our previous lesson. Inserting the Chart: Follow the steps to insert a pivot chart from a data range into a new worksheet. Building the Chart: Observe how dragging fields into the Axis (Categories) and Values areas of the PivotChart Fields pane immediately constructs both the pivot table and the pivot chart.
As you saw in the video, once you insert the pivot chart, you use the PivotChart Fields pane to build your visualization. This pane works almost identically to the one for pivot tables, but with slightly different names for the areas to reflect their visual role:
- Axis (Categories): Fields here become the labels on the horizontal or vertical axis (e.g., 'Project type' or 'Branch Office').
- Legend (Series): Fields here are used to break down the data into sub-categories, with each item getting a different color or pattern in the chart (e.g., 'Status' or 'Month').
- Values: The numeric field you want to measure and plot (e.g., 'Sum of Total revenue').
- Filters: Used for applying a high-level filter to the entire chart and table.
Method 2: Creating a Pivot Chart from an Existing Pivot Table
Often, you'll have already created a pivot table for analysis and then decide you want to visualize it. This is a very common workflow.
[PDF] Microsoft Excel Pivot Tables Essentials 2019 Workshop
This PDF guide from the University of Pittsburgh provides clear, numbered instructions for creating a pivot chart from an existing pivot table.
Please read the section "A. Create a PivotChart" on page 18. You can find it by searching for that heading. The steps are simple: follow the instructions to insert a chart based on a pivot table you've already built.
3. The Interactive Power of Pivot Charts
The true strength of a pivot chart lies in its interactivity. You can dynamically change the chart to explore data and answer new questions without ever having to rebuild it.
Manipulating Fields
Just as with a pivot table, you can drag fields between the different areas of the PivotChart Fields pane to instantly reconfigure your chart. For example, you can swap the fields on the Axis and Legend to see the data from a different perspective.
How to Make Pivot Chart in Excel
Return to the "How to Make Pivot Chart in Excel" video by Kevin Stratvert.
First, watch the section on modifying the chart. See how adding, removing, and reordering fields in the Axis area changes the level of detail and hierarchy in the chart. Then, skip ahead to see how to switch rows and columns. This demonstrates a powerful one-click method to swap the Axis and Legend fields, which is incredibly useful for finding the most effective view of your data.
Filtering Your View
You can filter a pivot chart in several ways:
- Using the interactive field buttons directly on the chart.
- Using the filter dropdowns in the PivotChart Fields pane.
- Placing a field in the Filters area for a report-level filter.
- Using Slicers for a more user-friendly, visual filtering experience (as we briefly saw in the previous lesson). Slicers control both the pivot table and the pivot chart simultaneously.
How to Make Pivot Chart in Excel
In the same Kevin Stratvert video, focus on the segments about filtering.
Watch from the filter demonstration to see how to use the filter dropdown in the fields pane to select specific items to display. Then, watch the section from on using the Filters area. This shows how to apply a filter to the entire chart using a field that isn't part of the Axis or Legend, which is perfect for drilling down into specific segments (like a single cookie type).
4. Polishing Your Chart for Presentation
A default pivot chart is functional, but it's often not ready for a report or presentation. As an analyst, part of your job is to make your visuals clear, professional, and easy to interpret.
Common clean-up steps include:
- Hiding the grey field buttons to create a cleaner look.
- Adding a descriptive title.
- Removing redundant legends or axes.
- Adding data labels for clarity.
- Adjusting colors and bar widths to improve readability.
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)
Let's return to the "Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1)" video.
Watch the segment from where the instructor visualizes the data. He takes a default pivot chart and, in under three minutes, transforms it into a clean, professional-looking visual by performing a series of essential formatting tweaks. Pay attention to each step he takes to "clean this up".
Conclusion
You've now learned how to turn your summarized data into dynamic, interactive visualizations. Pivot charts are a fundamental tool for any data or business analyst, enabling you to not only explore your data more effectively but also to communicate your findings with impact.
Here are the key takeaways from this lesson:
- Pivot Charts are linked to Pivot Tables: They are a visual representation of a pivot table, and any change in one is reflected in the other.
- Creation is flexible: You can create a pivot chart from raw data (creating the pivot table at the same time) or from an existing pivot table.
- Interactivity is key: You can dynamically change the chart's structure and filter its data using the PivotChart Fields pane, on-chart buttons, and slicers to explore different questions.
- Formatting matters: Cleaning up a default pivot chart by adding titles, hiding unnecessary elements, and improving its design is crucial for clear communication.
In our next lesson, we will explore another powerful technique for visual analysis. We will learn to apply conditional formatting to our data tables, using color, icons, and data bars to highlight patterns and exceptions directly within the cells of our worksheets. This will add another layer to your ability to make data speak.