 |
|
|
|
|
Download the associated
Workbook
for this lesson
Consolidation is the process of combining values from several ranges of data. It can be used for such things as bringing together budgets from different Departments into one, inventory reports, sales forecasts etc. The great thing about consolidation is that once your data has been pulled together into one combined worksheet you can perform a variety of calculations on the combined data. There are two types of Consolidation that can be performed, these are:
Using worksheets that have exactly the same layout, but containing different data
Using worksheets that have different layouts and containing different data
Performing the first type of Consolidation is the easiest and works using a layering operation. This means that once you open the workbooks 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.
Let's work through an example so you can see what I mean.
Open the attached folder WBDLesson6Level3-ConsolidationFiles , then open the four workbooks, called WBDLesson6Level3-Consolidation1, WBDLesson6Level3-Consolidation2, WBDLesson6Level3-Consolidation3 and WBDLesson6Level3-Consolidation4. Three of these Workbooks contain the Income of Hawley's Brewery for the years 1999, 2000 and 2001. The fourth Workbook is where we will perform our Consolidation and so this will need to be our active Workbook.
The first thing that we need to do is select the range where we wish our Consolidation to take place. In our case it is the range B3:Q6. Now go to Data>Consolidate. What you have in front of you now is the Consolidate dialog box. The top box labelled Function: This is where you select the function that you wish your Consolidation to perform. In our case study we will select Sum. The next box is labelled Reference: This is where we will select the ranges in our other Workbooks that we wish to Consolidate. Hit the Collapse button at the right of the Reference: box then select Window, then WBDLesson6Level3-Consolidation1. Now select the range B3:Q6, then collapse back through to your Consolidate dialog box and click on Add. Click on the collapse button to the right of the Reference: box again and follow the same steps and select the same ranges in WBDLesson6Level3-Consolidation2 and WBDLesson6Level3-Consolidation3.
You should now be able to see all three ranges in the All References: box. The Delete button directly underneath the Add button will delete references from the All References: box. The box underneath the All References: box is headed Use Labels In. We would use the Top row or Left column boxes only when you want to consolidate data with labels that are similar but arranged differently in each source area selected for consolidation. If you want to consolidate data that has different category labels but contains similar data arranged identically in each source area, or if you want to consolidate by position, do not include labels when you select source areas.
The last little checkbox titled Create links to source data when checked will update the consolidation area data automatically when the data changes in any of the source areas. In our case study we do not wish this to happen, so we will leave this checkbox unchecked. All we need to do now is select OK. Once you have done this you will see in the WBDLesson6Level3-Consolidation4 the results of your Consolidation. Notice that if you click around on the various values within the cells that there are NO formulas.
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.
Close down all the Workbooks on your screen and open the Workbooks WBDLesson6Level3-Consolidation5, WBDLesson6Level3-Consolidation6, WBDLesson6Level3-Consolidation7, WBDLesson6Level3-Consolidation8. Have a quick browse through these four Workbooks, noting that the layouts are slightly different especially in WBDLesson6Level3-Consolidation7. Make sure that WBDLesson6Level3-Consolidation8 is your active Workbook, then highlight the range A3:Q8 then go to Data>Consolidate.
In this type of consolidation we are going to use the Average Function, so lets select Average from the list under Function: Click in your Reference: box and select the collapse button to the right of this box, then select Window then WBDLesson6Level3-Consolidation5 and the range A3:Q8. Select your collapse button again, then the Add button. Using the same method, select the same range (A3:Q8) in WBDLesson6Level3-Consolidation6 and WBDLesson6Level3-Consolidation7, selecting Add after each selection. Once you have done this, ensure that Left column in Use labels in appears with a tick in it, then select OK.
Now that your consolidation is complete, you will notice that you have performed an average calculation on your data, and that the figures and label for the Lounge from WBDLesson6Level3-Consolidation7 has been inserted into your WBDLesson6Level3-Consolidation8 Workbook. This was because we ensured that the Left Column was selected under Use Labels In.
It is very easy to change the type of calculation that you wish to do when performing a Consolidation. All you need to do if you wish to see a different calculation result is to re-highlight your data (A3:Q8) and then go to Data>Consolidate then select the type of Function you require and click OK.