This guide will explain how to create a 3-D reference in Google Sheets.

##### Table of Contents

When working on spreadsheet documents with multiple tabs, you may need to refer to the same cell or cell range across several worksheets. These references are useful when you have multiple worksheets that have a consistent structure.

Microsoft Excel has a feature called 3-D references that allows you to reference the same cell range across multiple worksheets. For example, the formula **=SUM(Sales:Marketing!B3)** will return the sum of all the values in cell B3 of each worksheet in the document, starting from the Sales worksheet up to the Marketing worksheet.

While 3-D references are not supported natively in Google Sheets, there are a few workarounds you can use to create 3-D references yourself.

In this guide, we will provide a step-by-step tutorial on how to use 3-D references in a Google Sheets spreadsheet.

**The Anatomy of the REF_3D Function**

The syntax of the `REF_3D`

function is as follows:

=REF_3D(sheet_name, cell_range, total_sheets)

Let’s look at each argument to understand how to use the `REF_3D`

function.

**REF_3D()**is a function that allows users to create 3-D references in Google Sheets.**sheet_name**should be a text value that is common among all the sheets you want to add. For example, if we have a group of sheets labeled Sheet1, Sheet2, and Sheet3, we’ll use the value “Sheet” as the sheet_name argument.**cell_range**refers to the cell range to use for the 3-D reference.- The
**total_sheets**argument controls how many sheets we’ll use for our 3-D reference. - The
`REF_3D`

function only supports sheets that are labeled incrementally like data1, data2, and so on.

**A Real Example of Creating a 3-D Reference in Google Sheets**

Let’s explore a simple example where we can use a 3-D reference in Google Sheets to refer to the same range in multiple sheets.

In the Google Sheets document above, we have three worksheets labeled Sheet1, Sheet2, and Sheet3. Each of these sheets has a similar structure but uses data from different timeframes. For example, each sheet has a formula in cell** B14** that sums up all monthly values for Metric A.

We want to use a 3-D reference to calculate a grand total of all the values found in cell B14 in each of these sheets.

### Using the INDIRECT Function

Since 3-D references do not have a built-in function in Google Sheets that supports it, we can try using the `INDIRECT`

function to retrieve these values.

For example, we can use the following formula to retrieve the value of cell B14 in “Sheet1”:

=INDIRECT(“Sheet1”&!&”B14)

To retrieve the rest of the ranges, we can create a table that includes both the sheet names and the cell range.

Using the table above, we can use the following formula to dynamically generate the appropriate cell reference:

=INDIRECT(A21&”!”&B21)

We can now copy the formula down the table to find the remaining values. Now that we have consolidated all the values from multiple sheets, we can use the `SUM`

function to find the grand total.

### Using a Custom-Named Function

A more convenient workaround to the lack of 3-D reference support in Google Sheets is by setting up a named function. Named functions are a powerful feature in Google Sheets that allows users to create custom functions by defining the input and how the output is calculated.

In this guide, we’ll create a custom function named **REF_3D **that will allow us to create 3-D references. You can either create this function yourself or by importing the function from our sample spreadsheet. We’ll go over a step-by-step guide on how to set up the `REF_3D`

named function in the following section.

Once the named function has been set, we can use the following formula to add up all the Metric A totals:

=SUM(REF_3D(“Sheet”,”B14”,3)))

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to create a 3-D reference in Google Sheets.

**How to Create 3-D Reference in Google Sheets**

- To set up our custom
`REF_3D`

reference, click on**Data**>**Named functions**. - In the
**Named functions**panel, click on the**Add new function**option. - Add the named function details seen below.

After adding the argument placeholders, paste the formula**=REDUCE(,ARRAYFORMULA(sheet_name&SEQUENCE(total_sheets)&”!”&cell_range),LAMBDA(a,v,IFNA(VSTACK(a,INDIRECT(v)))))**.

You should now have a new named function you can use in the current spreadsheet. - Select an empty cell and type the
`REF_3D`

function. - We’ll add the arguments needed to make the
`REF_3D`

function work.

For this example, we want to create a 3-D reference using the range**A1:C1**across three sheets (Sheet1, Sheet2, and Sheet3). - Hit the
**Enter**key to evaluate the function. - To find the total of all these values, we can wrap the
`REF_3D`

function with the`SUM`

function.

To learn more about using multiple sheets in Google Sheets, you can read our post on how to use the built-in `IMPORTDATA`

function.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!