Consolidate Option

You have created budgets for various 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.

Applies To: Excel 2010, 2007, 2003:

  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.
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email [email protected] and yours could be the next Tip of the Week.

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations