Pivot Table in Excel is a powerful tool to summarize, analyze and visualize data. Excel Pivot table reports and charts help you to summarize large amount of data, do automatic calculations like sorting, filtering, summing, averaging, etc. and extract information as per your requirement.
If you are reading this article, I assume, you have at least the basic knowledge of spreadsheets and can perform simple tasks like sorting, filtering, editing and formatting an excel worksheet. Many people think that Pivot tables are for advanced users and shy away from using it. Let me tell you at this point that if you handle a lot of data and require analyzing and summarizing it, pivot tables can do wanders for you. Once you have the understanding about pivot tables and learn how to use it effectively, you will save a lot of your time and make the correct decision based on the data visualization with trends and comparison.
If you read and practice at the same time, you will learn faster. It will be a good idea to open a new excel sheet or download this workbook with pivot table.
What can Pivot Tables do?
- Summarizing and sub-totaling the data from a large data table
- Categorization and sub categorization of data based on your requirements
- Automatic sorting, filtering, data drill-down and aggregation without the need of using any formula
- Getting the subset of data which are important and meaningful
- Pivoting the data from rows to columns and from columns to rows to quickly visualize the data in various forms
- Finding average, maximum, minimum, data-count, etc.
- Listing down the unique value in any row or column
Well, a lot of theory here… Why not take a simple example and see the Pivot table in action… live!
About Excel Pivot Table
As I told you, pivot tables are most helpful when you need to analyze, compare or summarize a large amount of data. However, for understanding purpose, we will create a small data table. Let’s take an example of a cricket team and popularize the runs made by five players in five matches played in two years. We will analyze the performance of the team as a whole and of each player with respect to the runs made.
You may like to copy this table in your new excel sheet or use the downloaded excel file.
How to make a Pivot Table
Select the data range and click on PivotTable icon from insert ribbon (If you are using earlier versions of Microsoft office, Go to Data Menu and select PivotTable and PivotChart report).
You get a create PivotTable wizard which asks you to select a table or range. Here, since we have already selected the required range, we don’t need to do anything. Even if you don’t select the data previously, the PivotTable will automatically select suggested data for you.
Next you would be asked whether you want to place the report in a new worksheet or existing worksheet. If you select the existing worksheet, you will be asked for target location. For convenience and ease of learning, we will place our pivot table in a new worksheet.
As you finish the wizard clicking on ‘ok’, a new worksheet is automatically inserted and PivotTable Field List appears along with a blank pivot table report.
PivotTable Field List
Pivot Table Field List is used to add, remove, arrange and reposition the fields in the pivot table. If you look at it carefully, you will find that it is divided in two sections. The top section has the list of fields (all the headers) and the bottom section provides the layout to place and arrange the pivot table as per your requirements.
Here comes the most interesting part- Laying out the Pivot Table!
In the layout section of PivotTable Field List, you have four areas – Report Filter, Column Labels, Row Labels and Values. You can drag and place the fields from top section in any of these areas. As you drag any field in any of the areas in layout section, the same field also simultaneously gets included in the corresponding area of the PivotTable. Interesting…isn’t it?
Now the question is that which field to drag in which area? This totally depends on your requirement. Let’s understand it with an example. Say, we are interested in getting the summary of runs made by each player in each of the matches along with their grand total. So, we want the name of the players in rows and match labels in columns. To make a pivot table for this, first drag the player field in Row Labels area. Also observe the changes in the Pivot Table. Now drag the match field in Columns Labels area. Lastly drag the runs in Values area. Great…you have just created your pivot table in excel!
Additionally, if you wish to filter the data year wise, just drag the year field in the report filter area. By default, all is selected. Here you have the option to select any one data or make multiple selections. As you make changes here, the data changes simultaneously.
Now that you have already created a Pivot Table, it’s time to rearrange the fields and do experiments. Try moving the fields from Row Labels to Column Labels to Report Filter. Every time you move any field from one area to another, the pivot table layout and values change accordingly. Note here that you can add any field to either of the Column Labels, Row Labels or Report Filter area. Try adding a field to multiple areas. What happens? The field is automatically removed from its original area. Also note here that you can add two or more fields to these three areas. Experiment by dragging player, match and year fields in Row Labels and see the result. If you wish to remove any field, just click on it and select Remove Field. If you clear the check box in the field list, you will remove all instances of that field from Pivot Table.
The idea is you should keep manipulating the fields between different areas until you get the result of your choice. By doing some practice here, you can soon become an expert to get the desired result in a pivot table.
Value Field Settings
By default, value field is summarized by summation. However, you have the option to do the calculations of many types like average, count, max, min, standard deviation, etc. For doing this, click on the added field in values area (in this case Sum of Runs) and select value field settings. You get the value field setting window to select the desired function.
Once you are done with creating your PivotTable report or chart, try formatting it. Go to design ribbon and select a PivotTable style.
Drilling Down the Summary Value
This is one of the most important functions of pivot table report. Suppose you want to have the drill down of any summary value. Just double click on it and a new sheet will automatically be inserted with the drill down report of the corresponding value.
- Feel free to resize and drag the PivotTable Field List and place it anywhere in the worksheet. You can also dock it to either the left or right side of excel window.
- If you want PivotTable Field List to appear, click anywhere inside the pivot table. If you have closed PivotTable Field List window, right click the PivotTable -> Show field list.
- PivotTable Field List can automatically create a layout for you based on the type of the fields (numeric, date-time, text, etc.) Experiment by clicking on check boxes against each field to see the magic.
- If you are dealing with large volume of data especially from external source and want to create the pivot table based on filtered data, it is a good idea to first assign the field(s) for filter area and select the data to be filtered. It will significantly reduce the time of PivotTable creation and subsequent update.
- You are free to use the external data from various sources like Microsoft Access Database, Text Files, XML and other excel sheets. For doing this, choose external data source and follow the instructions (use data connection wizard for new connection) after clicking on ‘Insert PivotTable’ icon.
- You must have the header row in your data field (column name of each field) to create a pivot table.