# PRDV252: Intermediate Excel

Unit 9: Multi-Page Spreadsheets   In this unit, you will learn how to work with large workbooks, and this unit will highlight various features that will make working in Excel® more productive. We will explore formulas and functions that drill downthrough multiple worksheets for their answers. When a workbook gets large, it is important to understand how to format each worksheet tab so that information is easier to find. You learned about formatting worksheet tabs in Subunit 2.2.2. If needed, reread that section.

Completing this unit should take you approximately 3 hours.

☐    Subunit 9.1: 0.75 hours

☐    Subunit 9.2: 0.75 hours

☐    Subunit 9.3: 1.5 hours

Unit9 Learning Outcomes
Upon successful completion of this unit, you will be able to: - describe how to write a formula that drills down through multiple worksheets; - create and format multiple worksheets at one time; and - describe how to group and ungroup worksheets.

• Web Media: The Saylor Foundation’s “3D Formulas” Link: The Saylor Foundation’s “3D Formulas” (YouTube)

Instructions: Watch the video about 3D formulas. When you are finished, open up your own spreadsheet software and try to create some 3D formulas.

Completing this assignment should take approximately 45 minutes.

9.2 Formulas Across Workbooks   Now that you’ve created 3D formulas within one workbook using various worksheets, let’s look at creating a formula that uses two workbooks. This is a little bit more complicated because you have to be cognizant of where the workbooks are saved and if they are linked. If the workbooks are linked, they shouldn’t be moved once the formula is created or the formula may not work. If they are moved, both need to be moved so that the link can stay useful. When you create formulas between workbooks, you can either link them or just create the formula without linking them. But, if they are notlinked and you change or update a number in one workbook, the formula you created will not be updated with the new information. This may make more sense after you have watched the video.

• Web Media: The Saylor Foundation’s “Formulas Across Workbooks” Link: The Saylor Foundation’s “Formulas Across Workbooks” (YouTube)

Instructions: Watch the video about 3D formulas across workbooks. When you are finished, open up your own spreadsheet software and try to create a linked formula between two workbooks. You can use spreadsheets that you created earlier in this class.

Completing this assignment should take approximately 45 minutes.

9.3 Unit 9 Exercises   - Activity: The Saylor Foundation’s “Practice Exercise for Unit 9” Link: The Saylor Foundation’s “Practice Exercise for Unit 9” (PDF)

Instructions: Follow the instructions on the PDF. You will need the accompanying Excel® file below to complete this exercise.