The Consolidate command provides an effective way of consolidating financial information, whether imported or created in Excel. In short, Consolidate allows us to summarize our data quickly and accurately. Data can be consolidated from within the same worksheet, from other worksheets in the same workbook, or from other workbooks. Links to source data can be incorporated to update the totals automatically whenever the source data changes. Once the consolidation has been completed, it can be edited and consolidation ranges added or deleted. If the data ranges are identical, the data can be consolidated by position. If the ranges are not identical, Excel can rely on the column headings and/or row labels as cues for determining through what cells to sum. This is termed consolidation by category.
To begin the consolidation process, create a worksheet on which to place the consolidated data. If the worksheets containing the data have identical layouts, consolidate by position, which requires users to copy the column headings and row labels to the consolidated worksheet as shown in Figure 1.
Figure 1 - Copying the Column Headings and Row Labels to the Consolidated Sheet
To consolidate the income statements, do the following:
1. Position the cursor in the upper left-hand corner of the consolidation range. In this case, position the cursor in cell B9.
2. From the Data tab of the Ribbon, select Consolidate to open the dialog box shown in Figure 2.
3. Select SUM in the Function drop-down menu. While SUM will be the function used most often, there are other functions such as AVERAGE, MINIMUM, MAXIMUM, and STANDARD DEVIATION available.
Figure 2 - Using the Data Consolidate Command to Combine Data
5. Since we are combining by position, there is no need to check any of the Use labels in boxes. Check Create links to source data so that any changes to the data will flow through to the consolidated worksheet.
Figure 3 - Consolidation Results with Outlining Enabled
Let's review the process. We created a worksheet on which to consolidate the results of three divisions. With the cursor in the upper left-hand corner of the consolidation range, we executed the Consolidate command, highlighted and added the three data ranges to the Consolidate dialog box, and clicked OK. It's easy to see why most users think that this is a superior method when compared to sum-through formulas. The consolidation was performed without building or copying any formulas, the consolidations are computationally accurate, and users can drill down from the results to see the underlying data.
In our previous example, the data ranges were identical, so we could consolidate based solely on the relative position of the data elements within the data ranges. If the data ranges are not identical, that doesn't preclude us from using the Consolidate command. In those circumstances, we must consolidate by category, whereby Excel reads the row and/or column labels to determine what data cells should be summed. For example, if we were combining expense budgets and one of several departments had extra accounts, we would consolidate by category. Similarly, we would consolidate by category if the order in which the data columns were reported was different. To consolidate by category, simply check Top row and/or Left column in the Use labels in area of the Consolidate dialog box as shown in Figure 4.
Figure 4 - Checking the Appropriate Boxes to Consolidate by Category
Upon clicking OK, the Consolidate tool would consolidate the data, based on the information in the top row and left column.
Excel's Consolidate feature is not new to Excel 2007 or 2010. In prior version of Excel, you can find consolidate under the Data menu. Now is a great time to put this feature to work to handle your needs to consolidate multiple data ranges.