Unit 5: Multi-Page Spreadsheets
We often have to use multi-page spreadsheets to manage large sets of data. For example, you may want to use a different worksheet or page for each month for a budget spreadsheet. In this situation, we often use the same formulas on multiple worksheets within one workbook. You can accomplish this without having to rewrite the formula in each worksheet. In this unit, we learn how to work with multi-page spreadsheets.
Completing this unit should take you approximately 1 hour.
Upon successful completion of this unit, you will be able to:
- describe how to write a formula that drills down through multiple worksheets; and
- create and format multiple worksheets at one time.
5.1: 3D Formulas
We use 3D formulas to combine data from multiple worksheets into one worksheet. When creating a 3D formula, the formula's syntax is easier to read if your worksheets are adjacent to each other in your workbook. Since you need to name each worksheet in the formula, the syntax can get confusing to write and read when the workbooks you are working with are non-adjacent.
Watch this tutorial, which uses the example of quarterly sales to show how to use 3D formulas over multiple worksheets.
5.2: Formulas Across Workbooks
Now that we have created 3D formulas in 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 mindful of where the workbooks are saved and if they are linked. After you create the formula, you should not move linked workbooks since that may cause the formula to stop working. You need to move both together, so the link remains useful.
Watch this video, which uses the same quarterly sales example to show how to use formulas across multiple workbooks.