© Paradigm Publishing, Inc. Excel 2013 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management Chapter 4 Summarizing and Consolidating Data © Paradigm Publishing, Inc.
Download ReportTranscript © Paradigm Publishing, Inc. Excel 2013 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management Chapter 4 Summarizing and Consolidating Data © Paradigm Publishing, Inc.
Slide 1
© Paradigm Publishing, Inc.
1
Slide 2
Excel 2013
Level 2
Unit 1 Advanced Formatting, Formulas,
and Data Management
Chapter 4 Summarizing and
Consolidating Data
© Paradigm Publishing, Inc.
2
Slide 3
Summarizing and Consolidating Data
Quick Links to Presentation Contents
Summarize Data in Multiple Worksheets Using Range
Names and 3-D References
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks
Summarize Data Using the Consolidate Feature
CHECKPOINT 1
Create a PivotTable Report
Create a PivotChart
Summarize Data with Sparklines
CHECKPOINT 2
© Paradigm Publishing, Inc.
3
Slide 4
Summarize Data in Multiple Worksheets
Using Range Names and 3-D References
A workbook that has been organized with data in
separate worksheets can be summarized by creating
formulas that reference cells in other worksheets.
A worksheet reference precedes a cell reference and is
separated from the cell reference with an exclamation
point.
A formula that references the same cell in a range that
extends over two or more worksheets is often called a
3-D reference.
© Paradigm Publishing, Inc.
4
Slide 5
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References - continued
As an alternative, consider using range names to
simplify formulas that summarize data in multiple
worksheets.
A range name includes the worksheet reference by
default; therefore, typing the range name in the
formula automatically references the correct
worksheet.
© Paradigm Publishing, Inc.
5
Slide 6
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
To sum multiple worksheets using range names:
1.
2.
3.
4.
5.
6.
7.
Make formula cell active.
Type =sum(.
Type first range name.
Type a comma.
Type second range name.
Type a comma.
Continue typing range names separated
by commas until finished.
8. Type ).
9. Press Enter.
© Paradigm Publishing, Inc.
6
range names
Slide 7
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
To modify a named
range reference:
1. Click FORMULAS tab.
2. Click Name Manager
button.
3. Click range name to
be modified.
4. Click Edit button.
Edit
button
continues on next slide…
© Paradigm Publishing, Inc.
7
Slide 8
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
5. Click in Refers to text
box or click collapse
button.
6. Modify range
address(es) as
required.
7. Click OK.
8. Click Close.
Refers to
text box
© Paradigm Publishing, Inc.
8
Slide 9
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
A disadvantage to using range names emerges when
several worksheets need to be summarized, since the
range name reference must be created in each
individual worksheet.
If several worksheets need to be summed, a more
efficient method is to use a 3-D reference.
© Paradigm Publishing, Inc.
9
Slide 10
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
To use a 3-D reference
formula:
1.
2.
3.
4.
5.
6.
Make desired cell in
worksheet active.
Type =sum(.
Click first sheet tab to
be included.
Hold down Shift key.
Click last sheet tab to
be included.
Select desired range(s).
the three worksheets grouped
in the 3-D reference
continues on next slide…
© Paradigm Publishing, Inc.
10
Slide 11
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References - continued
7. Type ).
8. Press Enter.
© Paradigm Publishing, Inc.
3-D formula created using
point-and-click approach
11
Slide 12
Summarize Data by Linking to Ranges in
Other Worksheets or Workbooks
You can summarize data in one workbook by linking to
a cell, range, or range name in another worksheet or
workbook.
When data is linked, a change made in the source cell
(the cell in which the original data is stored) is
reflected in any other cell to which the source cell has
been linked.
© Paradigm Publishing, Inc.
12
Slide 13
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
To create a link to an external reference:
1.
2.
3.
4.
5.
6.
7.
8.
Open source workbook.
Open destination workbook.
Arrange windows as desired.
Make formula cell active in destination workbook.
Type =.
Click to activate source workbook.
Click source cell.
source cell
destination cell
Press Enter.
© Paradigm Publishing, Inc.
13
Slide 14
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
Linking to a cell in another workbook incorporates
external references and requires that a workbook
name reference be added to a formula. For example,
linking to cell A3 in a sheet named ProductA in a
workbook named Sales would require that you enter
=[Sales.xlsx]ProductA!A3 in the formula cell.
Notice the workbook reference is entered first in
square brackets.
The workbook in which the external reference is
added becomes the destination workbook.
The workbook containing the data that is linked to the
destination workbook is called the source workbook.
© Paradigm Publishing, Inc.
14
Slide 15
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
When you link to an external reference, Excel includes
the drive and folder names in the path to the source
workbook.
If you move the source workbook or change the
workbook name, the link will no longer work.
security warning
message
© Paradigm Publishing, Inc.
15
Slide 16
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
To edit a link to an external
reference:
1.
2.
3.
4.
5.
6.
7.
8.
Open destination workbook.
Click DATA tab.
Click Edit Links button.
Click link.
At Edit Links dialog box, click
Change Source button.
Navigate to drive and/or folder.
Double-click source workbook
file name.
Click Close button.
© Paradigm Publishing, Inc.
16
Edit Links
dialog box
Slide 17
Summarize Data by Linking to Ranges in Other
Worksheets or Workbook - continued
To break a link to an external reference:
1.
2.
3.
4.
5.
6.
7.
Open destination workbook.
Click DATA tab.
Click the Edit Links button.
Click link.
At Edit Links dialog box, click Break Link button.
At Microsoft Excel message box, click Break Links button.
Click Close button.
Break Links
button
© Paradigm Publishing, Inc.
17
Slide 18
Summarize Data Using the Consolidate Feature
To consolidate data:
1.
2.
3.
4.
5.
6.
7.
8.
9.
Make starting cell active.
Click DATA tab.
Click Consolidate button.
If necessary, change Function.
Enter first range in Reference
text box.
Click Add button.
Enter next range.
Click Add button.
Repeat steps 7 to 8 until all
ranges have been added.
continues on next slide…
© Paradigm Publishing, Inc.
18
Reference
text box
Slide 19
Summarize Data Using the Consolidate Feature -continued
10. If necessary, select Top
row and/or Left column
check boxes.
11. If necessary, click Create
links to source data
check box.
12. Click OK.
Left column
check box
© Paradigm Publishing, Inc.
19
Slide 20
1) A formula that references the
same cell over two or more
worksheets is often called this.
a. 3-D reference
b. 3-D worksheet
c. 3-D formula
Answer
d. 3-D cell
3) Using this key while clicking a sheet
tab selects all worksheets from the
first sheet tab through to the last.
a. Alt
b. Shift
c. Ctrl
Answer
d. Space bar
2) The Name Manager button is
located on this tab.
a. FORMULAS
b. HOME
c. DATA
d. INSERT
4) The Consolidate button is located
on this tab.
a. FORMULAS
b. HOME
c. DATA
d. INSERT
Next Question
© Paradigm Publishing, Inc.
Next Question
Answer
Answer
Next Question
Next Slide
20
Slide 21
Create a PivotTable Report
A PivotTable is an interactive table that organizes and
summarizes data based on category labels you
designate from row and column headings.
A numeric column you select is then grouped by the
row and column category and the data summarized
using a function such as Sum, Average, or Count.
© Paradigm Publishing, Inc.
21
Slide 22
Create a PivotTable Report - continued
Before creating a PivotTable, examine the source data
and determine the following elements:
Which rows and columns will define how to format and group
the data?
Which numeric field contains the values that should be grouped?
Which summary function will be applied to the values? For
example, do you want to sum, average, or count?
Do you want to be able to filter the report as a whole, as well as
by columns or rows?
Do you want the PivotTable to be beside the source data or in a
new sheet?
How many reports do you want to extract from the PivotTable by
filtering fields?
© Paradigm Publishing, Inc.
22
Slide 23
Create a PivotTable Report - continued
To create a PivotTable:
1. Select source range.
2. Click INSERT tab.
3. Click PivotTable button.
continues on next slide…
PivotTable
button
© Paradigm Publishing, Inc.
23
Slide 24
Create a PivotTable Report - continued
4. At Create PivotTable
dialog box, click OK.
5. Add fields as needed,
using PivotTable Fields
task pane.
6. Modify and/or format as
required.
PivotTable Fields
task pane
© Paradigm Publishing, Inc.
24
Slide 25
Create a PivotTable Report - continued
When the active cell is positioned inside a PivotTable,
the contextual PIVOTTABLE TOOLS ANALYZE and
PIVOTTABLE TOOLS DESIGN tabs become available.
PIVOTTABLE TOOLS DESIGN
tab
© Paradigm Publishing, Inc.
25
Slide 26
Create a PivotTable Report - continued
Slicers allow you to filter a PivotTable report or
PivotChart without opening the Filters list box.
When Slicers are added to a PivotTable or PivotChart,
a Slicer pane containing all of the unique values for the
specified field is added to the window.
Timelines is a new feature added to Excel 2013 that
allows you to group and filter a PivotTable or
PivotChart based on specific timeframes.
A date field you select adds a Timeline pane containing
a timeline slicer that you can extend or shorten to
instantly filter the data by the selected date range.
© Paradigm Publishing, Inc.
26
Slide 27
Create a PivotTable Report - continued
To add a slicer to a
PivotTable report:
1. Make any cell with
PivotTable active.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click Insert Slicer button.
continues on next slide…
© Paradigm Publishing, Inc.
27
Insert Slicer
button
Slide 28
Create a PivotTable Report - continued
4. At Insert Slicers dialog
box, click check box for
desired field.
5. Click OK.
Insert Slicers
dialog box
© Paradigm Publishing, Inc.
28
Slide 29
Create a PivotTable Report - continued
To add a timeline to a
PivotTable report:
1. Make any cell within
PivotTable active.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click Insert Timeline
button.
Insert Timeline
button
continues on next slide…
© Paradigm Publishing, Inc.
29
Slide 30
Create a PivotTable Report - continued
4. At Insert Slicers
dialog box, click
check box for
desired field.
5. Click OK.
6. Select desired
timeframe in
Timeline pane.
Timeline
pane
© Paradigm Publishing, Inc.
30
Slide 31
Create a PivotTable Report - continued
To change the PivotTable
summary function:
1. Make values in field cell
active.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click Field Settings
button.
Field Settings
button
continues on next slide…
© Paradigm Publishing, Inc.
31
Slide 32
Create a PivotTable Report - continued
4. At Value Field Settings
dialog box, click desired
function.
5. Click OK.
Value Field Settings
dialog box
© Paradigm Publishing, Inc.
32
Slide 33
Create a PivotChart
A PivotChart displays the data from a PivotTable in
chart form.
As with a PivotTable, you can filter the data to examine
various scenarios between categories.
Excel displays the PivotChart Fields task pane when a
PivotChart is active so that you can filter the data as
needed.
© Paradigm Publishing, Inc.
33
Slide 34
Create a PivotChart - continued
To create a PivotChart from
a PivotTable:
1. Make a cell active within
PivotTable.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click PivotChart button.
PivotChart
button
continues on next slide…
© Paradigm Publishing, Inc.
34
Slide 35
Create a PivotChart - continued
4. At Insert Chart dialog
box, select desired chart
type.
5. Click OK.
Insert Chart
dialog box
© Paradigm Publishing, Inc.
35
Slide 36
Create a PivotChart - continued
Before you begin creating a PivotChart from scratch,
examine the source data and determine the following
elements:
Which fields do you want to display along the x (horizontal)
axis? In other words, how do you want to compare data when
viewing the chart: by time period (such as months or years),
by salesperson names, by department names, or by some
other category?
Which fields do you want to display in the legend? In other
words, how many data series (bars in a column chart) do you
want to view in the chart: one for each region, product,
salesperson, department, or some other category?
Which numeric field contains the values that you want to
graph in the chart?
© Paradigm Publishing, Inc.
36
Slide 37
Create a PivotChart - continued
To create a PivotChart without
an existing PivotTable:
1. Select range containing
data for chart.
2. Click INSERT tab.
3. Click down-pointing arrow
on PivotChart button.
4. Click PivotChart option.
PivotChart
option
continues on next slide…
© Paradigm Publishing, Inc.
37
Slide 38
Create a PivotChart - continued
5. Click OK.
6. Add fields as needed in
PivotChart Fields list
pane to build chart.
7. Modify and/or format as
required.
© Paradigm Publishing, Inc.
PivotChart Fields
list pane
38
Slide 39
Summarize Data with Sparklines
Sparklines are miniature charts that are embedded
into the background of a cell. An entire chart exists in a
single cell.
Line or Column
Sparklines
Win/Loss
Sparklines
© Paradigm Publishing, Inc.
39
Slide 40
Summarize Data with Sparklines - continued
To create Sparklines:
1. Select empty range in
which to insert Sparklines.
2. Click INSERT tab.
3. Click Line, Column, or
Win/Loss type in
Sparklines group.
continues on next slide…
Sparklines
group
© Paradigm Publishing, Inc.
40
Slide 41
Summarize Data with Sparklines -continued
4. At Create Sparklines
dialog box, type data
range address, or drag to
select data range in Data
Range text box.
5. Click OK.
Create Sparklines
dialog box
© Paradigm Publishing, Inc.
41
Slide 42
Summarize Data with Sparklines -continued
Activate any Sparkline cell and the SPARKLINE TOOLS
DESIGN tab becomes visible.
SPARKLINE TOOLS DESIGN
tab
© Paradigm Publishing, Inc.
42
Slide 43
Summarize Data with Sparklines - continued
To customize Sparklines:
1. Click in any Sparklines
cell.
2. Click SPARKLINE TOOLS
DESIGN tab.
3. Change chart type,
show/hide points or
markers, change chart
style, color, or marker
color.
© Paradigm Publishing, Inc.
Show
group
43
Slide 44
1) This interactive table organizes
and summarizes data based on
category labels.
a. PivotChart
b. PivotTable
c. PivotData
Answer
d. PivotLabel
3) This feature allows you to filter a
PivotTable report or PivotChart.
a. Sparklines
b. Choppers
c. Dicers
d. Slicers
2) The PivotTable button is located
on this tab.
a. FORMULAS
b. DATA
c. INSERT
d. HOME
4) These miniature charts are
embedded into the background of
a cell.
a. Sparklines
b. Choppers
c. Dicers
d. Slicers
Answer
Answer
Next Question
Answer
Next Question
Next Question
© Paradigm Publishing, Inc.
Next Slide
44
Slide 45
Summarizing and Consolidating Data
Summary of Presentation Concepts
Summarize data by creating formulas with range names that reference
other worksheets
Modify the range assigned to a range name
Summarize data by creating 3-D formulas
Create formulas that link to cells in other worksheets or workbooks
Edit a link to a source workbook
Break a link to an external reference
Use the Consolidate feature to summarize data in multiple worksheets
Create, edit, and format a PivotTable
Filter a PivotTable using Slicers and Timelines
Create and format a PivotChart
Create and format Sparklines
© Paradigm Publishing, Inc.
45
© Paradigm Publishing, Inc.
1
Slide 2
Excel 2013
Level 2
Unit 1 Advanced Formatting, Formulas,
and Data Management
Chapter 4 Summarizing and
Consolidating Data
© Paradigm Publishing, Inc.
2
Slide 3
Summarizing and Consolidating Data
Quick Links to Presentation Contents
Summarize Data in Multiple Worksheets Using Range
Names and 3-D References
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks
Summarize Data Using the Consolidate Feature
CHECKPOINT 1
Create a PivotTable Report
Create a PivotChart
Summarize Data with Sparklines
CHECKPOINT 2
© Paradigm Publishing, Inc.
3
Slide 4
Summarize Data in Multiple Worksheets
Using Range Names and 3-D References
A workbook that has been organized with data in
separate worksheets can be summarized by creating
formulas that reference cells in other worksheets.
A worksheet reference precedes a cell reference and is
separated from the cell reference with an exclamation
point.
A formula that references the same cell in a range that
extends over two or more worksheets is often called a
3-D reference.
© Paradigm Publishing, Inc.
4
Slide 5
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References - continued
As an alternative, consider using range names to
simplify formulas that summarize data in multiple
worksheets.
A range name includes the worksheet reference by
default; therefore, typing the range name in the
formula automatically references the correct
worksheet.
© Paradigm Publishing, Inc.
5
Slide 6
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
To sum multiple worksheets using range names:
1.
2.
3.
4.
5.
6.
7.
Make formula cell active.
Type =sum(.
Type first range name.
Type a comma.
Type second range name.
Type a comma.
Continue typing range names separated
by commas until finished.
8. Type ).
9. Press Enter.
© Paradigm Publishing, Inc.
6
range names
Slide 7
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
To modify a named
range reference:
1. Click FORMULAS tab.
2. Click Name Manager
button.
3. Click range name to
be modified.
4. Click Edit button.
Edit
button
continues on next slide…
© Paradigm Publishing, Inc.
7
Slide 8
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
5. Click in Refers to text
box or click collapse
button.
6. Modify range
address(es) as
required.
7. Click OK.
8. Click Close.
Refers to
text box
© Paradigm Publishing, Inc.
8
Slide 9
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
A disadvantage to using range names emerges when
several worksheets need to be summarized, since the
range name reference must be created in each
individual worksheet.
If several worksheets need to be summed, a more
efficient method is to use a 3-D reference.
© Paradigm Publishing, Inc.
9
Slide 10
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References -continued
To use a 3-D reference
formula:
1.
2.
3.
4.
5.
6.
Make desired cell in
worksheet active.
Type =sum(.
Click first sheet tab to
be included.
Hold down Shift key.
Click last sheet tab to
be included.
Select desired range(s).
the three worksheets grouped
in the 3-D reference
continues on next slide…
© Paradigm Publishing, Inc.
10
Slide 11
Summarize Data in Multiple Worksheets Using
Range Names and 3-D References - continued
7. Type ).
8. Press Enter.
© Paradigm Publishing, Inc.
3-D formula created using
point-and-click approach
11
Slide 12
Summarize Data by Linking to Ranges in
Other Worksheets or Workbooks
You can summarize data in one workbook by linking to
a cell, range, or range name in another worksheet or
workbook.
When data is linked, a change made in the source cell
(the cell in which the original data is stored) is
reflected in any other cell to which the source cell has
been linked.
© Paradigm Publishing, Inc.
12
Slide 13
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
To create a link to an external reference:
1.
2.
3.
4.
5.
6.
7.
8.
Open source workbook.
Open destination workbook.
Arrange windows as desired.
Make formula cell active in destination workbook.
Type =.
Click to activate source workbook.
Click source cell.
source cell
destination cell
Press Enter.
© Paradigm Publishing, Inc.
13
Slide 14
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
Linking to a cell in another workbook incorporates
external references and requires that a workbook
name reference be added to a formula. For example,
linking to cell A3 in a sheet named ProductA in a
workbook named Sales would require that you enter
=[Sales.xlsx]ProductA!A3 in the formula cell.
Notice the workbook reference is entered first in
square brackets.
The workbook in which the external reference is
added becomes the destination workbook.
The workbook containing the data that is linked to the
destination workbook is called the source workbook.
© Paradigm Publishing, Inc.
14
Slide 15
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
When you link to an external reference, Excel includes
the drive and folder names in the path to the source
workbook.
If you move the source workbook or change the
workbook name, the link will no longer work.
security warning
message
© Paradigm Publishing, Inc.
15
Slide 16
Summarize Data by Linking to Ranges in Other
Worksheets or Workbooks - continued
To edit a link to an external
reference:
1.
2.
3.
4.
5.
6.
7.
8.
Open destination workbook.
Click DATA tab.
Click Edit Links button.
Click link.
At Edit Links dialog box, click
Change Source button.
Navigate to drive and/or folder.
Double-click source workbook
file name.
Click Close button.
© Paradigm Publishing, Inc.
16
Edit Links
dialog box
Slide 17
Summarize Data by Linking to Ranges in Other
Worksheets or Workbook - continued
To break a link to an external reference:
1.
2.
3.
4.
5.
6.
7.
Open destination workbook.
Click DATA tab.
Click the Edit Links button.
Click link.
At Edit Links dialog box, click Break Link button.
At Microsoft Excel message box, click Break Links button.
Click Close button.
Break Links
button
© Paradigm Publishing, Inc.
17
Slide 18
Summarize Data Using the Consolidate Feature
To consolidate data:
1.
2.
3.
4.
5.
6.
7.
8.
9.
Make starting cell active.
Click DATA tab.
Click Consolidate button.
If necessary, change Function.
Enter first range in Reference
text box.
Click Add button.
Enter next range.
Click Add button.
Repeat steps 7 to 8 until all
ranges have been added.
continues on next slide…
© Paradigm Publishing, Inc.
18
Reference
text box
Slide 19
Summarize Data Using the Consolidate Feature -continued
10. If necessary, select Top
row and/or Left column
check boxes.
11. If necessary, click Create
links to source data
check box.
12. Click OK.
Left column
check box
© Paradigm Publishing, Inc.
19
Slide 20
1) A formula that references the
same cell over two or more
worksheets is often called this.
a. 3-D reference
b. 3-D worksheet
c. 3-D formula
Answer
d. 3-D cell
3) Using this key while clicking a sheet
tab selects all worksheets from the
first sheet tab through to the last.
a. Alt
b. Shift
c. Ctrl
Answer
d. Space bar
2) The Name Manager button is
located on this tab.
a. FORMULAS
b. HOME
c. DATA
d. INSERT
4) The Consolidate button is located
on this tab.
a. FORMULAS
b. HOME
c. DATA
d. INSERT
Next Question
© Paradigm Publishing, Inc.
Next Question
Answer
Answer
Next Question
Next Slide
20
Slide 21
Create a PivotTable Report
A PivotTable is an interactive table that organizes and
summarizes data based on category labels you
designate from row and column headings.
A numeric column you select is then grouped by the
row and column category and the data summarized
using a function such as Sum, Average, or Count.
© Paradigm Publishing, Inc.
21
Slide 22
Create a PivotTable Report - continued
Before creating a PivotTable, examine the source data
and determine the following elements:
Which rows and columns will define how to format and group
the data?
Which numeric field contains the values that should be grouped?
Which summary function will be applied to the values? For
example, do you want to sum, average, or count?
Do you want to be able to filter the report as a whole, as well as
by columns or rows?
Do you want the PivotTable to be beside the source data or in a
new sheet?
How many reports do you want to extract from the PivotTable by
filtering fields?
© Paradigm Publishing, Inc.
22
Slide 23
Create a PivotTable Report - continued
To create a PivotTable:
1. Select source range.
2. Click INSERT tab.
3. Click PivotTable button.
continues on next slide…
PivotTable
button
© Paradigm Publishing, Inc.
23
Slide 24
Create a PivotTable Report - continued
4. At Create PivotTable
dialog box, click OK.
5. Add fields as needed,
using PivotTable Fields
task pane.
6. Modify and/or format as
required.
PivotTable Fields
task pane
© Paradigm Publishing, Inc.
24
Slide 25
Create a PivotTable Report - continued
When the active cell is positioned inside a PivotTable,
the contextual PIVOTTABLE TOOLS ANALYZE and
PIVOTTABLE TOOLS DESIGN tabs become available.
PIVOTTABLE TOOLS DESIGN
tab
© Paradigm Publishing, Inc.
25
Slide 26
Create a PivotTable Report - continued
Slicers allow you to filter a PivotTable report or
PivotChart without opening the Filters list box.
When Slicers are added to a PivotTable or PivotChart,
a Slicer pane containing all of the unique values for the
specified field is added to the window.
Timelines is a new feature added to Excel 2013 that
allows you to group and filter a PivotTable or
PivotChart based on specific timeframes.
A date field you select adds a Timeline pane containing
a timeline slicer that you can extend or shorten to
instantly filter the data by the selected date range.
© Paradigm Publishing, Inc.
26
Slide 27
Create a PivotTable Report - continued
To add a slicer to a
PivotTable report:
1. Make any cell with
PivotTable active.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click Insert Slicer button.
continues on next slide…
© Paradigm Publishing, Inc.
27
Insert Slicer
button
Slide 28
Create a PivotTable Report - continued
4. At Insert Slicers dialog
box, click check box for
desired field.
5. Click OK.
Insert Slicers
dialog box
© Paradigm Publishing, Inc.
28
Slide 29
Create a PivotTable Report - continued
To add a timeline to a
PivotTable report:
1. Make any cell within
PivotTable active.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click Insert Timeline
button.
Insert Timeline
button
continues on next slide…
© Paradigm Publishing, Inc.
29
Slide 30
Create a PivotTable Report - continued
4. At Insert Slicers
dialog box, click
check box for
desired field.
5. Click OK.
6. Select desired
timeframe in
Timeline pane.
Timeline
pane
© Paradigm Publishing, Inc.
30
Slide 31
Create a PivotTable Report - continued
To change the PivotTable
summary function:
1. Make values in field cell
active.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click Field Settings
button.
Field Settings
button
continues on next slide…
© Paradigm Publishing, Inc.
31
Slide 32
Create a PivotTable Report - continued
4. At Value Field Settings
dialog box, click desired
function.
5. Click OK.
Value Field Settings
dialog box
© Paradigm Publishing, Inc.
32
Slide 33
Create a PivotChart
A PivotChart displays the data from a PivotTable in
chart form.
As with a PivotTable, you can filter the data to examine
various scenarios between categories.
Excel displays the PivotChart Fields task pane when a
PivotChart is active so that you can filter the data as
needed.
© Paradigm Publishing, Inc.
33
Slide 34
Create a PivotChart - continued
To create a PivotChart from
a PivotTable:
1. Make a cell active within
PivotTable.
2. Click PIVOTTABLE TOOLS
ANALYZE tab.
3. Click PivotChart button.
PivotChart
button
continues on next slide…
© Paradigm Publishing, Inc.
34
Slide 35
Create a PivotChart - continued
4. At Insert Chart dialog
box, select desired chart
type.
5. Click OK.
Insert Chart
dialog box
© Paradigm Publishing, Inc.
35
Slide 36
Create a PivotChart - continued
Before you begin creating a PivotChart from scratch,
examine the source data and determine the following
elements:
Which fields do you want to display along the x (horizontal)
axis? In other words, how do you want to compare data when
viewing the chart: by time period (such as months or years),
by salesperson names, by department names, or by some
other category?
Which fields do you want to display in the legend? In other
words, how many data series (bars in a column chart) do you
want to view in the chart: one for each region, product,
salesperson, department, or some other category?
Which numeric field contains the values that you want to
graph in the chart?
© Paradigm Publishing, Inc.
36
Slide 37
Create a PivotChart - continued
To create a PivotChart without
an existing PivotTable:
1. Select range containing
data for chart.
2. Click INSERT tab.
3. Click down-pointing arrow
on PivotChart button.
4. Click PivotChart option.
PivotChart
option
continues on next slide…
© Paradigm Publishing, Inc.
37
Slide 38
Create a PivotChart - continued
5. Click OK.
6. Add fields as needed in
PivotChart Fields list
pane to build chart.
7. Modify and/or format as
required.
© Paradigm Publishing, Inc.
PivotChart Fields
list pane
38
Slide 39
Summarize Data with Sparklines
Sparklines are miniature charts that are embedded
into the background of a cell. An entire chart exists in a
single cell.
Line or Column
Sparklines
Win/Loss
Sparklines
© Paradigm Publishing, Inc.
39
Slide 40
Summarize Data with Sparklines - continued
To create Sparklines:
1. Select empty range in
which to insert Sparklines.
2. Click INSERT tab.
3. Click Line, Column, or
Win/Loss type in
Sparklines group.
continues on next slide…
Sparklines
group
© Paradigm Publishing, Inc.
40
Slide 41
Summarize Data with Sparklines -continued
4. At Create Sparklines
dialog box, type data
range address, or drag to
select data range in Data
Range text box.
5. Click OK.
Create Sparklines
dialog box
© Paradigm Publishing, Inc.
41
Slide 42
Summarize Data with Sparklines -continued
Activate any Sparkline cell and the SPARKLINE TOOLS
DESIGN tab becomes visible.
SPARKLINE TOOLS DESIGN
tab
© Paradigm Publishing, Inc.
42
Slide 43
Summarize Data with Sparklines - continued
To customize Sparklines:
1. Click in any Sparklines
cell.
2. Click SPARKLINE TOOLS
DESIGN tab.
3. Change chart type,
show/hide points or
markers, change chart
style, color, or marker
color.
© Paradigm Publishing, Inc.
Show
group
43
Slide 44
1) This interactive table organizes
and summarizes data based on
category labels.
a. PivotChart
b. PivotTable
c. PivotData
Answer
d. PivotLabel
3) This feature allows you to filter a
PivotTable report or PivotChart.
a. Sparklines
b. Choppers
c. Dicers
d. Slicers
2) The PivotTable button is located
on this tab.
a. FORMULAS
b. DATA
c. INSERT
d. HOME
4) These miniature charts are
embedded into the background of
a cell.
a. Sparklines
b. Choppers
c. Dicers
d. Slicers
Answer
Answer
Next Question
Answer
Next Question
Next Question
© Paradigm Publishing, Inc.
Next Slide
44
Slide 45
Summarizing and Consolidating Data
Summary of Presentation Concepts
Summarize data by creating formulas with range names that reference
other worksheets
Modify the range assigned to a range name
Summarize data by creating 3-D formulas
Create formulas that link to cells in other worksheets or workbooks
Edit a link to a source workbook
Break a link to an external reference
Use the Consolidate feature to summarize data in multiple worksheets
Create, edit, and format a PivotTable
Filter a PivotTable using Slicers and Timelines
Create and format a PivotChart
Create and format Sparklines
© Paradigm Publishing, Inc.
45