The case study I'm going to looking at is some what straight forward. It is a record of a company which has few branches Island wide. This data is a record of individual data of sales for nearly ten months of time. So my workbook contain thousands of data. Below you can see the part of my data set.
Let's take row 3 as an example. In that row first value (In cell A3) is the date of the transaction. Next one is the branch where transaction took place. Third one is the value. And fourth one is the category of the sale. So here it is Food. It is not compulsory to data to be always sales to create pivot tables, Only it needs to be something measurable.
If you have very large amount of data, it is very difficult to analyze and present them. Consider above data set. You can analyze them in several ways. You can plot the total sales against the time. Also you can use different time frames like days, weeks and months. Also you can plot data of each and every branch against the time to see the growth of the sales in individual branches. So you can see that we can plot these data in various ways to suit to our requirement. We can compare branches each other or we can compare categories each other. So you can see that we can analyze these data in many ways by changing the axes of charts time to time. To do that we need pivot tables and charts. I will explain about pivot charts in another post.
You need to know few things about Excel pivot tables before proceed further. One important thing is if you have created pivot tables with Excel 2003, please not that they are not compatible with higher versions of Excel. So it is advisable to find raw data you use to create those pivot tables in Excel 2003 and use them to create new pivot tables in Excel 2013. However newer versions of Excel pivot tables have higher level of compatibility. If you have created your pivot tables earlier with Excel 2007 or Excel 2010 you can use them in Excel 2013 without any problem. Also you can use external data to create pivot tables. So it is not compulsory to have data in the same work book where the pivot tables are. An another important thing. You need to have all the transactions in rows. You can't have the transactions in columns. If your transactions are in columns you need to somehow transpose them to rows as shown in above data set. Also it is advisable to have one transaction in one row. There should not be any blank columns or blank rows. And each column should have a unique title. And if your data set contain numeric values don't leave empty cells. Put zero instead. Check whether your data set fulfills all those requirements before start creating a pivot tables and pivot charts.
My data set fulfills all the requirements. So now let's start creating our pivot table. In Excel 2013 there are two method to create a pivot tables. I will explain both these methods. First we will look at quick method. Click in a cell inside the data range you use to create the pivot table. Then press Ctrl+asterisk(*). So you will see that all data is selected automatically.
Once all the data is selected you will see an icon call Quick Analysis icon. (Please see the bottom right hand corner of the above image.) You can use this icon to quickly analyse your data. Just click on that icon. You will get a menu bar like below.
It contains several menus like Formatting, Charts, Totals, Tables and Spark lines. So let's click on the Tables menu.
Under that menu you can find Table, two pivot table options and More option. If you take cursor on top of each icon it will show you a live preview. So I will click on first pivot table option. With that click Excel 2013 will create a new sheet like below.
So now you can see that the pivot table is created in the left hand side of the new sheet. This is a quick way of a creating a pivot table. In that method we created the pivot table for a data range. But when you create a pivot table it is better to create it on a table rather than a range of data. Because when time goes you may need to add new data to the data set. So if you have used a table it is much easier to update your pivot table later on. In this second method I will show you how you can create a pivot table after converting your data set to a table.
First click in a cell within the data range. Then go to "Insert" menu and click on "Table".
Once click on the "Table" you will get the "Create Table" dialog box. Excel also suggest a boundary for your data as well.
You can change that boundary to suit to your requirement. Define boundary of your table and click OK. With that click the table will be created and it will be selected. In this point simply give your table a name. In below image I have marked the place with a red circle where you can specify the name of your table.
Once you click on the pivot table you will get the "Create pivot table" dialog box
Here you can see that my table has automatically selected. It is because I have click inside my table before click pivot table. Also here you can decide where you need your pivot table to be created. Either in the sheet where your raw data are or in a new sheet. After making your selections click on the OK button.To give much space to data analyzing area, I choose new worksheet option.
Above is the result I got. As you can see this method is little bit different to the quick pivot table creation method. Because in that method we got pivot table right away. In this method we have got a some kind of place holder for our pivot table. Actual pivot table will be created once we select the fields from the right side panel. The bottom part of the right panel has separated to four areas. Filters, Columns, Rows and Values. Here you will notice that when you select text field or date field they will automatically listed under Rows area. And if you select a field which has numeric values it will automatically listed in the Value area. So now I will select Branch field and value field.
So now let's look at the Pivot table we have created. There are branches in the column A and Sum of values in column B. We can control the pivot table by the panel shown in the left hand side. You can see in that panel only check boxes of Branch and Value are checked. As a result only those two fields are shown in the pivot table. So let's click on the check box of the category field and see what will happen.
As you can see, now my pivot table has expanded. Now it shows both the Branch and Category. So in Rows area the category label is placed under Branch label. According to that, in the pivot table categories are listed under each branch. Think you need to list branches under each category. Then you can see how sales are varied for different branches for different categories. You can simply do this. what you need to do is drag Category label at the Rows area to top of the Branch label of same area. Here is the result you will get.
Now all the branches are listed under each category.So now let's move the category label to columns area and see what will happen.
Now it has also break down by categories as well. So each column represent a unique category. In addition to that you can filter column labels as you like. Not only that you can even pivot two fields. So let's interchange Branch and Category in the bottom part of the right panel and see what will happen.
So you can see how the Branches and Categories swapped with that action. This flexibility of the Excel pivot tables is a key reason to it's popularity.
