PreviousHow to Filter Imported Data in Google Sheets Next

A practical guide to pulling and refining live data from external spreadsheets

📌 Introduction

Google Sheets is a powerful cloud-based tool for data collaboration. When working across multiple spreadsheets, you may want to bring data from one sheet into another — but not all of it.

In this guide, you'll learn how to:

Use IMPORTRANGE to import data from other spreadsheets
Use QUERY to filter and extract only what you need
Combine both functions to maintain clean and relevant data views

This is especially helpful for reporting, dashboards, inventory tracking, and team management where you only want to show filtered data to specific users.

🔍 Syntax Overview

IMPORTRANGE fetches data from a different Google Sheet:

=IMPORTRANGE("spreadsheet_url", "range_string")

• "spreadsheet_url": The full URL of the source Google Sheet
• "range_string": The tab and range of cells (e.g., "Sheet1!A1:C100")

QUERY allows SQL-style filtering and sorting on a range:

=QUERY(data, "SELECT Col1, Col2 WHERE Col3 = 'Active'")

• data: A range or function (like IMPORTRANGE)
• "SELECT": Chooses the columns to show
• WHERE: Filters based on a condition (e.g., column value)

🧪 Example Formula

Suppose you manage team data across spreadsheets, and only want to show rows where a status column contains “Approved.” Here’s how you do it:


=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/your-sheet-id", "Sheet1!A1:D100"),
       "SELECT Col1, Col2, Col4 WHERE Col3 = 'Approved'")

What this does:

IMPORTRANGE connects to the external sheet and pulls the data
QUERY filters rows where the 3rd column has the word “Approved”
SELECT chooses which columns you want to display (Col1, Col2, Col4)

Tip: Always “Allow Access” when using IMPORTRANGE for the first time to connect spreadsheets.

Create Real-Time Multi-Sheet Reports in Google Sheets

A hands-on guide to building live reports from multiple spreadsheets using IMPORTRANGE & QUERY

📌 Introduction

As your data grows, managing information across several spreadsheets becomes challenging. Instead of copying data from different files manually, Google Sheets allows you to connect multiple spreadsheets and create real-time dashboards or reports.

In this guide, you’ll learn how to:

Use IMPORTRANGE to bring data from multiple sheets into one
Apply the QUERY function to filter and organize the imported data
Combine both to build dynamic, real-time multi-sheet reports

This is perfect for managing team inputs, department data, or collecting updates from several sources into one master view.

🔍 Syntax Overview

IMPORTRANGE Function:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100")

• "spreadsheet_url": The full link to the other spreadsheet
• "Sheet1!A1:D100": The tab and range to pull data from

QUERY with IMPORTRANGE:

=QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100"), 
       "SELECT Col1, Col2 WHERE Col3 = 'Open'")

• This lets you import and filter data at the same time
• Col1, Col2... are placeholders — QUERY treats the imported range as unnamed columns

🧪 Example Formula

Imagine you have different teams tracking tasks in their own spreadsheets. Each has a sheet named Sheet1 with the following columns:

Column A: Task Name
Column B: Owner
Column C: Status

You want to create a real-time master sheet that lists all Open tasks from 3 different spreadsheets.

Here’s how you do it:


={
  QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/URL1", "Sheet1!A1:C100"),
        "SELECT Col1, Col2 WHERE Col3 = 'Open'");
  QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/URL2", "Sheet1!A1:C100"),
        "SELECT Col1, Col2 WHERE Col3 = 'Open'");
  QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/URL3", "Sheet1!A1:C100"),
        "SELECT Col1, Col2 WHERE Col3 = 'Open'")
}

What this does:

Each IMPORTRANGE fetches data from a different spreadsheet
QUERY filters the data to only show tasks where status is “Open”
The { ... ; ... } brackets combine results from all three sources

Tip: The first time you use IMPORTRANGE with a new sheet, you must allow access. Google will prompt you to do so.

Google Sheets QUERY Function: A Beginner's Guide

Learn how to analyze and filter data with SQL-like precision in Google Sheets

📌 Introduction

The QUERY function in Google Sheets is one of the most powerful tools for analyzing, filtering, and transforming your spreadsheet data — all without writing any scripts or doing manual sorting.

Whether you're managing a project, tracking sales, or organizing survey results, QUERY allows you to extract the exact data you need, when you need it.

In this guide, you’ll learn how to:

Understand the QUERY function and its syntax
Select and filter rows and columns based on conditions
Sort and structure data just like in SQL

This guide is perfect for beginners looking to make their spreadsheets smarter and more efficient.

🔍 Syntax Overview

The basic syntax for QUERY looks like this:

=QUERY(data_range, "SQL-like query", [headers])

What each part means:

data_range: The range of cells to apply the query to, such as A1:D100
"SQL-like query": A string that resembles SQL, like "SELECT A, C WHERE B = 'Open'"
[headers]: Optional. Tells Google Sheets how many header rows your data has (usually 1)

💡 Tip: Column letters are written as Col1, Col2, etc., when you're using QUERY with IMPORTRANGE.

🧪 Example Formula

Let’s say you have a table that tracks orders. You want to show only those orders where the Status is "Pending" and sort them by date.

Assuming your columns are:

Column A: Order ID
Column B: Customer Name
Column C: Status
Column D: Order Date

Here’s the formula:

=QUERY(A1:D100, "SELECT A, B, D WHERE C = 'Pending' ORDER BY D ASC", 1)

What it does:

Shows Order ID, Customer Name, and Date only for rows where Status is "Pending"
Sorts the result in ascending order by Order Date
Uses 1 to tell the function that row 1 has headers

Use Cases:

Show tasks assigned to a specific person
Filter sales by region or product
Display students who scored above a certain grade

Advanced Tip: You can also use QUERY to group data:

=QUERY(A1:D100, "SELECT B, COUNT(A) WHERE C = 'Completed' GROUP BY B")

This counts how many completed orders each customer has.

5 Ways to Use IMPORTRANGE Effectively in Google Sheets

Connect, organize, and display data across spreadsheets with ease

📌 Introduction

Google Sheets makes it easy to work with data across multiple spreadsheets. One of the most powerful tools for this is the IMPORTRANGE function. It allows you to link data from one sheet to another — dynamically and automatically.

In this tutorial, you'll discover five practical ways to use IMPORTRANGE to build smarter, connected spreadsheets.

Here's what you’ll learn:

Link sheets across departments or teams
Create a master dashboard pulling from multiple sources
Filter imported data for specific use cases
Combine with QUERY to build live reports
Avoid copy-paste errors by syncing live data

Perfect for project managers, teachers, business owners, and anyone who juggles data across sheets.

🔍 Syntax Overview

The IMPORTRANGE function is simple but powerful. Here’s the basic syntax:

=IMPORTRANGE("spreadsheet_url", "range_string")

What it means:

"spreadsheet_url": The full URL (or just the spreadsheet ID) of the Google Sheet you're importing from
"range_string": The tab and cell range, like "Sheet1!A1:C100"

💡 Tip: The first time you use it, Sheets will ask you to "Allow Access" to the source file — just click it once.

🧪 Example Formula

1. Connect Data from a Remote Sheet

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "Sales!A1:D100")

What it does: Pulls sales data from another Google Sheet tab called Sales.

2. Combine with QUERY to Filter Data

=QUERY(IMPORTRANGE("URL", "Sales!A1:D100"), "SELECT Col1, Col3 WHERE Col4 > 100")

What it does: Only shows rows where sales are over 100.

3. Create a Master Sheet from Multiple Files

={
  IMPORTRANGE("URL1", "Sheet1!A1:D10");
  IMPORTRANGE("URL2", "Sheet1!A1:D10")
}

What it does: Stacks data from two sheets into one continuous table.

4. Build a Live Attendance Sheet

=IMPORTRANGE("URL", "Attendance!A2:B100")

Perfect for school/classroom dashboards that update in real time.

5. Reference Specific Columns Only

=QUERY(IMPORTRANGE("URL", "Sheet1!A1:F100"), "SELECT Col2, Col5")

What it does: Imports only selected columns, making your report cleaner and more focused.

Use Cases:

Consolidating departmental data into a single dashboard
Sharing limited data with team members
Creating executive summaries without manual updates

Automate Data Collection with IMPORTRANGE & ARRAYFORMULA

Pull and process live data across spreadsheets—without manual work

📌 Introduction

Manual data entry and copy-pasting are tedious and error-prone. Fortunately, Google Sheets offers two powerful functions to automate the process:

IMPORTRANGE: Automatically pulls live data from one spreadsheet into another
ARRAYFORMULA: Applies a formula to an entire column or range in one go

Together, these functions allow you to build workflows where data updates itself, calculations run automatically, and spreadsheets stay current—without human intervention.

🔍 Syntax Overview

IMPORTRANGE Syntax:

=IMPORTRANGE("spreadsheet_url", "Sheet1!A1:C")

• Pulls the specified range of cells from a separate spreadsheet.

ARRAYFORMULA Syntax:

=ARRAYFORMULA(expression)

• Automatically fills the formula down a column or across a range based on the size of the data.

💡 Combining them:

=ARRAYFORMULA(IF(LEN(A2:A), IMPORTRANGE(A2:A, "Sheet1!A1:C"), ""))

• Dynamically pulls ranges listed in column A and applies it across all rows using ARRAYFORMULA.

🧪 Example Formula

Suppose you have a list of spreadsheet URLs in column A, and you want to import the same range "Sheet1!A2:B" from each. Here's how to do it automatically:

=ARRAYFORMULA(IF(A2:A<>"", IMPORTRANGE(A2:A, "Sheet1!A2:B"), ""))

What it does:

Checks if a URL exists in column A
If true, uses IMPORTRANGE to pull in the defined range from that URL
Applies the formula to each row using ARRAYFORMULA

Another Example – Calculate Automatically on Imported Data:

=ARRAYFORMULA(IF(B2:B<>"", B2:B * 1.15, ""))

This multiplies each value in column B by 1.15 (e.g. tax rate or markup) and fills the result automatically for every row.

Use Cases:

Import and calculate values from multiple external spreadsheets
Build auto-updating budget trackers or inventory lists
Generate dynamic invoices or reports from raw inputs

Tip: Nesting ARRAYFORMULA with logical functions like IF, ISBLANK, or LEN gives you fine control over when and how data should flow and be processed.

Reminder: As always with IMPORTRANGE, be sure to allow access to each new spreadsheet the first time you connect it.

Connect Multiple Teams via Google Sheets

Link multiple project sheets into one real-time tracker

📌 Introduction

When managing multiple projects, it's common to have data spread across different Google Sheets — one for each team, department, or project lead. But constantly switching between sheets or copying data manually can be time-consuming and risky.

With Google Sheets, you can link spreadsheets together using IMPORTRANGE to create a central, live-updating project tracker. You can also use QUERY to filter and organize this data automatically.

In this guide, you'll learn how to:

Link project sheets to one central tracker using IMPORTRANGE
Display only relevant tasks using QUERY
Keep everything updated in real time — without manual work

This is ideal for project managers, teachers, or teams working on shared goals in different sheets.

🔍 Syntax Overview

IMPORTRANGE Syntax:

=IMPORTRANGE("spreadsheet_url", "sheet_name!range")

• Imports a specified range of data from another spreadsheet

QUERY + IMPORTRANGE Combo:

=QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A1:D100"), 
       "SELECT Col1, Col3 WHERE Col4 = 'In Progress'")

• This pulls only tasks that are currently in progress from another sheet

Key Notes:

Use the Col1, Col2... format in QUERY — these refer to columns A, B, etc.
First time you use IMPORTRANGE, you'll be prompted to "Allow Access"

🧪 Example Formula

Suppose you manage three teams and each has its own sheet:

Team A Sheet: https://sheets.google.com/d/TEAM_A
Team B Sheet: https://sheets.google.com/d/TEAM_B
Team C Sheet: https://sheets.google.com/d/TEAM_C

Each sheet has the following columns:

Column A: Task
Column B: Owner
Column C: Due Date
Column D: Status

You want to track only tasks that are In Progress across all teams in one sheet.

Here’s the formula:


={
  QUERY(IMPORTRANGE("https://sheets.google.com/d/TEAM_A", "Sheet1!A1:D100"), 
        "SELECT Col1, Col2, Col3 WHERE Col4 = 'In Progress'");
  QUERY(IMPORTRANGE("https://sheets.google.com/d/TEAM_B", "Sheet1!A1:D100"), 
        "SELECT Col1, Col2, Col3 WHERE Col4 = 'In Progress'");
  QUERY(IMPORTRANGE("https://sheets.google.com/d/TEAM_C", "Sheet1!A1:D100"), 
        "SELECT Col1, Col2, Col3 WHERE Col4 = 'In Progress'")
}

What this does:

Each QUERY + IMPORTRANGE pair filters "In Progress" tasks from one team
Curly brackets { ... ; ... } stack the results vertically
The result is a single master tracker that auto-updates as teams update their sheets

Use Cases:

Track multiple projects in a single dashboard
Monitor student assignments across classes
Manage HR, marketing, or development workflows