how to use pivot table
TRANSCRIPT
How to Use
dennissaren.wordpress.com
1.Microsoft Excel 2.Google Spreadsheets
1
What is Pivot Table?
dennissaren.wordpress.com 2
A PIVOT TABLE is a program tool used to reorganize data in a
spreadsheet to obtain a desired report more
effectively. dennissaren.wordpress.com 3
What are the advantages of using
Pivot Table?
dennissaren.wordpress.com 4
It sums up large amount of Data into a
small amount of space.
dennissaren.wordpress.com 5
It helps decision-makers use data more
efficiently.
dennissaren.wordpress.com 6
Keeps presentation of information more
organized.
dennissaren.wordpress.com 7
It provides interactive data analysis.
dennissaren.wordpress.com 8
It can link presentation to external data
sources.
dennissaren.wordpress.com 9
Data can be combined from multiple sheets.
dennissaren.wordpress.com 10
Can create chart of summarized data in
the Pivot Table.
dennissaren.wordpress.com 11
Now let’s START!
dennissaren.wordpress.com 12
Pivot Table in PART 1
dennissaren.wordpress.com 13
Here is the sample data for our Pivot Table tutorial.
dennissaren.wordpress.com 14
The sample is about Summary of Rice Sales.
dennissaren.wordpress.com 15
It states the branch, rice variety, period of sales and
total sales.
dennissaren.wordpress.com 16
Drag the data then click Insert Menu.
dennissaren.wordpress.com 17
In the Insert menu, Click Pivot Table.
dennissaren.wordpress.com 18
Dialogue box appears for data you want to analyze.
dennissaren.wordpress.com 19
Select table range or you may use an external data source.
dennissaren.wordpress.com 20
I prefer new worksheet to make presentation neat and tidy.
dennissaren.wordpress.com 21
To build a report, choose field from the PT Field List.
dennissaren.wordpress.com 22
Click Branch and drag to Report Filter.
dennissaren.wordpress.com 23
Assign Rice Variety to Row Labels.
dennissaren.wordpress.com 24
Drag Period to Column Labels.
dennissaren.wordpress.com 25
Click Sales and place it to Values.
dennissaren.wordpress.com 26
Values can be summarized according to preference.
dennissaren.wordpress.com 27
New presentation is generated on the right thru Pivot Table.
dennissaren.wordpress.com 28
To view only the total sales of Branch M-1.
Unclick All then
check M-1 and click
OK.
dennissaren.wordpress.com 29
So Branch M-1, shows the total sales in 155,000 .
dennissaren.wordpress.com 30
Let us switch Rice Variety and Period.
dennissaren.wordpress.com 31
Drag Rice Variety from Row Labels to Column Labels.
dennissaren.wordpress.com 32
Then drag Period to Column Labels.
dennissaren.wordpress.com 33
Rice Variety and Period are now switched in the graph!
dennissaren.wordpress.com 34
Wonder why Pivot Table Field List disappears?
dennissaren.wordpress.com 35
Click the cell on the Table to make Pivot Table Field List
reappear.
dennissaren.wordpress.com 36
To generate report of White Rice from Branch M-4
dennissaren.wordpress.com 37
Click the Branch Filter Field, branches appear in the
dropdown menu.
dennissaren.wordpress.com 38
Unclick All, then Click M-4.
dennissaren.wordpress.com 39
Unclick All, then Click M-4.
dennissaren.wordpress.com 40
Click the Column Labels, unclick Select All.
dennissaren.wordpress.com 41
Click White Rice, then Click OK.
dennissaren.wordpress.com 42
Branch M-2 with White Rice report is generated.
dennissaren.wordpress.com 43
How to make Chart presentation?
dennissaren.wordpress.com 44
To start, click Insert in the menu.
dennissaren.wordpress.com 45
In the Insert menu you find Charts.
dennissaren.wordpress.com 46
Click your preferred Chart.
dennissaren.wordpress.com 47
I want 3-D Bar graph for this table!
dennissaren.wordpress.com 48
Bar Graph Presentation is generated.
dennissaren.wordpress.com 49
Present Branch M-2 sales in the graph only.
Click the Branch and click M-2.
dennissaren.wordpress.com 50
In this Graph, Branch M-2 is generated.
dennissaren.wordpress.com 51
To change in sales of M-1, Black Rice for Qtr 3. New
amount is $55,000.
dennissaren.wordpress.com 52
Open the original report and input new figure of $55,000.
dennissaren.wordpress.com 53
Amount of $55,000 is now in place.
dennissaren.wordpress.com 54
How will this affect the table?
Note that original report
shows $45,000 only.
dennissaren.wordpress.com 55
Right Click $45,000, then option box appears, then Click
Refresh.
dennissaren.wordpress.com 56
New amount appears and Bar Graph changes data
automatically.
dennissaren.wordpress.com 57
PART 2
dennissaren.wordpress.com 58
It is a Web-based application that allows users to create,
update and modify spreadsheets and share the data live online.
What is Google Spreadsheets?
dennissaren.wordpress.com 59
The Ajax-based program is compatible with Microsoft Excel and CSV (comma-
separated values) files. Spreadsheets can also
be saved as HTML.
dennissaren.wordpress.com 60
In the google chrome browser, open https://docs.google.com.
dennissaren.wordpress.com 61
Google docs website will appear below.
dennissaren.wordpress.com 62
Click Docs menu.
dennissaren.wordpress.com 63
Choose Sheets and Click to open.
dennissaren.wordpress.com 64
Now let us choose the blank sheet.
dennissaren.wordpress.com 65
Here is now the Untitled spreadsheets.
dennissaren.wordpress.com 66
We use same sample in the MS Excel Pivot Table.
dennissaren.wordpress.com 67
You will notice that the raw data is in Sheet 1.
dennissaren.wordpress.com 68
Now let us create a title of the spreadsheet.
dennissaren.wordpress.com 69
Click Untitled spreadsheet and write SUMMARY OF RICE
SALES.
dennissaren.wordpress.com 70
Highlight data in the sheet and in the Menu Bar, CLICK Data.
dennissaren.wordpress.com 71
Now CLICK Pivot Table in the Data Menu.
dennissaren.wordpress.com 72
New table appears which is the Report Editor at the right.
dennissaren.wordpress.com 73
In the Rows, add field with either Branch, Rice Variety,
Period or Sales.
dennissaren.wordpress.com 74
The table on the right now generates 4 different branches.
dennissaren.wordpress.com 75
Grand Total appears because I clicked Show Totals.
dennissaren.wordpress.com 76
Place Rice Variety with Show totals in the Columns field.
dennissaren.wordpress.com 77
Table now generates Rice Variety in the Column field
with its Total.
dennissaren.wordpress.com 78
VALUES is really important. It is how you
will present figures in the presentation.
dennissaren.wordpress.com 79
Place Sales in Values field, and total amount of Sales.
dennissaren.wordpress.com 80
The sales information is generated on the table.
dennissaren.wordpress.com 81
To use account Sales, Click COUNT in the dropdown
menu.
dennissaren.wordpress.com 82
COUNT summary is generated with a Grand Total
of 16.
dennissaren.wordpress.com 83
Assuming a change in Sales at M-2 from 50,000 t0 60,000.
dennissaren.wordpress.com 84
Go to Sheet 1, change the figure under Sales column .
dennissaren.wordpress.com 85
Pivot Table shows the new total automatically.
dennissaren.wordpress.com 86
How to use Chart presentation?
dennissaren.wordpress.com 87
In google sheets, we find Chart in the Insert Menu Bar.
dennissaren.wordpress.com 88
Now CLICK Chart.
dennissaren.wordpress.com 89
Chart Editor appears.
dennissaren.wordpress.com 90
In the Chart Editor, there are options that we can
make.
dennissaren.wordpress.com 91
We can use any of the recommended charts.
dennissaren.wordpress.com 92
Click the desired Chart and click Insert Button.
dennissaren.wordpress.com 93
Selected Chart appears on the Pivot Table.
dennissaren.wordpress.com 94
There are also other Chart types that we can use.
dennissaren.wordpress.com 95
We can customize
Columns, Rows, Headers or
choose other types of
available chart templates.
dennissaren.wordpress.com 96
In this presentation, I switch Rows and Columns.
dennissaren.wordpress.com 97
Customization
dennissaren.wordpress.com 98
Can change the Title of the Chart.
dennissaren.wordpress.com 99
Click The Title and Write Summary of Rice Sales.
dennissaren.wordpress.com 100
Chart now shows the Edited Title - Rice Sales Summary.
dennissaren.wordpress.com 101
Can change Font.
dennissaren.wordpress.com 102
I can even change background Color of the
Chart.
dennissaren.wordpress.com 103
From white background, now the chart background is blue.
dennissaren.wordpress.com 104
dennissaren.wordpress.com
But as you excel in everything—in faith, in speech, in knowledge, in all earnestness, and in our love for you—see that you excel in this
act of grace also
2 Corinthians 8:7
105
dennissaren.wordpress.com
END OF SLIDE
106