Pivot Chart in Excel ( Table of Contents )
Pivot Chart in Excel
Most often when the data is big and huge it is difficult to draw conclusions and tell the stories behind the data. Pivot table could well be the tool which can help us in these crunch situations.
A pivot table can cut, slice, summarize and give meaningful results from the data. Usually in excel after summarizing the data we apply graphs or charts to present the data graphically to tell the story visually.
But pivot table does not require your special charting techniques rather it can build its own chart by using its own data. Pivot charts works directly with the pivot table and visualize the data in the most effective way.
In this article, I will explain you the process of creating pivot charts in excel. This will be beneficiary for you in your day to day workplace. Download the excel workbook to practice along with me.
How to Create Pivot Chart in Excel?
A pivot table is available in all the versions of Excel.
I have sales data of 4 regions across many months. I want to know the summary behind this data by using a pivot table in excel.
Step 1: Select the data.
Step 2: Go to Insert and apply a pivot table.
Step 3: Click OK.
Step 4: Drag and drop Region heading to the ROWS and Sum of Amt heading to the VALUES.
Now we have summary report region-wise put together of all the months.
This report is only showing numerical summary, if you need graphical summary you can insert Pivot Chart.
Step 5: Place the cursor inside the pivot table and go to Options. Under this, you will see the Pivot Chart option.
Step 6: Once you click on Pivot Chart it will show you all the available charts for you. Select any one of them as per your wish.
Step 7: Your initial chart looks like this.
Step 8: Now add Month heading to the COLUMNS field. It will break up the report into region-wise & month-wise.
Step 9: Now look back at the chart. It has automatically updated its charting field. It is also showing the breakup of region-wise & month-wise visuals.
Step 10: Now if you observe there is one drop-down list available for MONTH & REGION in the chart itself. This is the controls for the chart.
Step 11: If you want to show the result only for the month of Jan you can select the Jan month from the drop-down list.
Step 12: It will start to show the results only for the month of Jan. The important thing is not only in the chart section but also in the pivot region as well.
Step 13: Not only a pivot chart controls the pivot but pivot table too controls the pivot chart. Now the filter is applied in pivot chart but I can also release the filter in the pivot table as well.
Note: Both PIVOT TABLE and PIVOT CHARTS are interdependent. If any changes made to any file it will affect both of them simultaneously. Both are an interdependent option in excel.
Insert a Slicer to the Table
All these while you worked with filters in excel. But we have visual filters in excel. Slicers are visual filters to filter out any particular category for us.
In our previous section we have selected the month from the drop-down list either on pivot chart or on pivot field, but using SLICERS we can do this visually.
Step 1: Place a cursor inside the pivot table.
Step 2: Go to Option and select Insert Slicer.
Step 3: It will show you options dialogue box. Select for which field you need a slicer.
Step 4: After selecting the option you will see the actual slicer visual in your worksheet.
Step 5: Now you can control both table and chart from the SLICERS itself. As per the selection you make in the slicer both table and chart will show their results accordingly.
Advantages
- Effective and dynamic chart.
- Visualization and numbers are interdependent.
- Shows drops, highs, lows everything in a single graph.
- Large data into a concise size.
Things to Remember about Excel Pivot Chart
- If the data is increasing you need to change the range of the pivot table every time data increases. So I will advise you to use Excel Tables for auto-updating of the pivot ranges.
- Both PIVOT FIELD & PIVOT CHARTS are interdependent.
- SLICERS can control both of them at a time
- Since the chart is directly connected to the changing numbers range it becomes dynamic in nature.
Recommended Articles
This has been a guide to Excel Pivot Chart. Here we discuss how to create Pivot Chart in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
