Excel Consolidation

Excel Consolidation to Consolidate and Summarize Data from Different Worksheets

Consolidation is the process of combining values from several ranges of data either from within the same or different workbooks. It can be used to summarize data from different worksheets into master worksheet and create a report using a variety of calculations. Consolidate is found under the Data Menu; Data>Consolidate.

You can consolidate your data in three different ways. The attached workbook shows the three examples of Consolidation.

Consolidate by Position

Used when worksheet data is identical in order and location. This type of Consolidation is the easiest and works using a layering operation. This means that once you open the workbook(s) you wish to consolidate, you specify the ranges to consolidate, then the values in one worksheet are overlaid on those of another worksheet until all the worksheets are overlaid. It is then that you can select the calculation to be performed on your data. No formulas are used to perform this type of Consolidation, although you can set up the consolidation to update automatically when the source data changes, by checking the create links to source data box, rather than manually which is the default.

Consolidate by Category

Used when data is organized differently but has identical row and column labels. This type of Consolidation is a little trickier to perform, but works in nearly the same way. When you select your data to consolidate, you must this time include your row and/or column headings. Excel will then examine the row and/or column headings and will be able to plot the layout of your Worksheets and Consolidate your data for you by examining the contents of the ranges to be used.

Consolidate using 3-D Formulas

Used when worksheet data does not have a consistent layout or pattern. 3-D formulas are formulas that refer to multiple worksheets and can be used to combine this type of data. A formula such as =SUM(Sheet1!A6,Sheet2!C12, Sheet3!H9) can be used to add cells from different worksheets to consolidate into a Summary sheet. This type of Consolidation does not use the Consolidate dialog box found under Data>Consolidate, but is created entirely as a formula, so therefore will automatically update if the data it is dependent on changes.

The attached workbook shows the three examples of Consolidation.