Data Analysis With Pivot Table Part – 1/2

Pivot table is without a doubt the number one tool for data analysis in excel. Previously I’ve made an introductory post for pivot table. Now I’m going to show you how to make some data analysis with pivot table.

For the examples, I’m going to use the sample data I previously prepeared. This sample data consists of sales transactions of an imaginary company. This list has 1000 rows.

You can easily see monthly sales revenue by customer. Insert a pivot table, place Customer_ID in row area, Month on column area and Total in values area.

If you like to filter this table by customer representitive, than place Rep_ID into filter area. Now you can esaily filter sales by month for a single or multiple sales representitives.

Now lets check what is the avarage sales revenue for sales transactions by customer for every month. To do this, right-click any data on the table and select Summarize Values As then select Average (Notice that table header changed to average from sum).

There is also lots of other analysis that can be done with just one click. You can see the possible options in the picture below.

Now lets see another cool thing you can do with pivot table. You can place multiple layers of data into rows and columns. Lets make a table of sales revenue bu customer for each representitive for each month. To do this move Rep_ID over Customer_ID in row area. Here is the result:

As you can predict, you can make several layers for your analysis with several different combinations. These layers can expand and collapse as you wish. All you need to do is to click on the small +/-  buttons left of the row.

For more cool stuff about pivot tables, continue to next part of this tutorial.