Excel — Pivot Tables in 5 Minutes
Pivot Tables are considered to be advanced Excel. Here is the short and sweet version.
So, you have an excel sheet full of rows and columns of data. Now you want to group particular rows based on data in particular column and then probably perform more operations like summation. Usually you can use filters, get the data, manually paste it another sheet and then do the calculation. You then repeat the filter for other items in the column. You can see that is very inefficient and error prone. Therefore, it is handled automatically by Pivot Tables. We will see this through an example. Figure 1 shows a table where data scientist jobs in Australia from different websites are listed. Now you want to group this data to show number of jobs from each website aggregated by location.
First select the table. Then, add a blank Pivot Tables from Insert → Pivot Table and tell in the pop-up box if you want it on a new sheet or existing one. You can also look at the recommended Pivot Tables, we will go with blank one .
Figure 2 shows three areas in Pivot Table. The column names from base table are populated in Green Area. The Yellow Area is for performing aggregation and filtering operations and the resulting pivot table is shown in the Red Area. In this example, we will drag ‘Number of Results’ from Green area to ‘values’ in Yellow area, ‘Website’ to ‘Columns’ and ‘Location’ to ‘Rows’.
Viola! Excel has aggregated job listings according the City in Rows and Website in Columns as shown in Figure 3. Also, note that you can calculate not only summation of the values, but can do any other operation. Just click on drop down menu in ‘Values’ and change ‘Value Field Settings’ for different operations like Count, Average, Min, Max… etc.
By the way, for video people https://www.youtube.com/watch?v=qu-AK0Hv0b4 does a good job of explaining the Pivot tables.
