Consolidate data from multiple worksheets
in Excel 2007

Let's say you have the monthly sales data in separate worksheets and you wish to consolidate or summarize the data from each worksheet into a master worksheet. The data can be in the same workbook or in different workbooks. The data from these worksheets can be consolidated so that you can easily update it on a regular basis. In a simlar fashion you can consolidate data from different branches of your company. In fact, you can not only add the data from various worksheets in different workbooks but you can also use other formulas like average, count, max, min, etc.
To perform the consolidate function click on the data tab and then select consolidate. You can consolidate data in all worksheets where the data has been input in identical order and location. Or, you may wish to consolidate data that is organized differently in separate worksheets but has the same row and column headers. You can also use formulas or 3d references to consolidate your data in different worksheets. The training video demonstrates the process quickly.

Further reading

Consolidate data from multiple worksheets in Excel 2007