Analyzing data becomes much simpler when your raw or source data is stored on a separate sheet. This helps keep your analysis sheet organized and clean. In this article, I’m going to show you how to extract data from another sheet in Google Sheets. By storing your raw data on a separate sheet, you can import it as needed for analysis using the method described below.
Different Methods for Extracting Data from Another Sheet
In Google Sheets, there are several methods for extracting data from another sheet. The “QUERY” and “SORT” functions, exclusive to Google Sheets, are efficient tools for this purpose. You can use these functions to import data between sheets with ease.
Below are a few formulas you can use to extract data from other sheets in Google Sheets:
=QUERY('Sheet 3'!A1:Y, "select *")=QUERY('Sheet 3'!A1:Y, "select A,K,B,C")=SORT(Sheet2!A2:Y, 14, true)
You might also be interested in:
- How to import data from one Google Sheets to another
- Importing a CSV file into Google Sheets (importing CSV, TSV, and XLSX)
- How to create a master sheet in Google Sheets: streamlining data management and analysis
Why would you want to extract data from another sheet?
There are several reasons why you might want to retrieve data from another sheet in Google Sheets:
- You want to perform calculations or analysis on data from multiple sheets on a single sheet.
- You want to create a summary sheet that displays data from multiple sheets.
- You want to transfer data from one sheet to another without having to manually copy and paste.
Whatever the reason, Google Sheets offers several ways to extract data from another sheet, which we will explore below.
Referencing a single cell in another sheet
It’s quite simple. Just like in any other spreadsheet application:
- Type the equal sign (=) in the cell where you want to put the data.
- Go to the other sheet by clicking on the sheet tab at the bottom.
- Click on the cell you want to refer to.
- Press Enter. You will be taken back to the original sheet with the value you wanted to refer to.
You may have noticed that this creates a formula with the following components:
= 'SheetName'!CellAddress (e.g., =Sheet6!A5)
Don’t forget to enclose the sheet name in single quotes if the sheet name contains spaces.
Extracting a range of data from another sheet in Google Sheets
You can use formulas like QUERY or SORT when you want to import a range of data from another sheet into the same Google Sheets sheet.
Using the QUERY function to import data from another sheet into Google Sheets
The QUERY function is very useful in Google Sheets. It may be one of the key reasons why some people switch from Excel to Google Sheets. You can use the QUERY function in different ways.
In the examples below, I will show you how to use the QUERY function to extract data from another sheet. You can import the entire range or specific columns/rows for your analysis. This is the most basic way to use the QUERY function in Google Sheets.
In the QUERY function, you need to specify your data range as the first parameter. Then, you can write your query (SQL code) as the second parameter.
If your data is in a tab named “Sheet 3” and the data range is “A1:Y”, you can refer to that data range in another tab as ‘Sheet 3’!A1:Y.
Case 1: Importing all data from the range
The SQL code “SELECT *” retrieves all columns of the given range. Here is the corresponding QUERY formula:
=QUERY('Sheet 3'!A1:Y, "select *")
Case 2: Importing only certain columns
You can modify the previous formula by replacing * with the column headers, as shown below:
=QUERY('Sheet 3'!A1:Y, "select A,K,B,C")
Using the SORT function to sort and import data from another sheet into Google Sheets
You can sort the rows of a table or a given range based on the values in one or more columns using the SORT function. You can also use this SORT function to import data from another sheet while sorting it in numerical or alphabetical order.
Example:
The following formula imports data from the range “Sheet2!A2:Y” and sorts the range by “client name” in alphabetical order.
In this formula, the value “14” in the second parameter is the column number of the “client name” column. By providing “true” or “false” for the third parameter, you can sort the range in ascending or descending order, respectively.
=SORT(Sheet2!A2:Y, 14, true)
Conclusion
Keeping your raw data intact makes complex analysis easier. However, manually copying and pasting the data every time can be tedious. In such cases, you can use various Google Sheets functions to import data from other sheets into your analysis sheet.
In this article, I explained a few functions and methods that you can use to import data from one sheet to another in Google Sheets. You can customize these formulas based on your needs. Additionally, these methods are not limited to the functions described above; you can also use functions such as VLOOKUP, FILTER, etc., to extract data from another sheet.
To learn more about Google Sheets, visit the website Crawlan.com.
