Pivot Tables in Excel

Ramaiah Institute of Technology

1
What is a PivotTable?
 A PivotTable organizes and summarizes large
amounts of data.
 The data in one or more columns (also known
as fields) in your dataset can become row and
column labels in the PivotTable.
 The data in one column is usually chosen for
the Values which are summarised in the centre
of the table using a specific calculation.
2
Let’s Start
 Open the excel sheet in the data folder.
(Stationary sheet).
 Can you answer this question?
 How many toners were ordered from CSE
department?

3
Creating a PivotTable
 Columns where the values are repeated within
the column are ideal for PivotTable row or
column labels.
 So for example, in the dataset on the left,
Dept, Product might be placed in the row or
column headings.
 Amount might be used as the Values to be
summarized in the centre of the PivotTable.
4
Part -1
Creating a PivotTable

5
Follow the steps as on the slides now for
creating a pivot table

6
1
 Click anywhere within the range of data you
wish to use to create your PivotTable.

7
2
 From the Insert tab select PivotTable.

8
3
 Select New Worksheet
 Click on the OK button.

9
4
 A blank PivotTable and PivotTable Field List
will be displayed. Two new PivotTable Tools
tabs become available on the Ribbon:

10
Part -2
Arranging your data

11
1
 From the Field List, drag the fields with the
data you want to display in rows, columns
and values to the area on the PivotTable

12
2
 Can you answer this question?
 How many toners were ordered from CSE
department?

13
Pivot charts
 They allow you to visually represent the data
summarized in a pivot table, making it easier
to identify trends, patterns, and insights.
 Key Features of Pivot Charts:
 Dynamic Data: Pivot charts are linked to the
pivot table, meaning that any changes in the
pivot table (e.g., filtering, adding/removing
fields) will automatically update the pivot
chart. 14
 Aggregation: Like pivot tables, pivot charts
summarize data by aggregating values (e.g.,
sum, average, count) based on the categories
defined in rows and columns.
 Interactive: You can filter data directly on the
pivot chart using slicers or by interacting with
the chart itself.
 Customizable: Pivot charts support various
types of charts (bar, line, pie, etc.), and you
can customize the chart’s appearance, such as
changing colors, labels, and axis titles.
15
How to Create a Pivot Chart in
Excel:
•Create a Pivot Table:
•Select your data range.
•Go to Insert PivotTable
> .

•Place fields in the Rows, Columns, Values, and Filters areas.

•Choose the chart type you prefer (e.g., bar, line, pie).
•The chart will be linked to the pivot table and will reflect its data.
•Customize the Chart:
•Use the Chart Tools
on the ribbon to modify the chart’s design and format.

•Add or remove data labels, change chart styles, and apply filters as needed.

16
Part -3
Filtering your data

17
1
 In the pivot table sheet, select the filter option
and then select only “toner” as the option.

18
2
 Can you answer this question?
 How many toners were ordered from CSE
department?

19
Part -4
Alternative way of
filtering the data

20
1
 One more alternative way of filtering out data is by
using the “Filter report” option in the pivot table.
 In the pivot table sheet, drag the fields of row labels,
filter report and values as shown below.

21
2
 Now, you can select the required labels to be
displayed using the filtered option as shown.

22
Exercise
 Create a pivot table for the student data provided in the
‘Student’ sheet.
 Question 1:
1. Point out how many students are travelling by bus department
wise.
 (Hint: Use ‘row-labels’->Dept, ‘Column labels’->Mode of transport,
‘Values’-> Count of USN)
2. Point out how many female students are travelling by bus
department wise
 (Hint: Use ‘report-filter’ option to the column ‘gender’)

23
Exercise
 Create a pivot table for the student data provided in
the ‘Stuyr’ sheet.
 Question 2:
1. Point out how many students are there in each
department year wise.

24
Part -5
Grouping data year wise

25
Grouping data year wise
 Many of the data involved in the college is
based on the year-wise.
 How can we you use pivot table for
organizing the data based on year-wise??
 Let’s see it now.

26
1
 Open the sheet ‘Faculty’.
 Create a pivot table with these options now.

27
2
 The results are as shown below.

28
3
 Click on any cell of the date column and select the option
‘Group selection’

29
4
 Now specify the option of starting date and ending date as
required.
 Note: Please ensure to select both ‘months’ and ‘years’ while
grouping.

30
Exercise
 How many female assistant professors are there in
each department?
 (Hint: Use ‘row-labels’->Dept, ‘Column labels’-
>Designation, ‘Values’-> Count of Name, ‘Report filter’-
> Gender)

31
Exercise
 How many faculty are there in the age group of 27-30 in each
department?
 (Hint: Use ‘row-labels’->Dept, ‘row-labels’->Age, ‘Column labels’-
>Designation, ‘Values’-> Count of Name, ‘Report filter’-> Gender)
 (Hint: Use group selection on ‘Age’ column starting age as 27 and
ending age as 30)

32