Analyzing data is simpler when your raw or source data is on a separate sheet. This keeps your analysis sheet organized and tidy. In this post, I will demonstrate how to get 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 outlined below.
In Google Sheets, there are several methods to pull data from another sheet. The “QUERY” and “SORT” functions, exclusive to Google Sheets, are effective tools for this purpose. You can utilize these functions to import data between sheets with ease.
Looking for a quick answer?
In this blog post, I cover various methods to pull data from other sheets in Google Sheets. The discussed formulas include:
=QUERY('Sheet 3'!A1:Y, "select *")
=QUERY('Sheet 3'!A1:Y, "select A,K,B,C")
=SORT(Sheet2!A2:Y, 14, true)
You may also be interested in;
- How to import data from one Google Sheets to another,
- Importing CSV Into Google Sheets (import CSV, TSV, and XLSX).
- How to make a master sheet in google sheets: Streamlining Data Management and Analysis
Table of Contents
Why Would You Want to Get 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 it.
Regardless of your reason, Google Sheets provides several ways to get data from another sheet, which we’ll explore below.
Refer to a single cell in another sheet
This is straightforward. Like in all other spreadsheet applications,
- Type the equal (=) sign on the cell where you want the data
- Go to the other sheet by clicking the sheet tab at the bottom.
- Click on the cell you want to refer to.
- Press Enter. You will be returned to the original sheet with the value you want to refer to.
You may have noticed it builds a formula with the following components when following the above steps.
=’SheetName’!CellAddress (e.g., =Sheet6!A5)
! When you manually type the equation, don’t forget to wrap the sheet name with single quotes if the sheet name has spaces.
e.g., ='Sheet 6'!A5
Pull 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 of the same Google Sheet.
Use the QUERY function to import data from another sheet in Google Sheets
The QUERY function is really helpful in Google Sheets. It might be a key reason why someone switches from Microsoft Excel to Google Sheets. You can use the QUERY function in many different ways.
In the examples below, I will demonstrate how to use the QUERY function to retrieve data from another sheet. You can import either 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 give 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 as “Sheet 3″ and the data range is “A1:Y“, your data range can be referred from another tab as 'Sheet 3'!A1:Y
Case 1: Import all the data in the range
The SQL code “SELECT *” retrieves all the columns in the range given for the first argument. With that, your QUERY formula should be like the one below.
Example;
=QUERY('Sheet 3'!A1:Y, "select *")Case 2: Import only selected columns
You can modify the above formula by replacing the * with the column headings, as shown below.
Example;
=QUERY('Sheet 3'!A1:Y, "select A,K,B,C")Use the SORT function to sort and import data from another in Google Sheets
You can sort the rows of a given array or range by the values in one or more columns with the SORT function. You can also use this SORT function to import data from another sheet by sorting them in numerical or alphabetical order.
Example;
The following formula imports data from the range “Sheet2!A2:Y” and sorts the range by “customer name” in alphabetical order.
In this formula, the second parameter value “14” is the column number of the “customer name” column. By providing “true” or “false” for the third parameter, you can sort the range in either ascending or descending order, respectively.
=SORT(Sheet2!A2:Y, 14, true)Wrapping Up
Keeping your raw data untouched makes your life easier when performing complex analyses. However, manually copying and pasting data every time can be a tedious task. In that case, you can utilize various Google Sheets functions to import data from other sheets into your analysis sheet.
In this post, 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 those formulas depending on your requirements. Additionally, these methods are not limited to the functions described above; you can also utilize functions like VLOOKUP, FILTER, etc., to retrieve data from another sheet.
If you wish to learn how to import data from one Google Sheet to another or import data from a CSV, you can refer to our other posts mentioned earlier.
