How to make a pivot table

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?

  1. Summarizing and sub-totaling the data from a large data table
  2. Categorization and sub categorization of data based on your requirements
  3. Automatic sorting, filtering, data drill-down and aggregation without the need of using any formula
  4. Getting the subset of data which are important and meaningful
  5. Pivoting the data from rows to columns and from columns to rows to quickly visualize the data in various forms
  6. Finding average, maximum, minimum, data-count, etc.
  7. 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.

YearMatchPlayerRuns
20111Ram56
20111Tom20
20111Alex16
20111Santosh86
20111Robert52
20112Ram28
20112Tom42
20112Alex15
20112Santosh5
20112Robert65
20123Ram51
20123Tom23
20123Alex62
20123Santosh1
20123Robert0
20125Ram21
20125Tom18
20125Alex72
20125Santosh41
20125Robert53

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).

Icon of Pivot Table

Pivot Table in Excel

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.

Pivot Table Wizard

Excel Pivot Table Wizard

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.

Excel Pivot Table Field List

Field List in Pivot Table

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!

Pivot Table Example

Example of Pivot Table

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.

Rearranging fields

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.

Rearranging value fields

Rearranging value fields in Pivot Table

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.

Tips

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. You must have the header row in your data field (column name of each field) to create a pivot table.

4 thoughts on “How to make a pivot table

  1. Thanks
    Using the pivot table I want to customise the sequence of custom labels.
    pl. advice asap
    Best regards

  2. Can you please clarify the following “4.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.”

    Should one select the fields they wish to be used in the Pivot table, create the Pivot table and then filter from within the Pivot table?

    • While creating the pivot table, first drag the fields to report filter area and apply the filter. After that drag the fields in row labels and values. This will take lesser time for data to be populated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>