It’s no secret that charts are one of the best ways in Microsoft Excel to visualize your data for quick analysis. However, if you’re dealing with a particularly large or complex dataset or want a chart to be interactive and dynamic, PivotCharts are the way to go.
Why You Should Use PivotCharts (And Why They’re Better Than Regular Charts)
If you’re already going to put effort into inserting a PivotTable into your Excel workbook, then it’s a no-brainer to generate a PivotChart at the same time.
The difference between a regular chart and a PivotChart is striking, especially when you’re working with lots of data. In exactly the same way that a PivotTable condenses and groups complex information into a more understandable format, its corresponding PivotChart visualizes summarized data from a large dataset in a way that makes analysis super easy.
Let’s say you’ve been presented with this sales table containing 700 rows and nine columns, and you’ve been asked to present the information in chart form.
Using a regular Excel chart to organize this information would be pretty much impossible, especially if you want to make the chart interactive. Even if you visualize only a couple of columns, because certain fields are repeated several times, the chart is difficult to read and doesn’t help with data analysis.
On the other hand, this PivotChart presents much the same data as in the chart above, but in condensed form, meaning it’s much easier to interpret.
What’s more, you can filter the data in a PivotChart by interacting with the integrated drop-down menus, and you can also see a breakdown of a field by clicking the plus and minus icons in the bottom right corner—things you can’t do with a regular chart. On top of that, the chart title updates automatically according to the filters and settings you apply.
If you need to change the data source of a regular chart, you need to open the “Chart Design” tab, click “Select Data,” and navigate your way around a pernickety and cumbersome dialog box.
However, to do the same with a PivotChart, you can simply click and drag the relevant fields in the PivotTable Fields or PivotChart Fields dialog box—a much more intuitive process (more on this shortly).
In fact, the list of the benefits of PivotCharts could go on and on. Crucially, however, if your plan is to create a dynamic dashboard of interactive charts, or if you’re going to share your workbook with others, you should definitely opt for PivotCharts ahead of regular Excel charts. They’re easier to use (even for those less familiar with Excel), there’s no need for any complex formulas or processes, and as soon as you know how a PivotTable works, creating and interacting with a corresponding PivotChart is much easier than doing the same with a regular chart.
If you’re working with a small and uncomplicated dataset in Excel, a regular chart may be the optimal data visualization choice.
How to Create a PivotChart in Microsoft Excel
Generating a PivotChart is really quite straightforward, provided you do everything in the right order. So, follow these steps carefully, and you’ll end up with a PivotChart to impress!
Step 1: Structure Your Data Correctly
PivotTables and PivotCharts only work as expected when your source data is structured correctly, so you should focus on this before even thinking about condensing and visualizing your data.
Each value in a column should contain the same type of data. In the example below, all the values in column A are departments, all the values in column B are countries, and so on. These are known as fields (labeled A). On the other hand, each row should contain a group of related data. Here, row 2 contains all the details about blush sold in Canada, row 3 contains all the details about bronzer sold in Canada, and so on. These are called records (labeled B).
Make sure each column has a header that concisely describes what the column contains, and avoid having blank cells, rows, or columns in your dataset.
Equally important is ensuring your dataset doesn’t have any merged cells, since PivotTables and PivotCharts only work with flat data. You can kill two birds with one stone in this regard by formatting your dataset as an Excel table, which, as well as unmerging merged cells, groups the data into a single, recognizable database that feeds the PivotTable and PivotChart seamlessly.
To do this, select any cell in the dataset, click “Format As Table” in the Home tab on the ribbon, and choose a design. Alternatively, press Ctrl+T.
Then, in the Create Table dialog box, verify that the whole dataset is selected, check “My Table Has Headers” if it’s not already checked, and click “OK.”
Now that your dataset is correctly formatted, give your table a name in the Table Name field of the Table Design tab.
Table names must start with a letter, underscore, or backslash, and the remaining characters must be letters, numbers, periods, or underscores. A table can’t be named “C,” “c,” “R,” “r,” or be the same as a cell reference. Keep table names short, and consider starting them with something like “T_” to differentiate them from other named items in your workbook.
Step 2: Convert Your Dataset into a PivotTable and PivotChart
Now, you’re ready to create your PivotTable and PivotChart.
One way to do this is to create a PivotTable first by selecting a cell in your table, clicking “PivotTable” in the Insert Tab on the ribbon, and then using the resultant PivotTable to create a PivotChart.
However, the best approach is to create them both at the same time, as this way, you can check that the PivotChart looks exactly how you want it to. What’s more, it requires no extra effort to create a PivotChart if you’re already creating a PivotTable.
So, select any cell in your formatted Excel table, and in the Insert Tab on the ribbon, click the top half of the split “PivotChart” button.
I prefer to insert PivotTables and PivotCharts in new worksheets so that I have a clean, dedicated space for data analysis and visualization. If you’re in the same boat, select “New Worksheet.” Otherwise, go with “Existing Worksheet,” and select a cell in the Location field. Then, click “OK.”
Because PivotCharts are driven by PivotTables, areas for both appear together in the worksheet.
Now select the PivotChart area, and in the PivotCharts Field pane, click and drag the fields you want to visualize to the relevant area. In my case, I want departments and products along the x-axis (the Axis area), combined profits on the y-axis (the Values area), and countries represented as individual data points (the Legend area). As you move the fields into the different areas of the PivotChart Fields pane, the PivotTable and PivotChart instantly update to reflect your selection.
At the moment, because the PivotChart displays data for all departments and all products, it’s not the easiest to read. However, don’t fret—you can easily manipulate the PivotChart so that it displays the data clearly.
How to Manipulate a PivotChart to Display Exactly What You Want
Once you’ve created your PivotChart and start playing around with the various options, you’ll quickly realize that the tool is satisfyingly intuitive. Here are some tips to get you started.
Collapsing the Sub-Fields
When you first create a PivotTable and a corresponding PivotChart, any sub-fields are expanded by default. You can see this is the case in the PivotTable below, where the Row Labels column displays each product for each department.
So, a good first action to take when you create your PivotTable and PivotChart is to collapse these fields. There are many ways to do this, but by far the simplest is to click the “-” symbol in the bottom-right corner of the PivotChart.
When you do this, notice how both the PivotChart and the PivotTable update accordingly. Indeed, making a change to one will always affect the other, since they’re always working in tandem. As a result of this single click, I can instantly see the profit in each department in each country.
Using the PivotChart Filters
Now that the finer details are collapsed, you can start to delve into your data more precisely. As with many tools in Excel, there are various ways to do this, but I prefer to use the filter controls on the PivotChart itself. For example, let’s say you want to compare the profit made in each department in Mexico.
To do this, click the “Country” drop-down menu, check only “Mexico,” and click “OK.”
This time, the PivotChart has adopted an appropriate title, the data for Mexico is isolated for quick interpretation, and you can glance at the corresponding PivotTable to see the individual figures.
You could go one step further and analyze the profits made from the sales of each product within a particular department in Mexico. In this example, I selected “Sports” in the Department filter and clicked the “+” icon in the bottom-right corner of the chart to display the sub-fields.
How about comparing Mexico’s sports sales to the UK’s? That’s also straightforward—simply add “UK” to the country filter.
To start afresh and remove all filters, select the PivotChart, click “PivotChart Analyze” on the ribbon, and click “Clear Filters” in the Clear drop-down menu.
Visualizing Different Fields in a PivotChart
One of the biggest benefits of using PivotCharts rather than regular Excel charts is that you can quickly select different fields to visualize.
Suppose that, instead of displaying profits, you now want the PivotChart to show units sold. To do this, you first need to display the PivotChart Fields pane. This might appear as soon as you select your PivotChart, but if it doesn’t, click “Field List” in the PivotChart Analyze tab.
In the screenshot above, Profit is checked in the field list, and Sum Of Profits is displayed in the Values area. The quickest way to display the number of units sold instead is to uncheck “Profit,” and check “Units Sold.” Because the Units Sold field contains numerical data, and the Values area is empty, Excel correctly assumes that this is the new value you want to display, and the PivotChart instantly updates accordingly.
Formatting and Modifying the Display of a PivotChart
If you’re already familiar with formatting regular Excel charts, you don’t need to learn anything new to format a PivotChart or change the elements displayed.
Right-click any part of a PivotChart, and click “Format…” to launch the corresponding formatting pane. For example, right-click a column and click “Format Data Series,” right-click the background and click “Format Chart Area,” or right-click an axis and click “Format Axis.”
Alternatively, select part of your PivotChart, and open the contextual “Format” tab on the ribbon.
To show or hide different chart elements, like data labels, chart titles, or trendlines, select the PivotChart, click the large “+” icon next to it, and choose from the options displayed.
Changing the PivotChart Type
Finally, to switch to a different type of PivotChart, select the chart, and in the Design tab on the ribbon, click “Change Chart Type.”
Then, select a chart to preview how the chart will look with the current filters activated, and click “OK” once you’re set.
If your dataset contains dates, you can display figures in an Excel PivotChart according to certain time periods—including days, months, quarters, or years—using the dynamic timeline filter tool. To do this, select the chart, and in the Insert tab on the ribbon, click “Timeline” in the Filters group.