5-MINUTE READ | By Sophie Michaud
If you’re using Google Sheets, you probably need to reference or import data from another sheet every once in a while. It’s not very complicated, and there are a few ways to do that.
You can copy/paste the data, of course. For a tiny, select dataset, that might work. But that way, your data won’t be linked to the original source, which means it won’t update automatically if there are changes to the source data. Besides, we’re not fans of copy/pasting.
We have three ways for you to import your data from one Google Sheet to another — whether your sheets are in the same doc or not, we’ve got you covered.
1. Import data from another sheet in the same document
If the data you want to import is in the same document and comes from one cell only, you can use the simple formula [=SheetName!CellName] in the destination cell.
This method works fine for simple imports and calculations. It also links both cells. So when you make a change in the original cell, it’s reflected automatically in the destination cell.
What if your data is in another document or you want to transfer more than one cell at a time? ⬇️
2. Import data from a sheet in another document
You might keep your data in separate docs to calculate in one and report in another or keep your client’s data separate but want to combine it for your internal overview reporting.
In this case, you can use the [=IMPORTRANGE] formula.
- Open your destination sheet.
- Select an empty cell. Note that your data will transfer in the top left corner.
Make sure all the cells covered by your data are empty — otherwise, you’ll get an error message.
- Enter [=IMPORTRANGE(“SheetURL”,”SheetName!CellRange”)] in your top-left cell.
- Use the URL for the sheet you want to import data from. The sheet name and cell range are optional.
- Press “Enter”.
- Allow access to your sheet.
- That’s it! Your data is now imported and linked. Every time you update your original sheet, the destination sheet will update as well.
But wait, there has to be an easier way.
3. The easiest way to import data from one sheet to another
Whether you want to import your data from a sheet in the same doc or a different one, follow these simple steps to import and connect your data to another sheet.
If you don’t have a Supermetrics license for Google Sheets yet, get started with a trial.
- Open your destination sheet.
- Select an empty cell. Note that your data will transfer in the top left corner.
- Click on “Add-ons” ➡️ “Supermetrics” ➡️ “Launch sidebar”.
“Supermetrics” -> “Launch sidebar””/>
- Select your data source ↘️ scroll to Google Sheets.
- Enter the URL for the sheet you want to import your data from in the “Report configuration” field.
- Press “Get Data to Table”.
Pro tip: You can set up an automatic refresh and email for your imported data. Simply click on the “Schedule” tab in the sidebar.
This method is especially useful if you’re sharing a report with an external partner or client. So when you update your original sheet, they can get an email with the refreshed data.
Try Supermetrics today
Start your free, full-feature trial of Supermetrics for Google Sheets. No credit card required.
Looking for some advanced settings to customize your imported data? Here are the ones we’ve included so far. Simply enter the setting name in the “Options” ➡️ “Advanced Settings” section of the sidebar.
ALLOW_SUM_UNIQUE disables the safeguards we have against summing values of unique count metrics (e.g. users or unique pageviews)
COMBINE_DIMENSIONS concatenates dimension values when multiple dimensions have been specified
DATES_NOTE displays the query date range (and comparison date range if used) above the results table
KEEP_RESULTS_ON_REFRESH_ERROR in case a query refresh returns an error, with this setting, the results of a previous successful refresh are preserved
STORE_ERROR_QUERY stores the query even if it returns an error
TIME_DISP_START_DATE shows first day of month or week, instead of month/week number
DISABLE_DIMENSION_AUTOFORMAT prevents Google Sheets from applying automatic formatting to the dimension values
Over to you!
Those were the three ways to import your data from one Google Sheet to another.
Check out our Google Sheets template roundup
Looking for templates to get you started on your marketing analytics journey?
What you need to know about the IMPORTRANGE function
Of all of the G Suite web applications, Google Sheets may be the most impressive. Released in 2006, it quickly became a fierce competitor to stand up against Microsoft Excel as an up-and-coming spreadsheet editor.
Today, Google Sheets includes a wealth of editing and collaboration features that millions of students, workers, and hobbyists use on a daily basis.
One of Google Sheets’ most appreciated features is its robust set of functions. Functions are a core component to what makes spreadsheets so powerful, and each spreadsheet editing platform usually has several of its own unique functions.
Google Sheet’s IMPORTRANGE function allows a level of seamless connectivity between two or more spreadsheets, enabling many interesting possibilities. It allows you to import data into Google Sheets.
What Is IMPORTRANGE?
IMPORTRANGE is one of the many functions supported by Google Sheets. Functions are used to create formulas that can manipulate your spreadsheet data, make calculations, and more.
Some of the more than 100 supported functions include DATE, to convert a string into a date, COS, to return the cosine of an angle provided in radiances, and ROUND, allowing decimals to be rounded to a certain decimal place.
Included in this long list of functions is IMPORTRANGE. IMPORTRANGE enables a form of cross-spreadsheet integration, allowing a range of cells from another spreadsheet (or worksheet within the same sheet) to be imported.
This allows Google Sheets users to split up their data into multiple different sheets while still being able to view it using a simple formula. Uniquely, it also allows a level of collaboration where you can import data from a third-party sheet (if permitted) into your own.
How To Use IMPORTRANGE
The first step to making use of this powerful function is to have a spreadsheet that you want to import data from into Google Sheets. Either locate one or, as I’ll do in this example, create a dummy sheet with a few rows of data.
Here, we have a simple sheet of two columns and three rows. Our goal is to take this data and import it into another spreadsheet that we’re using. Create a new sheet or go into an existing sheet and let’s set it up.
You’ll begin with the same process as you would when using any function—click a blank cell so that you can access the function bar. In it, type =IMPORTRANGE. This is the function keyword we can use for importing sheet data.
The IMPORTRANGE function uses two parameters in its basic syntax: IMPORTRANGE(spreadsheet_url, range_string). Let’s go over both.
spreadsheet_url is exactly what it sounds like—the URL of the spreadsheet that you’re attempting to import a range of data from. You simply copy and paste the spreadsheet’s URL and here. Even easier, you can optionally just use the spreadsheet’s identifier string, also found in the URL.
This identifier is the long string of text found between “spreadsheets/d/” and “/edit” in the sheet’s URL. In this example, it’s “1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”.
The range_string parameter is just as simple. Instead of printing all of the spreadsheet data from another sheet, you can return a specific range. To import the data shown in the entire sheet of our example, the range would be A1:B4.
This can be simplified to just A:B if we’re fine with importing all future data from these columns. If we wanted to import the data without headers, that’d be A2:B4.
Let’s put together our full formula: =IMPORTRANGE(“1bHbpbisrzaLF34r91UD1SLdDCpx7gD4v_4RnFBvgbfI”, “A:B”)
You’ll notice that attempting to use this formula, assuming you’ve properly replaced spreadsheet_url, will initially show a reference error. You’ll need to then click on the cell to connect these two sheets.
If everything has been done correctly, you should now see the data imported into your current sheet.
Simple, right? It’s worth noting that formatting will not be preserved during this import, as you can see above, but all of the plaintext data will be.
Why Use IMPORTRANGE?
Now that you see how easy it is to use IMPORTRANGE, why would you ever use it? Let’s go over a few quick example use cases.
You may find yourself involved in a very complex sheet that has moving variables that you want to separate completely from other parts of your data. IMPORTRANGE is perfect for this, as it allows you to do just that.
Since IMPORTRANGE easily allows you to import data from another worksheet within the same spreadsheet, you can create a “Variables” worksheet where you can store anything with moving parts. A combination of IMPORTRANGE, QUERY, and CONCATENATE could then be used to bring everything together.
Collaborating With a Friend
Two heads are better than one, and IMPORTRANGE will even allow you to connect to sheets that your account doesn’t own, as long as it’s shared with you. If you’re working on a collaborative project, the work of two or more people can be dynamically consolidated into a single sheet using IMPORTRANGE.
Hiding Sensitive Data
If you have a private spreadsheet with rows or columns that you want to show publicly, IMPORTRANGE is great for that.
One example would be if you were to create a form with Google Forms. In it, you may ask for some of the respondents’ personal information—you obviously don’t want to give that out, right? However, the form may also ask less personal questions that you want to display in a public shared link. By setting the range_string accordingly and connecting the form’s response sheet, this can be achieved.
IMPORTRANGE is a powerful Google Sheets tool that serves well in a variety of different situations where you need to import data into Google Sheets. It’s one of my picks for the most important differences between Google Sheets and Microsoft Excel.
Do you have any questions on how you can use this function, or want to share one like it with us? Drop a comment below and we’ll check it out!
Craig is a long-time writer, coder, and marketer with years of experience in the technology and gaming spaces. Since 2008, he’s worked remotely with some of the most notable publications in these industries, specializing in Windows, PC hardware and software, automation, and the like. Read Craig’s Full Bio
I’m a not-so-secret spreadsheets nerd. I’m even in a sort of Spreadsheets Interest Group. The number of passionate people there tells me we’ve all relied on a good old spreadsheet at some point in our careers.
Even in this realm, Google Sheets are something of a superhero. Google Sheets spreadsheets can dynamically collect information for you while you sleep, and grab anything you want (stock prices, site analytics, and much more) from anywhere.
But what if you want to grab data from the web at large—perhaps to copy info from a table on a website? Maybe there’s a list of events, a grid of facts, or email addresses scattered around a webpage. Copying and pasting them would take forever—but Google Sheets has a better option.
You can import data from any web page using a little function called ImportXML , and once you master it you’ll feel like a certified Sheets Wizard. ImportXML pulls information from any XML field—that is, any field bracketed by a and a . So, you can grab data from any website and any metadata generated by any website, anywhere. Sure, you could copy-paste and then spend hours editing everything by hand, but why not automate the boring stuff?
Let’s do just that.
XML and HTML Basics
You’ll need to know some very basic HTML—or rather, the XML markup that designates sets of data in a webpage—to grasp the common functions here, so here’s a crash course. In essence, any set of and —the core building-blocks of a web page’s source code—mean that a certain set of data is contained inside them (perhaps like this ). The of a page will have some text in a
aragraph, sometimes containing old text and perhaps a link (followed by .
- Using File>Import OptionВ
- Using IMPORTRANGE Function
- Using QUERY Function
Looking to import data from another workbook in Google Sheets?
Luckily, there are several ways to get this done!
To help you choose with method to go with, here are brief summaries of each:
- File>Import Option – best for when you have a copy of the sheet (either in your Google Drive or on your local computer), and you want to import all the sheets from the source workbook to Google Sheets. You can check the other options if you want to import a single sheet instead.В
- IMPORTRANGE – best for importing a specific data range from another Google Sheets workbook. This option will allow you to import a single sheet or even just a portion of a sheet from an external workbook.В
- QUERY – best for maximum flexibility, such as formatting the columns, changing labels, and preprocessing the data before it is imported to your spreadsheet.
Using File>Import OptionВ
If you have a copy of the sheet (either in your Google Drive or on your local computer), this option is the best to follow. Here are the steps:
Step 1: Click the File option in the main menu, and then click Import in the drop-down box. A large pop-up box titled Import file will appear.
Step 2: You get four options for the source workbook of the sheet to import: (1) your Google Drive (labeled as My Drive), (2) Shared with me, (3) Recent, and (4) Upload. Choose the option that will let you select the source workbook.
Step 3: After selecting the workbook (or uploading it, if you chose to upload from your local computer, a smaller box will appear, asking you what to do with the current sheet. You can (1) create a new spreadsheet, (2) insert the new sheet(s) to the current workbook (thus not replacing the existing sheets), or (3) replace the existing spreadsheets. Select your option.
Once you have selected your option, you can now start processing the data in Google Sheets.В
Using IMPORTRANGE Function
The IMPORTRANGE function allows you to import a specific data range from another Google Sheets workbook. It has the following syntax:
Both the URL and the data range should be enclosed by quotation marks. The workbook must be either a public workbook or that you have access to it. If the workbook you are including is also yours, a prompt will pop up, asking you to connect the sheets. Click Allow access.В
Afterward, the data will be loaded.В
If the sheets are located in the same workbook (thus having the same URL), they still need to be added through the IMPORTRANGE function individually.В
Using QUERY Function
For the sake of completeness, we still briefly discuss the QUERY function here. The QUERY function is the Google SheetsвЂ™ way of incorporating SQL queries to Google Sheets, giving you an additional powerful way to process and analyze data. This method is a little bit of a cheat, because it also uses the IMPORTRANGE function, but gives you more flexibility, which includes formatting the columns, changing labels, and preprocessing the data before it is imported to your spreadsheet.
Together with the IMPORTRANGE, there is so much more you can do with QUERY function. Just make sure you can access the source workbook!В For starters, you can read here how to import data from another worksheet with SQL here: How to SQL Query Multiple Sheets in Google Sheets
After that, here are two tutorials you can follow:
- Importing a data range from a single sheet
- A workaround forВ IMPORTRANGEВ to import multiple sheets
Importing a data range from a single sheet
The IMPORTRANGE function allows you to import a specific data range from another Google Sheets workbook. It has the following syntax:
For example, we will import the following Pivot Table from a source spreadsheet to a destination spreadsheet:
The URL to the source spreadsheet and the range are needed to import the array using IMPORTRANGE function.
Both the URL and the data range should be enclosed by quotation marks. The workbook must be either a public workbook or one that you have access to. If the workbook you are including is also yours, a prompt will pop up, asking you to connect the sheets. Click Allow access.В
Afterward, the data will be loaded.В
A workaround to import multiple sheets
The question is, how can we import multiple sheets using the IMPORTRANGE function?
Unfortunately, we cannot specify multiple spreadsheets or multiple sheets via the IMPORTRANGE function. The function works with only one range from a single source specified.В
Nonetheless, there are workarounds. A simple workaround involves using a pair of braces with a set of items separated by a comma or a semicolon. The comma would put the ranges side-by-side:
while the semicolon would put them on a single column:
If we want to import multiple sheets from a single spreadsheet, we add an IMPORTRANGE function for each of the sheets, specifying their respective ranges, while specifying a single URL for all of them:
Written by Laura Tennyson
Google Sheets has transformed the way many of us work with spreadsheets. Instead of working in private, offline files, we can now collaborate with co-workers in real-time, in shared sheets. Emailing attachments and checking file versions are no longer necessary thanks to the cloud. And with powerful formulas like Importhtml you can pull live data directly into your spreadsheet from the internet, allowing you to create dashboards and charts. When you want to import Google Sheets data into another spreadsheet, however, the inbuilt functions are somewhat limited.
Google Sheets Importrange formula allows you to import a range of cells from one sheet to another and it’s great for one-off data transfers. But if you’ve ever tried to process large datasets or multiple imports, you’ll know that Importrange can struggle, leaving you with a bunch of error messages and a frozen spreadsheet.
If you want a simpler and more reliable way to import Google Sheets data into another Google Sheets file (or Excel file) you can connect your files and transfer data between them automatically. This enables you to:
- Import data from one Google Sheets file into another
- Pull data from Google Sheets into Excel
- Share data with colleagues — without sharing your spreadsheet
- Save time on copy-pasting and manual work
- Combine data from multiple Google Sheets into one
- Generate automated reports and dashboards
- Control data access
How to import Google Sheets data
Step 1: Open Sheetgo
Click on the blue button below to open Sheetgo.
Log in and click +Create workflow > Connect.
At the top of the screen, give the Untitled workflow a name so you can identify and edit the workflow later.
VLOOKUP is a powerful tool to check the data in your spreadsheets. However, when you want to pull an item from a worksheet in another Workbook, you might run into problems. Luckily, you can use the IMPORTRANGE function in Google Sheets to solve this issue.
In this article, we’ll show you how to combine IMPORTRANGE with VLOOKUP. Plus, we’ll also provide additional tips that might help enhance your experience.
Using IMPORTRANGE – Step by Step Guide
For the purposes of this article, we’ll use two workbooks from the computer hardware shop. Our goal is to pull the price data from the workbook “Stock 2” into “Stock 1”.
- Start by copying the key from the “Stock 2” workbook URL. You only need the part that is between “/d/” and “/edit.” You can see it highlighted in the picture above.
- Now you can use the IMPORTRANGE function to connect “Stock 1” and “Stock 2” workbooks. As you want to import data from “Stock 2”, go into “Stock 1”, click on any field and implement the formula:
IMPORTRANGE (spreadsheet_key, range_string)
In our example, the formula is:
Note that when the name of the name sheet contains more than a single word, you’ll need to use single quotes.
After using the formula, you may need to wait before the data from “Stock 2 “loads. Once it’s done, you’ll receive the message, “You need to connect these sheets“. Now, click on “Allow access” to finish the process.
As workbooks are now connected, we can finally use VLOOKUP, by combining it with IMPORTRANGE. Delete the data in B2, and use this formula:
What we implemented in our formula are range and index, while we used “0” for “is_sorted” syntax. Also, we didn’t have to define our range strictly. Instead of “A2:D8”, we could’ve used “A2:D”. That way, VLOOKUP checks every field in the “D” column. This solution is useful if you plan to add more data, as you won’t have to change the formula in “Stock 1”.
Enhance VLOOKUP and IMPORTRANGE Experience
When to Use “Issorted” Syntax?
The Is_Sorted syntax is FALSE by default, and we’re leaving it that way in our example. When using FALSE, Vlookup will search for exact matches. If there’s more than one correct value, it will use the first one. FALSE should be used if you don’t need sorting.
If the columns need to be sorted, for example, from smallest to the largest value, then you should use TRUE in your formula. In this case, Vlookup will try to find the closest value if there’s no exact match. If the closest value is bigger than search_key, the result won’t appear.
Use Wildcards with Vlookup in Google Sheets
We didn’t have that problem in our example, but sometimes you might not know the entire search key. Luckily, wildcards will assist you. It would help if you used Asterisk (*) when matching sequences of characters while the question mark (?) matches a single character.
Override VLOOKUP Limitation with Index Match Formula
Both in Excel and Google Sheets, VLOOKUP can’t look at its left and will return an error if the first column isn’t the search column. Use the following formula when needed
INDEX (return_range, MATCH(search_key, lookup_range, 0))
Index Match references the return column directly so that structural changes won’t affect it, unlike VLOOKUP. When you remove or insert a column inside, the Vlookup formula will become invalid, while Index Match won’t be affected. This function also works in Excel, but the arguments have different names.
VLOOKUP and IMPORTRANGE in Google Sheets and Excel: The Differences
Using VLOOKUP and IMPORTRANGE is almost the same in Google Sheets and Excel. However, there are four key differences:
While they work in the same way, formulas are defined differently in both apps. Google Sheets are considered more intuitive.
In Google Sheets, you’ll see the return value as you type formula. It will appear in the white box above the formula.
You can use wild characters with VLOOKUP only in Google Sheets.
In Google Sheets the overhead bar matches the color of the formula, making it easier to identify.
IMPORTRANGE to the Rescue
Using IMPORTRANGE with VLOOKUP gives you endless possibilities to combine the data in Google Sheets’ workbooks. As you’ve hopefully seen, formulas are intuitive, while the implementation of wildcard characters is better than in Excel. The only thing you might miss when working with a few workbooks is the side-by-side view that’s only in Microsoft Excel.
Do you often need to pull the data from a few workbooks at the same time? Have you used IMPORTRANGE in other spreadsheets? Let us know in the comments section below.
When it comes to analyzing data at work, we often have to pull information from several different sources in order to see the big picture. But if you need to manage product inventory or report on quarterly sales figures, spending time flipping between spreadsheets or manually copying and pasting data into one place can take up too much time. There’s a better option.
With G Suite—Google’s suite of cloud-based collaboration and productivity apps—there are a ton of ways you can skip repetitive tasks, including in Google Sheets.
Today, we’re kicking off a tips series to help you become a G Suite pro—starting with how to combine data from one spreadsheet into another. Check it out.
Combining data from two Google Sheets in four steps
With Sheets, it’s easy to combine data into one spreadsheet to create a single source of truth.
Step 1: Identify the spreadsheets you want to combine.
Pull up the two spreadsheets that you’d like to import data between. You should have the original spreadsheet (Ex: “Sales Revenue”) and the one you want to add information into (Ex: “Product Inventory”).
Step 2: Grab two things from the original sheet.
You need two pieces of information from the original spreadsheet in order to move the data: the spreadsheet URL and the range of cells where you want to pull the data from. In this example, our original spreadsheet’s name is “Sales Revenue.”
First, highlight and copy the full spreadsheet URL from the original spreadsheet (Note: you can also use the spreadsheet “key,” which is a code hidden inside the URL between the “d/” and “/edit.” It looks like a jumbled mix of letters and numbers.).
Next, before you switch to the new spreadsheet, make sure to note the range of cells where you want to pull the data from in the original spreadsheet. For example, A:1 to C:10.
Step 3: Use a Google Sheets function to port your data over.
Now we use the IMPORTRANGE function. First, click into the new spreadsheet where you’d like to add data into. In this example, it’s named “Product Inventory.” Insert columns or rows into the spreadsheet where you want to put data.
Next, type =IMPORTRANGE in the cell (you can choose to use all caps or not, it doesn’t matter.). The function will then ask you for three things:
- The URL of the original spreadsheet (or the spreadsheet key, both options work.)
- The name of the specific tab in your spreadsheet that you’re pulling information out of
- The range of cells for data you need
It will look similar to this:
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RNez4bhTMt_evAdHrFOBHeBgk1l5HAWVTb43EKpYHR8/edit#gid=0″,”Sales Revenue by Quarter!A1:C10”)
It’s important to note that you have to use the specific name of the tab in the sheet in the formula. So for this example, the name of the original spreadsheet housing multiple datasets is called “Sales Revenue,” but the name of the specific tab with our data in it is called “Sales Revenue by Quarter.” We want to use the specific tab’s name to avoid our function breaking in the future when new sheets or tabs are created.
Oh and another trick: don’t forget to add the exclamation point (!) before the data range. That’s important, too.
Step 4: Import your data.
After you’ve added your IMPORTRANGE formula, you can click enter.
If it’s the first time you’ve imported data from that particular spreadsheet, a pop-up might appear. Don’t worry! This security check makes sure you’re okay with granting any collaborators on this spreadsheet access to data that lives in another spreadsheet. It will ask you to “Allow access” when you see the #REF in your cell. Go ahead and click yes.
Voilà! Your data will appear in the new spreadsheet.
Last Updated on March 10, 2021 by Mark Ursell
This article shows you how to import live data from Google Sheets into Excel.
- Google Sheets or Excel – Which is Better?
- Google Sheets
- Why Connect Excel and Google Sheets to get price data?
- Connecting Sheets and Excel
- Video Tutorial – Import live data from Google Sheets into Excel
- Create Your Google Sheet
- Publish Your Google Sheet to the Web
- Import the Data Into Excel
- The Next Step: Backtest Your Strategy
Google Sheets or Excel – Which is Better?
Firstly, let’s have a look at the strengths and weaknesses of Excel and Google Sheets.
I use both Google Sheets and Excel almost every day. They are both powerful productivity tools that almost everyone is familiar with. They both have an extensive eco-system of tools and plugins that combine with them.
But I am only interested in the parts that are directly useful to me. And in this article, I am going to show you how to get the best of both worlds by linking Google Sheets with Excel.
I use Google Sheets to run my live Trading Strategies.
- A purely web-based system. It is permanently connected to the internet with direct access to the Google Finance database.
- Google Finance has a large amount of financial data available. You can get both historical and real-time live data using formulas.
- Google Sheets has an ever-growing number of plugins that you can use to improve the basic service.
- You can program your trading strategies into Sheets and then check your positions, trading signals and P/L from your phone when you are away from your desk.
I use desktop Excel for Backtesting My Trading Strategies and Market Analysis.
- Excel is desktop based. It can handle very large files and is often faster than Sheets which runs in your browser.
- You can work on files offlinem which can be an advantage for backtesting.
- Excel has a lot of different plugins that can improve the basic features.
- Excel has a greater number of functions and more overall functionality than Sheets.
Why Connect Excel and Google Sheets to get price data?
Connecting Sheets and Excel
As a trader, I love using the =GOOGLEFINANCE function. It gives me access to a vast amount of price, volume and other financial data.
This data is continuously updating, and I want to get the latest prices into my Excel spreadsheets.
If you want to learn more about how I use Google Sheets to manage my trading strategies, check out this article: How to Store Live Price Data In Google Sheets