ECMP 455 · Learning Project

Working with pivot tables

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.

Naming Excel Table:

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.

Answer: states-of-germany

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.

Advertisements

2 thoughts on “Working with pivot tables

  1. 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?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s