Lets’s say you have created budgets for various company branches on different worksheets in your excel spreadsheet. However you are wondering how you can automatically generate a consolidated budget for all the branches. Well, no need to worry because by using the consolidate option the task of summarizing data from various worksheets is simplified. Below we explain how that can be achieved.You can download the workbook used in the example to practice here.
Applies To: MS Excel 2003, 2007 and 2010
1. Insert a new worksheet
2. Rename Sheet1, Sheet2 and Sheet3 to Branch A, Branch B and Summary respectively
3. Enter the data given in the example below on the Branch A worksheet.
The same order and location of the data must be maintained on all the worksheets.
4. Enter the data given below on the Branch B worksheet.
5. Select the summary worksheet, click cell B3.
6. Select on Data, consolidate (under data tools).
7. The screen shot given below will be displayed.
8. Select the Edit Ref icon and highlight the data range B3:F10 on the Branch A worksheet.
9. Press Enter & select the Add button.
10. Repeat steps 8 and 9 on the Branch B worksheet.
11. The screen shot below will be displayed.
12. Select all the check boxes and then press enter or click OK.
The data will then be consolidated on the summary worksheet. The details can be displayed by clicking on 2 or the expand button as given above.
To consolidate more Branches or worksheets simply repeat steps 8 & 9 but on the appropriate worksheet.
