Working in a spreadsheet

How to Query Another Sheet in Google Sheets

by

[ad_1]

Working in a spreadsheet

If your Google Sheets knowledge is in a unique sheet, it’s simple to entry your knowledge. Here’s how to question one other sheet in Google Sheets.

Google Sheets permits you to create a number of sheets throughout the similar doc to hold your knowledge organized.

What if the info that you simply want to your present sheet is saved in a unique one, nonetheless? With Google Sheets, you possibly can shortly entry knowledge from a unique sheet, and even a wholly totally different Google Sheets doc.

Learn how to question one other sheet in Google Sheets beneath.

How to Pull Cell Data From Another Sheet in Google Sheets

The QUERY perform is a robust device for pulling a number of items of knowledge from one other sheet or doc. If you simply need to reference a number of cells, nonetheless, it may be somewhat overcomplicated.

For a fast and simple means to pull cell knowledge from one other sheet in Google Sheets:

  1. Click in the cell the place you need the info to seem.
  2. Type =( into the cell. Don’t press Enter at this level.
    start formula in google sheets
  3. Navigate to the sheet that you really want to pull knowledge from by clicking on the sheet identify.
    change sheet in google sheets
  4. Click in the cell containing the info you need to pull throughout.
    pull cell in google sheets
  5. Type ) and press Enter to full your components.
  6. Your knowledge will now be pulled throughout.
  7. You can use the fill down perform in Google Sheets to pull extra knowledge throughout.
    fill down in google sheets

How to Pull Cell Data From Another Document in Google Sheets

If the info you need to pull into your sheet is in a unique Google Sheets doc, it’s attainable to reference it instantly.

Here’s how:

  1. Open the Google Sheet containing your knowledge.
  2. Make a be aware of the identify of the sheet and the cell reference that you simply intend to pull throughout.
  3. Copy the whole lot in the URL of that doc, up to the ultimate ahead slash.
    copy url in google sheets
  4. Return to the doc you need to pull the info into and click on in the cell the place you need the info to seem.
  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 wished to question all the info, you’ll sort “select*”
    select range google sheets
  6. Finally, sort one other comma, after which sort the variety of headers that your knowledge has, adopted by a remaining closed bracket. If there’s one column heading, for instance, you’ll sort ,1)
    query formula in google sheets
  7. Press Enter and the info shall be pulled throughout.
    queried data results from same doc
  8. If you want to edit your components, click on in the highest left-hand cell of your knowledge, which is the place you first entered the components.

How to Query Another Document in Google Sheets

If the info you need to question is in a unique doc, you’ll want to use a barely totally different components.

Here’s the way it works:

  1. Open the Google Sheets doc containing the info you need to question.
  2. Note down the identify of the sheet and the vary of cells you need to question.
  3. Copy the URL of that doc up to the final ahead slash.
    copy url in google sheets
  4. Go again to the doc the place you need the info to seem. Click in the cell the place you need the queried knowledge to go.
  5. Type =question(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, sort one other comma, after which the variety of headings above your knowledge adopted by a remaining closed bracket. For instance, if there’s one column heading, you’ll sort ,1)
    complete formula in google sheets
  9. Press Enter.
  10. You might now be requested for permission to join your spreadsheets. Click Allow Access to affirm.
    allow access in google sheets
  11. Your queried knowledge ought to now seem.
    queried data results

Useful QUERY Functions in Google Sheets

In the above examples, the SELECT perform was used to choose the columns that we wished to question. However, you need to use this perform to make way more particular choices.

Here are a number of examples of what you are able to do:

  • Select all knowledge 
    =QUERY(‘My Current Sheet’!B3:D13, “SELECT *”, 1)
  • Select columns in a unique order 
    =QUERY(‘My Current Sheet’!B3:D13, “SELECT B, D, C”, 1)
  • Select solely knowledge over a selected worth 
    =QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C WHERE C > 20”, 1)
  • Order the chosen knowledge 
    =QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C ORDER BY B DESC”, 1)
  • Select solely the highest 5 values in ascending order 
    =QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C ORDER BY D ASC LIMIT 5”, 1)
  • Skip the primary 10 rows 
    =QUERY(‘My Current Sheet’!B3:D13, “SELECT B, C, D OFFSET 10”, 1)

Learn More About Google Sheets Functions

Google Sheets has a whole bunch of highly effective capabilities, of which QUERY is only one instance. Now you know the way to question one other sheet in Google Sheets, you may want to find out about a number of the different capabilities.

Google Sheets IF statements enable you to make your calculations extra clever. You can use the SUMIF perform in Google Sheets to discover the overall sum of cells that meet particular standards, or the COUNTIF perform to rely the variety of cells that meet your standards.

You also can use the VLOOKUP perform in Google Sheets to search the leftmost column of your knowledge and return any worth from the identical row.

[ad_2]