How-To

How to Query Another Sheet in Google Sheets

Working in a spreadsheet

If your Google Sheets data is in a different sheet, it’s easy to access your data. Here’s how to query another sheet in Google Sheets.

Google Sheets allows you to create multiple sheets within the same document to keep your data organized.

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:

  1. Click in the cell where you want the data to appear.
  2. Type =( into the cell. Don’t press Enter at this point.
    start formula in google sheets
  3. Navigate to the sheet that you want to pull data from by clicking on the sheet name.
    change sheet in google sheets
  4. Click in the cell containing the data you want to pull across.
    pull cell in google sheets
  5. Type ) and press Enter to complete your formula.
  6. Your data will now be pulled across.
  7. You can use the fill down function in Google Sheets to pull more data across.
    fill down in google sheets

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:

  1. Open the Google Sheet containing your data.
  2. Make a note of the name of the sheet and the cell reference that you intend to pull across.
  3. Copy everything in the URL of that document, up to the final forward slash.
    copy url in google sheets
  4. Return to the document you want to pull the data into and click in the cell where you want the data to appear.
  5. Type =importrange(“ and then paste the URL you copied in step 3.
    paste url in formula
  6. Type a final quotation mark, followed by a comma.
    add comma to formula
  7. In quotes, type the sheet name you noted in step 2, an exclamation point, and the cell reference you noted down in step 2.
    google sheet reference
  8. Add a final closed bracket, and press Enter.
  9. You may now be asked for permission to connect your spreadsheets. Click Allow Access to confirm.
    allow access in google sheets
  10. 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:

  1. Click in the cell where you want the first piece of data to appear.
  2. Type =query( but don’t press Enterquery in google sheets
  3. Navigate to the sheet with your data by clicking on the sheet tab.
    select google sheet
  4. Highlight all of the data that you want to work with.
    data range google sheets
  5. 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*”
    select range google sheets
  6. 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)
    query formula in google sheets
  7. Press Enter and the data will be pulled across.
    queried data results from same doc
  8. 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:

  1. Open the Google Sheets document containing the data you want to query.
  2. Note down the name of the sheet and the range of cells you want to query.
  3. Copy the URL of that document up to the last forward slash.
    copy url in google sheets
  4. Go back to the document where you want the data to appear. Click in the cell where you want the queried data to go.
  5. Type =query(importrange(“ and then paste the URL that you copied in step 3.
    importrange in google sheets
  6. 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.
    range of importrange in google sheets
  7. 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”
    query columns in google sheets
  8. 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)
    complete formula in google sheets
  9. Press Enter.
  10. You may now be asked for permission to connect your spreadsheets. Click Allow Access to confirm.
    allow access in google sheets
  11. Your queried data should now appear.
    queried data results

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.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top