This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.
Figure 708 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:
Figure 709 Choose a reference from each worksheet.
9. Click OK. You will have a report showing a superset of all customers and all months.
Figure 710 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.
Figure 711 Choose Go To Special Blanks.
Figure 712 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.