Page 210 - Excel Timesaving Techniques for Dummies

41_574272 ch36.qxd 10/1/04 10:54 PM Page 195
36 Consolidating Data
from Different

Worksheets
Technique

xcel’s Consolidation feature makes short work of combining numeri-
Save Time By cal data in tables stored on several different worksheets or even dif-
Eferent workbook files. For example, you can use this feature to total
Combining data by
position all budget spreadsheets prepared by each department in the company or
to create summary totals for income statements for a period of several
Combining data by years.
category
If you used a template to create each worksheet you’re consolidating or
Linking consolidated data
an identical layout, Excel can quickly consolidate the values by virtue of
their common position in the respective worksheets. However, even if
you laid out the data entries differently in each spreadsheet, Excel can
still consolidate them provided that you’ve used the same labels to
describe the data entries in their respective worksheets.

Most of the time, you will want to total the data that you’re consolidating
from the various worksheets. By default, Excel uses the SUM function to
total all the cells in the worksheets that share the same cell references
(when you consolidate by position) or use the same labels (when you
consolidate by category). You can, however, have Excel use any of the
following statistical functions when doing a consolidation: COUNT,
AVERAGE, MAX, MIN, PRODUCT, COUNTA (referred to as Count Nums),
STDEV, STDEVP, VAR, or VARP.

Consolidating Data by Position

You consolidate worksheets by position when they use the same layout
(such as those created from a template). When you consolidate data by
position, Excel does not copy the labels from the source areas to the des-
tination area, only the values.
To consolidate worksheets by position, follow these steps:

1. Open all the workbooks with the worksheets you want to consoli-
date. If the sheets are all in one workbook, open that file in Excel.
2. Create a new worksheet to hold the consolidated data.