# How to Create 3-D Reference in Google Sheets

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

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

1. To set up our custom `REF_3D` reference, click on Data > Named functions.
2. In the Named functions panel, click on the Add new function option.
3. 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.
4. Select an empty cell and type the `REF_3D` function.
5. 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).
6. Hit the Enter key to evaluate the function.
7. 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!

### Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

## How to Replace Blank Cells with Zero in Google Sheets

The Find and Replace function is useful when you need to replace blank cells in your Google Sheets…

## How to Use CONCATENATE Function in Google Sheets

The CONCATENATE function in Google Sheets is useful when you need to combine the text of two or…

The HYPERLINK function in Google Sheets is useful if you want to create a clickable hyperlink with a…

## How to Use COUNTBLANK Function in Google Sheets

The COUNTBLANK function in Google Sheets is useful if you want to get the number of empty cells…

## How to Highlight the Max Value in a Row in Google Sheets

To highlight the max value in a row in Google Sheets is useful to make the cell with…

## How to Restrict Data in Google Sheets with Data Validation

Learning how to restrict data in Google Sheets with data validation is useful to prevent others from entering…