Google Sheets allows collaborative work in real-time. From building out financial models and operating plans to working together simultaneously on spreadsheets, the productivity gained by multiple people editing a spreadsheet at once is remarkable.
There may come a time when you need to reference data from another sheet, instead of copying and pasting manually, use a function to link both sheets together.
Table of Contents
1. Copy & Paste
Google Sheets offers many features and add-ons that make it an invaluable tool for manipulating and organizing data. However, if your team works across different files and tabs, sometimes copy and pasting can become time-consuming if the original data needs updating – but there are a few tricks you can use to save yourself both time and effort!
Copy and Paste data between sheets by selecting cells you wish to copy and right-clicking for the quick menu, selecting “Copy,” then opening your destination sheet where you would like the copied cells pasted, selecting all columns containing those cells from which they came, selecting first column header and right-clicking that column header and clicking Paste; however only their values will be preserved; any formatting of copied cells won’t.
Another simple method for quickly copying and pasting data between sheets is highlighting cells you wish to copy before pressing Ctrl + C (Command + C on Mac) to copy them. After copying them, open up your destination sheet where you wish to insert them and right-clicking any column header and selecting Paste; your copied cells will now be pasted directly into it, overwriting any existing data.
Use the QUERY function to automatically import data from other spreadsheets into the current file. Similar to copy and paste, but returning actual source spreadsheet data instead of copied cell formats. To use QUERY type:=QUERY(first sheet name)![first cell name]. When making changes in one source sheet it will update all other sheets using this function!
2. Copy & Paste Formulas
Google Sheets functions and add-on tools make it possible to easily transfer data between spreadsheets without manually copying and pasting. For instance, using the IMPORTRANGE function you can easily pull data from another sheet into one existing one; use this technique to move formulas or values from new files into existing sheets or link multiple sheets together.
To do this, select a range of cells containing formulas in your first file that you would like to copy and paste into another sheet in a second file, highlight their names (which is hidden within URL and looks like random letters and numbers), switch over to your second sheet and press Ctrl + V to paste your selections – any changes to original spreadsheet will automatically reflect in its mirrored copy (assuming both sheets exist simultaneously).
Use the IMPORTRANGE formula to link a single cell in one spreadsheet with data from another sheet. To do so, select the cell where you would like the data to appear and type =. Next, select both sheets where data you would like imported is located – note if their names contain spaces or special characters, type it between quotation marks with an exclamation point after “sheet”.
Once the formula is in place, you can click on any other sheet to view its data. When your cursor lands over one, its icon in the bottom-right corner of your window changes into a plus sign; clicking this plus sign will fill your current column or row with information from that sheet.
This function provides an excellent means of moving data between spreadsheets. Simply use it to link single cells within sheets instead of moving entire worksheets’ worth of data across. Also, make sure that before using this function on a large-scale sheet you first test its performance on a smaller-scale sheet for optimization purposes.
3. Copy & Paste Ranges
If you want to transfer data quickly between sheets, there’s an easier and faster solution than manually copying and pasting: use the IMPORTRANGE function! IMPORTRANGE allows you to quickly pull in cells from other sheets within the same spreadsheet or from another one altogether; its first parameter identifies which sheet it should import; its second specifies a range of cells you wish to import; optional filters allow for further restriction if dealing with large datasets.
The third parameter of IMPORTRANGE specifies whether you would like an exact or approximate match. When searching exactly, Google Sheets will only return results that meet your search criteria; when selecting approximately, however, Google Sheets will try to locate data that may come close – even if that means returning results that don’t exactly match.
Imagine you have a roster sheet which tracks employee names. Anytime one of those names changes in one sheet, it automatically updates in every other sheet that displays this data. To accomplish this, click the row heading where it is located and select the row you want to change before right-clicking it to copy and copy, copy again using Ctrl+V or using Quick Paste shortcut.
If the other sheet includes hidden rows or columns, copying cells you wish to include can still be achieved using individual selection and keyboard arrow keys. When pasting them into an area with hidden rows or columns, Google Sheets will adjust their reference accordingly so they match those found there.
4. Copy & Paste Objects
Sometimes it becomes necessary to aggregate data and formatting from various spreadsheets into one master view. While copy-pasting may work, Google Sheets allows for automated updates on a set schedule so your data won’t get out of date as fast.
Google Sheets’ QUERY function is an invaluable way to access data across multiple sheets within one file. To use it, select a range in one sheet that you would like to refer to another sheet or spreadsheet and highlight an area on another sheet where this information should appear; select your fill Across Worksheets dialog box option which best meets your needs (for instance “Fill entire sheet”)
ImportRange can also help you import data from one spreadsheet into another without altering its original formatting, unlike QUERY which only pulls values of data – this makes reading and interpreting your data simpler!
Use the GOOGLE_APPS_SCRIPT function to develop custom functions that automatically update your data on a scheduled basis, ideal for integration with tools or services that require providing specific formats of your information such as webhooks or IFTTT integrations.
Create a custom function is fairly easy. Begin by opening the spreadsheet you wish to use for automated data transfer and adding a line of code with:
Your function should take the form of: