What if the data that you need for your current sheet is stored in a different one, however? With Google Sheets, you can quickly access data from a different sheet, or even an entirely different Google Sheets document. Learn how to query another sheet in Google Sheets below.
How to Pull Cell Data From Another Sheet in Google Sheets
The QUERY function is a powerful tool for pulling multiple pieces of data from another sheet or document. If you just want to reference a few cells, however, it can be a little overcomplicated. For a quick and easy way to pull cell data from another sheet in Google Sheets:
Click in the cell where you want the data to appear.Type =( into the cell. Don’t press Enter at this point. Navigate to the sheet that you want to pull data from by clicking on the sheet name. Click in the cell containing the data you want to pull across. Type ) and press Enter to complete your formula.Your data will now be pulled across.You can use the fill down function in Google Sheets to pull more data across.
How to Pull Cell Data From Another Document in Google Sheets
If the data you want to pull into your sheet is in a different Google Sheets document, it’s possible to reference it directly. Here’s how:
Open the Google Sheet containing your data.Make a note of the name of the sheet and the cell reference that you intend to pull across.Copy everything in the URL of that document, up to the final forward slash. Return to the document you want to pull the data into and click in the cell where you want the data to appear.Type =importrange(“ and then paste the URL you copied in step 3. Type a final quotation mark, followed by a comma. In quotes, type the sheet name you noted in step 2, an exclamation point, and the cell reference you noted down in step 2. Add a final closed bracket, and press Enter.You may now be asked for permission to connect your spreadsheets. Click Allow Access to confirm. Your data will now appear in your sheet.
Unlike the first method, you can’t drag down to fill other cells, since the formula is referencing one specific cell in your other document. You can provide a range of cells in your formula, however. To pull across all the cells from C4 to C8 in one go, for example, you would use the following reference at the end of your formula:
“My Reference Sheet!C4:C8”
How to Query Another Sheet in Google Sheets
If you have a lot of data you want to pull from another sheet, or you want more control over what gets pulled across, you can use the powerful QUERY function. This is more complicated to use but it is highly configurable. To query another sheet in Google Docs:
Click in the cell where you want the first piece of data to appear.Type =query( but don’t press EnterNavigate to the sheet with your data by clicking on the sheet tab. Highlight all of the data that you want to work with. Type a comma, and then in quotes, type select followed by the letters of the columns you want to pull data from. For example, if you wanted to pull data from columns B and D, you would type , “select B, D”. If you wanted to query all the data, you would type “select*”
Finally, type another comma, and then type the number of headers that your data has, followed by a final closed bracket. If there is one column heading, for example, you would type ,1) Press Enter and the data will be pulled across. If you need to edit your formula, click in the top left-hand cell of your data, which is where you first entered the formula.
How to Query Another Document in Google Sheets
If the data you want to query is in a different document, you’ll need to use a slightly different formula. Here’s how it works:
Open the Google Sheets document containing the data you want to query.Note down the name of the sheet and the range of cells you want to query.Copy the URL of that document up to the last forward slash. Go back to the document where you want the data to appear. Click in the cell where you want the queried data to go.Type =query(importrange(“ and then paste the URL that you copied in step 3. Type ), and then in quotes type the name of the sheet you noted in step 2, an exclamation point, and then the cell range you noted in step 2. Type a comma, and then in quotes, type select followed by the column numbers you want to pull data from. For example, if you wanted to pull data from the first and third columns, you would type ,“select Col1, Col3”
Finally, type another comma, and then the number of headings above your data followed by a final closed bracket. For example, if there is one column heading, you would type ,1)
Press Enter.You may now be asked for permission to connect your spreadsheets. Click Allow Access to confirm. Your queried data should now appear.
Useful QUERY Functions in Google Sheets
In the above examples, the SELECT function was used to select the columns that we wanted to query. However, you can use this function to make much more specific selections. Here are a few examples of what you can do:
Select all data=QUERY(‘My Current Sheet’!B3:D13, “SELECT *”, 1)Select columns in a different order=QUERY(‘My Current Sheet’!B3:D13, “SELECT B, D, C”, 1)Select only data over a specific value=QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C WHERE C > 20”, 1)Order the selected data=QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C ORDER BY B DESC”, 1)Select only the top 5 values in ascending order=QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C ORDER BY D ASC LIMIT 5”, 1)Skip the first 10 rows=QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C, D OFFSET 10”, 1)
Learn More About Google Sheets Functions
Google Sheets has hundreds of powerful functions, of which QUERY is just one example. Now you know how to query another sheet in Google Sheets, you might want to learn about some of the other functions. Google Sheets IF statements allow you to make your calculations more intelligent. You can use the SUMIF function in Google Sheets to find the total sum of cells that meet specific criteria, or the COUNTIF function to count the number of cells that meet your criteria. You can also use the VLOOKUP function in Google Sheets to search the leftmost column of your data and return any value from the same row. Comment
Δ
