working with multiple workbooks n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Working with Multiple Workbooks PowerPoint Presentation
Download Presentation
Working with Multiple Workbooks

Loading in 2 Seconds...

play fullscreen
1 / 20

Working with Multiple Workbooks - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

Working with Multiple Workbooks. Lesson 4 – Microsoft Excel 2010. Learning Goals.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Working with Multiple Workbooks' - lindley


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
working with multiple workbooks

Working with Multiple Workbooks

Lesson 4 – Microsoft Excel 2010

learning goals
Learning Goals

The goal of this lesson is for students to successfully work with an entire workbook in Microsoft Excel 2010. The student will learn to move between workbooks and worksheets, copy, move, freeze areas of a worksheet, insert and delete worksheets in a workbook as well as apply filters to data within the worksheet.

learning objectives
Learning Objectives
  • On completion of this lesson, students will be able to do the following:
    • Freeze and unfreeze rows and columns
    • Move between worksheets in a workbook
    • Copy and move worksheets in a workbook
    • Insert and delete worksheets in a workbook
    • Sort data
    • Apply filters to data
lesson introduction
Lesson Introduction
  • Microsoft Excel enables users to create multiple worksheets within a workbook.
  • This feature allows for similar data to be grouped together.
  • What is an example of data that would be appropriately grouped together in a workbook?
  • Remember: You must use the ribbon method of data manipulation for the certification test. Do NOT depend on right clicks and short cuts at this time. LEARN YOUR RIBBONS IN DETAIL!
freeze and unfreeze rows and columns
Freeze and Unfreeze Rows and Columns
  • Freezing rows and columns allows the user easily view information while seeing row or column headers.
  • Freeze (or unfreeze) a row and a column using Freeze Panes command on the View Ribbon.
  • What is the benefit of being able to freeze areas of a worksheet to enhance data readability?
how to freeze
How to Freeze
  • A Column: Select the column to the RIGHT of the one to keep still; View; Freeze Panes; choose
  • A Row: Select the row BELOW the one to keep still; View; Freeze Panes; choose
  • BOTH a Row AND a Column: CTRL + Row number below the one to be still AND Column to the right of the one to be still (blue cross effect); click on the intersecting cell; look at the cell address in the name box; click out of the data; click the specific cell that was in the intersection; View; Freeze Panes; Freeze Panes
how to unfreeze
How to Unfreeze
  • View; Freeze Panes; Unfreeze
  • Note: Headers and Footers can NOT be used when the freeze pane feature is used.
managing worksheets in a workbook
Managing Worksheets in a Workbook
  • Storing related worksheets in a single workbook increases organization and productivity.
  • Worksheets are inserted at the right of all sheets.
  • Worksheets can be moved or copied within the workbook, rather than rebuilding the worksheet from scratch.

Ribbon Method: Home, Cells, Format, (Insert, Move or Copy, Rename, etc)

managing worksheets in a workbook cont
Managing Worksheets in a Workbook, cont.
  • It’s easy to copy a worksheet!
    • Right click on the sheet tab and click “Move or Copy”
    • Use the tab scrolling buttons located at the bottom left side of the workbook.
  • To replace the contents of one worksheet with those of another:
    • Use the Select Allbutton to select an entire sheet, copy it, and paste it over a existing data or on a blank worksheet.
    • Use the Home, Format, Move or Copy, select the location to overwrite data.
inserting and deleting worksheets
Inserting and Deleting Worksheets
  • Worksheets may be inserted into a workbook rather than creating a new workbook. Just right click on a tab and choose “Insert” or click on the “Insert Worksheet” tab.
  • Right click on a worksheet tab and choose “Delete” to easily delete it.
sorting data
Sorting Data

Home, Editing, Sort & Filter, choose method.

  • Sortingdata allows the user to control the order in which the data is presented in the worksheet.
    • Ascending : A-Z, 1-3
    • Descending: Z-A, 3-1
to sort when data has calculations in the bottom row
To sort when data has calculations in the bottom row
  • A sortkey is the item (column) that is being sorted. (last name, age, etc.)
  • Begin by highlighting the ENTIRE sheet of data excluding the Sum/Average row, then Sort & Filter, Sort OR Custom Sort when solving multiple sorts.
  • Undoa sort using the Undocommand on the Quick Access Toolbar.
  • Use the Editinggroup on the Hometab or the Sortshortcut when you right click a range.
filtering data
Filtering Data
  • Filtering data allows the user to display only data that meets specific criteria that is set in the filter.
  • Filter data (one column or multiple columns) using the Sort & Filter Command located in the Editing Group on the Home Ribbon.
  • Remember: Filtering data temporarily hides records that do not meet specific criterion!
worksheet names
Worksheet Names

Worksheet names are different from file names—

they can contain up to 31 characters!

There is NO need to put the word “Sheet” at the end of a worksheet name.

Remember: File names can contain up to 255 characters!

slide17

Test Your Knowledge

Answer Key:

FALSEThere are three methods to copy a worksheet.

TRUE

FALSEAny sheet may be moved.

TRUE

TRUE

True/False

  • The only way to copy a worksheet in a workbook it to use the copy command on the shortcut menu.
  • The quickest way to insert a new worksheet into a workbook is to use the insert worksheet tab located to the right of the sheet tabs.
  • Once a new sheet is inserted into a workbook, it must remain in the location that it was inserted.
  • Freezing areas in a worksheet is only used to enhance the readability of the worksheet data.
  • Worksheet names may not exceed 31 characters.
test your knowledge
Test Your Knowledge

Answer Key:

  • C(Employee and Staff Information Sheet)
  • B(to the right of all sheets)
  • B(Freeze current area)
  • A(smallest number to largest number)
  • D(filtering data )

Multiple Choice

  • Which of the following is NOT an appropriate worksheet tab name? a. Employees b. Staff Information c. Employee and Staff Information Sheet d. Employee Information
  • When the insert worksheet command is selected, where is the new sheet inserted? a. To the left of all sheets b. To the right of all sheets c. In the middle of all sheets d. This command is not available
  • Which of the following is NOT a freeze panes option? a. Freeze panes b. Freeze current area c. Freeze top row d. Freeze first column
  • When sorting numerical data in ascending order, how will the data be sorted? a. Smallest number to largest number b. Largest number to smallest number c. Numbers cannot be sorted ascending d. Only numerical order is an option
  • _____ temporarily hides records that do not meet a specific criterion. a. Sorting b. Custom sort c. Conditional formatting d. Filtering data
vocabulary words
Vocabulary Words

Freezing

Freeze Pane

Sorting

Sort Key

Filtering

Please add the following vocabulary terms to your list of terms and definitions.

practice what you have learned
Practice What You Have Learned

Complete the following assignments.

When complete, save and submit.

Assignments

Excel Lesson 4 – Exercise 1

Excel Lesson 4 – Project 1