Working with Multiple Workbooks

1. Working with Multiple Workbooks Lesson 4 – Microsoft Excel 2010

2. 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.

3. 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

4. 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!

5. 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?

6. 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

7. How to Unfreeze • View; Freeze Panes; Unfreeze • Note: Headers and Footers can NOT be used when the freeze pane feature is used.

8. 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)

9. 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.

10. 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.

11. 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

12. 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.

13. Sorting Data, Expanded Option

14. 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!

15. Filtering Data, cont.

16. 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!

17. 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.

18. 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

19. Vocabulary Words Freezing Freeze Pane Sorting Sort Key Filtering Please add the following vocabulary terms to your list of terms and definitions.

20. 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