To summarize and report results from data on separate worksheets, you can consolidate the data from each separate worksheet into one worksheet (or master worksheet). The worksheets you consolidate can be in the same workbook as the master worksheet or in other workbooks.

When you consolidate data in one worksheet, you can easily update and combine it. For example, if you have a worksheet of expense figures for each of your regional offices, you might use data consolidation to combine these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the whole business. In today's tip we are going to consolidate income statements using data from branch A and branch B.

Note: You are welcome to download the workbook to practice this exercise Applies To: Microsoft Excel 2010 and 2013

1. Select the consolidation worksheet.

2. To consolidate branch A income statement data:

• Select cell A3.
• On the Data tab, in the Data Tools group, click Consolidate.

• In the Function box, click SUM.
• In the Reference box, click the Collapse Dialog button (edit reference icon) and select the branch A worksheet.
• Select the data range A3:B27.

• Click the Collapse Dialog button
• In the Consolidate dialog box, click Add .The branch A income statement data will be added to the reference list.

3.  To consolidate branch B income statement data:

• In the Reference box, click the Collapse Dialog button and select the February income statement
• Select the data range A3:B27.
• Click the Collapse Dialog button
• In the Consolidate dialog box, click Add. The branch B income statement data will be added to the reference list.

4. Select the check boxes under Use labels in, that indicate where the labels are located in the source ranges: Top row and Left column.

5. Tick the create links to source data check box.

6. Click OK.

As you can see a summary income statement has been created for the both branches. You can therefore analyse the financial performance of the organisation at a glance. Below we'll explain a few ways to consolidate data:

• Consolidate by position: Use this method when the data from multiple source areas are arranged in the same order and uses the same row and column labels. For example, when you have a series of expense worksheets that are created from the same template.
• Consolidate by category: Use this method when the data from multiple source areas is arranged differently, but the same row and column labels are used. For example, you can use this method when you have a series of inventory worksheets for each month that use the same layout, but each worksheet contains different items or a different number of items