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:
3. To consolidate branch B income statement data:
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: