This article explains how to extract data in a Google Sheet from other sheets in the same file or from an external Google Sheet. To import data from an external file, you need to know its URL.
Steps
Method 1 of 2: Extract Data from Another Sheet of the Same File
Step 1. Go to https://sheets.google.com from a web browser
If you are logged in to Google, the page offers you a list of Google Sheets associated with your account.
If authentication has not already occurred automatically, authenticate with your Google account
Step 2. Click on a Google Sheet name
This will open the file to work on.
-
You can also create a new spreadsheet by clicking on
Android_Google_New
Step 3. Go to the sheet where you want to import the data
In the lower section of the screen, with the tabs that identify the sheets, click on the sheet in which to import the data.
If your file has only one sheet, click on the symbol + in the lower left corner of the screen.
Step 4. Select a cell
Click on the target cell, which will highlight.
Step 5. Type in cell = Sheet1! A1
In place of "Sheet1" write the name of the sheet; instead of "A1" write the coordinates of the cell containing the data to be copied. The formula must contain: equal sign, sheet name, exclamation point and coordinates of the cell with the data to be copied.
If the sheet name contains spaces or non-alphabetic characters, enclose it in single quotes. For example, if you want to copy the contents of cell A1 from the "Budget $$$" sheet, the formula is as follows: = 'Budget $$$'! A1.
Step 6. Press the ↵ Enter key
The formula will be applied and the data will be extracted from the specified sheet.
Step 7. To copy the formula to adjacent cells as well, drag the blue square until it covers the desired area
If you want to import multiple cells from the same sheet, click and drag the blue square in the lower right corner of the highlighted cell, until it covers and selects the cells included in the desired area.
Method 2 of 2: Extract Data from an External Worksheet
Step 1. Go to https://sheets.google.com from a web browser
If you are logged in to Google, the page offers you a list of Google Sheets associated with your account.
If authentication has not already occurred automatically, authenticate with your Google account
Step 2. Open the Google Sheet to import data from
Click on the name of the spreadsheet to import data from.
Step 3. Click on the URL with the right mouse button and select Copy
After opening the file, right-click on the internet address in the address bar, highlight it in its entirety and then select Copy from the drop-down menu.
From a Mac with a trackpad or Magic Mouse, click with two fingers or holding down the Control key to simulate a right click
Step 4. Open the file to import the data into
From a new browser tab or window go to https://sheets.google.com and click on the file to import the data.
Step 5. Select a cell
Click on the target cell, which will highlight.
Step 6. Type the following formula in the cell:
= IMPORTRANGE ("File URL", "Sheet1! A1: B14"). Instead of "File URL" enter the previously copied URL; instead of "Sheet1! A1: B14" enter the name of the sheet and the range of cells to be imported. The formula must contain: equal sign, the word IMPORTRANGE all in uppercase characters, opening parenthesis, quotation marks, the file URL, quotation marks, a comma, a space, quotation marks, the sheet name, an exclamation point, the first cell of the range, colon, the last cell of the range, quotation marks and closing parenthesis.
To paste the URL you can right click and select Paste. Alternatively you can press Ctrl + V from Windows or ⌘ Command + V from Mac.
Step 7. Press the ↵ Enter key
The formula will be applied and the data will be extracted from the external sheet.
Step 8. In the pop-up window click on Allow access
The first time the target sheet imports data from a new source, you are prompted to authorize the operation. Your data will eventually be imported into the spreadsheet.
