How to Get Data from Different Sheets in Google Sheets

Jumping between Google Sheets tabs to consolidate your data can quickly turn into a messy copy-paste nightmare. It's a common bottleneck when you're trying to build a master dashboard or a summary report. This guide will show you exactly how to pull data from other tabs within the same spreadsheet and from entirely different spreadsheet files, all using a few powerful built-in functions. You'll soon transform your scattered sheets into a single, connected source of truth.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Referencing Data from Another Sheet in the Same Google Spreadsheet

First, let's tackle the most common scenario: pulling data from one sheet (or tab) to another within the same Google Sheets file. Let's say you have one sheet called "Q1 Sales" and you want to summarize some of that data on another sheet called "Annual Dashboard." You have two main ways to do this.

Method 1: The Simple Cell Reference

This is the simplest way to reference a specific cell or a range of cells from another sheet. It's perfect for pulling over single values (like a total sales figure) or creating a mirror of a dataset.

The basic syntax is: 'Sheet Name'!CellReference

The single quotes around the sheet name are important, especially if your sheet name has spaces or special characters (e.g., 'Q1 Sales'). If your sheet is named something simple like Sales, you can get away with Sales!A1, but it's a good habit to always use the quotes.

Example: Referencing a single cell

Imagine your total Q1 Sales are in cell F50 on the 'Q1 Sales' sheet. To bring that value into your "Annual Dashboard" sheet:

1. Click on the cell in your "Annual Dashboard" where you want the total to appear.
2. Type the equals sign =.
3. Type out the formula: 'Q1 Sales'!F50
4. Press Enter. That's it!

Now, if the value in 'Q1 Sales'!F50 ever changes, it will automatically update on your dashboard.

Example: Referencing a range of cells

If you want to copy over a whole table of data, you can reference a range. For example, to pull the entire data block from A2 to D20 on the 'Q1 Sales' sheet:

1. Click into cell A1 on your destination sheet.
2. Type the formula: ='Q1 Sales'!A2:D20
3. Press Enter. The entire table will populate automatically.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 2: Combining Data with Array Literals ({})

What if you want to stack data from multiple sheets on top of each other? For example, you have separate sheets for "Q1 Sales" and "Q2 Sales" with identical column structures (Date, Product, Rep, Amount), and you want to create one master list.

This is where Array Literals, also known as Curly Braces {}, come in handy. You separate data from different sheets with a semicolon , to stack them vertically.

Example: Stacking data from two sheets

Let's compile sales data from sheets 'Q1 Sales' and 'Q2 Sales'. We'll assume the data in both sheets runs from column A to D, and we want to skip the header row.

1. In a new sheet called "Master Sales," click into cell A1.
2. Type this formula:

={'Q1 Sales'!A2:D, 'Q2 Sales'!A2:D}

1. Press Enter.

Google Sheets will now fetch all the data from row 2 onwards from 'Q1 Sales', and directly underneath it, it will append all the data from row 2 onwards from 'Q2 Sales'.

A quick tip on combining arrays:

• Use a semicolon (,) to stack data ranges on top of each other (vertically). Make sure each range has the same number of columns.
• Use a comma (,) to place data ranges side-by-side (horizontally). Make sure each range has the same number of rows.

Pulling Data from a Completely Different Google Spreadsheet

Combining data inside one file is great, but the real power comes when you connect different spreadsheets. You might have one spreadsheet for your marketing ad spend and another for your Shopify sales data. To connect them, you need to use a specific, incredibly useful function.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

The Mighty IMPORTRANGE Function

The IMPORTRANGE function allows you to import a range of cells from one spreadsheet into another. It acts as a live link – when the source data changes, the imported data updates too.

The syntax for this function is:

IMPORTRANGE("spreadsheet_url", "range_string")

Let's break down those two arguments:

• spreadsheet_url: This is the full URL of the Google Sheet you want to pull data from. Just copy it from your browser's address bar. It's best to use the full URL in quotation marks.
• range_string: This tells Google Sheets which sheet and which cells to grab, using the same "Sheet Name!Range" format we used earlier. For example: "Sales Data!A1:F100". The sheet name and range must also be inside quotation marks.

Step-by-Step Example

Let's say we want to pull a summary report from our "Master Ad Spend" spreadsheet into our main "Company KPIs" spreadsheet.

1. Get the Source URL: Open the "Master Ad Spend" spreadsheet and copy its full URL. It will look something like this: https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ_12345/edit
2. Identify the Range: In the "Master Ad Spend" sheet, let's say the data we want is on a sheet named "Month End Summary" in the range A2:C13.
3. Write the formula: Go to your "Company KPIs" spreadsheet, click an empty cell, and type the full formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ_12345/edit", "'Month End Summary'!A2:C13")

1. Allow Access: The very first time you connect two spreadsheets, you'll see a #REF! error with a blue button that says "Allow access." Click this. This is a one-time security step to confirm you're intentionally linking these two files. Once you grant permission, the data will appear and will remain linked.

Common IMPORTRANGE Errors and How to Fix Them

• #REF! Error: If you've already tried allowing access, this usually means the URL or range string is incorrect. Double-check for typos. Make sure you included the sheet name correctly, especially if it has spaces (e.g., 'Data Sheet'!A1:B).
• You need to connect these sheets. Allow access.: This is what you'll see on the first try. Just hover over the cell and click the "Allow access" button. If the button doesn't appear, try re-editing the cell and pressing enter again.
• Slow Loading Times: IMPORTRANGE is a powerful function, but importing tens of thousands of rows can make your spreadsheet slow down. If you're working with massive amounts of data, try to only import the specific columns and rows you actually need, rather than entire sheets.

Next-Level Techniques: Combining IMPORTRANGE with Other Functions

Once you've mastered IMPORTRANGE, you can unlock serious spreadsheet power by nesting it inside other functions. This lets you combine and analyze data from different files as if it were all in one place.

Combining Data from Multiple Spreadsheets

Just like we did with our Q1 and Q2 sheets, you can use array literals {} to stack data imported from completely different spreadsheets.

Example: Consolidate data from Facebook Ads and Google Ads spreadsheets.

={
IMPORTRANGE("URL_for_Facebook_Ads_Sheet", "Campaigns!A2:E"),
IMPORTRANGE("URL_for_Google_Ads_Sheet", "Data!A2:E")
}

This formula pulls data from the "Campaigns" sheet of your Facebook Ads report, and right underneath it, stacks the data from the "Data" sheet of your Google Ads report. This is extremely useful for calculating cross-platform metrics like total spend or total conversions.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Filtering Imported Data with QUERY

The QUERY function is arguably the most powerful in Google Sheets. It allows you to use SQL-like commands to filter, sort, and aggregate your data. When you combine QUERY with IMPORTRANGE, you can pull only the specific data you need from another spreadsheet.

The structure looks like this: QUERY(IMPORTRANGE(...), "your query text")

Example: Pulling sales figures for only one sales representative.

Maybe your "Master Sales Data" spreadsheet has sales from everyone in your company, but you want to build a dashboard just for a rep named Maria. Her name is in column D.

=QUERY(
IMPORTRANGE("URL_for_Master_Sales_Data", "'All Sales'!A1:F"), 
"SELECT * WHERE Col4 = 'Maria'", 1
)

Let's break that down:

• The IMPORTRANGE part pulls in the entire dataset from A1:F of the 'All Sales' sheet.
• The QUERY then acts on that imported data:

This is much more efficient than importing the entire 10,000-row sales log and then trying to filter it afterward.

Final Thoughts

You now know how to get data from different sheets in Google Sheets, whether it's another tab in the same file or a completely separate spreadsheet. By using simple cell references, array literals, and the versatile IMPORTRANGE function, you can stop manually copying and pasting for good. Layering on functions like QUERY further transforms your spreadsheets into powerful, connected, automated reports.

While mastering these formulas is a huge step up, we know it can still feel like you're spending more time manually pulling data than analyzing it. At Graphed, we created a way to skip the spreadsheet wrangling entirely. Instead of struggling with formulas to connect platforms like Shopify, Google Analytics, and Facebook Ads, we let you connect them directly with a few clicks. You can then use plain English to ask for the exact chart or dashboard you need, and our AI builds it in seconds, giving you back hours of your week.