Page 280 - Excel 2013 All-in-One For Dummies

262 Consolidating Worksheets
After saving your workspace file, you can then open it as you would any other Excel workbook or template file: Choose File➪Open, open the folder with the .xlw file you want to use, click its file icon, and then click the Open button. (See Book II, Chapter 3 for details on opening Excel files.)
Consolidating Worksheets
Excel allows you to consolidate data from different worksheets into a single worksheet. Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets. For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years. If
you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets. However, even when the data entries are laid out differently in each spreadsheet, Excel can still con- solidate them provided that you’ve used the same labels to describe the data entries in their respective worksheets.
Most of the time, you 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 that use the same labels (when you consolidate by category). You can, however, have Excel use any of other following sta- tistical functions when doing a consolidation: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, or VARP. (See Book III, Chapter 5 for more information on these functions.)
To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data. (If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidat- ing sheets in different workbooks, open a new workbook. If the sheets in the various workbooks are generated from a template, open the new workbook for the consolidated data from that template.
Before you begin the consolidation process on the new worksheet, you choose the cell or cell range in this worksheet where the consolidated data is to appear. (This range is called the destination area.) If you select a single cell, Excel expands the destination area to columns to the right and rows below as needed to accommodate the consolidated data. If you select a single row, the program expands the destination area down subsequent rows of the worksheet, if required to accommodate the data. If you select a single column, Excel expands the destination area across columns to the right, if required to accommodate the data. If, however, you select a multi-cell range as the desti- nation area, the program does not expand the destination area and restricts the consolidated data just to the cell selection.