Table of Contents
What is Pivot Chart in Excel?
A pivot chart in Excel is an interactive excel chart that summarizes large amounts of data.
If your raw data is huge and is unorganized, then it becomes extremely very difficult for a person to summarize, understand the data comprehensively & also too difficult to visualize & analyze datasets, in these scenario Pivot table & pivot charts help’s you out.
A pivot chart 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.
Note: Prior to working on a pivot chart, you need to find and remove duplicates, delete leading, trailing or double spaces & remove blanks and errors in the raw data file.
How to Create a Pivot Chart in Excel?
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.
Two Options to Create a Pivot Chart in Excel.
- PivotChart with the help of an existing PivotTable.
- From a data table in excel with the help of recommended charts.
Now, let’s create a pivot table, for the salesperson by region wise.
To create a PivotChart in Excel, select the data range.
Then click the “Insert” tab within the Ribbon.
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.
Here I have selected, create both a PivotChart and PivotTable. t “Create PivotChart” dialog box appears, which is similar to “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.
Once you click ok, It inserts a both PivotChart & PivotTable, in a new worksheet.
“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. sales person to the Rows section, Region to the Columns section, and sales to the Values section.
Then the chart looks like as given below.
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.
Under Change chart type select Column, then Select the Clustered Column Chart.
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.
On the corner of the chart, you have an option to format chart elements based on your choice.
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.
You have an option to change Chart Style by the click of Styles icon on the corner of the chart.
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 About Pivot Chart in Excel
In 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
You can download this Pivot Chart Excel template here – Pivot Chart Excel Template
Difference Between Normal Chart & Pivot Chart in Excel
A normal or standard chart is used for a range of cells, whereas a pivot chart is purely based on data which you summarized in a pivot table.
Pivot table & charts are the enhanced form of data visualization in Excel, if you want to create a quick dynamic chart in excel, then it’s pivot chart.
- In any organization, from Business analyst to CEO, it is one of the most commonly used tools.
- It will provide insight & analysis of data, which helps out in decision making & to achieve business targets across the organization.
- A data source for PIVOT chart may be either from Spreadsheets, Text Files, Web Pages, Organizational Database.
This has been a guide to Pivot Chart in Excel. Here we discuss how to Create Pivot Chart in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –