Transcript Tutorial 5: Working with Excel Tables, PivotTables

Excel Tables
XP
• Purpose of tables
– Process data in a group
– Used to facilitate calculations
– Used to enhance readability of output
• Types of tables
– VLOOKUP tables (tutorial #7)
– Arrays for functional processing (lab only)
– Visual tables (tutorial #5)
– Pivot tables (tutorial #5)
– What-if tables (tutorial #10)
1
Visual Tables
XP
• A collection of similar data can be structured
in a range of columns and rows, representing
fields and records, respectively
• Of all the tables in Excel, these are most
similar to an Access table.
2
Creating an Effective Structured TableXP
• 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
3
Planning a Structured Visual Table
XP
• Freezing a row or column keeps headings
visible as you work with data in a large
worksheet
4
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)
5
Key table features
XP
• Sorting rows by one or more columns
• Filtering rows by the contents of one or more
columns
• Adding data
• Using the total row to calculate summary
statistics
6
Inserting Subtotals
XP
• 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
• 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
7
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
8
Pivot Tables
XP
• An information visualization method.
• Used for some data on a worksheet; not all.
– Not great for lots of columns – must decide what
to include that would be most important for
decision making.
– Must have numeric data – not good for character
type of data.
• The goal is to summarize data and create
information.
9
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)
10
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
11
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
12
Grouping Worksheets
XP
• Using multiple worksheets makes it easier to
group and summarize data
• Worksheet groups save time and improve
consistency among worksheets
– An action performed once affects multiple
worksheets
• Grouped worksheets must have exact
same organization and layout (rows and
columns)
13
Working with Grouped Worksheets
XP
• Excellent way to format and provide
calculations for multiple worksheets at the
same time.
• Use of “3-D” references can make calculations
more effective.
• Must understand what can and can’t be
grouped.
14