How to Create Pivot Chart in Excel?

Pivot Chart in Excel is an incredibly versatile tool that transforms complex data sets into clear, manageable visuals, enabling businesses and individuals to uncover hidden insights and trends. By leveraging this dynamic feature, you can enhance your data analysis, streamline reporting processes, and communicate results more effectively. Embrace the power of Pivot Chart in Excel to make informed decisions, present data compellingly, and set new standards for excellence in data visualization. Let this tool illuminate your data’s true story, turning numbers into narratives that drive action and understanding.

This Tutorial Covers:

1. How to create Pivot Chart in Excel? How to construct an excel pivot chart?
2. Insert Pivot Chart
   • Create From Data Source
   • Create From PivotTable
3. How to Filter Pivot Chart Data in Excel?
4. How to Change Pivot Chart Type in Excel-
5. How to Refresh Pivot Charts?
6. How to Move a Pivot Table in Excel? / How to Move Chart to New Sheet in Excel? / How to Move Chart to another Sheet in Excel?
7. How to use a slicer with a pivot chart to filter?

1. How to create Pivot Chart in Excel? 

In Excel, a pivot chart is a visual representation of data. It provides a high-level view of your raw data. It lets you evaluate data using numerous graphs and styles. It is regarded as the finest chart to use during a business presentation with large amounts of data.

 

Pivot Chart	Normal Chart
1.      A Pivot Chart is bidirectionally linked to a PivotTable.	1.      A ‘normal’ chart is usually based on a list of data in cells.
2.      Filters, sorts, and data rearrangements made to Pivot Charts are also applied to their corresponding PivotTable, and conversely.	2.      Using filters, sorts, and computations on these types of charts requires modifying the source data cell formulas or inputs.
3.      Slicers may also be applied to PivotCharts, and a single slicer can be applied to many PivotTables/Pivot Charts if they are based on the same data.	3.      Changes to the chart have no effect on the data.

2. Insert Pivot Chart

There are 2 ways to make a pivot chart in Excel.

1. Create From Data Source
2. Create From PivotTable

Steps to create a pivot chart from Data Source in excel:

Step 1: Prepare a data table as below and select any cell in the table.

Step 2: Go to Insert. Then click on Pivot Chart.

Step 3: Either create a new sheet or specify the table range where you want the chart to be placed under Existing Worksheet.

Step 4: Click OK. This will generate a blank pivot chart and pivot table. You can build a report and a chart by adding the desired fields.

Step 5: Drag Product Name field from above mentioned picture right site box to Axis (Categories) filed. Then Drag Quantity and Unit Price to the Values filed.

Result Outlined Below:

Steps to create a pivot chart from the following PivotTable in excel:

Step 1: Select any cell in PivotTable.

Step 2: Go to Insert. Then click on PivotChart.

Step 3: It will provide a list of possible charts; choose the one you want (for learning select Clustered Bar).

Step 4: Click OK.

Result Outlined Below:

3. How to Filter PivotChart Data in Excel? 

If you apply a filter to your pivot table, it will be added to your pivot chart automatically, and vice versa.

Steps to Filter Pivot Chart in Excel:

Step 1: Select any cell in pivot chart, Right-click on the chart and select “Show Field List.”

Step 2: Drag fields into the filter box of your pivot chart field list.

Simply drag the item’s button back to the PivotTable Fields list to remove it from the pivot chart.

Result Outlined Below:

4. How to Change Pivot Chart Type in Excel?

You can also alter the chart type by following the steps below.

Step 1: Select the pivot chart.

Step 2: Go to the Design tab, in the Type group, click on Change Chart Type.

Step 3: Choose your preferred chart type.

Step 4: Select a new chart type and click OK.

Result Outlined Below:

5. How to Refresh Pivot Charts?

You can update the data in your pivot chart manually or automatically, regardless of whether it comes from an external source or the same worksheet.

Option 1: Refresh a Pivot Chart Manually

Here we have an Excel table that consists of the Product Information.

And the PivotChart looks like this:

After some changes in the Table:

To Refresh the PivotChart, follow the below process:

Step 1: Select the pivotchart.

Step 2: Navigate to the PivotChart Analyze tab. In the Data area of the ribbon, select the Refresh drop-down arrow.

Step 3: Select “Refresh” to refresh the specified pivot chart. Choose “Refresh All” to refresh all pivot chart in your workbook.

Option 2: Refresh a Pivot Chart by right click.

Here we have an Excel table that consists the Product Information.

And the PivotChart looks like this:

After some changes in the Table:

To Refresh a Pivot Chart by right click, follow the below process:

Step 1: Right-click the pivot chart and select “Refresh.”

Result Outlined Below:

Option 3: Refresh a Pivot Table Automatically.

Here we have an Excel table.

And the PivotChart looks like this:

After some changes in the Excel Table:

To Refresh the PivotChart, follow the below process:

Step 1: Right-click the pivot table and select “PivotChart Option.”

Step 2: Go to the data tab and mark the box “Refresh data when opening the file.”

Step 3: Click Ok.

Result Outlined Below:

6. How to Move a Pivot Table in Excel?

Below are the steps to move a pivot chart to new sheet:

Step 1: Select the pivot chart.

Step 2: Navigate to the Design. In the Location area of the ribbon, select the Move Chart.

Step 3: In move chart dialog box, click on your desired option.

Step 4: Click Ok.

Result Outlined Below:

Option 2: Move Pivot Chart by right click.

Step 1: Right-click the pivot chart and select “Move Chart.”

Step 2: Move chart dialog box will open. Click on your desired option.

Step 3: Click Ok.

Result Outlined Below:

7. How to use a slicer with a pivot chart to filter?

Step 1: Select the pivot chart.

Step 2: Navigate to the PivotChart Analyze tab. In the Filter area of the ribbon, select the Insert Slicer.

Step 3: Choose the field you want to use as a filter.

Step 4: Click Ok.

Result Outlined Below:

Application of Pivot Chart in Excel

• Trend Analysis: Utilize Pivot Charts in Excel to visually display trends over time within your data sets, making it easier to identify patterns and fluctuations in your business or project metrics.
• Comparative Performance Review: Create Pivot Charts to compare the performance of different products, departments, or regions side by side. This allows for quick identification of high and low performers and aids in strategic decision-making.
• Data Segmentation and Analysis: Employ Pivot Charts to segment data by categories such as age, gender, or geographic location, enabling detailed analysis of customer demographics or market segments.
• Budget vs. Actual Spending: Use Pivot Charts to compare budgeted amounts against actual expenditures, providing a clear visual representation of financial performance and helping to manage resources more effectively.
• Sales and Revenue Tracking: Implement Pivot Charts to track sales and revenue over time. This can help in understanding seasonal trends, evaluating sales strategies, and setting future targets.
• Inventory Management: Leverage Pivot Charts to monitor inventory levels, identify restocking patterns, and analyze product turnover rates, aiding in efficient inventory management and reduction of carrying costs.

For ready-to-use Dashboard Templates:

1. 1. Financial Dashboards
   2. Sales Dashboards
   3. HR Dashboards
   4. Data Visualization Charts