How to make pivot charts in Excel

What is pivot chart?

Pivot Chart is a dynamic visualization tool that works together with Excel PivotTables. While PivotTables provide a way to summarize and analyze large datasets, PivotCharts offer a graphical representation of that summarized data, making trends and patterns easier to spot.

The beauty of pivot charts lies in their interactivity and flexibility. Like pivot tables, pivot charts are dynamic, meaning they can be easily manipulated to display different aspects of the data.

Pivot graphs are particularly useful for presenting complex data in a clear and concise manner. They enable users to identify trends, compare data points, and draw insights quickly. For example, with a pivot chart, you can easily visualize how sales vary across different geographical regions, product categories, or specific time periods.

You can create different types of charts, such as pie, bar, line or scatter, and customize them to suit your needs. You can also change their layout and appearance by applying different filters, fields, and formats. A pivot chart is linked to the pivot table that it is based on, so any changes you make to the pivot table will be reflected in the graph.

In essence, a pivot chart serves as a visual storyteller, transforming raw data into meaningful insights that are easy to understand and act upon.

How to make a pivot chart in Excel

Before you start creating a pivot chart, it's important to make sure your data is well-organized and structured. Each column should represent a different variable or category, and each row should contain a unique record. Don't forget to remove any blank rows or columns and double-check your data for errors or inconsistencies.

With your source data ready, follow these steps to create a pivot chart:

How to create a chart from a pivot table

If you already have a pivot table set up, here's how you can easily create a graph from it:

1. Select any cell within your PivotTable.
2. Navigate to the Insert tab on the Excel ribbon and click on the PivotChart button.
3. In the dialog box that appears, choose the type of graph you want to create. Here are some recommendations:
   • Bar charts are ideal for comparing values across different categories or time periods.
   • Line charts work well for illustrating trends over time or continuous data series.
   • Pie charts are effective for showing the proportion of each category in a dataset.
   • Scatter plots are useful for visualizing the relationship between two variables and identifying any correlations or patterns.
4. Once you've selected your desired type, click OK.

You will find the visual in the same worksheet next to your PivotTable. You can move it to a different location by dragging and change its size using the resize handles, just as you would with any other graph in Excel.

Pivot chart shortcut key

To swiftly make a graph based on a pivot table, you can use the following keyboard shortcuts:

1. Select any cell within the pivot table.
2. Press the F11 key to create a graph in a new Chart sheet.
3. Alternatively, use the Alt + F1 shortcut to insert a chart on the active sheet.

The newly created pivot chart will automatically use the default type. From there, you can easily modify it to suit your preferences by selecting a different chart type.

How to filter a pivot chart in Excel

By default, a pivot chart comes with a filter at the bottom, allowing you to filter the Axis data categories. In some cases, an additional filter may be available on the right side for the Legend, enabling you to hide or show different data series.

To add more filters to a pivot chart, follow these steps:

1. Click anywhere within your graph to display the Fields pane.
2. Drag the desired fields to the Filter area.

The added filters will appear at the top of the chart, as illustrated in the image below:

It's important to note that default and custom filters offer different options. The built-in filter automatically included for data categories provides all sorting and filtering options available in Excel:

On the other hand, the custom filter that you add to the Filter area only provides basic filter functionality. By default, it allows selecting just one item. To select multiple items, check the corresponding box at the bottom of the filter window:

How to modify a pivot chart

Pivot charts are not just powerful visualization tools; they're also incredibly flexible, allowing you to tweak nearly every aspect to suit your needs.

To show, hide or format graph elements like axes, labels, and legend, simply click the Chart Elements button (a plus sign) at the upper right corner of the graph:

For changing the chart style, click the Style icon at the upper right corner and choose from various predefined styles:

For more options to adjust colors and styles, explore the Design and Format tabs on the ribbon. These tabs offer additional settings and tools to fine-tune your pivot chart to perfection.

For more information, please refer to How to customize Excel graphs.

How to change pivot chart type

You can change the type of your pivot graph at any time after you make it. Here are the steps:

1. Click anywhere within your graph to activate the pivot chats tabs on the ribbon.
2. On the Design tab, in the Type group, click Change Chart Type.
3. Select the desired chart type from the options provided, and then click OK.

With these simple steps, you can quickly change the appearance of your pivot graph to better suit your data visualization goals.

How to remove fields buttons from pivot chart

When you create a pivot chart in Excel, it automatically includes the so-called "field buttons" to help you interact with your data:

• Value field button in the upper left
• Axis field button in the lower left for filtering data categories
• Legend filed button on the right for filtering data series

By hiding unnecessary field buttons, you can get a cleaner and more focused visualization of your data.

To remove a specific field button, right click that button and then select "Hide Value Fields Buttons on Chart" or "Hide Axis Fields Buttons" or "Hide Legend Fields Buttons", depending on which button you clicked.

If you want to remove all field buttons at once, simply right-click on any button and choose Hide All Field Buttons on Chart.

Another way to hide and show field buttons in a pivot chart is through the Field Buttons options on the PivotChart Analyze tab, in the Show/Hide group:

• To hide a specific field button, click on the corresponding item in the dropdown list to remove the checkmark.
• To show a hidden field button, click on the unchecked item in the dropdown list.
• To hide all field buttons in the pivot chart, click Hide All. To make all field buttons, click Hide All once again.

Change how to summarize values in pivot chart

By default, numeric values in a pivot chart are summed, while non-numeric values are counted. To adjust how data is summarized, Excel offers the following customization option:

1. Right-click the Value field button.
2. Choose Value Field Settings… from the context menu.
3. Select the desired summary function.

How to change pivot chart data source

When dealing with large datasets in Excel, you may have multiple pivot tables covering different regions or time periods. Imagine you've meticulously set up a pivot chart for one table with specific design and formatting. It would be nice to replicate that visual for another table without redoing all the work. The solution lies in changing the source data for a graph.

1. If you wish to preserve the original chart, then copy the worksheet containing it.
2. On the copied sheet, select the graph.
3. On the PivotChart Analyze tab, in the Data group, click Change Data Source.
4. In the dialog box that pops up, choose the Select a table or a range option, then select the new source data for your graph, and click OK.

In some cases, the pivot chart may appear broken as the fields used in the original graph do not exist in the new data source. For example, when changing from Q1 source data to Q2 data, you may get a blank visual.

To fix this, simply select the Q2 field and drag it to the Values area. Your nicely formatted pivot graph will now display the Q2 data.

To confirm this change, select the graph, click the Change Data Source button on the Analyze tab, and you will see that it is now based on the Q2 table.

Move pivot chart to new sheet

Similar to a standard graph, you can relocate your Excel pivot chart to another worksheet. Here's how:

1. Right-click the pivot graph and select Move Chart from the context menu. Alternatively, click the Move Chart button on the Analyze tab, in the Actions group.
2. In the dialog window that appears, choose one of the following options:
   • New sheet - this will create a new Chart sheet and move the visual there.
   • Object in – this will move the graph as an object to any existing worksheet.
3. Click OK.

You can also easily move your graph back to its original sheet using the same steps.

How to refresh pivot chart in Excel

Refreshing a pivot chart is just like refreshing a pivot table. There are two main methods:

Refresh pivot chart automatically when opening the workbook

To ensure your graph stays up-to-date every time you open your workbook, follow these steps:

1. Right-click the graph and choose PivotChart Options… from the context menu.
2. In the dialog box that appears, go to the Data tab, and check the box labeled Refresh data when opening the file.

Note. The settings you select in this dialog will apply to both the chart and the associated pivot table.

Practice workbook for download

Excel PivotChart - examples (.xlsx file)