How to Reference Another Sheet in Google Sheets

Learn how to reference another sheet in Google Sheets with cell formulas, bring over ranges, and connect separate files with IMPORTRANGE.

Oct 18, 2026
•
11 min read

Contents

Don't Miss the Good Stuff

Get tech news that matters delivered to your inbox.

To reference another sheet in Google Sheets, select a destination cell, enter =Sheet2!A1 with your source tab name and cell address, then press Enter. For a tab name containing spaces or punctuation, use single quotes, as in ='Sales Data'!B4.

That syntax connects tabs inside the same spreadsheet. To pull values from a separate spreadsheet file, use IMPORTRANGE and approve the connection on a computer.

The short version

  • Another tab: Enter =Sheet2!A1, replacing the tab name and cell address.
  • Names with spaces: Put single quotes around the source tab name.
  • Several cells: Use ARRAYFORMULA to return a range into an empty destination area.
  • Another file: Use IMPORTRANGE, then click Allow access to connect the spreadsheets.

Your taskMethodMain requirement
Return one cell on web or mobileDirect sheet referenceSource tab name and cell address
Connect separate filesIMPORTRANGESource access and connection approval
Return a rangeARRAYFORMULAEmpty output area
Return or combine ranges with bracesArray literalPunctuation matching your spreadsheet locale
Append rows from multiple tabsVSTACKRoom below for the combined rows
Place ranges side by sideHSTACKRoom to the right for the combined columns
Total source valuesSUMSource ranges containing the values
Multiply source valuesReferences joined with *Source cells containing the values
Find a matching valueXLOOKUPMatching lookup and result dimensions
Return matching rowsFILTERCondition range aligned with source rows
Keep a source fixed when fillingAbsolute referenceDollar signs before the fixed column and row
Use a readable range nameNamed rangeA name defined in the spreadsheet
Reference a column that grows with its tableTable referenceSource table and column names
Keep results without updatingPaste values onlyCopied source cells or formula results

Reference a cell on another tab

Open the tab where you want the result and select the destination cell. Enter the following formula, replacing Sheet2 with the source tab's name and A1 with the cell you want to return, then press Enter.

=Sheet2!A1

The exclamation point separates the tab name from the cell address. When the name contains spaces or punctuation, put single quotes around the name, leaving the exclamation point outside the quotes.

='Sales Data'!B4

On Android, iPhone, or iPad, open the spreadsheet in the Google Sheets app and select the destination cell. Enter the same formula syntax, substituting your source tab name and address.

Connect a separate spreadsheet with IMPORTRANGE

Use a computer for the initial connection between separate spreadsheet files. Copy the source spreadsheet's URL, open the destination spreadsheet, and select an empty cell.

Enter the following formula, replacing SOURCE_URL with the copied URL and changing the tab name and range to match your source. Keep the URL and range text inside double quotes.

=IMPORTRANGE("SOURCE_URL","'Sales Data'!A1:C10")

Press Enter, then select the formula cell when #REF! requests a connection. Click Allow access to connect the spreadsheets and return the source data.

For a formula controlled by cells, put the source URL in A2 and the range text in B2. Then enter the following formula in another empty cell, press Enter, and complete the same connection approval if prompted.

=IMPORTRANGE(A2,B2)

Include the source tab name in an ordinary range string to choose the intended tab. Without a tab name, IMPORTRANGE imports that range from the source file's first sheet.

Bring over a range of cells

Select an empty destination cell where the returned range should begin. Enter the following formula and press Enter to return cells A1:C10 from the source tab.

=ARRAYFORMULA('Sales Data'!A1:C10)

Leave enough empty cells below and to the right for all returned rows and columns. This example needs an output area 10 rows tall and three columns wide, so choose a starting cell with that much space.

To append one tab's rows beneath another tab's rows, use VSTACK. Enter the following formula in an empty destination cell and press Enter to combine two ranges with the same three columns.

=VSTACK(January!A2:C10,February!A2:C10)

For ranges placed side by side, use HSTACK instead. Keep the combined output area empty, replace the tab names and ranges with your own, and press Enter after entering the formula.

=HSTACK(January!A2:C10,February!A2:C10)

Array literals offer another way to return a range using braces, as in ={'Sales Data'!A1:C10}. To stack equally wide ranges vertically, enter ={January!A2:C10;February!A2:C10}; to place equally tall ranges side by side, enter ={January!A2:C10,February!A2:C10}. Press Enter and keep the output area empty.

Array punctuation and function argument separators vary with your spreadsheet locale. These examples use English function names and US locale separators.

Calculate or find values on another tab

A sheet reference can supply the values inside a calculation. To total a column with SUM from another tab, select the result cell and enter the following formula.

=SUM('Sales Data'!B2:B100)

To total ranges from several tabs, include each source range in the function, as in =SUM(January!B2:B100,February!B2:B100). You can also multiply values in Google Sheets using sheet references, such as ='Sales Data'!B4*'Sales Data'!C4. Press Enter after entering either formula.

When you need the value associated with a matching item, use XLOOKUP. Put the item to find in destination cell A2, select the result cell, and enter this formula.

=XLOOKUP(A2,'Sales Data'!A2:A100,'Sales Data'!D2:D100)

This searches the source range A2:A100 and returns the corresponding value from D2:D100. Keep the lookup and result ranges the same dimensions; for example, avoid pairing A2:A100 with D2:D50. Press Enter to return the result.

For several matching rows, use FILTER instead of a single matching result. Select an empty destination cell and enter the following formula to return rows from A2:D100 where the corresponding cell in column B equals Paid.

=FILTER('Sales Data'!A2:D100,'Sales Data'!B2:B100="Paid")

Keep the condition range aligned with the rows you want returned, and leave the output area empty. Press Enter to return the matching rows.

Keep a reference fixed when filling formulas

Use dollar signs when a formula should keep pointing to a fixed source cell as you fill it into other cells. To keep both the source column and row fixed, enter this formula and press Enter.

='Sales Data'!$B$4

While editing a reference, press F4 on Windows or Fn + F4 on Mac to cycle through absolute and relative references. Keyboard shortcuts depend on your keyboard, operating system, and language, so you can also type the dollar signs directly.

To fill a formula down, select its cell along with the destination cells below it. Press Ctrl + D on Windows or ChromeOS, or Command + D on Mac.

Use named ranges and table columns

A named range lets you refer to source cells with a readable name. Select the source cells, open Data > Named ranges, enter a name such as sales_total, and click Done.

On the destination tab, you can use that name in a formula without repeating the tab name and cell addresses. Enter the following formula and press Enter to total the named range.

=SUM(sales_total)

You can also make a table in Google Sheets by selecting the source data and choosing Format > Convert to table. Use the resulting table name and column heading in your destination formula, replacing the example names below with those in your spreadsheet, then press Enter.

=SUM(DeptSales[Sales Amount])

Table references expand or contract when the table's data changes. To return the column's values rather than a total, enter =ARRAYFORMULA(DeptSales[Sales Amount]), press Enter, and keep the output cells empty.

Fix reference and import problems

For a reference within the same spreadsheet, first compare the formula's tab name and cell address with the intended source. Put single quotes around names containing spaces or punctuation, and make sure the exclamation point sits between the tab name and address.

When a formula returns several cells, check that its destination area is empty and large enough. For XLOOKUP, align the lookup and result dimensions; for FILTER, align the condition range with the returned rows.

A new IMPORTRANGE connection needs access to the source, permission to edit the destination, and connection approval. If you cannot open the source, ask its owner for access first, then return to the destination formula and click Allow access. Work or school sharing policies can restrict access.

Imports require internet access and can refresh with a delay. Each request has a 10 MB received data limit, so reduce the imported range when you only need a smaller set of cells.

Source copy and download restrictions can block new imports while previously established formulas continue working. INDIRECT cannot replace IMPORTRANGE for a separate file, so keep using IMPORTRANGE for that connection.

Keep the current values without a live formula

To keep a static result, select the cells containing the reference formulas and copy them. Select the destination, then press Ctrl + Shift + V on Windows or ChromeOS, or Command + Shift + V on Mac to paste values only.

Pasting values over the original formula cells replaces the formulas with their current results. Use this when you want to stop those results from updating through the formulas.

Frequently Asked Questions

Can you make a clickable link to another tab instead of returning its values?

Yes, you can create a navigation link to a tab or range. Select a cell, open Insert > Link > Select a range of cells to link, and select the target tab and range. Click Ok, then Apply; the link opens the source without importing its values.

Can a dropdown use options from another tab?

Yes, you can use another tab's cells as the dropdown source. On a computer, select the destination cells and open Data > Data validation > Add rule. Under Criteria, choose Dropdown from a range, enter a source such as 'Sales Data'!A2:A100, and click Done.

How do you reference another tab in conditional formatting?

Use INDIRECT for the reference to another tab in your rule. Select the destination cells, open Format > Conditional formatting, and select Custom formula is under Format cells if. In Value or formula, enter =A1>INDIRECT("'Sales Data'!B4"), choose the formatting, and click Done.

Can other destination editors import more data from an authorized source?

Yes, any editor of an authorized destination spreadsheet can import from any part of the connected source. Google says the connection remains effective until the person who granted it is removed from the source. Removing that person also removes their access to the source file.

Does copying an entire tab keep it synchronized with the original file?

No, copying a tab creates a snapshot. Open the arrow beside the source tab, choose Copy to > Existing spreadsheet, and select the destination spreadsheet. You can then reference the copied tab locally with a formula such as =SheetName!A1, but changes in the original file will not update that copy.

Share