Bachelor of Science (Honours) in Data Science and Artificial Intelligence
DA 102 – Pivot Tables
Learning Objectives
01 Understand the concept of pivot table
02 Create pivot table given source data
03 Field placement and customization
04 Aggregation and summary functions
05 Drill down and detailing data
2
Objectives
● You should be able to define what a pivot table is and explain its purpose in data
analysis.
● You should be able to identify the prerequisites for creating a pivot table, including
well-structured data.
● You should be able to demonstrate the steps to create a pivot table in Excel.
● You should be able to differentiate between the pivot table field areas: Rows, Columns,
Values, and Filters.
● You should be able to customize the pivot table layout, including changing row/column
labels and value calculations.
3
Data & Its Summarization
Data & Its Summarization
● Consider data of the form
Sales information is presented
Date of sale
Color of the product
Region in which sale happened
Number of units sold
Sale value
6
Data & Its Summarization
● Consider data of the form
Obtain total sales value
Obtain total sales value by Color
Obtain total sales value by Region
Obtain total sales value by Color and
Region
Obtain total sales value by Color, total
units sold
Obtain total sales value by Region and
total units sold
Obtain total sales value by Color and
Region, Units sold by Color and Region 7
Data & Its Summarization
● Consider data of the form
Obtain total sales value
Name range: sales: F5:F456
SUM(sales)
8
Data & Its Summarization
● Consider data of the form
Obtain total sales value by Color
Name range: color - C5:C456
Steps:
1. Obtain UNIQUE Color as
UNIQUE(color)
2. Apply conditional sum on the
obtained values
9
Data & Its Summarization
● Consider data of the form
Obtain total sales value by Color
Steps:
1. Obtain UNIQUE Color as
UNIQUE(color)
2. Apply conditional sum on the
obtained values
10
Data & Its Summarization
● Consider data of the form
Obtain total sales value by Color and
obtain column-wise presentation
Steps:
1. Obtain UNIQUE Color as
UNIQUE(C5:C456)
2. Apply conditional sum on the
obtained values
11
Data & Its Summarization
● Consider data of the form
Obtain total sales value by Region
Name range: region – D5:D456
Steps:
1. Obtain UNIQUE Region
as UNIQUE(D5:D456)
2. Apply conditional sum on
the obtained values
12
Data & Its Summarization Obtain total sales value by Color and
Region
● Consider data of the form
Steps:
1. Obtain UNIQUE Color
2. For each Color, obtain UNIQUE
Region
3. Compute conditional sum with
multiple criteria
13
Data & Its Summarization Obtain total sales value by Color and
Region
● Consider data of the form
Steps:
1. Obtain UNIQUE Color
2. For each Color, obtain UNIQUE
Region in transpose form
3. Compute conditional sum with
multiple criteria
4. Note change in the formulas
14
Data & Its Summarization
● Given the data and the queries the formulas change
● Given change in the presentation the formulas change
● Given the change in queries, formulas change
● Formulas requires maintenance
● Reporting is not flexible and easy
● Further analysis on the data needs augmenting the formulas
15
Pivot Table
● A pivot table is a dynamic data summarization and analysis tool
● It allows users to extract insights, trends, and patterns from complex datasets
● It organizes and summarizes data
● Pivot table results in a manageable and understandable form of data
● Key characteristics of pivot table are
16
Pivot Table Characteristics
● Summarization: Pivot tables help summarize and aggregate large datasets by grouping and
categorizing data based on user-defined criteria.
● Flexibility: Pivot tables easily rearrange and manipulate the layout of the pivot table to view
data from different angles and dimensions
● Calculation: Pivot tables can perform various calculations, such as sums, averages, counts,
and more, on the data within the table.
● Dynamic Updates: Pivot tables are dynamic, meaning they automatically update when the
source data changes.
● A pivot table acts as a pivot point for your data, allowing you to pivot or reorganize the data
in various ways to extract meaningful insights and make data-driven decisions more
effectively
17
Pivot Table – Source data requirements
● No blanks: Source data should NOT have blanks
● Special characters: Source data should NOT have special characters
● Blank rows: Source data should NOT have blank rows
● Blank columns: Source data should NOT have blank columns
● NO section headings: Avoid data in section headings
18
Pivot Table – Creation
● Click on any row of the data table
● Click "Insert" on the ribbon
● Choose Pivot Table
19
Pivot Table – Creation
● A dialogues box appears and asks for two inputs:
● What is the data table cells range?
● Where to place the pivot table?
20
Pivot Table – Creation
● Data table cells range – Excel will be able to guess the cells range and pre-populate it.
● In this example, Excel identifies the rage as: 'pivot-table-02-example'!$B$4:$F$456'
● pivot-table-02-example is the sheet name
● $B$4:$F$456 is the range of cells in the sheet on which pivot table need to be constructed.
21
Pivot Table – Creation
● Data table cells range – Excel will be able to guess the cells range and pre-populate it.
● In this example, Excel identifies the rage as: pivot-table-02-example!$B$4:$F$456
● pivot-table-02-example is the sheet name
● $B$4:$F$456 is the range of cells in the sheet on which pivot table need to be constructed.
22
Pivot Table – Creation
● Choose where to place the pivot table.
● Specify a cell in the "current" work sheet
● Or Choose a new worksheet.
● In this example, I have chosen cell H4 in the "current" worksheet.
23
Pivot Table – Creation
● H4 cell contains a blank pivot table
24
Pivot Table – Creation
● PivotTable Fields Menu contains two panes
● First pane has all column names of original data table
● Second pane shows selected options
● Rows, Columns, Values, & filters
25
Pivot Table – Operations
● Sales grand total
26
Pivot Table – Operations
● Sales grand total and number of units sold grand total
27
Pivot Table – Operations
● Sum of sales by region – Automatically identifies Unique regions and obtains their respective sum of sales
● Distinct values of Region are placed in rows.
28
Pivot Table – Operations
● Sum of sales by region – Automatically identifies Unique regions and obtains their respective sum of sales
● Distinct values of Region are placed in columns. Change the report appearance
29
Pivot Table – Operations
● Sum of units sold by region – Automatically identifies Unique regions and obtains their respective sum of units
● Distinct values of Region are placed in rows.
30
Pivot Table – Operations
● Sum of sales by region and by color– Automatically identifies Unique regions and obtains their respective sum of sales
● Distinct values of region and color are placed in rows.
Rows has
Region & Color
Column - None
31
Pivot Table – Operations
● Sum of sales by region in which all unique region values to be placed in rows; all unique color values to be placed in columns; Perform sum
over sales computation
Choose Rows to have
Region
Columns to have Color
32
Pivot Table – Operations - filters
● Filter function allows you to narrow down the data displayed within the pivot
table by selecting specific criteria or values.
● Filters are used to focus on a subset of the data that is most relevant to your
analysis or reporting needs. Here's how the filter function in a pivot table is
typically used:
● Filter by Multiple Criteria: In more advanced pivot tables, you can apply multiple filters
simultaneously to refine the data even further. For example, you can filter to show
sales data for a particular region and a specific product category.
● Clear Filters: Most pivot table software or tools provide an option to clear filters, which
resets the pivot table to display all the data without any filtering criteria applied.
33
Pivot Table – Operations - Filters
● Sum of sales by color and filter by region; Perform sum over sales by color using the filter criter
Choose Rows to have
Color
Filter by Region
Sum the sales value
34
Pivot Table – Operations - Filters
● Sum of sales by color and filter by region; Perform sum over sales by color using the filter criter
Choose Rows to have
Color
Filter by Region
Sum the sales value
35
Pivot Table – Operations – Filters
● Filter values – sales value between 30 and 70
Choose Rows to have date
Columns to have Region
Sum the sales value
Filter by sales value between 30 and 70
36
Pivot Table – Operations - Filters
● Sum of sales by color and filter by region; Perform sum over sales by color using the filter criter
Choose Rows to have
Color
Filter by Region
Sum the sales value
37
Pivot Table – Operations – Filters
Choose Rows to have date
● Filter dates between 09/01/2016 and 10/31/2016
Columns to have Region
Sum the sales value
Filter by dates between function
38
Pivot Table – Operations – Filters
● Filter dates between 09/01/2016 and 10/31/2016
Choose Rows to have date
Columns to have Region
Sum the sales value
Filter by dates between function
39
Pivot Table – Operations – Filters
● Filter values – sales value between 30 and 70
Choose Rows to have date
Columns to have Region
Sum the sales value
Filter by sales value between 30 and 70
41
Pivot Table – Operation - Count
● Count units by region
Choose Rows to have
region
Count the units value
42
Pivot Table – Operation - Count
● Count units by region
Choose Rows to have
region
Count the units value
43
Pivot Table – Operation - Count
● Count units by region
Choose Rows to have
region
Count the units value
44
Pivot Table – Operation - Average
● Average sales by color and by region
Choose Rows to have
Color
Columns to have Region
Average sales value
45
Pivot Table – Operation - Average
● Average sales by color and by region
Choose Rows to have
Color
Columns to have Region
Average sales value
46
Pivot Table – Operation - Average
● Average sales by color and by region
Choose Rows to have
Color
Columns to have Region
Average sales value
47
Thank You!