Wow that was a quick week it is Friday again and that means another #formulafriday
You can download the example workbook for this Excel tip below.
[ddownload id=”9577″]
Today let’s look at 3D References ……do we need glasses?…..no not quite…it is a simple formula which refers to the same cell or range of cells on multiple sheets. It is really useful if you have data in the same patterns over multiple worksheets, with the same type of data recorded on all sheets such as budget figures you are consolidating.
Let’s look at an example and add up the stationary budgets for 3 company departments – Sales, Logistics and Accounts. The budgets currently are on 3 worksheets as below.
Each department has the exact same data structure in each of their individual worksheets. Lets add up or SUM the Budgets for 2014 through to 2016 on the Budget Consolidation worksheet. The formula looks like this.
To enter the 3D formula follow these steps
- Click the cells where you want to enter the function, in this case is is cell C6 on our consolidation page
- Hit =and begin your function example- SUM – AVERAGE – COUNT, in our example we are using SUM
- Click the tab of the first worksheet you want to include in your function
- Hold down the SHIFT key and select the last worksheet tab you want in your function
- Select the cell or range of cells you want to reference
- Finish the formula by hitting enter
So what happens if we add, delete, or move a worksheet tab?.
If you need to ADD in another worksheet or copy a worksheet between the two end sheets ( Sales and Logistics) then the new worksheet will be automatically added into the formula. For example let’s add in another department, say IT.
It is automatically included in the calculation in the consolidation sheet and the value is changed.
If you DELETE one of the worksheets between the two end sheets referenced in our formula then the calculation is again automatically adjusted and the references removed from the function.
If you MOVE a worksheet outside of the referenced worksheet range then the calculation is again automaticallt adjusted and the rferenced removed from the function.
ENDPOINTS – If you move an endpoint (end sheet in the reference) then Excel will adjust the calculation to include the new worksheets between them, the exception to this is if you reverse the end points. If you do this then the 3D reference changes the endpoint. For example if we reference SALES:LOGISTICS as in our example above. If you move sheet SALES after LOGISTICS then the formula will adjust from IT:LOGISTICS. If you move LOGISTICS in front of SALES then the formua will adjust SALES:ACCOUNTS,
Have you used 3D references before?. Did they save you time? Share in comments below.
- Create A Micro Chart In Excel
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
- Create A Timestamp In Excel With Formulas
- Use TRIM for a speedy data clean up
