MOAC – Excel Lesson 6 – Managing Worksheets Student Assessment Instructions
Competency Assessment
Project 6-1: School of Fine Arts Enrollments
In this exercise, you will move and copy worksheets, rename worksheets, change the tab color, and rearrange worksheets within a workbook.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the SFA Enrollments data file for this lesson.
2. With the Advertising tab active, click Ctrl+A. This selects the entire worksheet.
3. Click Format in the Cells group on the Home tab. Click Move or Copy Sheet.
4. In the Move or Copy dialog box, click the Create a copy box and click OK.
5. On the Advertising (2) worksheet, select A2 and key Foundational Studies. Press Enter.
6. Select A6:C20 and press Delete.
7. Click Format, click Rename Sheet, and key Foundations. Press Enter.
8. Click Format, click Tab Color, and click Dark Red.
9. Click Format and click Move or Copy Sheet. In the Before sheet box, click (move to end) and click OK.
10. SAVE the workbook as SFA Enrollments 6-1 and then CLOSE the file.
LEAVE Excel open for the next project.
Project 6-2: Graphic Design Institute, Part 1
For this project, you will rename worksheets, hide and unhide worksheets, and insert and delete worksheets from a workbook.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Training Expenditures data file for this lesson.
2. Right-click Sheet1. Click Rename and key Budget. Press Enter.
3. Double-click the Sheet2 tab. Key January. Press Enter.
4. Rename Sheet3 March and press Enter.
5. Rename Sheet4 Previous Qtr. and press Enter.
6. Click the Insert Worksheet tab. Rename the new sheet Summary.
7. Click the March tab and click the Insert arrow in the Cells group on the Home tab. Click Insert Sheet.
8. Name the new worksheet February.
9. Click the Previous Qtr. tab. Click Format, click Hide & Unhide, and click Hide Sheet.
10. Click Format, click Hide & Unhide, and click Unhide Sheet. In the Unhide dialog box, click OK.
11. With the Previous Qtr. tab selected, click the arrow next to Delete and click Delete Sheet. Click Delete on the dialog box to confirm that you want to delete the Previous Qtr. sheet.
12. SAVE the workbook as Training Expenditures 6-2.
LEAVE the workbook open for the next project.
Proficiency Assessment
Project 6-3: Graphic Design Institute, Part 2
In this project, you will move between worksheets, change the workbook view, and group worksheets to enter data on multiple sheets.
USE the workbook from the previous project.
1. Click the View tab to make it active.
2. On the Budget worksheet, select E18 and click Zoom to Selection in the Zoom group.
3. Click 100% in the Zoom group.
4. Click Zoom In on the Status bar and increase magnification to 150%.
5. Click the January tab and click Select All. Click Copy.
6. Click the Summary sheet tab, select A1, and click Paste.
7. On the February worksheet, select A1, right-click, and click Paste. Click the Paste
Options button and select Keep Source Formatting.
Troubleshooting If the formatting is not copied, make the January worksheet active and select the text containing the formatting. Double-click the Format Painter and apply the formatting to the necessary cells.
8. Double-click A2 to put it in Edit mode (noted on Status bar). Select January, key February, and press Enter.
9. Select C4 and key February.
10. Delete the January expenditures from C5:C17. Then enter the February expenditures for the items listed next. (Not all items have February expenditures; leave those cells blank.)
Courseware development $2,500
Courseware purchase 400
Certification 250
Train-the-trainer 1,200
Hardware purchases 10,500
Consulting fees 150
Instructor fees 4,000
Travel 600
Per diem 400
11. Select A2 on the Summary worksheet and key Quarterly Expenditures.
12. Copy C4:C18 from the February worksheet to the Summary sheet. Paste the data next to the January data.
13. Copy C4:C18 from the March worksheet to the Summary sheet. Paste the data next to the February data.
14. Select A1:E1 and click Merge & Center two times.
15. Click Merge & Center two times for cells A2:E2.
16. SAVE the workbook as Training Expenditures 6-3. CLOSE the workbook.
LEAVE Excel open for the next project.
Project 6-4: School of Fine Arts Directory
For this project, you will update the school’s staff directory.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Updated Directory data file for this lesson.
2. At the bottom of the worksheet, add information for three new staff members:
DeGrasse, Kirk Media Studies Associate Professor 2/15/07
Sheperdidian, Janet Student Services Academic Advisor 3/1/07
Playstead, Craig Administration Associate Dean 4/1/07
3. Gail Erickson has been promoted to Professor. Click Find & Select. Change her title.
4. Use the Find & Select feature to replace BioMedical with Biomedical Art.
5. Use Find & Select to go to A33. Sidney Higa’s title should be Vice President.
6. Click Format and change the name of Sheet1 to Directory.
7. Click Sheet2. Press Ctrl and click Sheet3. Click Format and hide the blank worksheets.
8. Name the workbook Staff Directory 6-4.
9. SAVE and CLOSE the workbook.
LEAVE Excel open for the next project.
Mastery Assessment
Project 6-5: School of Fine Arts Course Recommendations Debra Core, an academic advisor, has asked you to search the enrollment data and highlight courses for some of the continuing education students with whom she is working.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Advisor Recommendations data file for this lesson.
2. Identify the courses that investigate various aspects of color.
a. Use the Find & Select options to search the entire workbook.
b. Use color as the search string.
c. In the Within field, click Workbook.
d. Find all courses that have color as part of the course name.
3. Your search should return a list of six courses. Add yellow fill color to highlight each course.
a. Click the first course (Biomedical Arts). Click Fill Color.
b. Click the second course and click Fill Color in the Fonts group.
c. Continue until the six courses have been highlighted.
4. Identify the available painting courses.
a. Use painting as the search string.
b. Search the workbook and mark painting courses with a light blue fill.
5. Mark photography courses with a light green fill.
6. SAVE the workbook as Advisor Recommendations 6-5. CLOSE the file.
LEAVE Excel open for the next project.
Project 6-6: Contoso, Ltd.
This exercise has you use the Find & Select command to locate specific information and fill blank spaces in a worksheet. Be sure to freeze the column headings so they remain visible as you scroll through the list of Contoso employees.
GET READY. LAUNCH Excel if it is not already running.
1. OPEN the Contoso Employees data file for this lesson.
2. Use the Freeze Panes command so that the column headings in row 4 remain visible
as you scroll to the end the data range.
3. Find and Replace all occurrences of Billing Clerk with Accounts Receivable Clerk.
4. Use Find and Replace options to find all blank cells on the worksheet. Key Records Management in each blank in column C.
5. SAVE the workbook as Contoso Employees 6-6.
LEAVE Excel open for the next project