Tutorial 6: Managing Multiple Worksheets and Workbooks - PowerPoint PPT Presentation

Loading...

PPT – Tutorial 6: Managing Multiple Worksheets and Workbooks PowerPoint presentation | free to download - id: 6661f2-ZDY0O



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Tutorial 6: Managing Multiple Worksheets and Workbooks

Description:

Tutorial 6: Managing Multiple Worksheets and Workbooks New Perspectives on Microsoft Excel 2010 * Creating Templates A template workbook Includes all text (row and ... – PowerPoint PPT presentation

Number of Views:0
Avg rating:3.0/5.0
Date added: 27 June 2019
Slides: 43
Provided by: JuliaL152
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Tutorial 6: Managing Multiple Worksheets and Workbooks


1
Tutorial 6 Managing Multiple Worksheetsand
Workbooks
2
Objectives
  • Create a worksheet group
  • Format and edit multiple worksheets at once
  • Create cell references to other worksheets
  • Consolidate information from multiple worksheets
    using 3-D references
  • Create and print a worksheet group

3
Objectives
  • Create a link to data in another workbook
  • Create a workbook reference
  • Learn how to edit links
  • Create and use an Excel workspace
  • Insert a hyperlink in a cell
  • Create a custom template
  • Save a workbook to SkyDrive and use the Excel Web
    App

4
Visual Overview
5
Worksheet Groups and 3-D References
6
Grouping Worksheets
  • 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
  • A worksheet group can contain adjacent or
    nonadjacent worksheets

7
Grouping Worksheets
8
Entering Formulas in a Worksheet Group
  • Grouped worksheets must have exact same
    organization and layout (rows and columns)
  • The formula is entered in the same cells in all
    worksheets in the group

9
Grouping Worksheets
  • Any formatting changes made to the active sheet
    are applied to all sheets in the group
  • When worksheets are ungrouped, each one functions
    independently again
  • If you forget to ungroup worksheets, any changes
    you make in one will be applied to all worksheets
    in the group

10
Working with Multiple Worksheets
  • Copying worksheets
  • Use an existing worksheet as a starting point for
    creating another one
  • Duplicates all values, formulas, and formats into
    new worksheet, leaving original worksheet intact
  • Edit, reformat, and enter new content as needed

11
Working with Multiple Worksheets
  • Referencing cells and ranges in other worksheets
  • Using multiple worksheets to organize related
    data allows you to reference a cell or range in
    another worksheet in the same workbook

12
Using 3-D References to Add Values Across
Worksheets
  • When worksheets have identical row and column
    layouts, enter formulas with 3-D references to
    summarize the worksheets in another worksheet
  • 3-D reference specifies not only the range of
    rows and columns, but also the range of worksheet
    names in which the cells appear
  • General syntax of a 3-D cell reference

13
Using 3-D References to Add Values Across
Worksheets
14
Using 3-D References to Add Values Across
Worksheets
15
Using 3-D References to Add Values Across
Worksheets
  • If you change the value in one worksheet, the
    results of formulas that reference that cell
    reflect the change

16
Printing a Worksheet Group
  • Same page layout settings apply to all
    worksheets in the group at the same time
  • All worksheets in the group can be printed at once

17
Visual Overview
18
Links and External References
19
Linking Workbooks
  • When creating formulas in a workbook, reference
    data in other workbooks by creating a link
    between the workbooks
  • When two files are linked, the source file
    contains the data, and the destination file
    (dependent file) receives the data
  • When source and destination workbooks are in
    different folders, workbook reference must
    include the files complete location (the path)

20
Linking Workbooks
21
Link Workbooks When
  • Separate workbooks have the same purpose and
    structure
  • A large workbook is too unwieldy to use
  • Information from different workbooks can be
    summarized
  • Source workbooks received from another person or
    group are continually updated

22
Navigating Multiple Workbooks
  • To change which workbook is active
  • Use Switch Windows button
  • - or -
  • Click Excel program button on the taskbar, then
    click the thumbnail of the workbook

23
Arranging Multiple Workbooks
  • Windows arranged in a tiled configuration
  • Other options horizontal, vertical, cascade

24
Creating Formulas with External References
  • A formula can include a reference to another
    workbook (external reference), which creates a
    set of linked workbooks

25
Updating Linked Workbooks
  • When data in a source file changes, the
    destination file should reflect those changes
  • If source and destination files are open when a
    change is made
  • Destination file is updated automatically
  • If destination file is closed when source file is
    changed
  • Choose whether to update the link to display
    current values, or continue to display older
    values when you open the destination file

26
Updating a Destination Workbook with Source
Workbooks Closed
27
Managing Links
  • Use Edit Links dialog box to manage links

28
Creating an Excel Workspace
  • Saves information about all currently opened
    workbooks (e.g., locations, window sizes)
  • Has the file extension .xlw
  • Does not contain workbooks themselvesonly
    information about them

29
Visual Overview
30
Templates and Hyperlinks
31
Creating a Hyperlink
  • A link in a file to information within that file
    or another file
  • Can be used to
  • Quickly jump to a specific cell or range within
    the active worksheet, another worksheet, or
    another workbook
  • Jump to other files

32
Inserting a Hyperlink
  • Use the Hyperlink button

33
Editing a Hyperlink
  • Change its target file or Web page
  • - or -
  • Modify the text that is displayed
  • - or -
  • Change the ScreenTip for the hyperlink

34
Creating Templates
  • A template workbook
  • Includes all text (row and column labels),
    formatting, and formulas, but no data
  • Is a model from which you create new workbooks
  • Any changes or additions made to the new workbook
    do not affect the template file

35
Creating a Workbook Based on an Existing Template
  • Templates provide commonly used worksheet formats

36
Creating a Workbook Based on an Existing Template
37
Creating a Custom Workbook Template
  • Build the workbook with all necessary labels,
    formatting, and data then save the workbook as a
    template

38
Creating a New Workbook from a Template
39
Using the Excel Web App and SkyDrive
  • Office Web Apps
  • Web-based versions of Microsoft Excel, Word,
    PowerPoint, and OneNote
  • Allow you to create, view, and edit Office files
    directly from a Web browser, and share files and
    collaborate with other users online
  • Are part of Windows Live, a collection of
    services and Web applications
  • To save files to SkyDrive or use Office Web Apps,
    you need a Windows Live ID

40
Saving a Workbook to SkyDrive
41
Editing a Workbook with the Excel Web App
  • Limited number of commands available

42
Editing a Workbook with the Excel Web App
About PowerShow.com