excel 2013 level 2 unit 1 advanced formatting, formulas, and data management
Post on 11-Jan-2016
40 Views
Preview:
DESCRIPTION
TRANSCRIPT
© Paradigm Publishing, Inc. 1 Contents
© Paradigm Publishing, Inc. 2 Contents
Excel 2013
Level 2
Unit 1 Advanced Formatting, Formulas,
and Data Management
Chapter 4 Summarizing and Consolidating Data
© Paradigm Publishing, Inc. 3 Contents
Summarizing and Consolidating Data
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
Quick Links to Presentation Contents
© Paradigm Publishing, Inc. 4 Contents
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. 5 Contents
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. 6 Contents
Summarize Data in Multiple Worksheets Using Range Names and 3-D References -continuedTo sum multiple worksheets using range names:1. Make formula cell active.2. Type =sum(.3. Type first range name.4. Type a comma.5. Type second range name.6. Type a comma.7. Continue typing range names separated
by commas until finished.8. Type ).9. Press Enter.
range names
© Paradigm Publishing, Inc. 7 Contents
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.
continues on next slide…
Edit button
© Paradigm Publishing, Inc. 8 Contents
Summarize Data in Multiple Worksheets Using Range Names and 3-D References -continued5. 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. 9 Contents
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. 10 Contents
Summarize Data in Multiple Worksheets Using Range Names and 3-D References -continuedTo use a 3-D reference formula:1. Make desired cell in
worksheet active.2. Type =sum(.3. Click first sheet tab to
be included.4. Hold down Shift key.5. Click last sheet tab to
be included.6. Select desired range(s).
continues on next slide…the three worksheets grouped
in the 3-D reference
© Paradigm Publishing, Inc. 11 Contents
Summarize Data in Multiple Worksheets Using Range Names and 3-D References - continued
7. Type ).8. Press Enter.
3-D formula created using point-and-click approach
© Paradigm Publishing, Inc. 12 Contents
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. 13 Contents
Summarize Data by Linking to Ranges in Other Worksheets or Workbooks - continuedTo create a link to an external reference:1. Open source workbook.2. Open destination workbook.3. Arrange windows as desired.4. Make formula cell active in destination workbook.5. Type =.6. Click to activate source workbook.7. Click source cell.8. Press Enter.
source cell destination cell
© Paradigm Publishing, Inc. 14 Contents
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. 15 Contents
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. 16 Contents
Summarize Data by Linking to Ranges in Other Worksheets or Workbooks - continued
To edit a link to an external reference:1. Open destination workbook.2. Click DATA tab.3. Click Edit Links button.4. Click link.5. At Edit Links dialog box, click
Change Source button.6. Navigate to drive and/or folder.7. Double-click source workbook
file name.8. Click Close button.
Edit Links dialog box
© Paradigm Publishing, Inc. 17 Contents
Summarize Data by Linking to Ranges in Other Worksheets or Workbook - continuedTo break a link to an external reference:1. Open destination workbook.2. Click DATA tab.3. Click the Edit Links button.4. Click link.5. At Edit Links dialog box, click Break Link button.6. At Microsoft Excel message box, click Break Links button.7. Click Close button.
Break Links button
© Paradigm Publishing, Inc. 18 Contents
Summarize Data Using the Consolidate Feature
To consolidate data:1. Make starting cell active.2. Click DATA tab.3. Click Consolidate button.4. If necessary, change Function.5. Enter first range in Reference
text box.6. Click Add button.7. Enter next range.8. Click Add button.9. Repeat steps 7 to 8 until all
ranges have been added.
continues on next slide…
Reference text box
© Paradigm Publishing, Inc. 19 Contents
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
Contents© Paradigm Publishing, Inc. 20
CHECKPOINT 11) A formula that references the
same cell over two or more worksheets is often called this.a. 3-D referenceb. 3-D worksheetc. 3-D formulad. 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. Altb. Shiftc. Ctrld. Space bar
2) The Name Manager button is located on this tab.a. FORMULASb. HOMEc. DATAd. INSERT
4) The Consolidate button is located on this tab.a. FORMULASb. HOMEc. DATAd. INSERT
Next Question
Next Question
Next Question
Next Slide
Answer
Answer
Answer
Answer
© Paradigm Publishing, Inc. 21 Contents
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. 22 Contents
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. 23 Contents
Create a PivotTable Report - continuedTo create a PivotTable:1. Select source range.2. Click INSERT tab.3. Click PivotTable button.
continues on next slide…
PivotTable button
© Paradigm Publishing, Inc. 24 Contents
Create a PivotTable Report - continued4. 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. 25 Contents
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. 26 Contents
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. 27 Contents
Create a PivotTable Report - continuedTo 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…
Insert Slicer button
© Paradigm Publishing, Inc. 28 Contents
Create a PivotTable Report - continued4. At Insert Slicers dialog
box, click check box for desired field.
5. Click OK.
Insert Slicers dialog box
© Paradigm Publishing, Inc. 29 Contents
Create a PivotTable Report - continuedTo 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.
continues on next slide…
Insert Timeline button
© Paradigm Publishing, Inc. 30 Contents
Create a PivotTable Report - continued4. 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. 31 Contents
Create a PivotTable Report - continuedTo change the PivotTable summary function:1. Make values in field cell
active.2. Click PIVOTTABLE TOOLS
ANALYZE tab.3. Click Field Settings
button.
continues on next slide…
Field Settings button
© Paradigm Publishing, Inc. 32 Contents
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. 33 Contents
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. 34 Contents
Create a PivotChart - continuedTo create a PivotChart from a PivotTable:1. Make a cell active within
PivotTable.2. Click PIVOTTABLE TOOLS
ANALYZE tab.3. Click PivotChart button.
continues on next slide…
PivotChart button
© Paradigm Publishing, Inc. 35 Contents
Create a PivotChart - continued4. At Insert Chart dialog
box, select desired chart type.
5. Click OK.
Insert Chart dialog box
© Paradigm Publishing, Inc. 36 Contents
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. 37 Contents
Create a PivotChart - continuedTo 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.
continues on next slide…PivotChart
option
© Paradigm Publishing, Inc. 38 Contents
Create a PivotChart - continued5. Click OK.6. Add fields as needed in
PivotChart Fields list pane to build chart.
7. Modify and/or format as required.
PivotChart Fields list pane
© Paradigm Publishing, Inc. 39 Contents
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. 40 Contents
Summarize Data with Sparklines - continuedTo 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. 41 Contents
Summarize Data with Sparklines -continued4. 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. 42 Contents
Summarize Data with Sparklines -continued Activate any Sparkline cell and the SPARKLINE TOOLS
DESIGN tab becomes visible.
SPARKLINE TOOLS DESIGN tab
© Paradigm Publishing, Inc. 43 Contents
Summarize Data with Sparklines - continuedTo 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.
Show group
Contents© Paradigm Publishing, Inc. 44
CHECKPOINT 21) This interactive table organizes
and summarizes data based on category labels.a. PivotChartb. PivotTablec. PivotDatad. PivotLabel
3) This feature allows you to filter a PivotTable report or PivotChart.a. Sparklinesb. Choppersc. Dicersd. Slicers
2) The PivotTable button is located on this tab.a. FORMULASb. DATAc. INSERTd. HOME
4) These miniature charts are embedded into the background of a cell.a. Sparklinesb. Choppersc. Dicersd. Slicers
Next Question
Next Question
Next Question
Next Slide
Answer
Answer
Answer
Answer
© Paradigm Publishing, Inc. 45 Contents
Summarizing and Consolidating Data
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
Summary of Presentation Concepts
top related