Pivot Tables: A Comprehensive Guide
PIVOT TABLES
CREATING
PIVOT TABLES
O
B
J
E
C
T
I
V
E
S
•
What is a pivot table
•
The use of pivot tables
•
Advantages of using a pivot table
•
Creating pivot tables
•
Reports
W
H
A
T
I
S
A
P
I
V
O
T
T
A
B
L
E
A pivot table is a tool used to reorganise data in a spreadsheet to obtain a desired report more
effectively.
This might include sums, averages, or other statistics, which the pivot table groups together in a
meaningful way.
Once you create a pivot table, you can quickly transform huge numbers of rows and columns into a
meaningful, nicely formatted report.
T
H
E
U
S
E
O
F
A
P
I
V
O
T
T
A
B
L
E
•
A pivot
t
able is used to summarise, sort, reorganise, group, count,
total or average data stored in a table.
•
It allows us to transform columns into rows and rows into columns.
•
It allows grouping by any field (column) and using advanced
calculations on them etc.
A
D
V
A
N
T
A
G
E
S
O
F
U
S
I
N
G
A
P
I
V
O
T
T
A
B
L
E
•
Keeps presentation of information more organised
•
Provides interactive data analysis
•
Helps decision makers to use data more efficiently
•
Sums up a large amount of data into a small space
•
Creates report quickly
•
Can combine data from multiple sheets
C
R
E
A
T
I
N
G
A
P
I
V
O
T
T
A
B
L
E
•
Export source data to excel (It will open as an excel file)
•
Save the excel file to your computer
•
Click a cell in the source data or table range.
•
Go to Insert > Recommended PivotTable.
•
Select the PivotTable that looks best to you and press OK.
C
R
E
A
T
I
N
G
A
P
I
V
O
T
T
A
B
L
E
c
o
n
t
.
1.
Select the cells you want to create a PivotTable from.
Note:
Your data shouldn't have any empty rows or columns. It must have only a single-row heading.
2.
Select
Insert
>
PivotTable
.
C
R
E
A
T
I
N
G
A
P
I
V
O
T
T
A
B
L
E
c
o
n
t
.
Under
Choose the data that you want to analyze
, select
Select a table or range
This will create a new spreadsheet where you'll build your dynamic pivot table reports
.
C
R
E
A
T
I
N
G
A
P
I
V
O
T
T
A
B
L
E
c
o
n
t
.
•
In
Table/Range
, verify the cell range.
•
Under
Choose where you want the PivotTable report to be
placed
, select
New worksheet
to place the PivotTable in a
new worksheet or
Existing worksheet
and then select the
location you want the PivotTable to appear.
•
Select
OK
.
•
This will create a new spreadsheet where you'll build your
dynamic pivot table reports.
•
The process of building a pivot table in Excel Online uses drag and drop
functionality.
•
You add a field to an area simply by dragging it there.
•
Don't want a field in a box anymore? Drag it out, and it disappears.
•
Beyond that, you'll learn how to use these tools as we go along.
C
R
E
A
T
I
N
G
A
P
I
V
O
T
T
A
B
L
E
c
o
n
t
.
R
E
P
O
R
T
S
•
A pivot table starts out empty. All you'll see on the right edge of the sheet is
the pivot table editor, where you'll find all the options for building your pivot
table.
•
The editor is divided into two horizontal sections.
•
The top section lists all the fields—these are all the columns from your table
data.
•
In the bottom section, you'll find the actual area for manipulating the pivot
table. It's divided into four parts: Filters, Columns, Rows, and Values
E
X
A
M
P
L
E
O
F
A
P
I
V
O
T
T
A
B
L
E
Thank You
Please feel free to contact us should you have
any questions:
info@chiva-africa.org
+27 (0) 83 500 7222
Pivot tables are powerful tools for reorganizing and analyzing data efficiently. They help in summarizing, sorting, and grouping data to create meaningful reports quickly. Learn about the advantages of using pivot tables and how to create them effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
PIVOT TABLES CREATING PIVOT TABLES
OBJECTIVES OBJECTIVES What is a pivot table The use of pivot tables Advantages of using a pivot table Creating pivot tables Reports
WHAT IS A PIVOT TABLE WHAT IS A PIVOT TABLE A pivot table is a tool used to reorganise data in a spreadsheet to obtain a desired report more effectively. This might include sums, averages, or other statistics, which the pivot table groups together in a meaningful way. Once you create a pivot table, you can quickly transform huge numbers of rows and columns into a meaningful, nicely formatted report.
THE USE OF A PIVOT TABLE THE USE OF A PIVOT TABLE A pivot table is used to summarise, sort, reorganise, group, count, total or average data stored in a table. It allows us to transform columns into rows and rows into columns. It allows grouping by any field (column) and using advanced calculations on them etc.
ADVANTAGES OF USING A PIVOT TABLE ADVANTAGES OF USING A PIVOT TABLE Keeps presentation of information more organised Provides interactive data analysis Helps decision makers to use data more efficiently Sums up a large amount of data into a small space Creates report quickly Can combine data from multiple sheets
CREATING A PIVOT TABLE CREATING A PIVOT TABLE Export source data to excel (It will open as an excel file) Save the excel file to your computer Click a cell in the source data or table range. Go to Insert > Recommended PivotTable. Select the PivotTable that looks best to you and press OK.
CREATING A PIVOT TABLE CREATING A PIVOT TABLE cont. 1. Select the cells you want to create a PivotTable from. Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading. cont. 2. Select Insert > PivotTable.
CREATING A PIVOT TABLE CREATING A PIVOT TABLE cont. cont. Under Choose the data that you want to analyze, select Select a table or range This will create a new spreadsheet where you'll build your dynamic pivot table reports.
CREATING A PIVOT TABLE CREATING A PIVOT TABLE cont. cont. In Table/Range, verify the cell range. Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear. Select OK. This will create a new spreadsheet where you'll build your dynamic pivot table reports.
CREATING A PIVOT TABLE CREATING A PIVOT TABLE cont. cont. The process of building a pivot table in Excel Online uses drag and drop functionality. You add a field to an area simply by dragging it there. Don't want a field in a box anymore? Drag it out, and it disappears. Beyond that, you'll learn how to use these tools as we go along.
REPORTS REPORTS A pivot table starts out empty. All you'll see on the right edge of the sheet is the pivot table editor, where you'll find all the options for building your pivot table. The editor is divided into two horizontal sections. The top section lists all the fields these are all the columns from your table data. In the bottom section, you'll find the actual area for manipulating the pivot table. It's divided into four parts: Filters, Columns, Rows, and Values
EXAMPLE OF A PIVOT TABLE EXAMPLE OF A PIVOT TABLE Name of the Clinic Data / Period Jul-20 Aug-20 Sep-20 PHC headcount under 5 years PHC headcount 5-9 years PHC headcount 10-19 years PHC headcount 20 years and older PHC client seen by professional nurse PHC client seen by public doctor PHC client seen by sessional doctor PHC professional nurse clinical work days PHC public doctor clinical work days PHC sessional doctor clinical work days
Thank You Please feel free to contact us should you have any questions: info@chiva-africa.org +27 (0) 83 500 7222
