How to Make Pivot Charts in Excel

This guide will explain how to create a pivot chart in Microsoft Excel.

When analyzing data, a data summarization tool such as a pivot table allow you to aggregate your dataset dynamically. Pivot tables in Excel allow you to filter, group, and pivot your data through an intuitive drag and drop interface.

Meanwhile, pivot charts are a visual or graphical representation of data summarized in a pivot table. Like pivot tables, users can add, remove, or rearrange different fields to visualize the exact data they wish to analyze.

Excel allows users to quickly convert their data into a pivot table and pivot chart in just a few clicks. In this guide, we will provide a step-by-step tutorial on how to use the Pivot Chart tool to create a pivot chart from your Excel data.

A Real Example of Adding Comments Withing a Google Sheets Formula

Let’s explore a simple example where we can use a pivot chart to analyse our Excel data.

In the table above, we have a spreadsheet containing regional sales data for a certain retail corporation. We want to visualize this data as a chart to see potential trends with sales over time and across different regions.

To create a dynamic visualization that we can quickly iterate over and adjust, we can select our data and click Insert > PivotChart.

Similar to pivot tables, pivot charts are set up by placing fields from your dataset into one of four fields:

• Filters – Fields placed in this area determine which data to include or exclude specific data. For example, adding the region field will allow you to select which region’s data to view through a filter dropdown menu.
• Columns – This area determines the categories to break down your values with across the top axis. For example, if our dataset had product categories, we can use that as a column field to show a sales breakdown by type of product.
• Rows – Fields placed here determine how the data is broken down along the side axis of the pivot table. For calculating data on a day-to-day basis, we can place the date field in the rows column to aggregate by date.
• Values – This area determines what numerical data to summarize or aggregate. By adding the sales_amount field here we’ll be able to find the total sales breakdown per date and region.

In the pivot chart above, we can see that our pivot chart and pivot table are updated based on the placement of our fields earlier.

Similar to regular charts, we can also adjust how the chart looks.

By adding a filter field, we also have access to a dropdown menu where users can select what data to showcase in the chart.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to create pivot charts in Excel.

How to Add Comments Within Google Sheets Formulas

1. First, select the data you wish to show as a pivot chart. Select any cell in your table and use the shortcut Ctrl+A to select the entire table.
   
   In this example, we’ll select a table containing regional sales data for a retail corporation.
2. Next, head to the Insert tab and click on the PivotChart option.
   
   You can choose between inserting both a PivotTable and a PivotChart or just a PivotChart. For this example, we’ll select the option PivotChart & PivotTable.
3. Next, you should now see the Create PivotTable dialog box.
   
   We have the option to place our new PivotChart in a new worksheet or in an existing worksheet. For this example, we’ll select New Worksheet. Click OK to proceed.
4. You should now see a blank PivotChart element in a new worksheet (or an existing sheet depending on your choice in the previous step). Excel also opens up a PivotChart Fields side panel.
   
   Drag the right fields to either one of the four areas: Filters, Legend, Axis, and Values.
   
   In this example, we’ll add the region field as a filter. We’ll also set the sales_date field as the axis and the sum of our sales_amount field as the values to summarize for each date.
5. The PivotChart should now automatically update after selecting which fields to include.
   
6. Excel also allows users to modify the type of chart to use. Right-click on the chart and select Change Chart Type.
   
   In the Change Chart Type dialog box, browse through the list of chart types and select your desired chart type. Click on OK to proceed.
   
   In this example, we’ll convert our bar chart into a line chart.
   
7. We can also modify which chart elements to include in the final chart. To do this, select the target chart then click the “+” icon.
   
   Uncheck the chart elements you wish to hide and check the elements you want to include.
   
   

FAQs

1. How do I update a pivot chart when the underlying data changes?
   If the source data changes, click on the pivot table, go to the “PivotTable Analyze” tab, and click “Refresh.” The pivot chart will update automatically to reflect the changes.
   
   

To learn more about working with charts in Excel, you can read our post on how to overlay charts in Excel.

That’s all for this guide! Don’t forget to check out our library of spreadsheet resources, tips, and tricks for both Google Sheets and Microsoft Excel!