Merge Worksheets Using Consolidate

March 15, 2018 - by Bill Jelen

Excel offers a Consolidate command on the Data tab. This ancient command lets you consolidate data from multiple worksheets or workbooks, even if the data is a different shape.

I suppose it is quaint to talk about Consolidate when Power Query is far more flexible at combining sheets. But there are times when a quick Consolidate command will do the job without firing up Power Query.

Consolidate is designed for a data set with one row of headings across the top and one column of labels on the left. All of the other cells in the table should be numeric.

Let's say that you wanted to consolidate these two data sets. Normally, they would be on different worksheets or different workbooks, but I've put them side by side here. Note that it is okay if items are missing from any data set. The important part is that both data sets have items along the left and months across the top.

Find a blank section of the worksheet where the results will appear. Select the top-left corner cell. Choose Data, Consolidate.

In the Consolidate dialog:

• Note that you can choose from 11 functions in the Function drop down. I've never chosen anything other than Sum
• In the lower left, choose Top Row and Left Column
• Click in the Reference box. Highlight the first range to be conolidated (A1:E14 in this case). Click the Add button to finish specifying the first range.
• Click in the reference box again. Highlight the second range go be consolidated (G1:M18).
• If you have more ranges, you would click Add and then specify the next range. When you are specifying the last range, you don't have to click Add. Simply click OK.

The results are shown below. (I've narrowed the columns widths to allow them to fit on your screen.) I am always slightly annoyed at the following:

• Excel does not sort the results.
• The top-left cell is always left blank, even if the original tables had a label there.
• If there is not a data point for a cell, it is left blank instead of zero.

To fix the results, I will type a heading in the top-left corner. Select the numbers. Ctrl + H to Replace. Leave Find What blank. Type 0 in Replace With. Click Replace All. Optionally, sort by the first column.

Note that each workbook can remember the settings for one consolidation. If you have to update the consolidation, you can select the same or a new top-left cell and run the Consolidate command again.

Thoracic Thursday - my favorite heart-pounding features in Excel.

Title Photo: John Jennings / Unsplash