How to Pull Data from Another Sheet in Google Sheets

Keeping raw data on one sheet and summaries on another is a smart way to organize a Google Sheets file. To make that work, you need to pull data from one tab (or a completely different spreadsheet) into another. Google Sheets does this with simple cell references, lookup functions, and IMPORTRANGE for other files. This guide covers each method with copy-and-paste examples.

Quick Answer

To pull one cell from another tab in the same file, type ='Sheet Name'!A1 (for example, ='Sales Data'!D14). To pull from a different spreadsheet, use =IMPORTRANGE("spreadsheet_URL", "Sheet1!A1:C10") and click Allow access the first time.

Method 1: Reference a Cell or Range in Another Tab

1. Click the cell where you want the data.
2. Type =, then click the other sheet’s tab at the bottom and click the cell you want. Press Enter. Sheets writes the reference for you, like ='Sales Data'!D14.
3. Drag the fill handle to copy the formula for neighboring cells.

Rules: use single quotes around sheet names with spaces, and an exclamation point before the cell. Ranges work in functions too: =SUM('Sales Data'!D2:D50).

Method 2: Look Up Matching Data with XLOOKUP or VLOOKUP

To pull a value based on a match, such as a product’s price from a Price List tab:

• XLOOKUP: =XLOOKUP(A2, 'Price List'!A:A, 'Price List'!C:C, "Not found")
• VLOOKUP: =VLOOKUP(A2, 'Price List'!A:C, 3, FALSE)

Use FALSE in VLOOKUP for exact matches.

Method 3: Pull Filtered Rows with FILTER or QUERY

• FILTER: =FILTER('Sales Data'!A2:D, 'Sales Data'!B2:B="East") pulls every East row.
• QUERY: =QUERY('Sales Data'!A1:D, "select A, D where B = 'East' order by D desc", 1) filters, sorts, and chooses columns.

Both update automatically when the source data changes.

Method 4: Pull Data from a Different Spreadsheet (IMPORTRANGE)

1. Open the source spreadsheet and copy its URL from the address bar.
2. In the destination file, click a cell and type: =IMPORTRANGE("paste_URL_here", "Sheet1!A1:D100")
3. Press Enter. When you see #REF!, hover over the cell and click Allow access.

You need at least view access to the source file. Combine IMPORTRANGE with QUERY or FILTER to import only the rows you need: =QUERY(IMPORTRANGE("URL","Sheet1!A1:D"),"select Col1, Col4 where Col2='East'",1).

Tips

• Renaming sheets: references update automatically when you rename a tab in the same file, but IMPORTRANGE ranges in text strings don’t; update them manually.
• Named ranges: define a name with Data > Named ranges and use it in formulas, like =SUM(Q3Sales).
• Open-ended ranges like A2:D include new rows automatically.

Troubleshooting

• #REF! “Unresolved sheet name”: check the sheet name spelling and quotes.
• IMPORTRANGE loading forever: very large ranges are slow; import fewer columns or rows.
• #REF! “Array result was not expanded”: cells below or beside the formula aren’t empty. Clear them.
• #N/A in lookups: the value isn’t found; check for extra spaces with TRIM.

Frequently Asked Questions

Does pulled data update automatically?

Yes. References and IMPORTRANGE refresh when the source changes, though IMPORTRANGE may take a short time.

Can I pull data from Excel files?

Only after converting them to Google Sheets format (File > Save as Google Sheets).

Summary

1. Same file: ='Sheet Name'!A1 or ranges in functions.
2. Matching data: XLOOKUP or VLOOKUP.
3. Filtered rows: FILTER or QUERY.
4. Different file: IMPORTRANGE and Allow access.