In this post, I am going to show how to insert charts using pivot tables. Chart types are the same as I discussed in my earlier posts but the nature and functionality of pivot charts are different and more useful in data analysis context.
Pivot tables, charts, and slices altogether help in creating dashboards in excel. In earlier posts, I have already talked about inserting pivot tables. Now, I am moving towards pivot charts and slices.
I will create different pivot tables from the data (included with the lecture) and then will apply charts on it.
Yearly Sales by Country:
Scenario: Let’s, add a pivot chart for the pivot table that shows yearly sales by Country and select a Line chart to display the yearly trend.
Switch Row/Column in ‘Select Data Source’ window useful to show the Years in the X axis, the Revenue in the Y axis, and the Countries as categories. Collapsing/expanding the Country fields to hide/show the States fields is also visual from PivotChart.
Yearly Sales by Product Category:
Scenario: Let’s add another pivot chart for the pivot table that shows yearly sales by Product Category and selecting a Column chart to display the yearly sales by category so that the years are together.
Note: Expanding/Collapsing function through PivotCharts is only available in Excel 2016, it is not available in the older versions.
So this is how we insert PivotCharts. There are a few other things that I learned from the lecture regarding chart formattings which I am explaining below.
Adding and Formating Data Labels:
I added a pie chart to show Yearly sales by age group by following the same procedure as shown in the videos above. In this video I am showing how to label a chart using “Data Labels” (right click on the chart to get to this option) and then formatting “Data Labels” to show “percentage” of each group instead of the value. (percentage makes more sense to see the proportional sales by each age group)