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.
- To create a Pivot Chart 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 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 a pivot table & chart, here you need to always select in a new worksheet.
- Once you click OK, It inserts 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., salesperson 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. Clustered Bar Chart. A clustered bar chart represents data virtually in horizontal bars in series, similar to clustered column charts. These charts are easier to make. Still, they are visually complex.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 boxSearch BoxA search box in Excel finds the needed data by typing into it, then filters the data and displays only that much info. When working with large datasheets, this simple tool may save a lot of time. 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 in excelChange Chart Style In ExcelWe can change the structure of the chart by using chart styles. You can reuse a chart style you've created by saving it as a chart template. by the click of the 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 the 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 a 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 the 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 a downloadable excel template. You may learn more about excel from the following articles –