How to create Pivot table with different example. (To summarize the data using pivot table)

The above worksheet shows details of account mobilization drive from 1^st  july to 5^th july of Indian Bank in its five branches(NORTH,SOUTH,EAST,WEST,CENTRAL).This worksheet also shows A/C type(CA,SB,FDA),amount and Gender of new customers. Using above worksheet as source data write steps to create PIVOT tables to get the following information.

a)What is the daily total deposit for each branch.

To summarize data using pivot table

To find what is the daily total deposit for each branch.

Click on any cell on the table.

Click on insert Tab, then click on PIVOT table in table group,

create Pivot table dialog box appears.

Select new work sheet and click on OK .

A new worksheet to insert pivot table appears.

From the Pivot Table list drag date field to row labels

drag branch field to column labels

drag amount filed to å values.

The Pivot table is prepared.

b) How many account were opened at each branch, broken down by account type.

To find how many accounts were opened at each branch, broken down by account type.

Click on any cell on the table.

Click on insert Tab, then click on PIVOT table in table group,

create Pivot table dialog box appears.

Select new work sheet and click on OK .

A new worksheet to insert pivot table appears.

From the Pivot Table list drag branch field to row labels

drag A/C type  field to column labels

drag amount filed to å values.

Right click on amount area in the table and select

‘summarize data by count’

The Pivot table is prepared.

c) To show frequency distribution of deposit amount as 1 - 5000,5001-10000,..for all new accounts.

To show frequency distribution of deposit amount as 1 - 5000,5001-10000,..for all new accounts.

Click on any cell on the table.

Click on insert Tab, then click on PIVOT table in table group,

Create Pivot table dialog box appears.

Select new work sheet and click on OK .

A new worksheet to insert pivot table appears.

From the Pivot Table list drag amount field to row labels

drag amount filed to å values.

Click on first column in pivot table area Right click and select grouping.

Enter 1 in starting at 5000 in By box and click on Ok

Right click on second colunm in the table and select

‘summarize data by count’

The Pivot table is prepared.

d)Gender(Male/Female) distribution of different type of accounts opened.

To summarize Gender(Male/Female) distribution of different type of accounts opened.

Click on any cell on the table.

Click on insert Tab, then click on PIVOT table in table group,

Create Pivot table dialog box appears.

Select new work sheet and click on OK .

A new worksheet to insert pivot table appears.

From the Pivot Table list drag Gender field to row labels

drag A/C typefield to column label

 drag amount to å values.

Right click on second colunm in the table and select

‘summarize data by Sum’

The Pivot table is prepared.

e) Gender breakup of accounts opened in different Branches.

Gender breakup of accounts opened in different Branches.

Click on any cell on the table.

Click on insert Tab, then click on PIVOT table in table group,

Create Pivot table dialog box appears.

Select new work sheet and click on OK .

A new worksheet to insert pivot table appears.

From the Pivot Table list drag Branch field to row labels

drag gender field to column label

 drag A/C type  to å values.

Right click inside table and select

‘summarize data by Count’

The Pivot table is prepared

Related Posts by Categories