It is easy to do your analysis when your raw/ source data is in a separate Sheet. Because it makes your analysis Sheet cleaner. In this post, I will show you how to import data from one sheet to another in Google Sheets. Then you can store your raw data in another Sheet and import them as a when required to your analysis using the method described here.
There are multiple ways to import data from one sheet to another in google sheets. The “QUERY” and “SORT” functions given below are unique to Google Sheets. You can use these functions to import data from one sheet to another in Google Sheets easily.
You may also interested in How to import data from one Google Sheet to another, and Importing CSV Into Google Sheets (import CSV, TSV, and XLSX).
Refer to a single cell in another sheet
Like in any other spreadsheet, you can directly refer them in the formula typing sheet’s name followed by an exclamation mark and the cell is being copied. You can also use named ranges.
Include sheet name inside two single quotes if sheet name contains spaces.
Import range of data from one Sheet to another 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 QUERY function to import data from one sheet to another in Google Sheets
The QUERY function is one of the most useful functions in Google Sheets. This function may be one reason for someone to move from Microsoft Excel to Google Sheets. You can use the QUERY function in multiple ways.
In the below examples, I will show you how you can use the QUERY function to pull data from another sheet. You can import either the entire range or a few columns that require your analysis. This is the simplest use of the Google Sheets QUERY function.
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
Case 1: Import all the data in the range
The SQL code “
SELECT *” retrieves all the columns in your range given as the first parameter. Your function should like below.
=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.
=QUERY('Sheet 3'!A1:Y,"select A,K,B,C")
Use SORT function to sort and import data from one sheet to another in Google Sheets
You can sorts the rows of a given array or range by the values in one or more columns with the SORT function. You can use this SORT function also to import data from one sheet to another by sorting them in numerical or alphabetical order.
The following formula import data from the range “
Sheet2!A2:Y” and sort 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 either ascending or descending order respectively.
Keeping your raw data untouched make your life easy when doing complex analysis. However, copying and pasting data every time is a tedious task. In that case, you can get the help of various Google Sheet functions to import data from other sheets to 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 requirement. And also these methods are not limited to the functions described above, you can also use functions like VLOOKUP, FILTER, etc… also to pull data from another sheet.
If you want to learn how to import data from one Google Sheet to another, or import data from CSV, you can read our other posts mentioned above.