How to use a pivot
table?
What is a Pivot Table?
A pivot table is a three
dimensional view of data. Pivot tables
can be easily tailored to fit your ad hoc reporting requirements. They will allow you to quickly analyze,
summarize and display large amounts of data in formatted lists and tables that
are meaningful to you.
Pivot Table Concepts
A pivot table is made up
of 4 primary areas.
1. Detail Area (DISPLAY) – Where
you place the fields that you want to see information about.
2. Row Area (GROUP) – Where you
can group information and create summary totals for in a row view.
3. Filter Area (FILTER) – Where
you can set entire page filters
4.
Column Area (Group) - Where you can
group information and create summary totals for in a columnar view. [NOT USED
OFTEN]
Pivot table menu options
For more detail information
about the pivot functionality and tool bar options, select the help icon 
on the pivot table tool bar.
Learn how to use a pivot table?
The best way to learn how to
use a pivot table is by actually creating a pivot table from scratch. In the following sections we will go through
the steps to create a pivot table, modify, sort, group, add summary totals and
more. Please spend a few minutes going
through this exercise to fully understand and utilize the power of a pivot
table.
Step 1: Print this html
document and have next to you as we build a sample pivot table.
Now you should be able to
see a blank pivot table that is named “Sales and Profit Analysis – By Customer
and Item”.
* * Be sure to observe how the pivot table changes with
each selection in the exercises below. * *
Exercise #1:
Working with the detail area (Display):
General topics covered in this section:
·
Decide which columns of information
you want to see in your pivot table.
·
Drag the fields over to the DISPLAY
area in the order you want to see them.
·
You can add and delete fields
(columns). Work with the order fields (columns) appear.
·
Sort the information by a certain
field (column).
·
Filter the information by one or
multiple fields (columns).
1.
Open
the “Field List” 
from the tool bar.
2.
Drag
the following fields into the “Detail” area (DISPLAY). Be sure to keep them in the following
order. (INV_NUMBER, INV_DATE, CUST_NAME,
TOTAL_SALES, & SLSPRS_ID)
* You have now created a basic pivot table.
3.
Now
switch the order of Invoice Number & Invoice Date.
§
Drag
the “INV_NUMBER” field after of the “INV_DATE” field.
4.
Practice
sorting:
§
Highlight
the “INV_DATE” column and select sort ascending button 
on the tool bar.
§
Highlight
the “CUST_NAME” and sort descending
.
§
Highlight
the “TOTAL_SALES” column and sort ascending and descending.
5.
Working
the filters in the DISPLAY area.
§
Select
the down arrow 
by “CUST_NAME”.
§
Uncheck
“All” and select two or three customers.
Select “OK”.
§
Select
the down arrow by “INV_DATE”.
§
Uncheck
“All” and select a date that is currently being displayed. Select “OK”.
§
Select
the down arrow by “CUST_NAME” and check “All”. Select “OK”.
§
Select
the down arrow by “INV_DATE” and check “All”. Select “OK”.
6.
Now
drag the “SLSPRS_ID” field off the pivot table until you see a red X or
right click on the column and select “Remove Field”.
Exercise #2: Working with the row
area (GROUP):
General topics covered in this section:
·
Use this section to group
information or create totals by a group.
·
You can filter information by fields
in the GROUP area.
·
You can add summary totals for
fields.
·
Hide or show detail.
·
Can have a single or multiple sub
groups.
1. Drag the “CUST_NAME” field to the
“Row” area (GROUP).
2. Working with filters in the row area
(GROUP).
§
Select
the drop down arrow by “CUST_NAME”.
§
Unselect
“All”, and select two customers. Select
“OK”.
§
Select
the drop down arrow by “CUST_NAME” and select “All”. Select “OK”.
3. Add a summary total to the sales
field.
§
Highlight
the “TOTAL_SALES” column.
§
Select
the “Auto Calc.” button 
on the tool bar.
§
Select
“Sum”
1. Drag the “Quantity” field from the
field list just before the “TOTAL_SALES” field.
§
Follow
steps above from #3 to add a summary total for the “Quantity” field.
2. Hide or show detail.
§
Highlight
the “CUST_NAME” field.
§
Select
the “Hide Details” button 
on the tool bar.
§
Highlight
the “CUST_NAME” field.
§
Select
the “Show Details” button 
on the tool bar.
3. Change the information that you are
grouping by.
§
Now
drag the “CUST_NAME” field off the pivot table until you see a red X or
right click and select “Remove Field".
§
Drag
the “ITEM_NMBR” field to the “Row” area (GROUP).
§
Select
the “Hide Details” button on the tool bar.
§
Select
the “Show Details” button on the tool bar.
4. Multiple groups with sub groups.
§
Leaving
the “ITEM_NMBR” field in the GROUP area, select and drag the “CUST_NAME” field
just to the right of the “ITEM_NMBR” field.
(You should now see each
customer’s sales activity within each item number.)
5. Hide or show detail.
§
Highlight
the “CUST_NAME” field.
§
Select
the “Hide Details” button on the tool bar.
§
Select
the “Show Details” button on the tool bar.
Exercise #3:
Working with the page (FILTER) area:
General topics covered in this section:
·
Use this section to set global filters
of fields you may not want to see on your DISPLAY area.
·
You have multiple fields to filter
by at any one time.
1. Add fields to the (FILTER)
area.
§
Drag
the “INV_DATE by Month” field to the PAGE FILTER area.
§
Select
the drop down arrow by “INV_DATE" By Month” field and uncheck
“All” and select year 2005. Select “OK”.
§
In
the drop down area unselect year 2005 and select “July-05”. Select “OK”.
2. Multiple filters
§
Drag
the “SLSPRS_ID” field into the PAGE FILTER area.
§
Select
the drop down arrow by “SLSPRS_ID” and uncheck “All” and select a
sales person. Select “OK”.
§
Set
both the filters for “INV_DATE By Month” and “SLSPRS_ID” to “(All)”.
Exercise #4:
Copying the Pivot Table and pasting into Excel:
1. Using the mouse click in the title area “SALES AND
PROFIT ANALYSIS – BY CUSTOMER AND ITEM”.
§
After clicking
on the title area the entire pivot table should appear shaded.
§
See below.
2. Select the copy button 
on the tool bar.
3. Open Excel and select the paste button
.
4. Now you can format and work with the data in
Excel. It is also much easier to print
pivot table information from Excel.
In Conclusion:
Hopefully now you have an
appreciation of the power and flexibility of pivot tables. You control what fields to display, sort,
group, subtotal, filter and paste into Excel.
We recommend that you refer often to this document and practice screen
in order fully utilize the capabilities of pivot tables within this
application.