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.

    sample data

    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.

    create a table including the sheet name and range

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

    =INDIRECT(A21&”!”&B21)
    using the indirect function

    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.

    find sum of all output from the INDIRECT function

    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)))
    using the REF_3D function

    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.
      create a new named function to allow 3-D Reference in Google Sheets
    2. In the Named functions panel, click on the Add new function option.
      select "Add new function"
    3. Add the named function details seen below.
      set up named functionAfter 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.
      type named function to use 3-D Reference in Google Sheets
    5. We’ll add the arguments needed to make the REF_3D function work.
      3-D Reference in Google SheetsFor 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.
      output of REF_3D function
    7. To find the total of all these values, we can wrap the REF_3D function with the SUM function.
      aggregate output of REF_3D 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.

    0 Shares:
    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You May Also Like