Consolidating Data from Several Worksheets
Filling Across Worksheets
When you consolidate data, you use formulas that summarize the results contained in several worksheets or workbooks in a single cell. To copy information from one worksheet into another sheet or worksheet group, you can use the same fill command that you use to copy formulas, values, and formats from one cell into a range of cells. To copy the values and formats from one worksheet to another, click the tab of the first worksheet to make it the active sheet. Press and hold down the Shift key, click the tab of the last worksheet and then release the Shift key. The tabs are highlighted and the word Group appears in the title bar. The first worksheet is the active sheet. Select the range, click Edit on the menu, point to Fill, and then click Across Worksheets. The Fill Across Worksheets dialog box opens. You can choose to copy only the contents of the selected cells, the formats, or both. Right-click the worksheet tab and click Ungroup Sheets on the shortcut menu. The contents and formats have been copied to the worksheet. You need to adjust the width of the columns so all of the cell entries are visible.
Practice
In the Singers.xls workbook, you’ll do something similar by creating a single worksheet named “Annual” that will display the total cash flow values for the entire fiscal year.
To insert the Annual worksheet:
- Click on the First Sheet button
and right-click the January tab and then click Insert on the shortcut menu or right click on the January tab and choose insert.
The insert dialog box opens.
- On the General tab, click the Worksheet icon if necessary and then click the OK button. A new worksheet is inserted to the left of the January sheet.
- Double-click the new sheet tab, type Annual as the new sheet name, and then press the Enter key.
Now the Annual worksheet needs to have the same format and structure as the monthly worksheets you’ve been working on. To ensure consistency among the worksheets, you can copy the format from the January worksheet to the Annual worksheet.
Copying Information across Worksheets
To copy information from one worksheet into another sheet or worksheet group, you can use the same fill command that you used to copy formulas, values, and formats from one cell into a range of cells.
- To copy the values and formats from the January to the Annual worksheet, click the January tab to make it the active sheet.
- Press and hold down the Shift key, click the Annual tab, and then release the Shift key. The Annual tab is highlighted and the word Group appears in the title bar. The January worksheet is the active sheet.
- Select the range A1:B23 on the January worksheet.
- Click Edit on the menu, point to Fill, and then click Across Worksheets.
- Verify that the All option button is selected and click OK.
- Right-click the January tab and click Ungroup Sheets on the shortcut menu.
- Click the Annual tab. The contents and formats have been copied to the worksheet.
- Adjust the width of the columns so all of the cell entries are visible by double-clicking on the border between the columns at the column header.
- Increase the width of column B to 10 characters (you can also do this by dragging the column border or by using Format>Column>Width). Save your work and then go to the next section on Summing Values Across Worksheets.
The Fill Across Worksheets dialog box opens. You can choose to copy only the contents of the selected cells, the formats, or both.
