Spreadsheets are a great tool for dealing with large amounts of data. However, when the information spans multiple sheets, it can be a bit difficult to keep track of changes from one tab to another. Fortunately, Google Sheets has the ability to link data from your spreadsheets using the appropriate functions.
We'll 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 Another Tab in Google Sheets on Windows PC, Mac, or Chromebook
If you're using Google Sheets on a computer, you can link data between tabs by doing the following:
- Proceed to Google Sheets and either the document you want to add links to or create a new sheet.
- Click on the cell in which you want to create the link and then type the equals sign, =.
- Type the sheet number and the cell you want to link. For example, if you want to link the first cell of 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 the sheet number followed by an exclamation mark, followed by the cell number.
- If the sheet has been given a name, enter the sheet name in single quotes. For example, you want to link cell B2 from a sheet called DATA, then the function syntax will be ='DATA'!B2.
- If you want to import multiple cells, you will need to enter the range in your function. For example, if you want to link data from sheet 2 with data from cells C1 through C10, the function will look like =Sheet2!C1:C10. Note that this will not copy all data from multiple cells into a single cell. It will only copy cells relative to the position of other linked data. For example, if you need to use the function =Sheet2!C1:C10 and paste it into cell A2, it will only copy the cell value into cell C2 on sheet 2. If you paste this formula to A3, it will will only reflect data from sheet 2 C3 etc.
- If you get a #REF error, either the address you're connecting to doesn't exist or your syntax contains an error. Check if you spelled the sheet or cell name correctly.
If you want to avoid spelling mistakes on the sheets, you can click on the cell you want to link. To do this:
- Click on the cell on which you want to create a link then type =.
- Click on the sheet you want to link from the menu below, then click on the cell you want to link and press the Enter key.
- If you did it correctly, you should be automatically redirected to the sheet where you placed the link.
- If you want to copy a range of values, click and drag your mouse until you select all the cells you want to link.
How to Link Data from Another Tab in Google Sheets on an iPhone
A similar function can be created when using Google Sheets on your iPhone. To do so, follow these steps:
- Open your Google Sheets mobile app.
- Open an existing sheet or create a new one.
- Go to the sheet on which you want to put a link and select the cell in which you want to have this link.
- Type =
- Type the name of the sheet followed by an exclamation mark. If the sheet has been given a name or contains spaces, type the name in single quotes. For example, if the sheet is called DATA SHEET the function will be ='DATA SHEET'!
- Type the cell or range of cells you want to import. For example, if you want to import cells B1 to B10 on sheet 2, you would enter the formula =Sheet2!B1:B10. If you enter a sheet with spaces or names like in the example above, the syntax would be ='DATA SHEET'!B1:B10.
How to Link Data from Another Tab in Google Sheets on Android Device
The process of linking data from one sheet to another in Android is similar to that of iPhone. If you want your sheets to be connected, follow the steps given above for the iPhone.
Link data from cells in the same sheet
If you want to link cells on the same sheet, the process is quite similar to those shown above. This is a handy tool if you want to reference other cells in a formula, or if you want your data to change dynamically whenever particular cell values change. To do this, whether on desktop or mobile, follow these steps:
- On your open Google Sheets, select a cell where you want to have the reference, then type =.
- Type the cell or range of cells you want to link, or click or click and drag the cells.
- If you end up with a #REF error, check to see if you have a spelling or punctuation error.
This trick comes in handy when creating dynamic spreadsheets because you can place many values in particular cells and then hide those cells afterwards. This means that anyone using the spreadsheet only needs to see the relevant data without having to look at hidden cell values. A change in the value of a particular cell will affect every cell that contains a link.
Link data from an entirely separate file
With Google Sheets, you're not limited to using data from a single file. There is a way to import data from other files into your spreadsheet. This means that any changes made to this file will also reflect on your linked spreadsheet. This can be done using the IMPORTRANGE function.
This particular command, however, is only available on the desktop version of Google Sheets. If you're using mobile data, save your work to the cloud, then open the files on a computer. To use the IMPORTRANGE function, follow the steps below:
- Open Google Sheets.
- Open both the file you want to import and the file you want to link the data to.
- Highlight the file whose data you want to copy. 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.
- Highlight the file to which you want to copy the data. Choose a cell from which the data import will start. Click on the cell then type =IMPORTRANGE.
- Type an open parenthesis '(' and then paste the address you copied in double quotes. You can either right-click and then click Paste or use the shortcut Ctrl+V to copy the address exactly.
- Type a comma ',' then type the sheet and cell range of the file you want to copy. These values must also be quoted. For example, if you want to copy cells A1 through A10 from sheet 1 of the file, you would write to "Sheet1!A1:A10". If the sheet name has been changed or has spaces, you do not need to enter single quotes between the double quotes. Type a closed parenthesis ')'.
- Press Enter. If you get the syntax right, you'll see the information load. If you see an error saying Loading, just refresh the sheet or close it and then open it again. If you see a #REF error, check either the spelling of the address or the quotes or commas. #REF errors usually mean something wrong with the syntax. If you get a #VALUE error, it means Google Sheets can't find the file you're linking. The address itself may be wrong or the file was accidentally deleted.
Below is one of the most frequently asked questions when discussing data association on Google Sheets:
Can this be used to extract information from a completely different Google sheet? Or should it be in the same worksheet?
As shown above, the data can come from the same spreadsheet or from another file entirely. The difference is that you will need to use the IMPORTRANGE function for data from an external sheet, instead of just using the equals sign '=' for spreadsheet data.
Be aware, however, that if you are using an external file, if that file is deleted, all cells with the IMPORTRANGE function will display a #REF or #VALUE error, which means Google Sheets cannot find the linked data.
Eliminate busy work
Dynamic spreadsheets are a great tool for those who manage a lot of data. Automatic updating of all related tables eliminates all the tedious work required to modify redundant information. Knowing how to link data from a different tab in Google Sheets increases your efficiency and productivity. Anything that reduces unnecessary redundancies is always a plus.
Do you know of any other ways to link data from different tabs in Google Sheets? Share your thoughts in the comments section below.