In this article we will show you how to reference other spreadsheets in Google Sheets in just a few clicks. We explain how to reference sheets and tabs in the same workbook as well as sheets in another workbook. Simply follow the steps below:
In the example below we will demonstrate how to utilize data from another spreadsheet within the same workbook.
For the purpose of this demonstration an example data set was created. Access this and follow along by clicking this link.
For this example we will call data from column B in Sheet2 by referencing it. We will populate the data in an empty cell in Column D of Sheet1.
Select an empty cell to populate the data in. Simply click the cell to highlight it.
In our example we will populate data in cell D3 of Sheet1, therefore we click to highlight the cell.
In the highlighted cell enter the formula in the format of:
=(SheetName!CellRef)
Formula Breakdown:
SheetName!: The name of the sheet you want to reference followed by the exclamation point (!), the sheet name must be exactly the same as displayed in Google Sheets. As an example, if the sheet name is Sheet2 this needs to be entered as Sheet2!
CellRef: This is the cell designation in the previously referenced sheet that contains the data you want to populate.
Once entered Google Sheets will preview the results.
In our example we want to reference data from cell B3 in Sheet2 so our example formula is:
=Sheet2!B3
The data from this cell is previewed, we can see this data is California.
With the formula entered press enter, the results will be populated into the cell.
In our example the data from cell B3 in Sheet2 has been populated into the cell in Sheet1.
To populate any additional cells, click the cell where the results have been populated, in the bottom right hand corner of the cell is a blue square, click this and drag over the other cells to populate the rest of the data.
We can reference a spreadsheet from a completely separate workbook in Google Sheets using the IMPORTRANGE function.
For the purpose of this demonstration sample workbooks were created, access these by clicking the links below to follow along:
In this demonstration we will populate data into a cell of column E located in Sheet1 of Workbook1 by referencing a cell located in Sheet1 of Workbook2.
Click an empty cell where you want the data to populate. The cell will be highlighted.
In our example we will click the cell E3 in Workbook1.
Open the worksheet needing to be referenced and copy the URL from the address bar at the top of the display.
In our example we will open Workbook2 and copy the URL
In the selected cell enter the formula in the following format:
=IMPORTRANGE(“URL”,“SheetName!CellRef”)
Formula Breakdown:
IMPORTRANGE: This is the function used to import data from another worksheet in Google Sheets.
URL: The URL address of the Worksheet needed to reference data from. This is the URL we copied in the previous step. The URL must be encapsulated in double quotations (“ ”).
SheetName!: The name of the sheet where the data is located in the workbook to be referenced followed by the exclamation point (!). This needs to match the name of the sheet exactly.
CellRef: The cell reference which contains the data you want to populate and is located in the previously stated workbook and sheet. If you want to include a range of data you can also use a cell range here, for example E3:E6.
In our example we will reference cell E3 which is located in Sheet1 of Workbook2. With these parameters we will enter:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EYW2byGcmQlFBk-Ayctfn36UwIBmfiRI_fPHsYCtzhU/copy?usp=sharing","Sheet1!E3")
With the formula inputted press Enter, the cell will display #REF! Click the cell again and a pop message will display asking permission to connect the workbooks. Click Allow access.
This only needs to be done the first time referencing another workbook.
The data from the referenced location will be populated in the cell.
In our example the data from cell E3 in Sheet1 of Workbook2 has been populated into cell E3 in Sheet1 of Workbook1.
To quickly populate additional cells we can modify the formula to include a cell range instead of a single cell. To achieve this we simply change the cell_ref at the end of the formula.
In this example the formula is:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EYW2byGcmQlFBk-Ayctfn36UwIBmfiRI_fPHsYCtzhU/copy?usp=sharing","Sheet1!E3")
By amending the formula to:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1EYW2byGcmQlFBk-Ayctfn36UwIBmfiRI_fPHsYCtzhU/copy?usp=sharing","Sheet1!E3:E6")
The difference between the formulas is that the cell ref E3:E6 is entered instead of E3. We can see that the cell range has now been imported and populated instead of just a single cell.
We hope this article has helped you and given you a better understanding on how to reference another sheet in Google Sheets. You might also like our articles on how to use the Google Sheets Not Equal operator and how to add an absolute reference in Google Sheets.
To optimize your workflow, we recommend reading our guide on how to extract names from an email address in Google Sheets.
-How to Use Named Ranges Google Sheets
