Using the programs Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets.

If you used a template to create each worksheet youre 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 consolidate them provided that youve used the same labels to describe the data entries in their respective 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 statistical functions when doing a consolidation: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, or VARP.

If the sheets in the various workbooks are generated from a template, open the new workbook for the consolidated data from that template.

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.

The cells that you specify for consolidation are referred to as the source area, and the worksheets that contain the source areas are known as the source worksheets.

If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you dont type ).

If the cells contain formulas, Excel uses their calculated values, but if the cells contain text, Excel ignores them and treats them as though they were blank (except in the case of category labels when youre consolidating your data by category).

He began teaching business users about computers back in the 1980s, and has been a dedicated educator ever since.

Merging Worksheets In Excel For Mac