Combine Four Quarterly Reports

October 24, 2022 - by Bill Jelen

Problem: I have four worksheets for Q1 through Q4. Each worksheet has months across the top and customers down the side. The months and customers are not the same. I want to combine them into a single report.

Here is a more robust example of Consolidate. You have four worksheets named Q1, Q2, Q3, and Q4. On each sheet, a list of customers in A2:A??. Across Row 1, you have Customer followed by Jan, Feb, Mar in Q1. Customer followed by Apr, May, Jun. The correct months are in B1:D1 for sheets Q3 and Q4. The important part here is a single column of labels on each sheet and a single row of labels on each sheet. — Figure 767. Combine these four lists into a yearly report.

Strategy: The Consolidate command needs the row headings to be of the similar type, but not the exact same values. Consolidate will work here because column A in each worksheet contains customers (although not the same customers). Row 1 contains months (although not the same months).

1. Add a new worksheet named Year.
2. Select cell A1 on the Year worksheet.
3. Choose Data, Consolidate.
4. Click the Collapse button at the right end of the Reference box.
5. Browse to Q1. Select A1:D7. Click the icon at the right edge of the Reference box to return to the Consolidate dialog.
6. Click the Add button in the Consolidate dialog.
7. Repeat steps 4-6 for Q2, Q3, and Q4.
8. Ensure Top Row and Left column are checked in the lower left corner of the Consolidate dialog. The dialog should look like this:

The All References box in the Consolidate dialog point to the proper ranges on Q1, Q2, Q3, and Q4. — Figure 768. Choose a reference from each worksheet.

9. Click OK. You will have a report showing a superset of all customers and all months.

The amazing results of Consolidate have customers in A, then 12 months going across: Jan, Feb, Mar, ... , Dec. Two annoyances remain: cell A1 does not say "Customer". And, if a customer did not appear in a given quarter, their sales amount is empty instead of zero. — Figure 769. Excel consolidates the four quarters to one report.

10. Type Customer and press Enter to fill in the blank heading in A1.
11. Many empty cells appear in the consolidated data. This means that the customer did not have a record in that quarter. Select B2:M15. Press Ctrl+H to display Find & Replace. Leave the top box blank. Type 0 in the lower box. Click Replace All.
12. Sort the data by customer.

Additional Details: Make sure to add Q1 before Q2 and so on. The order of the months in row follows the order that the references were added.

Gotcha: There is a Browse button in the Consolidate dialog. This means that you can combine worksheets from different workbooks. However, the Browse button requires you to type the worksheet name and used range from memory without seeing the workbook. It would be much easier to open all four workbooks before using Consolidate. You can use View, Switch Windows to move to another workbook while entering a reference.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash