How to Pull Data From Another Sheet in Google Sheets

To pull data from another sheet in Google Sheets, use a tab reference when the data is in the same file and IMPORTRANGE when it is in a different file. Both stay linked, so the result updates when the source changes.

Another tab in the same file:

=Sales!B2

Tab name with spaces or special characters (use single quotes):

='Q1 Sales'!B2

A different Google Sheets file:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-file-id/edit","Sales!A1:C10")

A cell reference points to a cell so its value shows up somewhere else. IMPORTRANGE is a Google Sheets function that brings a block of cells from a different spreadsheet file into the current one.

Prefer clicking? Type = in the destination cell, click the other tab, click the cell you want, and press Enter. Sheets writes the reference for you.

Which method fits your situation

Start here, then jump to the section you need.

Every formula in this guide uses commas between arguments, the US default. Some regional settings use semicolons instead. More on that in the errors section.

Pull a cell or range from another tab in the same file

Say your file has a tab named Sales with this data:

Single cell

In any other tab, enter:

=Sales!B2

The result is East. The pattern is the tab name, an exclamation mark, then the cell address. If you change B2 on the Sales tab, the result changes too.

A range that spills

Enter this in one cell of another tab:

=Sales!A1:C4

It returns the header and the first three data rows: 3 columns by 4 rows, starting in the cell where you typed it. Google Sheets calls this a spill: one formula fills neighboring cells automatically.

The spill needs empty room. If any cell in the target area already contains something, you get a #REF! error saying the array result was not expanded because it would overwrite data. Clear those cells and the result appears.

You can also reference a whole column, such as =Sales!A:A. It is convenient because new rows are included automatically, but it spills down the entire column (header and blanks included), so nothing can sit below it. A bounded open range like Sales!A2:A starts at row 2 and skips the header.

Tab names with spaces

If the name has spaces or characters like hyphens or ampersands, wrap it in single quotes:

='Q1 Sales'!B2

Quotes are harmless on simple names, so when in doubt, add them. Use straight single quotes, not curly ones pasted from a document.

Pull data from a different Google Sheets file with IMPORTRANGE

Use IMPORTRANGE when the data lives in a separate file.

1. Open the source file and copy its URL from the address bar.
2. In the destination file, type the formula with the URL and a range in quotes:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-file-id/edit","Sales!A1:C10")

3. Press Enter. The cell shows #REF! with a prompt to connect the sheets.
4. Click Allow access. The data fills in.

How it works

• spreadsheet_url: the full URL of the source file, or just its ID (the long string after /d/). Put it in quotes.
• range_string: the sheet name and range together, like "Sales!A1:C10". If you leave out the sheet name, Sheets uses the source file's first tab.

First-time access prompt

You approve access once per pair of files. After that, other IMPORTRANGE formulas between the same two files work without another prompt.

According to Google's IMPORTRANGE help page, you need permission to view the source file, and the data you import is limited to 10MB per request. The same page notes that IMPORTRANGE depends on a connection to the source and does not work with volatile functions like NOW, RAND, and RANDBETWEEN in the imported data (TODAY is an exception). Check that page for current details, since Google can change these rules.

Imported data refreshes on its own when the source changes. A short delay is normal, so do not expect instant updates.

Using IMPORTRANGE with a URL stored in a cell

Hardcoded URLs are tedious to edit. Put the URL in A1 and the range in A2, then reference them:

=IMPORTRANGE(A1, A2)

Now you can swap files or ranges by changing two cells, which is handy for templates you hand to teammates.

Look up a value from another sheet

A lookup finds a row by a key, such as a product ID, and returns a value from that row. Suppose a Prices tab holds:

On an order tab, cell A2 contains P-200. This formula:

=VLOOKUP(A2,Prices!A:C,3,FALSE)

returns 4.25 (format the cell as currency to show $4.25). The arguments are the key to find, the range to search (the key must be in its first column), the column number to return (3 is Price), and FALSE to require an exact match.

For a lookup in another file, put IMPORTRANGE where the range goes:

=VLOOKUP(A2,IMPORTRANGE("url","Prices!A:C"),3,FALSE)

If your key sits to the right of the value you want, see how to do a VLOOKUP to the left. To decide between the two main lookup functions, read XLOOKUP vs VLOOKUP. XLOOKUP is available in Google Sheets, so =XLOOKUP(A2,Prices!A:A,Prices!C:C) also returns 4.25 here.

Pull only the rows you want

FILTER

FILTER returns the rows that meet a condition. Using the Sales data above:

=FILTER(Sales!A2:C, Sales!C2:C>100)

The first argument is the data to return. The second is a test, and both ranges must start at the same row and cover the same number of rows. FILTER spills, so keep the cells below and to the right empty. If nothing matches, you get #N/A; wrap it as =IFERROR(FILTER(...),"No matches") for a friendlier message.

QUERY

QUERY lets you choose columns and conditions with a short, SQL-like string:

=QUERY(Sales!A1:C,"select A, C where C > 100",1)

This returns the headers Product and Revenue, then Notebooks 150 and Staplers 220. The final 1 tells QUERY the data has one header row.

With imported data, QUERY cannot use column letters. Refer to columns as Col1, Col2, Col3, and so on:

=QUERY(IMPORTRANGE("url","Sales!A1:C"),"select Col1, Col3 where Col3 > 100",1)

The query string must be in quotes, and text values inside it need single quotes, like where Col2 = 'East'.

Make the sheet name dynamic with INDIRECT

INDIRECT turns text into a reference. If cell A1 holds Q1 Sales, this pulls B2 from whichever tab A1 names:

=INDIRECT("'"&A1&"'!B2")

Pair it with a dropdown in A1 and one formula can switch between monthly or quarterly tabs. The "'" pieces add the single quotes, so names with spaces work.

Know the trade-offs:

• INDIRECT is volatile, meaning it recalculates often, which can slow big sheets.
• It breaks silently if you rename a tab. A normal reference like =Sales!B2 updates itself on rename; INDIRECT's text does not.
• It cannot reach another file by itself. For that, use IMPORTRANGE.

Common errors and fixes

Two more tips. A missing quote around a tab name with spaces is the most common cause of a parse error. And if you don't have access to a file you are importing, ask the owner for at least view access, because IMPORTRANGE cannot get around permissions.

To change separators, go to File > Settings and check the locale. A US locale uses commas; many European locales use semicolons.

Excel vs Google Sheets note

Excel uses the same Sheet1!A1 and 'Q1 Sales'!B2 style for tabs in the same workbook. For another workbook, Excel writes references like '[Budget.xlsx]Sheet1'!A1, which work reliably while both files are available.

IMPORTRANGE has no direct Excel equivalent. For recurring imports, Power Query (Data > Get Data) is usually the better choice because it can combine, clean, and refresh data from other files.

FILTER and XLOOKUP require Microsoft 365 or Excel 2021 or later. In older versions, use INDEX/MATCH for lookups, or a helper column with a flag and a filter or pivot table to get matching rows. VLOOKUP works in every version.

When a formula isn't the right tool

Formulas are great for small to medium needs, but they have limits.

• Large data or scheduled refreshes: Google points to Apps Script or Connected Sheets for bigger jobs. Connected Sheets links a sheet to data in BigQuery, and Apps Script can automate copying data on a schedule.
• Many files to merge: Dozens of IMPORTRANGE formulas get fragile and slow. Consider consolidating into one file, or move the data to a database and report from there.
• Sensitive data: IMPORTRANGE passes data into the destination file, so anyone who can open that file can see what you imported. Share the destination carefully, and do not import more than people need.

Quick checklist

• Same file: =Sheet!A1, with single quotes if the name has spaces.
• Different file: =IMPORTRANGE("url","Sheet!A1:C10"), then click Allow access.
• Lookup by key: VLOOKUP or XLOOKUP, with IMPORTRANGE inside for another file.
• Matching rows only: FILTER or QUERY (Col1, Col2 for imported data).
• Keep spill areas empty to avoid #REF!.
• Use INDIRECT only when the tab name truly must change.