Excel: Combine Four Quarterly Reports

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.

1. 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:

   

2. Choose a reference from each worksheet.
   9. Click OK. You will have a report showing a superset of all customers and all months.

   

3. 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. To replace the blanks with zeroes, use Home, Find & Select, Go To Special. Choose Blanks in the Go To Special dialog. Click OK. Type a zero and press Ctrl+Enter to fill the blanks with zero.

   

4. Choose Go To Special Blanks.

   

5. Type a zero and press Ctrl+Enter.
   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.