How to Link Data from a Different Tab in Google Sheets
Spreadsheets are a great tool when dealing with large amounts of data. When the information spreads to several sheets though, it can be a bit difficult to keep track of the changes made from tab to tab. Fortunately, Google Sheets has the ability to link data across your worksheets with the use of the appropriate functions.
We will show you how to link data from a different tab in Google Sheets and how to dynamically connect information throughout your project.
How to Link Data from a Different Tab in Google Sheets on a Windows, Mac, or Chromebook PC
If you are using Google Sheets on a computer, you can link data between tabs by following these steps:
Step 1Proceed to Google Sheets and either the document that you want to add links on or create a new sheet.
Step 2Click on the cell that you want to create the link in then type in the equal sign, =.
Step 3Type in the number of the sheet and the cell that you want linked. For example, if you want to link the first cell on the second sheet it will be written as Sheet2!A1. Cell A2 on sheet 3 will be written as Sheet3!A2. Remember that the syntax will be sheet number followed by an exclamation mark, followed by the cell number.
Step 4If the sheet has been given a name, type in the name of the sheet inside single quotes. For example, you want to link cell B2 of a sheet called DATA, then the function syntax will be =’DATA’!B2.
Step 5If you wish to import multiple cells, you will have to type the range into your function. For example, if you want to link the data from sheet 2, with the data from cells C1 to C10, then the function will look like =Sheet2!C1:C10. Do note that this will not copy all of the data from multiple cells into one single cell. It will only copy the cells relative to the position of the other linked data. For example, if you are to use the function = Sheet2!C1:C10 and paste it on Cell A2, it will only copy the value of the cell on sheet 2 cell C2. If you paste this formula on A3, it will only reflect data on Sheet 2 C3, etc.
Step 6If you get a #REF error, this means that the address that you are linking to either does not exist or your syntax has an error. Check if you have spelled the name of the sheet or the cell correctly.
If you want to avoid sheet spelling errors, you can click the cell that you want to link. To do this:
Step 1Click on the cell that you want to create a link on then type in =.
Step 2Click on the sheet on that you want to link on the menu below then click on the cell that you want to link and press the Enter key.
Step 3If you did this correctly, you should be automatically redirected to the sheet that you placed the link in.
Step 4If you want to copy a range of values, click and drag your mouse until you select all the cells that you want to link.
How to Link Data from a Different Tab in Google Sheets on an iPhone
A similar function can be made when you are using Google Sheets on your iPhone. To do this, follow these steps:
- Open your mobile Google Sheets App.
- Either open an existing sheet or create a new one.
- Proceed to the sheet that you want to put a link on, and select the cell you want to have that link.
- Type in =
- Type in the name of the sheet followed by an exclamation point. If the sheet has been given a name or contains spaces, type in the name inside single quotation marks. For example, if the sheet is named DATA SHEET the function will be =’DATA SHEET’!
- Type in the cell or the range of cells that you want to import. For example, if you want to import cells B1 to B10 on sheet 2, you’ll be entering the formula =Sheet2!B1:B10. If you’re entering a sheet with spaces or names as in the example above, the syntax would be =’DATA SHEET’!B1:B10.
How to Link Data from a Different Tab in Google Sheets on an Android Device
The process of linking data from one sheet to another in Android is similar to the process as on the iPhone. If you want your sheets connected, then follow the steps as given above for the iPhone.
Linking Data from Cells Within the Same Sheet
If you want to link cells together on the same sheet, then the process is pretty similar to the ones given above. This is a handy tool if you want to reference other cells in a formula, or if you want to have your data dynamically change whenever particular cell values change. To do this, either on a desktop or on mobile, do the following:
Step 1On your open Google Sheets, select a cell that you want to have the reference in then type in =.
Step 2Either type in the cell or range of cells that you want to link, or click, or click and drag the cells.
Step 3If you end up with a #REF error then check if you have a spelling or punctuation error.
This trick is handy when creating dynamic spreadsheets, as you can place a lot of values in particular cells and then hide these cells afterward. This means that anyone using the spreadsheet only needs to see the relevant data without having to look at the hidden cell values. A change in the value of a particular cell will reflect on every cell that has a link in it.
Linking Data from an Entirely Separate File
With Google Sheets, you are not limited to using data from within a single file. There is a way to import data from other files to your spreadsheet. This means that any changes made to that file will reflect on your linked spreadsheet as well. This can be done using the IMPORTRANGE function.
This particular command however is only available on the desktop version of Google Sheets. If you are using data from your mobile, save your work onto the cloud and then open the files on a computer. To use the IMPORTRANGE function, follow the steps below:
Step 1Open Google Sheets.
Step 2Open both the file you want to import and the file you want to link the data to.
Step 3Highlight the file that you wish to copy the data from. Click on the address bar above, and copy the entire address. You can either right click then choose Copy, or use the shortcut Ctrl + C.
Step 4Highlight the file that you want to copy the data to. Choose a cell where the data import will start from. Click on the cell and then type in =IMPORTRANGE.
Step 5Type in an open parenthesis ‘(‘ then paste in the address you copied within double quotation marks. You can either right-click then click on Paste or use the shortcut Ctrl + V to copy the address exactly.
Step 6Type in a comma ‘,’ then type in the sheet and cell range from the file you want to be copied. These values should also be within quotation marks. For example, if you want to copy sheet 1 cells A1 to A10 from the file, you will write in “Sheet1!A1:A10”. If the sheet name has been changed or has spaces, you do not need to type in single quotation marks within the double quotation marks. Type in a closed-parenthesis ‘)’.
Step 7Press Enter. If you got the syntax correctly, you would see the information load. If you see an error that says Loading, just refresh the sheet or close the sheet and then open it again. If you see a #REF error, then check either the address spelling or the quotations or commas. #REF errors usually mean something wrong with the syntax. If you get a #VALUE error then that means Google Sheets can not find the file that you are linking. The address itself may be wrong or the file was accidentally deleted.
Below is one of the more common questions asked whenever discussions regarding linking data on Google Sheets come up:
Can this be used to pull information from a completely different Google Sheet? Or does it need to be in the same spreadsheet?
As stated above, the data can either come from within the same worksheet or from another file entirely. The difference is that you’ll need to use the IMPORTRANGE function for the data from an external sheet, as opposed to just using the equal sign ‘=’ for data within the worksheet.
Be aware though that if you’re using an external file, if that file gets deleted, all cells with the IMPORTRANGE function will display either a #REF or #VALUE error signifying that Google Sheets can’t find the data being linked.
Dynamic spreadsheets are a great tool for those who handle a lot of data. Automatically updating all related tables eliminates all the busywork required in changing redundant information. Knowing how to link data from a different tab in Google Sheets increases your efficiency and productivity. Anything that cuts down on unnecessary redundancy is always a plus.
Do you know of other ways to link data from different tabs in Google Sheets? Share your thoughts in the comments section below.