What is the Pivot Chart in Excel?
Pivot Chart in excel is an in-built Programme tool in excel which helps you out to summarize selected rows and column of data in a spreadsheet. It’s the visual representation of a pivot table or any tabular data which helps to summarize & analyze the datasets, patterns, and trends. In Simple words pivot chart in Excel is an interactive excel chart that summarizes large amounts of data.
How to Create a Pivot Chart in Excel? (Step by Step with Example)
Let us learn how to create a Pivot Chart in Excel with the help of an example. Here we do the Sales Data Analysis.
Below mentioned data contains a compilation of sales information by date, salesperson, and region, Here I need to need to summarize sales data for each representative by region-wise in the chart.
- Step 1: To create a PivotChart in Excel, select the data range.
- Step 2: Then click the “Insert” tab within the Ribbon.
- Step 3: Then select the “PivotChart” drop-down button within the “Charts” group. If you want to create a PivotChart only, then select “PivotChart” from the drop-down or if you want to create both a PivotChart and PivotTable, then select “PivotChart & PivotTable” from the drop-down.
Step 4: Here I have selected, create both a PivotChart and PivotTable. t “Create PivotChart” dialog box appears, which is similar to the “Create Pivot Table” dialog box. It will ask for the options, i.e. from a table range or from an external database. By default, it selects table range and it will ask you where to place pivot table & chart, here you need to always select in a new worksheet.
- Step 5: Once you click OK, It inserts a both PivotChart & PivotTable, in a new worksheet.
- Step 6: “PivotChart Fields” task pane appears on the left side which contains various fields i.e. Filters, Axis (Categories), Legend (Series) and Values. In the PivotTable Fields pane, select the Column fields applicable to the pivot table, you can drag and drop i.e. salesperson to the Rows section, Region to the Columns section, and sales to the Values section.
Then the chart looks like as given below.
- Step 7: You can name this sheet as “SALES_BY_REGION”, click inside the PivotTable, you can change the chart type, in Change Chart Type option, based on your choice under Analyze tab in the home, select PivotChart, insert chart popup window appears, in that Select Bar, under that select Clustered Bar chart. Right, Click on the Pivot Chart, select Change Chart Type.
- Step 8: Under Change chart type select Column, then Select the Clustered Column Chart.
- Step 9: Now you can summarize the data with the help of interactive controls present across the chart. When you click on Region Filter Control. A search box appears with the list of all the regions, where you can check or uncheck boxes based on your choice.
- Step 10: On the corner of the chart, you have an option to format chart elements based on your choice.
- Step 11: You have an option to Customize the Pivot Table Values, by default excel uses the SUM function to calculate the values available in the table. Suppose if you select only region & values in the chart, it will display the total SUM of Sales for each region.
- Step 12: You have an option to change Chart Style in excel by the click of Styles icon on the corner of the chart.
- Step 13: This Chart will get updated when you change any dataset values in a pivot table. This option can be optimized by following steps. Right-click and select PivotChart Option.
In the above chart options, go to the Data tab and click on the checkbox “Refresh data when opening a file”. So that refresh data gets activated.
Things to Remember
In an excel pivot chart, you have an option to insert a timeline to Filter Dates (monthly, quarterly or yearly) in a Chart to summarize sales data (This step is applicable, only when your dataset contains only date values).
You can also use a Slicer with a Pivot Chart to Filter region wise data or other field data of your choice to summarize sales data.
- A pivot chart is a Key Metrics tool for monitoring company Sales, finance, productivity and other criteria’s
- With the help of Pivot chart, you can identify negative trends & correct it immediately
- One of the drawbacks of a pivot table is, this chart is directly linked to the datasets associated with Pivot Table which makes it less flexible, because of this, data outside the Pivot Table cannot be added
This has been a Step by Step Guide to Create a Pivot Chart in Excel. Here we discuss how to create a Pivot Chart in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –