PivotCharts from PivotTables

What is a PivotChart?

A PivotChart is the visual representation of a PivotTable. The two are intrinsically linked. The PivotTable does the heavy lifting of summarizing your data, and the PivotChart visualizes that summary.

Let's begin by reading a short introduction that defines a PivotChart and its relationship to a PivotTable.

The key takeaway is that PivotCharts are dynamic. You aren't just creating a chart; you're creating an interactive tool.

Look at the image below. It shows all the components working together: the PivotTable summarizes the data, the PivotChart visualizes it, and the Slicers (the interactive buttons) allow a user to filter everything with a single click. This is the kind of dynamic report you will be able to build.

Hands-On: Building an Incident Status Chart

Let's build one ourselves. Given your background in reviewing cyber-related data, we'll use a simplified incident-tracking dataset.

Your Data:
First, create a new Excel sheet and enter the following data. To make it dynamic, convert this range into an Excel Table (Ctrl + T) and name the table IncidentData.

Step 1: Create the PivotTable

Before we can have a PivotChart, we need a PivotTable. Let's create one that counts the number of incidents by department and status.

1. Click anywhere inside your IncidentData table.
2. Go to the Insert tab and click PivotTable.
3. In the dialog box, ensure IncidentData is selected as the table/range and choose to place it on a New Worksheet. Click OK.
4. In the PivotTable Fields pane:
   • Drag Department to the Rows area.
   • Drag Status to the Columns area.
   • Drag IncidentID to the Values area. It should default to Count of IncidentID.

Your PivotTable should now summarize the count of open and closed incidents for each department.

Step 2: Create the PivotChart

Now for the main event. With the PivotTable ready, creating the chart is simple.

The following video from Excel Campus shows exactly how to insert a PivotChart from a PivotTable you've already built.

Now, apply this to your incident report:

1. Click any cell inside your new PivotTable.
2. Go to the PivotTable Analyze tab on the ribbon.
3. In the Tools group, click PivotChart.
4. The Insert Chart dialog will appear. Choose a Clustered Column chart for now and click OK.

You've just created a PivotChart! It visually represents the data from your PivotTable. Notice the grey "field buttons" on the chart itself. These allow you to filter directly from the chart, and any changes you make there will also reflect in the PivotTable.

For a text-based guide on this process, you can refer to the "How to create a chart from a pivot table" section in the Ablebits article (Resource ID: LINK) you looked at earlier.

Making the Chart Truly Interactive with Slicers

The field buttons are useful, but for creating user-friendly MIS reports, Slicers are the best tool. They are visual filters that make exploring the data intuitive for anyone, regardless of their Excel skill level.

Let's add a slicer to your PivotChart to filter by Priority. This will allow a manager to see the incident status for only "High" priority items, for example.

This next video is fantastic because it shows not only how to add a slicer but also how the chart dynamically responds.

Your Turn to Add a Slicer:

1. Click on your PivotChart or PivotTable to bring up the context-sensitive tabs.
2. Go to the PivotTable Analyze tab.
3. In the Filter group, click Insert Slicer.
4. In the dialog box, check the box for Priority and click OK.
5. A slicer for Priority will appear. Now, click on the "High", "Medium", and "Low" buttons. Watch how your PivotChart instantly updates to show only the data for the selected priority level. You can select multiple items by holding Ctrl or by using the multi-select button on the slicer.

This interactivity is the core reason PivotCharts are so valuable for MIS dashboards and reports.

Conclusion

Congratulations! You've successfully moved from creating static charts to building dynamic, filterable visualizations. This is a foundational skill for creating the dashboards and summary reports that are central to an MIS Executive role.

Key Takeaways:

• A PivotChart is a visual representation that is directly and dynamically linked to a PivotTable.
• You can create a PivotChart by selecting your PivotTable and clicking PivotChart on the PivotTable Analyze tab.
• The real power of PivotCharts comes from interactivity. Using Slicers provides a user-friendly way to filter the report and see the chart update in real-time.

In our next lesson, we'll focus on making your charts look professional. We will take the PivotChart you built today and learn how to format chart elements like titles, axes, data labels, and colors for a presentation-ready look.