Occasionally, every Google Sheets user needs to reference data from another sheet, or even a spreadsheet, to create a combined master view. Doing this will simplify the process of consolidating information from multiple worksheets into one.
This article provides tips on linking data from other sheets and spreadsheets, along with alternative methods for doing so. Also, this guide will provide a comprehensive comparison of each of the approaches mentioned so you can make a decision based on your own preferences.
Linking Cells From the Current Sheet to Another Tab in Google Sheets
For this first option, it is applicable if you wish to reference data from the current and other sheets.. Achieving this method requires following the steps below:
- Open a sheet in Google Sheets.
- Place your cursor in the cell where you want the referenced data to show up.
- Use this formula if you want to link data from the current sheet: ={A1:A3}
- Where A1:A3 is the range of cells from your current active sheet. Use curly brackets for this argument.
- Use this formula if you want to link to another tab in Google Sheets: ={Sheet1!A1:A3}
- Sheet1 is the name of your referenced sheet and A1:A3 is a specified range of cells that you want to import data from. Use curly brackets for this argument.
When doing this method, remember to not forget to put the sheet’s name in single quotes if it includes spaces or other characters like ):;”|-_*&, etc.
Linking Cells From One Sheet to Another Tab in Google Sheets
For this second option, it may be applicable if you want your master document to update automatically, on a set schedule. To do this, the following instructions will help you link data between Google Sheets:
- Open a sheet in Google Sheets.
- Place your cursor in the cell where you want the imported data to show up.
- Use this formula: =Sheet1!A1
- Sheet1 is your referenced sheet, followed by an exclamation mark, and A1 is the cell you want to import data from).
- Or you may use =’Sheet two’!A1
- Put the sheet’s name in single quotes if it includes spaces or other characters like ):;”|-_*&, etc.
Doing this method, you have to take note that if you want to import the range of cells from one sheet to another, just place your cursor on the cell in your data destination worksheet that already contains one of the above-mentioned formulas (=’Sheet two’!A1 or =Sheet1!A1). Once you’ve selected your desired range, drag the slider in that direction. Your document can also be directed in any other direction.
Linking a Column From One Sheet to Another Tab in Google Sheets
If you wish to link a column from one sheet to another tab in Google Sheets, you may be able to do it using the third method. Follow the instructions below:
- Open a sheet in Google Sheets.
- Place your cursor in the cell where you want the imported data to show up.
- Use this formula: ={Sheet1!A:A}
- Sheet1 is the name of your referenced sheet and A:A is a range that specifies that you will pull the data from the A column. Also use curly brackets for this argument.
Import Data From Multiple Sheets Into One Column
If you wish to import data from multiple sheets into one column, this third method is the most applicable to use. Note that while this method may seem a little complicated, concentrating on the formula will make applying this method simple for you.
Let’s go over an illustration of how to combine data from many columns in various sheets into one. In this example, I have three tabs with data: Red, Green, and Blue. For this instance, my task is to compile all client names on the “Colors” sheet.
To do this, take a look at the formula below:
={
“Colors”;
FILTER(‘Red’!C2:C, LEN(‘Red’!C2:C) > 0);
FILTER(‘Green’!C2:C, LEN(‘Green’!C2:C) > 0);
FILTER(‘Blue’!C2:C, LEN(‘Blue’!C2:C) > 0)
}
- “Colors” – is a given name of my column,
- FILTER(‘Green’!C2:C, LEN(‘Green’!C2:C)> 0) – this expression means that I take all data from column C of the “Green”, excluding the values that are equal or less than 0.
- As a result, I get the names of all my customers from three different sheets gathered in one column.
One benefit of this method is that if you alter the titles of the sheets that serve as your data sources, the formula will immediately update.
Import Data From One Google Sheet to Another Based on Criteria
Some cases may raise a requirement for a backup spreadsheet that would be copying values and format from the source file, but not the formulas. This method may be applicable.
Consider that you wish to import the filtered values onto another sheet after filtering your data set according to a set of criteria. You can do this using the FILTER function.
Here is the syntax: =FILTER(data_set,criterium1, criterium2,…)
- criterium – the criteria to filter the data set.data_set – a range of cells to filter.
To discover more filtering options, you may watch this video about the Google Sheets FILTER function.
Conclusion on How to Link Sheets in Google Sheets
Google Sheets is one of the best ways to log and organize data. It makes it easy to capture and organize that data and organize projects right inside of a web browser. There’s even so much more to learn when it comes to using Google Sheets to store and work with data.
As your knowledge in Google Sheets grows and expands, you may want to maximize your overall work productivity. Check out how FileDrop can make your every task easier.
FileDrop is a free Google Sheets and Docs add-on that allows you to drag and drop files into a spreadsheet cell with automatic linking and add them to Google Drive. Visit our website to learn more.
