PIVOT TABLE

PIVOT TABLE

Formula Use & Explanation: A Pivot Table enables you to sort out and calculate a huge sums of data fairly easily.

Example Used: Show the total salary amounts paid out to each of the different Designations in the company.

PIVOT TABLE STEPS IN DETAIL

STEP 1

• On your data sheet, Click on Data from the toolbar and then click on ‘Pivot Table and Pivot Chart Report’.

STEP 2

• Select the two options as shown circled in Red below i.e. ‘Microsoft Office Excel List or Database’ & ‘Pivot table’ then click ‘Next’.

STEP 3

• Now select the entire data table as shown below i.e. from Cell B4 to Cell E13 then click ‘Next’.

STEP 4

• Click on ‘Layout’ , as shown below.

STEP 5

• Drag all the headings from the right of the screen into the ‘Row’ column, as shown below.

STEP 6

• Now drag just the heading ‘Salary’ once again and this time drop the heading into the ‘Data’ column which is in the center, as shown below.

• Now double click on the word ‘Salary’ that you just dragged, and select ‘Sum’ from the options shown in the box, this is done since we need to calculate the total salary amounts paid to each designation.

• Click OK.

STEP 7

· Next select the two options as shown below i.e. ‘Preserve Formatting’ & ‘Refresh on open.’

· Next click on Finish.

STEP 8

· What you see below is an un-formatted Pivot Table.

STEP 9

· To format the Pivot Table double click on each of the headings, as shown below.

· In the box that appears, select ‘None’ and click OK.

STEP 10

· What you now see is a formatted Pivot Table which shows the sum total of all the salary’s paid.

STEP 11

· You can also choose individual Designations from the drop down box on the top left corner of the sheet, as shown below.

STEP 12

· To format the cell borders etc. select the entire Pivot Tale as shown below, and click Format Cells.

· Or you can also use the default formatting templates, by clicking on Format from the toolbar and then click on Auto Format.

A complete Pivot Table will look like this: