Excel Consolidation
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| Comparing the Same Workbook |
| Complex Intersections Chart-Mark Points of Intersection Between 2 Lines |
| Conditional Formatting |
| Excel Conditional Line Chart |
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.