Consolidating Data from Different Worksheets

Excel's Consolidation feature makes short work of combining numerical data in tables stored on several different worksheets or even different workbook files. For example, you can use this feature 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 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.