Microsoft Office Tutorials and References
In Depth Information
workbook in the database to have the same number of periods as
However, within the workbook, the data ranges do need to have a
parallel structure. To illustrate, here are the data ranges for the
three worksheets in the sample database:
Setting Up the Subtotals
As a general rule, it’s useful to maintain common subtotals in your
data worksheets, as shown in the bottom section of Figure 7-3.
Doing so offers at least two benefits.
One benefit is that these subtotals eliminate the need to calculate
them within your reports. You simplify your reports if you can refer
to the subtotals you need frequently.
Another benefit is that they help you to improve the accuracy of
your reports. You’ll be more accurate if you report a pre-calculated
subtotal, rather than calculating it each time you use it. You’ll also
be more accurate when you have pre-calculated numbers to which
you can reconcile your detailed reports.
The following formulas illustrate how the subtotals are calculated:
Within the larger data area defined for the budgeted amounts, I’ve
inserted additional rows to accommodate subtotals whose number
of rows might change over time. I could have avoided the border
rows if I had defined the subtotals like this: