How to Create Pivot Table in Excel

This video illustrated hot to create pivot table in excel.

[embedyt] https://www.youtube.com/watch?v=2enbb8iqtMM[/embedyt]

What is a Pivot Table?

A pivot table is an excel report that quickly summarise a large amount of data.

It automatically performs sort, count, total or average of data stored in the spreadsheet and displays result as a report.

Usually, the reports will be stored in another worksheet and is the default option. However, it is also possible to specify in other locations such as in different spreadsheet file.

Suppose we have the following list of sales records for a company and we want to create a report that lists sales amount by city, shipping mode for each region.

Following is the steps to create the Pivot Table.

1. Select all data for the report
2. click “Pivot Table “ from the insert ribbon
3. Select destination of the PivotTable
4. click “OK”
5. Drag and drop fields (columns) into the corresponding area

Explanation

Suppose we have the following spreadsheet that lists all sales record for a company and now I want to calculate the number of sales for each individual city and group the result by region. The easiest way is to create a pivot table like this.

Step 1 Select all data for the report

To create a pivot table like this I firstly go to the Orders sheet, this is where the source data stored.

Then I select the entire table, in this scenario because we have over 9000 records, I simply select all columns from column A to column T.

Step 2 click “Pivot Table “ from the insert ribbon

Go to the “Insert” ribbon and click pivot table.

Step 3 & 4. Select destination of the PivotTable & Click “OK”

After clicked create pivot table Excel will automatically displayed a the  create pivot table dialogue.

As we have already selected the data range it is filled automatically.  Now the next and the only thing needed to do is to choose where we want to place the pivot table report or should say the report. By default, it will go for a new spreadsheet. However we can also click existing location  and then enter the location that we want the PivotTable should be stored under.

Now we simply go for new worksheet and click “OK”.

Step 5. Drag and drop fields (columns) into the corresponding area

After click ok it will automatically create the pivot table in a new worksheet. It also populate the pivot field window.

What is Pivot Field?

The answer this very simple it is simply columns. 1 fields in pivot table is equal to 1 columns in the source table.

The pivot field windows allow us to assign where should a column be placed in the pivot table.

The pivot field windows have 5 lists.

1. Pivot field list
2. filter list
3. row list
4. column list
5. Value list

A pivot table is separated into 4 different areas and the pivot field windows allow us to specify which area should a field be placed under.

This is the rows

This is the filter

This is the columns

This is the values

Demonstration of how to create a pivot table?

Now let’s see how to create a pivot table

To format a pivot table as like above, we need to assign the following fields into the corresponding area

First things to do is definitely go back to the worksheet that’s we just create click the pivot table.

1. Drap and drop the field City into the Row list
2. Drap and drop the “Ship Mode” into Columns List
   
   
3. Drap and Drop the Sales into Values
4. Drap and Drop the Region field into the filter

Now you can see that the pivot table automatically summarised the report by counting number of sales for each City and ship mode. However, this is different from our requirement, we are requiring to sum the amount of sales for each City and ship mode. To do this, we have to change the method of calculation for the sales field. Following is the steps:

1. Click the Sales Field from the Value list
2. Select “Value Field Settings”
   
3. Change the Summarise Value Field By “Sum”
4. Click “Number Format”
5. Change the Number Format to the Currency format.

Now we have created the pivot table as what we wanted.

This is the way to create a very basic pivot table. Pivot Table is much more powerful than this. It can be very complicated. However, as long as you are familiar with it, you will find the joyful of using a pivot table to summarise to report.