TOPIC 8 - ANALYSING

DATA USING PIVOT TABLE

How Pivot Table Works?
In certain circumstances, we need to produce a summary of the data in a more meaningful
format. Pivot Table enables users to summarise the data by specifying the row and column as
well as to include a filter that restricts the data with more specific characteristics.

Components of Pivot Table

List of fields
available in data
source

Pivot Table output

Specify field(s) to Specify field(s) to

filter data appear in column

Specify field(s) to Specify field(s) to

appear in rows produce values

Figure 6-1

Detail description of each parameter set for the Pivot Table is given below:

1
Fields List: Fields List shows all the individual field included in the Pivot Table. The fields
listed in this section depends on the source of data specified by the user. The fields may come
from single or multiple data sources.

Column: A field that appears as a column in the Pivot Table output. Each item identified in the
field occupies a column. In such case, a field with five unique items will occupy five columns
(unless the user imposes a filter to the field).

Rows: A field dragged into this section will appear as a series of rows in the Pivot Table output.
Like Column section, each item listed in the specified field will occupy a row in the output
(unless the user imposes a filter to the field included in Row section).

Filter: Filter section allows the user to limit the records produced in the Pivot table output based
on specific fields. Having specified the field for filtering purpose, users then may choose specific
items listed under the respective field.

Values: Value is the most crucial section for data analysis as it represents a field to be used for
the summary purpose. The value represents measures such as a sum of quantity, the sum of
amount, average/minimum or maximum value from a given data.

STRUCTURING DATA SOURCE FOR PIVOT TABLE

Structuring the data source to accommodate the Pivot Table analysis is an essential step to
consider. Meaningful analysis of the data using Pivot Table depends highly on how the data is
being structured.

Figure 6-2

2
 Figure 6-3

BUILDING PIVOT TABLE

Using Table or Range of Data

Identify the data table. For this section, open a datafile named
‘Compushop_Trading_Sales_Report.xlsx’ (obtain the data file from
https://tinyurl.com/excel-tissa-2019).

3
 To show/hide
fields list

Task 1: Show Total Sales by Region for CompuStore Trading.

The steps required to complete Task 1 are given below:

Add the following fields into the Pivot Table (Table 6-1):

Field Name Section

Region Rows
Sales (RM) Values
Table 6-1

4
 Figure 6-4

The refined output will be as Figure 6-10:

Figure 6-5

Task 2: To add another dimension on the Pivot Table output. For this task, we are going to
extend the Sales report by showing the amount of sales order contributed by each product
category for each of the region.

Field Name Section

Region Rows
Product Category Column
Sales (RM) Values

5
6
SHOWING SUBTOTALS AND GRAND TOTAL IN PIVOT
TABLE

Total and Subtotal Reporting

Task 3: Produce multilevel reporting and subtotal of total order by region and its respective
stores.

Using the same output from Task 2, revise the Pivot Table Field setup as follows:
Field Name Section
Region Rows (first line)
Store ID Rows (second line)
Product Category Column (first line)
Sales (RM) Values

7
USING AND CUSTOMISING A PIVOT TABLE

Sorting the Data in Pivot Table

a. You may sort your data based on your row or column field to have a better view of the
pattern of the data.
b. Referring to Task 3 output earlier, by default, the data in column and row are sorted
alphabetically.

Task 4a: Sort the Pivot Output in Task 3 by Product Category (Descending order).

Task 4b: To sort the Grand Total sales amount (by Region) in ascending order. Similarly, you
may apply the Sorting option on the cells containing value using the following steps:

Apply Filter into Pivot Table data

In certain circumstances, the user would like to identify and produce only records that meet
certain criteria. The filter function in Pivot Table helps the user to achieve this purpose.

There are two ways of filtering data in the Pivot Table:

 To show the selected item (s) in a given column/row (Task 5)
 To insert a specific field for filtering purpose (Task 6)

Task 5: To filter sales data only based on the Product Category, i.e. Desktop and Laptop.

8
Task 6: To apply data Filter using selected Field (s). For this task, we are going to filter the
Sales data for all stores located in Central and South Regions.

Setup the Pivot Table as follows:

Field Name Section

Region Filter section (first line)
Store ID Filter (second line)
Product Category Row (first line)
Product Code Row (second line)
Quarter Column
Sales (RM) Values

9
 Figure 6-6

Changing Data Field Summary Calculation

To view what are the values contributed to the sum of sales for each cell, double click the cell,
and a new sheet will be created to show the details of cell values that contribute to the sum of
sales.

10
Other than Sum, there are, however, several other options available in presenting numbers, e.g.
Average, Maximum, Minimum, Count etc. The paragraph below summarises several summary
options available for Pivot Table:

Sum : adding up values from all underlying data

Count : Display the total number of values in the underlying data
Average : Produce an average of the values from the underlying data
Max : Report the highest value from the underlying data
Min : Report the smallest value from the underlying data
Product : Calculate the product (by multiplying of the values for the underlying
data)

Task 7: Change the Pivot Table output produced in Task 6 as Average.

11
Difference Summary Calculation
Difference summary calculation facilitates user to compare one part of the data with another. The
use of difference summary enables users to produce a difference value based on two different
parts of the data.

Task 8: Produce a Pivot Table that shows the total differences (value) between Q2, Q3 and Q4
in comparison with Q1 Total Sales.

Percentage Summary Calculation

Other than presenting value for each column, the Pivot Table function also provides additional
options in presenting figure. Several options available for percentage summary calculation that
you may try out include:

 % of
 % of Row Total
 % of Parent Row Total
 % of Column Total
 % of Parent Row Total
 % of Parent Column Total
 % of Parent Total

12
 % of Grand Total
Task 9: Change view of Sales values for each Product Category to the percentage (%) of Total
quarterly sales (for all regions).

Task 10: Change view of Sales values for each Product Category to the percentage (%) of
Grand Total

13
Using Index Summary Calculation
Based on the previous task output, we could observe that Printer (with Product code of 980) has
reported the highest contribution towards the Total Sales of the year. Does this mean, this
product is utmost important than other products? Index Summary Calculation function enables
the user to specify the relative importance of values presented in each cell.

Task 11: Produce Index Summary Calculation based on Quarterly Sales as reported for each
product code.

The index value for each data cell is computed based on the weighted average of each cell, as
generated using the following formula:

(Cell value * Grand Total) / (Row Total * Column Total)

As indicated, Printer (Code: 980) has the highest index value relative to another data cell,
suggesting its relative importance over the Grand Total.

Figure 6-7

14
Running Total Summary Calculation
Apart from reporting quarterly sales report, it is also useful to observe the cumulative sales report
as the fiscal year progress from one quarter to another. In this case, we might want to view the
cumulative sales total from Quarter 1, Quarter 2, Quarter 3, Quarter 4 and the Grand Total. In
Pivot Table function, such feature refers to Running Total Summary.

Task 12: Report cumulative Total Sales from each quarter.

15
Group Pivot Table Items
A pivot table allows us to group multiple items, e.g. Product Category, as a group.

Task 13: Group Desktop and Laptop Product Category as ‘Computer’.

New group created

16
 Figure 6-8

CUSTOM PIVOT TABLE CALCULATION

Creating Calculated Field

In certain circumstances, the user would like to perform a specific calculation based on the data
included in the Pivot table. The calculated data is expected to transform the existing data in
certain manner that would be more relevant for user’s decision making.

Task 14: Compute the profit margin based on the Sales value using the Calculated Field
function. Assume, the profit margin has been set as 30% of the Sales value.

17
 Double click selected
field to be included into
the formula from this list

Task 15: Compute the tax amount based on ranges of sales amount of all product categories. For
store sales of RM350,000 and below, the tax amount will be 20% while the sales above 350,000
will be taxed for 25%.

18
 Figure 6-9

Insert Calculated Items

Other than inserting a calculated field, we may also insert calculated items into the Pivot table.
This function allows us to sum up the values of selected items of any field.

Task 16: Create a calculated item that totals up sales of Laptop and Desktop (label the combined
fields as Computer Sales).

19
Figure 6-10

Newly combined
items
Figure 6-11

20
PRODUCING DYNAMIC CHARTS FROM THE PIVOT
TABLE

PivotChart, as the name implies, is an excellent tool for users to produce a graphical output based
on the selected data available. However, PivotChart is somewhat different than the regular Excel
chart. While PivotChart promises several useful functionalities that regular chart does not offer
such as hiding or items from chart output as well as enable chart refresh as a result of underlying
data change. In another respect, it poses limitations in relative to the regular chart, such as the
applicable chart types and formatting options are somewhat limited.

Task 17: Produce a PivotChart showing annual sales by Region.

Filter Column
- -
Row Value
Region Sales Amount

Chart Options

21
Task 18: Produce PivotChart showing Quarterly Sales data by Product Category. Also, to allow
users to choose the Region and Store of their choice for the PivotChart output.

Filter Column
Region Quarter
StoreID
Row Value
Product Category Sales Amount

22
 Figure 6-12

Note:
 Do observe that, the PivotChart is linked together with the PivotTable. Hence, any
changes made, e.g. deleting or filtering the data in one part of it will be automatically
reflected in the other.
 Choose any elements of the chart and right-click to explore various options available to
enhance the PivotChart.

23
USING SLICER FUNCTION

The purpose of Slicer is to filter the data in the Pivot Table output visually. It works very
similarly to the Report filter function. Therefore, you may either consider either the Report Filter
or Slicer function.

Task 19: Using the slicer to navigate the data in the Pivot Table.

Set up a new pivot table by specifying the followings:

Filter Column
- Quarter
Row Value
Product Category Sales Amount
Table 6-2

Figure 6-13

Step 1: For this exercise, insert slicer based on the following fields:
a. Region
b. StoreID
c. Product Category
d. Product Code
e. Quarter
24
Filter the sales data based on each of the following cases:

a. Quarterly Sales of Desktop and Laptop for all Stores located in the Central region.

Note: Notice that, only S-01, S02 and S-03 are automatically selected (only three
stores located in Central region). The same applies to Product Code.

b. Computer Accessories Sales report of all stores in Quarter 4.

25
c. Present a line graph showing a comparison of Sales report of all Product Categories
(EXCEPT Desktop and Laptop) in Quarter 3, between East and North Regions.

26
Figure 6-14

27
MODIFYING THE DATA AND REFRESHING THE PIVOT
TABLE

In case you have made changes on your data set, e.g. add new records, add new fields or
modification of the data, you need to refresh the pivot table so that it will consider the updated
data.

Figure 6-15

UPDATING OR CHANGING DATA RANGE FOR PIVOT

In case the user would like to change the data source for the Pivot table.

28