Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Multi-Page Budgets—Going to the Third Dimension
to the Third Dimension
This chapter will be using Excel with more than just rows and columns. The
objective is to use sheets, in addition to rows and columns, in the same workbook.
So far, most spreadsheets in this topic used models with rows and columns only.
You are now going to utilize the third dimension in the model, namely sheets. You
may want to prepare Payroll reports for four or five weeks on separate sheets and
compile the results (going to the third dimension) into the last sheet for a summary.
Other possible applications include summing four quarters
sheets into an annual
report, inventory reports of different locations consolidated into a corporate total
inventory report, or a summary budget for a number of departments.
The first part of this chapter illustrates the preparation of the multidimensional
Excel workbook. What you want to do is make all the sheets in the workbook
identical in format. You will also be inserting the same basic information on all the
sheets. After these preparations, you will finally compile the results of all the sheets in
the summary sheet.
A PAYROLL EXAMPLE
This example demonstrates how to set up a monthly payroll report for five weeks on
five separate sheets. After you have the data for the five weeks, you are going to sum
the data into the totals sheet. First, you are going to format six sheets, one for each
week and one for the totals, in an Excel workbook. It is like preparing a template.
If you open a new workbook, you probably have the default number of sheets,
three. In this example, you will need six sheets, one for each week and one for the
totals compilation. You will have to add three sheets. Use Shift
F11 to add sheets.
Make it a total of six. Rename the sheets Week1, Week2, Week3, Week4, Week5,
and Totals by double-clicking on each tab. See Figure 19.1. The Excel workbook is
named Chapter 19A.
Since most of the information and the formatting on all six sheets will be the
same, you will have to select all six sheets so that you can do it on all of them at once.
When all the sheets are selected, you will be able to enter the data simultaneously on
all of them.
There are two ways to select all the sheets. The first way is to click on the sheet
tab Week1, hold the CTRL key, and click to select each and every one of the other