xp. objectives sort data and filter data summarize an excel table insert subtotals into a range of...
TRANSCRIPT
XPXPXP
CHAPTER 5: WORKING WITH EXCEL TABLES, PIVOTTABLES, AND PIVOTCHARTS
XPXPXPObjectives• 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
XPXPXPPlanning a Structured Range of Data
• 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
XPXPXPCreating an Effective Structured Range of Data
• 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
XPXPXPPlanning a Structured Range of Data
• Freezing a row or column keeps headings visible as you work with data in a large worksheet
5
XPXPXPSave Time with Excel Table Features
• 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
XPXPXPCreating an Excel Table
7
XPXPXPSorting Data• 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
XPXPXPSorting Data• Use sort dialog box to sort multiple columns• Primary and secondary sort fields• Up to 64 sort fields possible
9
XPXPXPSorting Using a Custom List• 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
XPXPXPFiltering Data• 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
XPXPXPUsing the Total Row to CalculateSummary Statistics
• 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
XPXPXPCreating Subtotals (Introduction)• 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
XPXPXPInserting Subtotals• 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
XPXPXPInserting Subtotals
15
XPXPXPUsing the Subtotal Outline View• 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
XPXPXPPivot Tables• 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
XPXPXPAnalyzing Data with PivotTables• Provide ability to “pivot” the table (rearrange,
hide, and display different category fields to provide alternative views of the data)
18
XPXPXPAnalyzing Data with PivotTables• Summarize data into categories using
functions (COUNT, SUM, AVERAGE, MAX, MIN)• Values fields contain summary data• Category fields group the values
19
XPXPXPCreating a PivotTable• Use PivotTable dialog box to select data to
analyze and location of the PivotTable report
20
XPXPXPCreating a PivotTable• 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
XPXPXPAdding Fields to a PivotTable
22
XPXPXPCreating a PivotTable• 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
XPXPXPRefreshing a PivotTable• 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
XPXPXPGrouping PivotTable Items• Grouping items combines dates or numeric
items into larger groups so that the PivotTable can include the desired level of summarization
25
XPXPXPFiltering and Slicing a PivotTable• 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
XPXPXPFiltering and Slicing a PivotTable• 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
XPXPXPFiltering and Slicing a PivotTable
28
XPXPXPCreating a Calculated Field• 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
XPXPXPCreating a PivotChart• 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
XPXPXPCreating a PivotChart
31