How to create a Pivot Table in Excel?

In the previous tutorial we learnt what is the pivot table feature in Excel. In this tutorial you will be able to learn how to create a Pivot Table.

Let’s move straight into a simple example. This guide will be very easy and useful for you to understand the essential steps of how to create a pivot table.

Here I am considering a data set containing annual wages of three different ranks of university professors in two different disciplines.
You can download the data set using below link.

In this data set there are columns for id,rank, discipline, years since PHD, years of service, sex and salary. There are exactly 397 rows of data in it.

How to insert the Pivot Table?

Pivot Table Fields Task Pane

Top section is the fields sections which lists down the available fields for Pivot Table. Bottom of the PivotTable Fields Task Pane has the “Areas” section four Areas labelled as FILTERS, COLUMNS, ROWS and VALUES.

You can drag and drop suitable fields into these four section from Fields section.
FILTER: The fields that you intend to use to filter the data of the PivotTable should be placed within the FILTER AREA
ROWS: Values of the fields that will be placed within ROWS AREA will be used to create the rows of the Pivot Table.
COLUMNS: Values of the fields that will be placed within COLUMNS AREA will be used to create the columns of the table.
VALUES: The fields that will be placed within the VALUE AREA will be used to calculate and display the values of cells of the Pivot Table.

Setting Up the Pivot Table Layout.

You will notice that after dragging the “salary” field into VALUES AREA it changes to “Sum of salary”. It gives the sum of salary for all the rows in original data set that match with given row and column criteria in Pivot Table. If there are five Assistant Professors in discipline “A” who has 10 years of service, it gives the sum of salary of the Assistant Professors in that category.
Let us say that we need to display the average salary instead of sum of salaries for each value field.

How to change value field settings in pivot table

1. Click the “sum of salary” item within the VALUES AREA
2. Click “Value Field settings…” menu item from the menu popped up.
   “Value Field Settings” dialog box will be appeared.
3. Select “Summarize Values By” tab
4. Select “Average” from the selected field.
5. Click OK.

Now you have completed the setting up of your basic Pivot Table. This table summarizes the average salary of each category of professors with different years of service.

How to group columns in pivot table

You can group the columns or rows of Pivot Tables. Grouping is a very useful feature. We will discuss about grouping in a different tutorial in detail.
For the time being, let us group the years of service columns into class intervals of 5 years. In this example we use automatic grouping option. This option works only on numerical or date fields only.

How to group items using automatic grouping option?

1. Click one of the field items in years of service column labels.
2. Go to “Analyze Tab” → “Group” command group → Click “Group Field”.
   The “Grouping” dialog box appears.
3. Keep the “Starting at” and “Ending at” checked and default values unchanged. Make sure they represent the actual ends of the data series.
4. Change the value of input box labeled as “By:” to 5.
5. Click OK
   You will notice the columns of the table has been changed to represent class intervals of 5, in years of service.

ON and OFF displaying Sub totals and Grand totals

You can easily on or off sub totals and grand totals in a Pivot Table.

1. Go to “PivotTable Tools” → “Design” → “Layout” command group.
2. Click “Subtotals” → Select preferred option from the drop down menu. In this example let us click “Do Not Show Subtotals”

3. Click “Grand Totals” → Select preferred option from the drop down menu. In this example let us click “Off for Rows and Columns”.

How to change pivot table style

You can select any of the styles from the “PivotTable Styles” section of the “Design Tab”.
If you do not like any of the available styles, you can create a new Pivot Table Style.
You can also have the following options under “PivotTable Style Options”

• On and Off column and row headers.
• Apply or remove banded columns and rows to the Pivot Table.

How to change the number Formatting of Pivot Table Values

It will be very important to change the Number Format of the Pivot Table Values. You can follow the steps given below for that purpose.

1. Click the relevant field item within the VALUES AREA of the Pivot Table Fields Task Pane. In this example, click on “sum of salary” item.
2. Click “Value Field settings…” menu item from the menu popped up.
   Value Field Settings dialog box will be appeared.
3. Click “Number Format” button.
4. The usual “Format Cells” dialog box appears showing only the options for changing number formats in cells.
5. Change the number format for Pivot Tables to suit the requirement.
   In this example we have to use currency formats.

Download the worked example file here.