Consolidating budget allocations for different departments which are on separate worksheets can be a tedious task, unless you automate it. 3-D Reference is a Microsoft® Excel® function that is useful for referencing several worksheets that follow the same pattern and cells and contain the same type of data. In this week’s tip we will show you how you can consolidate budget allocations in different worksheets.
Note: Download the sample workbook to practice this exercise.
Applies to: Microsoft Excel 2007, 2010 and 2013.
The screen shots below will be used to illustrate this exercise. The total budget values are in cell C15 on the three department worksheets
1. With the Summary worksheet open, select the Formulas tab and then select Define Name.
2. In the New Name fields, enter as below and select OK.
Rules when naming ranges
· No Spaces
· No cell addresses
· Cannot begin with a number
· Can use an underscore instead of a space
3. Select cell C15 on the summary worksheet enter =Sum( .
4. Then press F3
5. Select Budget Tools and then OK as per the below screenshot.
6. Press Enter.
7. The total budget allocation for the three departments will be $ 2, 156 ,000 .00
