Transcript Tutorial 5: Working with Excel Tables, PivotTables, and

XP
Objectives
•
•
•
•
•
XP
Sort data and filter data
Summarize an Excel table
Insert subtotals into a range of data
Outline buttons to show or hide details
Create and modify a PivotTable and PivotChart
2
Planning a Structured Range of Data
XP
• A collection of similar data can be structured
in a range of columns and rows, representing
fields and records, respectively
• A structured range of data is commonly
referred to as a list or table
3
Creating an Effective Structured Range of DataXP
• Enter field names in top row of range
• Use short, descriptive field names
• Format field names to distinguish header row
from data
• Enter same kind of data for a field in each
record
• Separate data (including header row) from
other information in the worksheet by at least
one blank row and one blank column
4
Planning a Structured Range of Data
XP
• Freezing a row or column keeps headings
visible as you work with data in a large
worksheet
5
Save Time with Excel Table Features
XP
• Format quickly using a table style
• Add new rows and columns that automatically
expand the range
• Add a Total row to calculate a summary
function (SUM, AVERAGE, COUNT, MIN, MAX)
• Enter a formula in a cell that is copied to all
other cells in the column
• Create formulas that reference cells in a table
by using table and column names
6
Creating an Excel Table
XP
7
Sorting Data
XP
• Sort data in ascending or descending order
• Use the Sort A to Z button or the Sort Z to A
button to sort data quickly with one sort field
8
Sorting Data
XP
• Use sort dialog box to sort multiple columns
• Primary and secondary sort fields
• Up to 64 sort fields possible
9
Sorting Using a Custom List
XP
• A custom list indicates sequence to order data
– Four predefined custom sort lists
• Two days-of-the-week custom lists
• Two months-of-the-year custom lists
– Can also create a custom list to sort records in a
sequence you define
10
Filtering Data
XP
• Filtering data temporarily hides any records
that do not meet specified criteria
• After data is filtered, it can be sorted, copied,
formatted, charted, and printed
11
Using the Total Row to Calculate
Summary Statistics
XP
• You can calculate sum, average, count,
maximum, and minimum on all columns in a
table or on a filtered table in a Total row
12
Creating Subtotals (Introduction) XP
• Subtotals can be created on columnar data
– The data must be sorted for subtotals to be
created
– Column headers must also appear in the data
• Subtotal command
– Offers many kinds of summary information
(counts, sums, averages, minimums, maximums)
– Inserts a subtotal row into range for each group of
data; adds grand total row below last row of data
•
Inserting Subtotals
XP
• Sort data so that records with the same value in a
specified field are grouped together before using
Subtotal command
– It cannot be used in an Excel table
– First convert the Excel table to a range
• Click SubTotal on the Data ribbon
14
Inserting Subtotals
XP
15
Using the Subtotal Outline View
XP
• Control the level of detail with buttons
– Level 3: Most detail
– Level 2: Subtotals and grand total, but not
individual records
– Level 1: Only the grand total
16
Pivot Tables
XP
• Interactive table used to group and summarize
either a range of data or an Excel table into a
concise, tabular format for easier reporting
and analysis
• Dynamic organization; can be “pivoted” to
examine data from various perspectives by
rearranging its structure
• Best used to analyze data that can be
summarized in multiple ways
• Pivot tables can be created from lists or
external data sources
17
Analyzing Data with PivotTables
XP
• Provide ability to “pivot” the table (rearrange,
hide, and display different category fields to
provide alternative views of the data)
18
Analyzing Data with PivotTables
XP
• Summarize data into categories using
functions (COUNT, SUM, AVERAGE, MAX,
MIN)
• Values fields contain summary data
• Category fields group the values
19
Creating a PivotTable
XP
• Use PivotTable dialog box to select data to
analyze and location of the PivotTable report
20
Creating a PivotTable
XP
• PivotTable Field List has two sections
– Upper field list section displays names of each
field; use check boxes to add fields to PivotTable
– Lower layout section includes boxes for four areas
in which you can place fields
21
Adding Fields to a PivotTable
XP
22
Creating a PivotTable
XP
• Apply PivotTable styles by using a preset style
or modifying its appearance
• Formatting PivotTable values fields
– Applying PivotTable styles does not change
the numeric formatting
23
Refreshing a PivotTable
XP
• You cannot change data directly in the
PivotTable
• Instead, you must edit the Excel table, and
then refresh, or update, the PivotTable to
reflect the updated data
24
Grouping PivotTable Items
XP
• Grouping items combines dates or numeric
items into larger groups so that the PivotTable
can include the desired level of summarization
25
Filtering and Slicing a PivotTable
XP
• Filters can be applied to a PivotTable
• PivotTable filters can be based on:
– Field values
– Row and column label groupings
• PivotTable filters can be removed
26
Filtering and Slicing a PivotTable
XP
• Slicer—small window that contains a button
for each item in a field
• Slicer—helpful when filtering a PivotTable
based on multiple tables
• Slicers can be customized
27
Filtering and Slicing a PivotTable
XP
28
Creating a Calculated Field
XP
• Custom calculation options:
– % of Grand Total
– % of Column Total
– % of Row Total
– % of Parent Row Total
– Running Total
– Rank Smallest to Largest
– Rank Largest to Smallest
29
Creating a PivotChart
XP
• PivotChart—interactive graphical
representation of PivotTable data
• Changing the position of a field in the
PivotTable or the PivotChart changes the
corresponding object as well
• Create a PivotChart:
– Click in the PivotTable
– Click PivotChart in the Tools group on the ANALYZE
tab
30
Creating a PivotChart
XP
31