Pivot Table

Posted in Statistics, Total Reads: 666
Advertisements

Definition: Pivot Table

A pivot table is a format which comprises of information of two or more data sets, which is listed down in a tabular format, and which helps to analyse the information and data using sorting, getting a summary etc. It is also useful in calculating totals, averages, maximum values etc.


The functionality of Pivot Table was first introduced in Microsoft Excel 5 in 1994 which was followed by improved features in later versions of Excel. In 2007 Oracle introduced similar functionality called PIVOT and UNPIVOT in Oracle 11g


Illustration:

S. No

Item

Brand

Units

Price (in $)

Date

1

Bat

B1

10

15

1/6/2015

2

Bat

B2

5

18

1/6/2015

3

Bat

B3

7

12

1/6/2015

4

Deuce Ball

D1

3

10

1/6/2015

5

Deuce Ball

D2

5

11

1/6/2015

6

Deuce Ball

D3

7

5

1/6/2015

7

Deuce Ball

D4

4

6

1/6/2015

8

Deuce Ball

D5

2

5

1/6/2015

9

Wicket

W1

7

7

1/6/2015

10

Wicket

W2

3

5

1/6/2015

11

Gloves

G1

4

6

1/6/2015

12

Gloves

G2

8

11

1/6/2015

13

Gloves

G3

5

13

1/6/2015

14

Bat

B1

12

15

1/6/2015

15

Bat

B2

4

18

2/6/2015

16

Bat

B3

7

12

2/6/2015

17

Deuce Ball

D1

3

10

2/6/2015

18

Deuce Ball

D2

8

11

2/6/2015


The above table can have hundreds of rows for the table making it difficult to summarize data for users. Pivot Table can be useful, in such cases, to elicit only required information in summarized form.

Item

1/6/2015

2/6/2015

3/6/2015

4/6/2015

Bat

22

23

15

25

Deuce Ball

21

20

13

18

Wicket

10

15

8

11

Gloves

17

14

8

15


The table above shows the output of the Pivot Table function (The information sought here is units sold of the Merchandise ABC Date-wise with aggregation (sum) used on number of units.)


Pivot tables are not automatically created. For example, in Microsoft Excel, the entire data in the original table must be selected first and then move to Insert -> Pivot table; a new worksheet comprising a Pivot Table Field List will be created that shows all the column headers present in the data. 


Example of a pivot table for a sales company

Date of Sale

Sales Person

Item Sold

Color of Item

Units Sold

Per Unit Price

Total Price

10/01/15

David

Notebook

Blue

8

2500

20000

10/02/15

Mike

Laptop

Grey

4

3500

14000

10/03/15

David

Mouse

Grey

6

85

510

10/04/15

Mike

Notebook

White

10

2700

27000

10/05/15

Mike

Mouse

Blue

4

80

320


On the right hand side of the worksheet, the fields that will be created are visible. The layout design of the pivot table would appear below this list by default.

Each fields from the list can be dragged on to the layout, which contains following four options:

• Report Filter

• Column Labels

• Row Labels

• Summation Values


Advertisements



Looking for Similar Definitions & Concepts, Search Business Concepts