LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010. Lesson 4:
Working with. Multiple Workbooks. Learning Goals//The goal of this lesson is for ...
IT ACADEMY LESSON PLAN
10
Microsoft Excel
®
Lesson 4
Turn potential into success
LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010
Lesson 4: Working with Multiple Workbooks
Learning Goals//The goal of this lesson is for students to successfully
Learning Objectives
On completion of this lesson, students will be able to do the following: Freeze and unfreeze rows and columns
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.
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
Explain that Microsoft Excel enables users to create multiple worksheets within a workbook. This feature allows for similar data to be grouped together. Give examples of data that would be appropriately grouped together in a workbook. Explain that student will learn to move, insert, delete, and copy worksheets within a workbook. Describe the benefit of being able to freeze areas of a worksheet to enhance data readability. Also point out that the ability to sort and filter data in a workbook allows for appropriately organized data to be presented to the user.
Freeze and Unfreezing Rows and Columns
Instructors should do the following: Explain that freezing rows and columns allows the user easily view information while seeing row or column headers.
Demonstrate how to freeze a row and a column using Freeze Panes command on the View Ribbon.
Demonstrate how to unfreeze a row and a column using the Freeze Panes command on the View Ribbon.
Tech Tip Explain that the unfreeze panes option if only visible when a pane is frozen.
Managing Worksheets in a Workbook
Instructors should do the following: Explain that storing related worksheets in a single workbook increases organization and productivity.
Demonstrate how to move easily between worksheets in a workbook by clicking the worksheet tab that appears at the bottom of the worksheet directly above the status bar.
LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010
Demonstrate how to move between worksheets in a workbook using the worksheet tab scrolling buttons located on the bottom left side of the workbook.
Explain that worksheets can be moved or copied within the workbook, rather than rebuilding the worksheet from scratch.
Demonstrate how to move a worksheet within a workbook by dragging the sheet tab to a new location within the workbook.
Demonstrate how to copy an entire worksheet and paste the information to a new sheet within the same workbook.
Point out that there are three easy methods to copy worksheets in a workbook.
Demonstrate how to copy an entire worksheet and paste the information to overwrite data in an existing worksheet within the same workbook.
Explain that worksheets may be inserted into a workbook rather than creating a new workbook.
Demonstrate how to insert a new worksheet into a workbook using the insert worksheet tab that appears on the right of the worksheet tabs or by right-clicking on any sheet tab and selecting insert for the shortcut menu.
Demonstrate how to delete a worksheet from a workbook by right -clicking on the sheet tab and selecting delete from the shortcut menu.
HOT KEY Insert Worksheet—Shift + F11
Sorting Data
Instructors should do the following: Explain that sorting data allows the user to control the order in which the data is presented in the worksheet.
Explain the difference between sorting data in ascending and descending orders.
Explain that a sort key is the item that is being sorted.
Demonstrate how to sort data in ascending or descending orders using the Sort & Filter Command located in the Editing Group on the Home Ribbon.
Demonstrate how to undo a sort using the Undo command on the Quick Access Toolbar.
Demonstrate how to sort data using the Sort command on the shortcut menu.
LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010
Filtering Data
Lesson Quiz
Instructors should do the following: Explain that filtering data allows the user to display only data that meets specific criteria that is set in the filter.
Demonstrate how to filter data using the Sort & Filter Command located in the Editing Group on the Home Ribbon.
Demonstrate how to filter multiple data items using the Sort & Filter Command located in the Editing Group on the Home Ribbon.
Demonstrate how to apply a custom filter using the Sort & Filter Command located in the Editing Group on the Home Ribbon.
True/False 1. The only way to copy a worksheet in a workbook it to use the copy command on the shortcut menu. 2. 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. 3. Once a new sheet is inserted into a workbook, it must remain in the location that it was inserted. 4. Freezing areas in a worksheet is only used to enhance the readability of the worksheet data. 5. Worksheet names may not exceed 31 characters. Multiple Choice 1. Which of the following is NOT an appropriate worksheet tab name? a. b. c. d.
Employees Staff Information Employee and Staff Information Sheet Employee Information
2. When the insert worksheet command is selected, where is the new sheet inserted? a. b. c. d.
To the left of all sheets To the right of all sheets In the middle of all sheets This command is not available
3. Which of the following is NOT a freeze panes option? a. b. c. d.
Freeze panes Freeze current area Freeze top row Freeze first column
LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010 4. When sorting numerical data in ascending order, how will the data be sorted? a. b. c. d. 5.
_____ temporarily hides records that do not meet a specific criterion. a. b. c. d.
Quiz Answers
Smallest number to largest number Largest number to smallest number Numbers cannot be sorted ascending Only numerical order is an option
Sorting Custom sort Conditional formatting Filtering data
True/False 1. False, there are three methods to copy a worksheet. 2. True 3. False, any sheet may be moved. 4. True 5. True Multiple Choice 1. C (Employee and Staff Information Sheet) 2. B (to the right of all sheets) 3. B (Freeze current area) 4. A (smallest number to largest number) 5. D (filtering data )
Class Projects
Lesson 4—Exercise 1 As assistant to the sales manager for XYZ Products, you are asked to create a spreadsheet that will be used to present sales employee data to the sales manager. Open the lesson4ex1 student data file in Excel 2010, and complete the following:
Freeze Row 3 and Column A of the worksheet.
Delete Sheet2 and Sheet3 in the workbook.
Rename Sheet1 to Salary Data.
Create a copy of the Salary Data Sheet and move it to the right.
Rename the rename Sheet2 to Sales Data.
Delete the column on the Sales Data Sheet for Weekly Salary.
Rename the column C heading to Weekly Sales.
LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010
Sort the salary amount from highest to lowest month on the Weekly Salary sheet.
Create a custom filter to show any salary on the Weekly Salary sheet over $500.
Save the file as Lesson4ex1a in the Excel 2010 folder.
Remove the filter.
Format all sales on the Weekly Salary sheet to display as currency.
Save the file as Lesson4ex1b in the Excel 2010 folder.
Close Excel.
Project 4—Exercise 1 You were recently selected as the Economics department work study student assistant. The professor that you are assisting has created a basic worksheet to use for sharing all grades with the students. Open the workbook Lesson4Project 1 and make the following modifications:
Video and Training Resource Links
Merge and center the sheet title for increase readability.
Freeze appropriate areas so that column and row headings are visible as data increases.
Copy the data from Sheet1 to Sheet2.
Delete the score column on Sheet2.
On sheet2, rename column B to be Student ID #.
Move Sheet2 to the left of Sheet1.
Rename Sheet2 to Student Information.
Rename Sheet1 to Student Scores.
Sort the grades is ascending order.
Save the workbook as Lesson4project1 in the Excel 2010 folder.
Close the program.
Microsoft Office Online provides online training, demonstrations, and quizzes that include detailed explanations, preferred methods, and lesson tutorials for each Microsoft Office program. These resources are suggested to engage students in hands-on experience, self-paced lesson participation, and lesson reinforcement. E-Learning Courses from Microsoft Learning Course 10296: Beginner Skills in Microsoft Excel 2010 Show the class the information for this course and explain that this course is designed to help you use the Microsoft Excel 2010 interface, commands, and features to present, analyze, and manipulate various types of data.
LESSON PLAN: MICROSOFT OFFICE 2010//Microsoft Excel 2010 Video Basics Sort data Show the class the information for this video and explain that the video is intended to demonstrate how to sort data in a worksheet. Filter data by using an Autofilter Show the class the information for this video and explain that the video is intended to demonstrate how to see just the data you want by applying an AutoFilter. Freeze and Unfreeze rows and columns Show the class the information for this video and explain that the video is intended to demonstrate how to freeze specific rows and columns in a spreadsheet.
