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

264 Consolidating Worksheets
1. Open all the workbooks with the worksheets you want to consolidate.
If the sheets are all in one workbook, open it in Excel.
Now you need to activate a new worksheet to hold the consolidated data. If you’re consolidating the data in a new workbook, you need to open it (File➪New or Alt+FN). If you’re consolidating worksheets gener- ated from a template, use the template to create the new workbook in which you are to consolidate the spreadsheet data.
2. Open a new worksheet to hold the consolidated data (Ctrl+N).
Next, you need to select the destination area in the new worksheet that
is to hold the consolidated data.
3. Click the cell at the beginning of the destination area in the consolida- tion worksheet, or select the cell range if you want to limit the desti- nation area to a particular region.
If you want Excel to expand the size of the destination area as needed to accommodate the source areas, just select the first cell of this range.
4. Click the Consolidate command button on the Data tab of the Ribbon or press Alt+AN.
Doing this opens the Consolidate dialog box similar to the one shown in Figure 4-13. By default, Excel uses the SUM function to total the values in the source areas. If you want to use another statistical function such as AVERAGE or COUNT, select the desired function from the Function drop-down list box.
5. (Optional) Select the function you want to use from the Function drop- down list box if you don’t want the values in the source areas summed together.
Now, you need to specify the various source ranges to be consolidated and add them to the All References list box in the Consolidate dialog box. To do this, you specify each range to be used as the source data in the Reference text box and then click the Add button to add it to the All References list box.
6. Select the cell range or type the cell references for the first source area in the Reference text box.
When you select the cell range by pointing, Excel minimizes the Consolidate dialog box to the Reference text box so that you can see what you’re selecting. If the workbook window is not visible, choose
it from the Switch Windows button on the View tab or the Windows taskbar and then select the cell selection as you normally would. (Remember that you can move the Consolidate dialog box minimized to the Reference text box by dragging it by the title bar.)
If the source worksheets are not open, you can click the Browse command button to select the filename in the Browse dialog box to enter it (plus an exclamation point) into the Reference text box, and then you can type