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**?

**Answer:** 2013

**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?

**Answer:** 42

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.

Thanks for the update, Anila! I had no idea that pivot tables were a thing! I wish I had known about them sooner — it would have saved me a lot of time in some of my university science classes when I had to make large data tables in Excel. So far, what would you say has been the hardest part about learning to use Excel? Have you ran into any challenges?

LikeLike