When working with Excel, most Excel users refer to cells/ranges in the same worksheet and sometimes may have to refer to other cells or ranges of cells in one or two other worksheets.
Sometimes, you may have a need to refer to the same cell (say D2) or the same range of cells (say D2:L10) across all the worksheets.
3D Reference is a functionality that allows you to do this easily.
3D Reference in Excel – An Overview
In Excel, a 3D reference refers to a cell or a range of cells across multiple worksheets.
Think of it this way.
Suppose you have three worksheets in your Excel workbook namely Jan, Feb, and Mar. Each worksheet has a cell D2 which contains a certain value.
Now you want to add all the values of cell D2 across all the worksheets.
One way would be to go to each worksheet and add all these separately. If you have a lot of worksheets, this can be quite inconvenient and may cause mistakes.
What if we could reference cell D2 on all worksheets in one go and then tell Excel to sum them up?
This can be done using the 3-D reference, which is a functionality in Excel that allows you to refer to the cell or range of cells across multiple workbooks.
Referencing the same cell across multiple worksheets is known as a 3D reference.
Let’s understand this concept further with a practical example.
Take a look at the dataset shown below. The Excel workbook shown here contains 4 worksheets for the months of January, February, March, and April (named as JAN, FEB, MAR & APR respectively).
These worksheets contain data for the Sale made by each Rep during that month.
Each worksheet also contains the Total Sales for that month in cell B14.
The 5th worksheet (named CALC) will contain all the calculations which will be performed on the sales data from the previous worksheets.
Now our goal is to calculate the sum of the Total Sales made from January to April. Or we may want to calculate the average sale made during these months.
In either case, we will need a 3D reference to refer to cell B14 on all worksheets starting from JAN to APR.
Using a 3D reference will help us perform calculations quite easily. So in this article, we will look at different methods of using 3D reference in Excel.
Method 1: Using 3D Reference in Excel Formulas
In this method, we will take a look at how 3D reference is used in Excel formulas.
In this method, we will be using the SUM function.
As an example, we will be using the same datasets which we saw earlier.
There are 4 worksheets, each containing the Sale made by different Reps during each month from January to April. The goal is to add the Total Sales values from January to April (which are in four different sheets).
The sum will be calculated in cell A1 of the CALC worksheet.
In order to use an Excel formula along with a 3D reference the syntax is as follows:
In the above formula, the argument written inside the brackets is a 3D reference.
- Function_Name is the name of the function.
- First_Sheet is the name of the starting worksheet.
- Last_Sheet is the name of the ending worksheet.
- First_Cell is the starting cell and
- Last_Cell is the ending cell.
In our case, we are simply using a single cell which is B14 across all our worksheets but the formula has the flexibility to use a cell range as well. So in this case the formula will be:
- Select the cell in which you want to calculate the sum. (In this case, it will be cell A1 of the CALC worksheet)
- Enter the formula as shown below.
As you can see, the sum of the Total Sales from cells B14 of all worksheets from JAN to APR has been calculated.
In this method, we have seen how to use 3D reference inside the SUM function.
You can use other functions such as AVERAGE, MAX, etc.
To see a list of all the functions that use 3D reference go to the following link:
Method 2: Creating 3D Reference Named Range for use in Excel Formulas
In this method, we will look at how to name a 3D reference and use it in an Excel formula.
For a change, we will use the AVERAGE function instead of the SUM function which we used earlier.
I am going to use the same datasets which we saw earlier.
There are 4 worksheets, each containing the sales made by different representatives during each month from January to April.
The goal is to calculate the average sale per month made from January to April. The average value will be calculated in cell A1 of the CALC worksheet.
- In the ribbon, click on the Formulas tab.
- In the Defined Names section, click on the Define Name button.
- A window with the title New Name will appear as shown.
- In the Name field, type the name that you want to assign to your 3D reference. In this case, I have named it ‘TotalSale’ as shown.
- From the drop-down menu against Scope, select Workbook. This is an important step because we want this name to be valid in the entire workbook as we would be adding the values across worksheets
- In the Refers to field, type the following:
(This is the 3D reference to cell B14 that we made earlier in Method 1.)
- Click on OK.
- Select the cell in which you want to calculate the average. (In this case, it will be cell A1 of the CALC worksheet)
In order to use an Excel function along with a named 3D reference the formula is as follows:
In the above formula, Function_Name is the name of the function.
3D_Reference_Name is the name that is given to a 3D reference. So in this case, the formula will be:
- Enter the formula as shown below.
As you can see, the average of the Total Sales from cells B14 of all worksheets from JAN to APR has been calculated.
In this method, we have seen how to use a named 3D reference inside the AVERAGE function.
Apart from that, we have seen how to give a name to a 3D reference for further use in Excel formulas.
In this article, we have learned what a 3D reference is in Excel and how to use it in Excel formulas.
Method 1 shows how to use a 3D reference inside an Excel formula using the SUM function as an example.
Method 2 shows how to give a name to a 3D reference and then use the name in an Excel formula.
The AVERAGE function was shown as an example here. Giving a name to your 3D reference is very convenient.
If you are using a lot of functions, then simply typing the reference name instead of the whole 3D reference is much easier.
Other Excel articles you may also like to read:
- How to sum across multiple sheets in Excel using 3D reference
- AutoSum in Excel (Shortcut)
- How to Sum Positive Numbers in Excel – Easy Formula
- SUMPRODUCT vs SUMIFS Function in Excel
- Circular References in Excel – How to Find and Fix it!
- Absolute Reference in Excel (Shortcut)
- How to Subtract Multiple Cells from One Cell in Excel
- What does $ (dollar sign) mean in Excel Formulas?
- How to Sum a Column in Excel?