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.

Create Pivot Chart

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

Create 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.

Create Pivot Chart

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.

Create Pivot Chart

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:

Create Pivot Chart

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

Step 1: Select any cell in PivotTable.

Create Pivot Chart

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

Create Pivot Chart

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

Create Pivot Chart

Step 4: Click OK.

Result Outlined Below:

Create Pivot Chart

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.”

Create Pivot Chart

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

Create Pivot Chart

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

Result Outlined Below:

Create Pivot Chart

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.

Create Pivot Chart

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

Create Pivot Chart

Step 3: Choose your preferred chart type.

Create Pivot Chart

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

Result Outlined Below:

Create Pivot Chart

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.

Create Pivot Chart

And the PivotChart looks like this:

Create Pivot Chart

After some changes in the Table:

Create Pivot Chart

To Refresh the PivotChart, follow the below process:

Step 1: Select the pivotchart.

Create Pivot Chart

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

Create Pivot Chart

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

Create Pivot Chart

Option 2: Refresh a Pivot Chart by right click.

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

Create Pivot Chart

And the PivotChart looks like this:

Create Pivot Chart

After some changes in the Table:

Create Pivot Chart

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

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

Create Pivot Chart

Result Outlined Below:

Create Pivot Chart

Option 3: Refresh a Pivot Table Automatically.

Here we have an Excel table.

Create Pivot Chart

And the PivotChart looks like this:

Create Pivot Chart

After some changes in the Excel Table:

Create Pivot Chart

To Refresh the PivotChart, follow the below process:

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

Create Pivot Chart

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

Create Pivot Chart

Step 3: Click Ok.

Result Outlined Below:

Create Pivot Chart

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.

Create Pivot Chart

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

Create Pivot Chart

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

Pivot Chart in Excel

Step 4: Click Ok.

Result Outlined Below:

Create Pivot Chart

Option 2: Move Pivot Chart by right click.

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

Create Pivot Chart

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

Pivot Chart in Excel

Step 3: Click Ok.

Result Outlined Below:

Pivot Chart in Excel

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

Step 1: Select the pivot chart.

Create Pivot Chart

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

Create Pivot Chart

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

Pivot Chart in Excel

Step 4: Click Ok.

Result Outlined Below:

Pivot Chart in Excel

Pivot Chart in Excel

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. Financial Dashboards
    2. Sales Dashboards
    3. HR Dashboards
    4. Data Visualization Charts

 

Leave a Comment

Your email address will not be published. Required fields are marked *

Categories