With small amounts of data, the pivot table is sufficient for most types of data. However, as the data get larger, it is much easier to represent the data using graphical methods such as lines, pies, and bars. The good news for us is that creating a pivot chart from a pivot table is very simple, and it is very easy to change and update fields as needed. We will make a number of different charts for the pivot tables created in the previous recipes.
To proceed with creating pivot charts, we need the source data first. Luckily for us, that would be the pivot tables created in the previous recipe.
As usual, we will build upon files from previous recipes and add the pivot chart so that we may see where the data comes from and why it is significant.
The following are the steps to create a pivot chart with the DatabaseData_v3.xlsx file, which results in the creation of the PivotChart sheet in it:
Open the
DatabaseData_v3.xlsxfile again. Open the PowerPivot window and click on PivotChart (instead of the PivotTable button), located as a drop-down menu of the PivotTable button. Save it as New Worksheet.Select CountryRegion | Name and move it under Slicers Vertical
Select StateProvince | Name and move it under Slicers Vertical
Select Address | City and move it under Slicers Vertical
Select BusinessEntityAddress | BusinessEntityID as Count of BusinessEntityID and move it under Values
Then, rename each field accordingly (
Country Name,State Name, andCity Name), copy the highlighted chart, and paste it below as shown in the following screenshot:
Change the chart type of the bottom chart to a pie chart.
From here, there are three different types of reports that we can easily create.
Place CountryRegion | Name under the Axis Fields area, and now we have a chart of number of clients per country, represented both as a bar graph and as a pie graph as shown in the following screenshot:
Similar steps can be repeated for state/province and for city. Of course, because it will list all states for all the countries, the charts will be very disorganized. Play around with it and filter it to select only one country. For instance, Germany will appear as shown in the following screenshot:
Once we are finished with changing chart types, filters, and other ways to create charts to represent the data, rename the sheet as
PivotChartand save it and close.Certain chart types are more effective than others in representing a specific type of data. We have created a chart from the beginning here, adding and removing datasets just like the pivot table.
The following are the steps to create a pivot chart with the GDPData_v3.xlsx file:
Open the
GDPData_v3.xlsxfile again. Click on the pivot table.Under the PowerPivot Tools | PivotChart option select a chart type. We will create a column chart first, then copy the chart, and change the second chart type to Doughnut. Change the doughnut's chart layout to Layout 3 and the result will be as shown in the following screenshot:
The column chart shows the generally increasing GDP per nation, but the numbers for China are very small and hard to compare. The doughnut chart is more effective in showing the exponentially increasing GDP of China, but is still difficult.
For each of the Sum of … column, change the Show Value As button as % of… with China as its base. This will display all other GDP as a percentage of China. The resulting bar graph shows that the GDP of China showed great growth year after year compared to other countries. It would appear as follows:
We explored both ways of creating a pivot chart: from the PowerPivot window directly, and directly from the pivot table. Pivot charts, as evidenced, are very simple to create in the same fashion as the pivot table, but with a greater range of options for displaying the data in a meaningful manner.
The more qualitative data (DatabaseData.xslx) were focused on counting the numbers by the country, by the state, and by the city. This was achieved by setting one axis as one of these and filtering correspondingly. In our case, the pie graph was the most appropriate in showing the percentage of number of clients per region, allowing the user to make an intelligent business decision to focus marketing on other regions with less number of clients.
In the more quantitative data (GDPData.xslx), we focused more on the comparison between different variables over the years. China has shown strong growth, and Korea has got-along through the 2008 financial crisis due to its less financial-focused and more industrial-focused economy, which helps the user make an intelligent business decision to invest or not invest in different nations based on their growth and their resilience to a global financial crisis.
In addition, as seen in the GDP data, changes in the ways of representing data (percentage, and so on) are reflected in the corresponding pivot table charts. The combined representation of data using both a pivot table and a pivot chart enables the user to identify trends in percentage levels as well as on absolute levels.
As an additional item, try switching the rows and columns by pressing the Switch Row/Column button under the PivotChart Tools | Design tab. This will put the years as rows and countries as columns, which shows an interesting difference in how the chart appears.
Also, if we need two different types of chart on the same page, we can simply create two separate pivot tables and charts, and then move the charts together onto another sheet to represent different sets of data, which refers to a similar set of data.