Pivot Table
This article covers meaning & overview of Pivot Table from statistical perspective.
What is 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
Hence, this concludes the definition of Pivot Table along with its overview.
Browse the definition and meaning of more similar terms. The Management Dictionary covers over 1800 business concepts from 5 categories.
Continue Reading:
