Let us learn about Pivot Tables in Excel – A Guide. One of the most powerful, yet less used feature of Microsoft Excel is the ‘PivotTable’. Using this, you can analyze large amount of data and summarize them with just a few mouse clicks. Let us understand them here and learn how to create and customize them. We will be working on Microsoft Excel 2010. Excel has far exceeded Lotus in popularity and to an extent that when it comes to the number of users, Excels’ manifolds every year and Lotus’ is approaching a zero.
The main two reasons attributed to this are that firstly, Lotus 1-2-3 never accepted challenge from Windows and hence instead of moving forward to a GUI interface, preferred sticking to DOS version. Secondly, there wasn’t a ‘PivotTables’ or a similar feature available in Lotus 1-2-3. The feature, exclusive to Excel, was found to be so useful, that people were willing to learn a new software package for it. Now, let us know in detail about the PivotTables, we will be working with Excel Beta. It may vary a bit for other versions.
What is PivotTables
PivotTables allow the easy analysis of data by summarizing it and unlike the manual summary or analysis, it is interactive and you can easily make changes to it to obtain your desired results. The columns and rows can be interchanged or ‘Pivoted’, this being the primary feature of pivot tables. The data should necessarily be ‘raw’ data, that is, it should not have been summarized before, preferably, some sort of a list.
Let us take an example of a list of sales transactions of a company for the past six months, examine the following data;
As we can see, the above list of figures is a summary of all the salespersons’ transactions in any given month. It is not a ‘raw’ data required to make a Pivot Table, rather, the end product of a PivotTable. To make the above table manually, it might have taken anything from minutes to hours, but using ‘PivotTables’ in Excel, it is just a matter of few seconds. Let us start with the original list of transactions;
With this ‘raw’ data, let us learn how to create the monthly summary as above with Excels’ PivotTables.
Starting with PivotTables
Launch Excel Beta and load some raw data into the worksheet. The data can be a list of just anything. From a list of your collection of books to financial statistics, it can be anything. Once the above raw data list is loaded, select any cell within the report and we can get started with making the pivot tables.
On selecting a cell, go to the ‘Insert’ tab on the ribbon and select the ‘PivotTable’ icon.
Now, a ‘CreatePivotTable’ box will appear with two questions,
- What data should the new PivotTable be based on
- Where should it be created
Since, we had already selected a cell, the entire list has already been selected. The value ‘$A$1:$G$88’, represents the whole list that has been selected. You can also select a list on any other region or another worksheet. There is also an option use an external data source such as Access database or MySQL server database table. Next you can choose where you want to create your PivotTable. It can be either on the same worksheet (existing worksheet) or a different worksheet (new worksheet). Here, let us create it on a ‘New worksheet’.
A blank PivotTable is created on a new worksheet, along with the ‘Pivot Table Field List’. This field list appears on clicking any cell within the Pivot Table.
The list of fields displayed on the top part of the ‘Pivot Table Field List’ is the collection of column headings from the raw data worksheet. You can choose the way you’d want to summarize the raw data on the Pivot Table by making your selections on the lower four boxes. By default, they will be blank. You will have to drag the fields from above and drop it in these boxes. A pivot table is then created based on your instructions. If you don’t get your desired result, you just need to change the contents of the lower four field, drag back the existing values and drop new fields to it. The most important box of the four is the ‘Value’ box. Dropping any column to this box will summarize its contents in some way, either by summing or averaging or determining minimum or maximum, or any other way. It is most often than not, numerical data. Let us drop the ‘Amount’ column to the ‘Value’ box and analyze the result.
You can see the ‘Amount’ checkbox has been ticked and a ‘Sum of Amount’ field in the value box. This indicated that the ‘Amount’ column has been summed. The Pivot Table obtained is,
As per our instruction, the pivot table has been obtained for the summation of the ‘Amount’ column. This is our first Pivot Table. You may not find it particularly useful and would want a little more insight on your data than that. Let us use another column using which we could split this total. So, let us drag and drop ‘Salesperson’ into the ‘Row Labels’ box of the ‘PivotTables Field List’. On updating, your pivot table will start to take shape and look similar to the screenshots that follow,
As you can see, it took no more than a couple of clicks to create what would have taken hours, if manually done.
Now, we have successfully created a useful summary of our data. It gives us enough information. But it doesn’t seem enough. Let us learn ways to create a more complex table and customize them.
Let us start with two-dimensional table. Select ‘Payment Method’ and drag it to the ‘Column Labels’ box in the ‘PivotTables Field List’.
You will obtain a PivotTable similar to the one below,
Now, let us proceed to making a three-dimensional column. All you have to do is drag the ‘Package’ column to the ‘Report Filter’ box as shown below,
Your Three-Dimensional PivotTable is now created and looks like this;
You can now filter your report based on whichever holiday package you’ve selected. The above result as a breakdown Salesperson vs. Payment Method is displayed for ‘All’ packages. You can change it to specify any package, like Sunseekers. Click on the drop down arrow in Packages row and select the ‘Sunseekers’ option. Now your results will be displayed only for the Sunseekers package as shown below,
Now we have a three-dimensional PivotTable ready. Let us customize it further. For example, you only want to see the ‘Cheques and Credit cards’. Select the drop down arrow beside the ‘Column Labels’ and uncheck the ‘Cash’ checkbox.
Your refined PivotTable will look like this,
Formatting the PivotTable
There is a lot of formatting you could do to the existing PivotTables. Currently, the results look plain and boring and the numbers look like any numerals. Let us start from changing the numerals in to dollar currency. The more tempting option is to select the whole table and use standard number formatting buttons to complete the formatting. But the problem arises when you change the structure of the PivotTable. On changing the structure, those number formats will be lost. So, we should use the more permanent way to do it.
Locate the ‘Sum of Amounts’ field in the ‘Value’ box and select it. A menu will appear. From the menu, select the ‘Value Field Settings..’,
On selection, the ‘Value Field Settings’ box appears. Here, select the ‘Number Format’ button. The ‘Format Cells’ box will appear.
From the given ‘Category’ list, select ‘Accounting’ and change the ‘Number of decimal places’ to 0. Click ‘OK’ till you get back to the PivotTable. Now your PivotTable numbers have been correctly formatted.
You can also format the entire PivotTable. On the menu ribbon there will be an option for ‘PivotTable Tools’. Select the ‘Design’ tab from there.
Use the drop down arrow of ‘PivotTable Styles’ to see the collection of built-in styles. Choose any one of them to apply it to your PivotTable.
Your formatted PivotTable will look something like the following screenshot,
Working with other formats
Apart from currency, we can also work with other formats such as dates. With chronological work order, there might be a lot of dates on which transactions would have taken place, but using Excel, you can group the dates into day, weeks, months or years. For this, let us make a few changes to the ‘Column Label’ box. Replace ‘Payment Method’ column with ‘Date Booked’ column.
On doing so, the PivotTable changes accordingly and the data is displayed as transactions per day, resulting in a very wide column. To change this, right-click on any cell containing date, a menu will appear. From the menu, select ‘Group’ and the ‘Grouping’ box will appear. Select ‘Months’ and click OK.
The Table that now appears is much more useful,
Another interesting thing is that you have more than one field in the ‘Row Headings’ or ‘Column Headings’ box. Then your table will be much more detailed, as shown,
Similar editing can be done with ‘Column Headings’ or even ‘Report Filter’ box.
Let us proceed to see how to get Averaged values. In the ‘Values’ box, select the ‘Sum of Amount’ field. From the context-menu, select the ’Value Field Settings’.
In the ‘Value Field Settings’ box, got to the ‘Summarize Values By’ tab and select ‘Average’ from the list.
Here, you can also change the ‘Custom Name’ from ‘Average of Amount’ to something like ‘Avg’.
Once you click OK and go back to your PivotTable, you can notice that all the values have changed from summed totals to averages and the column name has also changed to ‘Avg’;
If you wish, you can have the Sum, Average and Count, all on the same table as shown below,
It’s quite easy actually, what you’ll have to do is on a new PivotTable, drag ‘Salesperson’ into ‘Column label’, then, drag ‘Amount’ into ‘Values’ box thrice. For the first ‘Amount’ field, change its custom name to ‘Total’, format type and reduce the decimal places to 0. For the second ‘Amount’ field, change its Custom Name to ‘Average’, function to ‘Average’ and reduce the decimal places to 0. For the third ‘Amount’ field, change its name to ‘Count’ and function to Count. Then, drag the automatically created
field from ‘Column Labels’ to ‘Row Labels’.
In conclusion, there are a lot of features available in PivotTables. You can explore all its features just by right-clicking on about every cell! The ribbon tabs dedicated to it have even more options for you to explore.