What is a Excel 3D Reference and How Do You Use One?
Excel 3D References can come in handy when you need to perform a calculation on data located in the same cell across multiple worksheets. These type of calculations are sometimes forgotten. There is also some confusion on how to set it up. Below I will attempt to make creating an Excel 3D Reference a bit easier.
I have created a workbook that contains 4 separate worksheets. One for each of three regions and one for the totals. Each worksheet is in the exact same layout. The Product values are located in the same cell across the three worksheets and the total sales number is located in Cell B6 on each of the three worksheets.
Lets assume that we need to create another worksheet that will total up the individual product sales as well as the total across the three regions. This is perfect example of when to use Excels 3D Reference functionality to create a formula that calculates the numbers we need.
Create the Sum() of a 3D Reference
I wil create our calculation on the Totals worksheet. First click into the cell B3 (in our example) then enter =SUM(Region1:Region3!B3)
This tells Excel to take the value in cell B3 on each of the worksheets from Region1 to Region3 and sum them together. It is also worth noting that the order of the worksheet tabs in the bottom of the screen are important. The above formula assumes that Region1 is the first tab and Region3 is the last tab. Excel will take the B3 cell value from any amount of tabs between the first and the last. For example if you drag the Region2 tab to the right of the Totals tab it will NOT be included in the total sum.
Once the total for one of the products is calculated you can autofill the formula down for the other products in the list. The end result will look like the below screen shots.
See below for the end results. It has the sum of each product on the Totals worksheet using the sum of the 3d References.
I hope this helps some of my past, present and future students. Please post any questions or comments below. For more of my Excel Tips and Tricks.