Reference Another Sheet in Google Sheets [Easiest Way 2023]
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:
How to Reference Another Spreadsheet or Tab in the Same Workbook
In the example below we will demonstrate how to utilize data from another spreadsheet within the same workbook.
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.
1. Select the cell where you want the data to populate
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.
2. Enter the formula =(SheetName!CellRef)
In the highlighted cell enter the formula in the format of:
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:
The data from this cell is previewed, we can see this data is California.
3. Press Enter to see the results
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.
Reference Spreadsheet From Another Workbook in Google Sheets
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.
1. Click the cell where you want to populate the data
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.
2. Obtain the URL address of the worksheet to be referenced
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
3. Enter the formula =IMPORTRANGE(“URL”,“SheetName!CellRef”)
In the selected cell enter the formula in the following format:
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: