Excel Pivot Tables, PivotCharts And Why They Are Important
A guide to enhancing your Microsoft Excel skills
There are many functions and capabilities that are possible when dealing with data in Microsoft Excel. A very important aspect of Excel that can deal with data in a variety of ways is something called pivot tables. Pivot tables are important because they allow anyone to filter and extract significance about the data set they are working with. Pivot tables allow anyone to look at their data in a number of ways and perspectives. It is from these pivot tables, that users can create visualizations that are interactive for anyone viewing them. In this article, I will go through how to create pivot tables and PivotCharts as well as the different capabilities that they possess. Throughout this article I use a college basketball data set that can be accessed with the link below.
Pivot Tables
Once the data is into your excel you can create a pivot table. To create a pivot table you must first highlight all of your data and then go to the insert tab and then click on Pivot table . The Pivot Table button allows the user the most freedom with their data in order to look at all possible combinations of their categories in the data. The Recommended Pivot Tables button can also be clicked if the user wants to see excel show some pivot tables already made with your data.
Once you click OK the pivot table dashboard will open in a new excel sheet and the user is free to looks at all aspects of the data.
The left side of the dashboard is where all the numbers show up once the user starts adding things to the filters, rows, columns and values sections on the right side of the dashboard. All of the columns of the data show up in the box and can be clicked and dragged into any of the four categories. If something is dragged in into the rows field, all of the data from that column will show up in rows on the left side. The same is true if something is dragged into the column field except the data will show up across the page in columns. The values field allows the user to get data associated with categories in the rows or columns and show their values. Lastly, the filter field is extremely useful because it allows the user to break down the data an only see certain parts of that data that are important to them. A representation of these basic functions of pivot tables is show to the left.
As you can see with my college basketball data to the left, Pivot tables allow the user to filter multiple things at the same time such as year and conference as well as have multiple values associated with the rows such as wins and games.
Another important capability of pivot tables is the vast amount of functions that can be instantly applied to the data in the columns and rows.
The values in the pivot table can be converted in a variety of ways. The lists on the left are just some of the possible ways to get important values from the data instantly. Users can get the sum of the numbers in the category, the count, etc. which can be used to find important statistics.
If a user wants to instantly make calculations from the data in a column they can run calculations on each individual cell with one click. Instead of typing out long formulas in each individual cell, pivot tables allow you to make calculations that would other wise require long formula inserted in each cell.
Another intriguing possibility with pivot tables is create a new field that is calculated from function made up of other field. In pivot tables, a user can add, subtract, multiply and divide different fields in the pivot table. Just like creating a function in a cell, a user can create a function made up of different fields to generate a new column of data that can be used to run different statistical tests such as t-tests, chi-square tests, etc.
The image on the right is an example of how I quickly created my own column called efficiency which is a team defensive efficiency subtracted from a team offensive efficiency. It took the sum of each teams offensive and defensive efficiency and made a calculation quickly with each individual cell in that column.
Another cool function, shown above, that is very useful is the color scale that can be created based off the individual data points. This can be done in pivot tables and in a normal excel workbook. This is extremely useful because it provides a visual representation for the user to be able to determine where values stand on a scale. In my representation I used dark red to show values that are bad (negative) and green to show values that are good (green). I also did a similar thing for the wins column which allowed me to look to see if any team had both green (good) columns quickly. This done by using the Conditional Formatting button in the Home tab. It allows you to create a variety of scales such as ones based on individual numbers, a range of numbers, etc.
Pivot Table Visualizations
Now that you know how to create pivot tables with the information that you desire, you can create interactive pivot table visualizations. PivotCharts, as they are known, are used by data scientists to represent their filtered findings with a bar chart, line chart, pie chart, etc. that is interactive for anyone using them. An example would be if someone created a scatter plot of something but I wanted to change what is on the x axis to see if the trends change. PivotCharts are created by going into the insert tab in the excel banner and clicking the PivotChart button. The result of this action is shown below.
In order to best represent why PivotCharts are an important aspect of data science, I created a couple of examples of how I used them to answer a question.
In example 1 above I wanted to find out which teams, that made the postseason, had the most average wins in the ACC and how much Virginia had in comparison. First I started with the handy pivot table and filtered out every year except 2020, because there was no postseason, filtered to only teams in the ACC that made the postseason. Thanks to Pivot Tables I can do all of that quickly and easily. Then I created a PivotChart, as described above, to represent this information. One cool part about these interactive PivotCharts is the fact that you can highlight the most important information hat you want people to see. My goal was to not only see all of the teams but I needed to see Virginia in order to answer my question. Another cool aspect about this PivotChart is that at any point you can change the filters or data that is being shown with the grey boxes on the left side of the chart. Within this chart the use can hover over individual bars to see the exact value or click on the bar to get every row of data that is put into that bar, this will be shown in example two.
Next I wanted to answer the question that is big in the world of Virginia basketball, “Does pace of play reduce a teams chances of winning a basketball game.” Example two is my answer to that question. So again I used a pivot table to filter down what category of teams I wanted for my question. I decided to filter down to only teams that had a number one seed in the postseason because they were the teams that were successful at winning. Then I added values for pace of play, which is the amount of possession per 40 minutes, and values for a statistic that represents the teams chance of beating any team in college basketball. I created a scatter plot to represent the data as shown above. Another cool feature of PivotCharts is the ability to click on a point and have a representation of what team that is shown on the graph. I used the color tool to change the color of the point that I wanted to represent which was Virginia.
Another cool aspect of PivotCharts is something called a Pivot Breakdown. Pivot Breakdowns allow you to see things that are not in your Pivot Table. For example if the user did not know which conference Virginia is in, he/she could check the Pivot Breakdown which would help them make more conclusions as a data scientist. By simply clicking on a filtered row in the Pivot Table or a point in the PivotChart excel allows the user to get a breakdown of every data point associated with that team, as show below in the Pivot Breakdown of Virginia.
Something that I did not do, that PivotCharts possess, is produce trend lines on scatter plots that will allow you to get the slope, intercept, R² value as well as the forms of error. This can be done by creating a scatter plot as I did above, right clicking on a data point and clicking Add trend line.
The Format Trendline box will appear and it allows the user to change the color, line width, shape of data points and type of trend so that it best fits your data.
Also as a part of the Format Trendline box a user can add the equation for the trend line and the R² value simply by clicking the boxes shown to the left.
Also PivotCharts allow the user to forecast forward and backwards which is useful for data scientists who need to predict future years, teams, etc.
Summary
All of the aspects shown above are reasons why Pivot Tables and PivotCharts are one of the most important tools in excel and an extremely important part of data science. Both can be used to do difficult and time consuming tasks quickly and easily. Pivot Tables can be used by managers to get a breakdown of the revenues from each of their individual departments or athletes trying to figure out which team they played best against in different years. PivotCharts allow data scientists to show their clients the information they want but also explore other numbers that may not have been highlighted. The power of these methods to take large amounts of data an shrink it down into different smaller tables is something that can be useful to what any data scientist is doing.
