The Consolidate command isn’t new. It’s been in Excel since the 1990s. Found on the Data tab, it has a peculiar requirement: It only works if your data has exactly one column of labels and one row of headings. While this is a limiting factor, there are a lot of data sets that follow this shape.
Consolidate easily handles differing numbers of rows or columns. In the figure below, three worksheets in a workbook show a chart of accounts in column A. Some items might be missing from any worksheet, meaning that each account could appear on a different row. In this example, the worksheets have differing numbers of months: four months in Report1, five months in Report2, and three months in Report3.
To consolidate these three worksheets, follow these steps:
1. Insert a blank worksheet in your workbook.
2. Select the top-left-corner cell where you want the consolidated data to appear. Make sure there’s nothing to the right or below the active cell.
3. From the Data tab, select Consolidate to open the dialog.
4. The Function dropdown in the Consolidate dialog defaults to Sum. You can change this to Count, Average, Max, Min, Product, Count Numbers, Standard Deviation, or Variance.
5. There is a Reference Edit icon on the right side of the Reference box. Click the icon, navigate to the first worksheet, and select the data, headings, and labels. Use the Close icon on the right side of the Consolidate: Reference dialog to return to the Consolidate dialog.
6. The address of the first data set is left in the Reference box. Click the Add button to move the first reference to the All References box. After you click Add, the original reference appears in both All References and in the Reference box. While this might seem confusing, Microsoft leaves the old reference in place in case your next data set simply needs to change from Sheet1 to Sheet2.
7. Click the Reference Edit icon on the right side of the Reference box. Select the data for your second worksheet. Click the Close icon to return to the Consolidate dialog. Click the Add button to add this second reference to the All References dialog.
8. For your last data set, the steps are slightly different. Use the Reference Edit button, then select the third data set. Click Close. But at this point, it’s your choice if you press the Add button or not. While clicking the Add button seems like the correct step, the Consolidate will work if the last data set is left in the Reference box.
9. Check the Top Row and Left Column boxes in the lower-left corner of the Consolidate dialog.
- Click OK to perform the consolidation.
Note that I have opened the Consolidate dialog again, displaying it on top of the results. You can see that, even though we left the final data set out of the All References box in step 8, Excel now displays that reference in the All References box.
There are some unusual features in the results. As you can see, cells F3:H3 and B4:E4 are blank instead of zero. If a row is missing from one data set, the result cells are left empty to alert you to a potential problem. If there’s a valid reason for them to be blank (for example, perhaps you post amortization entries only in December), you can use the Find and Replace dialog to change the blank cells to zero.
The labels in the first column of your data may or may not sort correctly. And any heading in the upper-left cell of your data set (such as cell A1 in our example) isn’t copied to the upper-left cell of the results. If you need to sort the data in your first column, you’ll want to add a heading in that cell before sorting.
The Consolidate command is powerful and flexible. It handles situations where the chart of accounts changed midyear and the earlier reports are missing certain accounts. Consolidate can also be used in these situations:
- You have a single data set with duplicate entries in the first column. You can Consolidate a single range to have those entries consolidated and the results added.
- You can have a single worksheet consolidated from several workbooks. In the past, these workbooks could be closed during the consolidation. After Excel moved from XLS files to XLSX files, however, you have to have each workbook open.
- When your data is stored in multiple workbooks, you can use the Create Links to Source Data box. The resulting report will have group and outline buttons to show you each individual source cell.
For each report created with Consolidate, Excel stores the ranges used. If the underlying data changes, select the top-left corner cell and perform a Consolidate again to update the results. (Since the ranges remain, you won’t have to select the data again. Just open the dialog, check that the references are still there, and click OK.)
Download this month’s workbook to try it out for yourself: SF-2018-05-Consolidation.
SF SAYS
Although Consolidate was designed back when each Excel workbook held a single worksheet, it’s still useful today.
May 2018
