3D Reference in Excel – What is it and How to Use it?

by

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.

worksheet with April data

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.

Also read: What is Absolute Cell 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:

Function_Name(First_Sheet:Last_Sheet!First_Cell:Last_Cell)

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:

SUM(JAN:APR!B14)
  1. Select the cell in which you want to calculate the sum. (In this case, it will be cell A1 of the CALC worksheet)
  2. Enter the formula as shown below.
enter the 3D reference sum formula in cell A1

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:
https://support.microsoft.com/en-us/office/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6.

Also read: Toggle Between Cell References (Absolute / Relative / Mixed) Shortcut

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.

  1. In the ribbon, click on the Formulas tab.
click the formulas tab in the ribbon
  1. In the Defined Names section, click on the Define Name button.
click on the define name option in the formulas tab
  1. A window with the title New Name will appear as shown.
new name window
  1. 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.
enter the name
  1. 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
select the scope as workbook
  1. In the Refers to field, type the following:
=JAN:APR!B14

(This is the 3D reference to cell B14 that we made earlier in Method 1.)

enter the 3D reference formula in the new name refers to field
  1. Click on OK.
click OK
  1. 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:

Function_Name(3D_Reference_Name)

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:

=AVERAGE(TotalSale)
  1. Enter the formula as shown below.
average formula to sum across worksheets using 3D reference formula

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.

Also read: How to Delete Defined Name in Excel? (Name Manager + VBA)

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “3D Reference in Excel – What is it and How to Use it?”

  1. Hi Scott,
    In the above explanation, you indicated that the sum will be calculated in cell A1 of the CALC worksheet but I only see Cell C available in CALC worksheet. I am still confusing, can you please present more detail !
    Thank you
    Thao

    Reply

Leave a Comment