How to reference all sheets in google sheets

Are you working from multiple sets of spreadsheets? Sometimes you’ll want to reference data from one sheet into another, or even merge your spreadsheets together for a master view of your data. In this guide, we offer multiple methods to effectively reference data from another Google Sheet, including the IMPORTRANGE formula (explained in more detail in how to use IMPORTRANGE in Google Sheets), and how to merge your Google Sheets using Layer. If you are working with Excel, rather than Google Sheets, see our guide to linking and referencing multiple sheets in Excel in how to reference another sheet in Excel.

Why reference another Google Sheet?

There are many reasons why you may want to reference data from another Google Sheet. It’s a simple way to easily compare data from different sources, without having to manually copy and paste between multiple sheets. An example of this is referencing other Google Sheets to illustrate year-on-year changes. Let’s say you want to import the data from your 2020 sales into a new sheet containing your 2021 sales data, in order to compare the two and analyze the results.

You can reference a single cell from another Google Sheet, or multiple cells, and pull data from another sheet into your current sheet according to your requirements. Here we’ll detail four methods for referencing data from other Google Sheets. The first will show how to easily import data from other sources into your Google Sheets workbook. The second will show how to import a range of data using the IMPORTRANGE formula. The third will explain how to use hyperlinks to have easy access to another data source where the data does not need to be present within the current workbook. Finally, we will show you how you can link Google Sheets using Layer.

How to reference another Google Sheet?

This first method uses a cell-by-cell reference to identify the source of your data and import it into your Google Sheet.

1. 1. Open the Google Sheet where you wish to import data.
2. 2. Click on the cell where you want your data to appear.
3. 3. Type in the sheet name and cell of the data you wish to import, substituting it into the following format =’sheet_name’!cell. For example, =Sheet1!A1 or ='Sheet number two'!B4.

Please note: When a sheet includes spaces or other non-alphanumeric characters, you should include single quotes around the sheet name, as shown above.

1. 4. This will copy the data from the cell into the original cell you selected.
2. 5. Google Sheets may suggest Autofill to import the rest of the data from that column.

This example would copy the data from C2 ($20,402) from one sheet, into the selected cell in the other sheet. It’s important to note that with this method, your data won’t be formatted in the same way it is in its original source. If you want to preserve the formatting of your data, you may want to use the IMPORTRANGE formula.

How to pull data from another Google Sheet using IMPORTRANGE?

If you want to reference a range of cells in another Google workbook, you should use the IMPORTRANGE function. For more detailed information on how to use this function, check out our post on how to use IMPORTRANGE in Google Sheets.

1. 1. Open your two Google Sheet files; the one where you want to import the data, and the one which contains the data you wish to import.
2. 2. Select the cell where you want to import your data to.
3. 3. Use the formula =IMPORTRANGE(“sheet URL or key”, “sheet name! cell range”) to select the cells you wish to link to. In this example, the URL would be followed by “Sales 2020!C2:C19”.

Please note:

• IMPORTRANGE requires both the sheet URL and a data range to be specified. You can’t specify another complete workbook by including only the URL.
• IMPORTRANGE expects text, not ranges or URLs so ensure the title is within inverted commas, as shown.

1. 4. Ensure that IMPORTRANGE is not within another formula.
2. 5. Ensure that IMPORTRANGE has any necessary permissions to work with both sheets

Google Sheets will ask you to permit to link the sheets before linking the data. Simply click the button to allow access and the process will complete automatically.

How to Search in Google Sheets? (Multiple Methods)

If you work with a lot of data, you might want to know how to search within a spreadsheet. Here's how to search in a Google Spreadsheet on any device

READ MORE

How to link data between multiple Google sheets with hyperlinks?

Sometimes you’ll want to have easy access to other Google sheets data sources, but won’t want to have that data present in your current workbook. In this instance, it makes sense to use a hyperlink, so the other data source (or sources) are a click away, without taking up space in your current google sheet. Creating a hyperlinked cell in Google sheets is simple. Follow the steps below to create a link to another data source.

1. 1. Select the cell in which you want to insert the hyperlink.
2. 2. Open the “Insert” menu and click “Link”.

1. 3. A dialog box will open; paste the URL of the link you wish to insert.

1. 4. Click “Apply”.
2. 5. Alternatively, select the cell in which you want to insert the hyperlink and press Ctrl and K together. This will bring up the dialog box and you can paste your URL from there.

Hyperlinks are not limited to other Google sheets either. You can create a hyperlink to any URL on the web, allowing you to add quick access to videos, articles, reports, or images, or even advanced data created in external programs such as IBM SPSS.

How to link multiple Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

• Share & Collaborate: Automate your data collection and validation through user controls.
• Automate & Schedule: Schedule recurring data collection and distribution tasks.
• Integrate & Sync: Connect to your tech stack and sync all your data in one place.
• Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

Linking and referencing multiple Google sheets is a vital process for many who work with data in Google docs. Fortunately, the process is quick and efficient when you use the functions described above. Alternatively, where data needs to be accessible but not present within the current workbook, or you want to reference data that is incompatible with Google sheets (eg. video) using a hyperlink is an easy and effective option.

Did we help you find what you were looking for? Here are some other articles you may find useful:

• Transfer Data from one Excel Worksheet to Another Automatically
• Import CSV to Google Sheets

Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

How do you reference all sheets?

How do I reference another sheet in Google Sheets query?

How do I search all sheets in Google Sheets?