Question: I am in charge of finance for a large company with various branches throughout the country. The branch accountants send their budgets quarterly to the Head Office. I then present a consolidated budget to the company board. Currently I have to recreate the consolidated budget for all the branches. This obviously takes time and it is prone to mistakes being made. Can you help me to automate the task of budget consolidation?
Answer: Yes, with the Data Consolidation command
Why: To automate the task of summarizing and consolidating data from multiple worksheets/workbooks into one worksheet.
Applies To: Excel (2010, 2007, 2003, XP, 2000)
1. Insert a new worksheet
2. Rename Sheet 1, Sheet 2 and Sheet 3 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. For Excel 2010 and 2007; click on Data, consolidate (under data tools)
7. For Excel 2003, XP& 2000; click on the data menu, consolidate
8. The screen shot given below will be displayed
9. Select the red arrow under references & highlight the data range B3:F10 on the Branch A worksheet.
10. Press Enter & click the Add button
11. Repeat steps 9 and 10 on the Branch B worksheet
12. The screen shot below will be displayed
13. Refer to the screen shot above (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 plus signs as given above.
To consolidate more Branches or worksheets simply repeat steps 9 & 10 but on the appropriate worksheet.
