Excel 2024: Consolidate Quarterly Worksheets

April 30, 2024 - by Bill Jelen

There are two ancient consolidation tools in Excel.

To understand them, say that you have three data sets. Each has names down the left side and months across the top. Notice that the names are different, and each data set has a different number of months.

This is the first of three data sets to be consolidated. Names appear in A2:A7. Columns across the top are the months Jan, Feb, and Mar.

Some names in column A are the same as the previous image, but some are new. There are now five months across the top: Apr, May, Jun, Jul, and Aug.

The final data set has months Sep, Oct, Nov, Dec. Again, names in column A. Some are repeated and some are new.

This is a cartoon illustrating what is about to happen. Three cartoon spreadsheets at the top are running towards each other, shouting "Let's Combine Into One Spreadsheet". At the bottom, a larger worksheet (with three faces and six arms) has all of the data from all three worksheets.

Illustration: Cartoon Bob D'Amico

You want to combine these into a single data set.

The first tool is the Consolidate command on the Data tab. Choose a blank section of the workbook before starting the command. Use the RefEdit button to point to each of your data sets and then click Add. In the lower left, choose Top Row and Left Column.

When you click OK, a superset of all three data sets is produced. The first column contains any name in any of the three data sets. Row 1 contains any month in any data set.

This worksheet shows the result of the Consolidation. Names from all three lists appear in A2:A12. Across the top, all 12 months from Jan through Dec appear. Numbers populate the center of the grid. Several cells that should be numeric in the middle are empty instead of 0.

In the above figure, notice three annoyances: Cell A1 is always left blank, the data in A is not sorted, and if a person was missing from a data set, then cells are left empty instead of being filled with 0.

Filling in cell A1 is easy enough. Sorting by name involves using Flash Fill to get the last name in column N. Here is how to fill blank cells with 0:

1. Select all of the cells that should have numbers: B2:M11.

2. Press Ctrl+H to display Find & Replace.

3. Leave the Find What box empty, and type a zero in the Replace With: box.

4. Click Replace All.

The result: a nicely formatted summary report, as shown below.