Introduction to Pivot Table

Pivot Table for Data Analysis

We know that a lot of tools are available for data analysis. But Pivot Table in Excel provides the best and easy method of data analysis.

A pivot table is a powerful tool of excel to summarise and present a meaningful report of complex data in a table. Introduction of pivot table has created a new life for data interpretation and presentation. The main users of pivot tables are accountants, auditors, other finance and compliance professionals.

Let us discuss a simple example to understand the peculiar features of pivot tables. Mr Alex was working as an Accountant in the financial service firm. His reporting manager asked him to make a monthly expense report and its analysis based on a quarterly report of the regions.

Mr Alex did not have that much knowledge of excel and he consulted us for advise. After hearing his requirement, we taught him the technique of a pivot table. It was very simple and less time consuming and apart from all, it was very easy also.

See how he solved this problem

Learn the Application of the Pivot Table

A sample format of the excel table given by Mr Alex’s reporting manager is given below.

Next step to make this report in a table. To do that we have to enter Ctrl + T

Then the table will display as shown below

Then go to the insert option in the ribbon, then click Pivot Table option then it will display as given below

Then it will create a pop in excel as “create Pivot Table” option, and there we have to select the data we want to analyse.

Select Table Range – Normally it has selected the table like “Table 1”.If it has not selected the table or range, you have to select the range or table by dragging or clicking the table.
Use an external data source – This option is using for analyzing an external data file and it is an advanced level which will discuss in another post
The place where you want to post the pivot table – Here normally we are selecting the “New Sheet” so the pivot table will display in a new sheet otherwise we have to select an “Existing Worksheet”, Then it displays in the selected place in the same worksheet.

It displays a table shown below and clicks “Ok”

Then on the new sheet, we can see two tables. On the right side, “Pivot Table Field List” and based on your analytical requirement, you can drag the rows and columns into the table and the same will display on the left side.

In “Pivot Table Field List”, we can see “Row Label” “Column Labels”, “Values”, “Report Filter” and the list of fields to add to the report.

Row Label is used for representing the row of the report

Column Label is used to represent the column of the report

Values represent the numerical figures corresponding to the row and column in the report

Report Filter is used to filtering the date with specified parameters in the report

Suppose Mr.Alex want to find the region-wise expense heads for the first quarter and analyse, it is simple to do with the pivot table. He just needs to drag the “Head of Expense” from the field to” Row Label”, “Month” to “Column Label”, “Payment in USD” to “Values” and “Region” to “Report Filter”

The picture depicted below will give you a clear idea about it

Like this, he can solve and analyse much information from a complex data table.

From this report, If Mr Alex want to see the report in Region wise, he has to select the filter option on the top of the report

Click “All” option corresponding to the Region column then select the required region and automatically the Pivot Table will display the selected region’s expense heads and the corresponding amount