In this post, I am going to show how easy it is to analyze the data using pivot tables as compare to applying functions and other excel operations. I was so excited to know more about pivots and the properties associated with it. Learning from the next lecture and then doing the lab and quiz after that was so interesting. I realized that it’s easy and less time consuming to derive reports of your choice with the help of pivot tables. After we have prepared the data in an Excel table, we can start to create pivot tables to aggregate the data and create some reports.
Again, I am using the data file which is provided with this lecture. I will write the question/scenario and then will show the tasks performed, to get the solution, by recording my screen through screencastifying. This Google extension is very handy and easy to use to quickly record the desktop.
Let’s get started…
We are going to look into the yearly sales data broken down by countries, product categories, and age groups.
First, let’s start by naming the Excel table. Name the Excel table to SalesTable. From now on, every time we add a pivot table, it should be based on this SalesTable.
Adding first pivot table:
Insert a new pivot table based on the SalesTable to a new sheet. Arrange the layout so that the pivot table displays the Product Category and Sub-Category in the Rows, Year in the Columns, and Revenue (Sum of) as the Values.
Question: Which year did the company start selling Touring Bikes?
Adding second pivot table:
Insert another pivot table to the same sheet, next to the existing pivot table. Arrange the layout so that the pivot table displays the Country and State in the Rows, Year in the Columns, and Revenue (Sum of) as the Values. Sort the pivot table by Sum of Revenue so that the Country and State with the highest revenue is displayed first.
Question: Rank the States for Germany, from the highest to lowest revenue.
Adding third pivot table:
Let’s add another pivot table. This time arrange the layout so that the pivot table displays the Frame Size in the Rows and Revenue (Sum of) as the Values. Hide the rows that do not have a Frame size (blank Frame size), then sort the pivot table by Sum of Revenue so that the Frame size with the highest revenue is displayed first.
Question: Which frame size sold the most?
In the process of preparing for this post, I not only learned about a few operations that we can perform on pivot tables but also how to edit videos (merging & trimming) using YouTube editor.
Stay tuned for more updates on pivot tables.