Visualize Data with PivotCharts

1. What is a PivotChart?

A regular chart is a static snapshot of a specific data range. If you want to see a different slice of the data, you often have to create a new chart. A PivotChart, on the other hand, is the visual representation of a PivotTable. This direct link makes it incredibly powerful.

When you rearrange, filter, or update the PivotTable, the PivotChart instantly reflects those changes. Conversely, interacting with the chart's own filter controls will update the PivotTable. They are two sides of the same analytical coin.

2. Creating and Building Your First PivotChart

The best way to understand PivotCharts is to build one. The process is very similar to creating a PivotTable, and because of your background with PivotTables and standard charts, you'll find the workflow intuitive.

We will use an excellent, comprehensive video from Kevin Stratvert that walks through the entire process, from inserting the chart to analyzing the data. The video uses a sample workbook which you can download via a link in the video's description to follow along. This hands-on practice is key.

The PivotChart Fields Pane

As you saw in the video, the PivotChart Fields pane is your command center. It looks almost identical to the PivotTable Fields pane, but the areas have slightly different names that are more intuitive for charting:

• Axis (Categories): This is equivalent to the Rows area in a PivotTable. The fields you place here will appear along the horizontal (category) axis of your chart. For example, Customer Name or Month.
• Legend (Series): This is equivalent to the Columns area. Fields placed here break down your data into colored series. For example, if you place Year here, you might get a separate colored bar or line for 2022 and 2023 for each category on the axis.
• Values: This is identical to the Values area in a PivotTable. It holds the numeric field you want to calculate and plot, such as Sum of Revenue or Count of Units.
• Filters: This is identical to the Filters area. It allows you to apply an overall filter to both the PivotTable and PivotChart.

The real power here, as demonstrated in the video, is the ability to drag and drop fields between these areas to "pivot" your view of the data, with the chart updating instantly.

3. Interacting with and Customizing a PivotChart

Once your PivotChart is created, you can interact with it and format it to make your insights clear and professional.

Dynamic Filtering

One of the main advantages of a PivotChart is its interactive filtering capability. You can filter directly from the chart itself, which is ideal for dashboards where you might want to hide the underlying PivotTable.

Changing Chart Type and Formatting

A PivotChart is still a chart. You can change its type and apply all the formatting skills you learned in the previous lesson. You can change a column chart to a pie, bar, or line chart as needed. You can also customize titles, add data labels, change colors, and adjust axes using the Chart Design and Format tabs, or by opening the Format Pane (Ctrl+1).

There's no need to relearn these skills; just know that they apply perfectly to PivotCharts. A well-formatted PivotChart is a hallmark of a professional report.

4. Hands-On Practice

Now it's your turn to put this all together. Using the "Kevin Cookie Company" workbook you downloaded:

1. Create a New PivotChart: On a new sheet, insert a PivotChart that shows the total Revenue for each Customer Name.
2. Add a Dimension: Add the Cookie Type field to the Legend (Series) area. Notice how the chart now shows the revenue breakdown by cookie for each customer.
3. Switch the View: Swap the Cookie Type and Customer Name fields. Put Cookie Type on the Axis (Categories) and Customer Name in the Legend (Series). Observe how the chart instantly pivots to show a different perspective.
4. Filter the Data: Use the on-chart filter for Cookie Type to display only the data for "Chocolate Chip" and "Oatmeal Raisin".
5. Change Chart Type: With your filtered data, change the chart type to a Stacked Column Chart. This will show the total revenue for each cookie type, with the contribution of each customer stacked within the bars.
6. Format Your Chart: Give your chart a professional title, such as "Top Cookie Sales by Customer". Remove the legend if it feels too cluttered and add Data Labels to the bars instead.

This exercise will solidify your understanding of how the components of a PivotChart work together to create a flexible and powerful analytical tool.

Conclusion

In this lesson, you've bridged the gap between data summarization and data visualization. You've learned that a PivotChart is more than a static picture; it's an interactive interface for exploring your data.

Key Takeaways:

• A PivotChart is a chart that is dynamically linked to a PivotTable.
• You build and modify a PivotChart by dragging fields into the Axis, Legend, Values, and Filters areas of the PivotChart Fields pane.
• The key advantage of a PivotChart is its interactivity—changes to filters or field arrangements in one are instantly reflected in the other.
• All standard chart customization and formatting techniques apply to PivotCharts, allowing you to create polished, professional visuals.

This skill is fundamental to building dashboards. In our next lesson, we will explore another essential visualization technique: creating combination charts. These charts allow you to plot multiple data series on the same graph, often using a secondary axis, which is perfect for comparing metrics with different scales, like sales revenue in dollars and units sold.