It’s not uncommon for project managers to have multiple tabs open and flip between project boards, budgets, and timelines. There’s a lot of information to keep track of, and data is invaluable when it comes to determining performance targets and measuring success.
And, if you’re like me, you likely rely on Google Sheets to do a lot of math for you. Why waste time trying to crunch numbers when there are tools to calculate for you? There are countless Google Sheets formulas you can use. However, to use them successfully, you need to understand exactly what you need to do, and the best way to do it.
In this article we’re looking at the IMPORTRANGE function and how it can help you work faster and smarter.
What is IMPORTRANGE?
IMPORTRANGE is a function in Google Sheets (it does not exist in Excel) that allows you to import data from one spreadsheet to another. That’s right, you can easily consolidate information into one spreadsheet instead of flipping back and forth between others.
The syntax for IMPORTRANGE is: =IMPORTRANGE(spreadsheet_url, range_string)
- IMPORTRANGE: This is the specific function – importing a range of data.
- spreadsheet_url: This points to the URL of the spreadsheet where you’re importing data from.
- range_string: This specifies the range of data you’re importing.
4 steps to use IMPORTRANGE successfully
Before you begin, ensure you have access to each spreadsheet (check your permissions), and double-check the specific data ranges you want to import. Once everything is confirmed, follow these steps:
Step 1: Open your new spreadsheet and click in the top left cell – this is where the data will be imported to. To begin start typing the function =IMPORTRANGE (it will also appear in the drop-down menu of available functions, so you can select it from there too).
Step 2: Copy the full URL of the spreadsheet that you’re importing data from. If you want the formula to be shorter, you can copy just the spreadsheet key. Remember to keep the spreadsheet destination in quotations.
Spreadsheet Key: 1DVwrdD3ZRzuT0zE5P_QChDbuvQ6_dWFL6j-eLoooMzE At this stage your formula would look like this: =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1s-eZFADNQfTOCpNSiwL7c9Lngn_byzwF4JKS53Xzl-w/”, range_string)
Step 3: Insert the range string for the data you want to import. This also needs to be in quotations.
Example: “B2:B13” At this stage your formula would look like this: =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1s-eZFADNQfTOCpNSiwL7c9Lngn_byzwF4JKS53Xzl-w/”, “B2:B13”)
Step 4: Finish your formula by adding a closing parenthesis and hit enter. If this is the first time you’re importing data between the two sheets, you will get a #REF error like the one below.
Click “Allow access” and your data will be imported.
The import will happen immediately; there is no wait time, but if it’s a large amount of information it may take a few seconds.
Common IMPORTRANGE errors
There are two common parse errors users come across when using IMPORTRANGE. Thankfully, both are easy to troubleshoot.
#ERROR!: This is the most common parse error and indicates an issue with the formula’s syntax. This includes forgetting quotations around ranges or missing a closing parenthesis, so double-check that everything is formatted properly.
#REF!: As we mentioned above, you will see this parse error the first time you import data. If that’s the case a box will appear asking you to “Allow access” and once you click it, everything will load as expected. However, you will also see this if you try to manipulate or format any cells that you’re importing – you cannot do this with the IMPORTRANGE function. If that’s the case, delete your formula and redo it with everything as-is. If you want to format anything, you can’t use the IMPORTRANGE formula.
The benefits of using IMPORTRANGE
There are countless scenarios for when you should use the IMPORTRANGE function. When exactly should you use it? It really comes down to what type of work you’re doing, and how much time you want to save.
Some of the benefits of using IMPORTRANGE include:
- You save time by streamlining data imports.
- It’s a quick and simple way to compare data.
- You can easily import select rows of data from a larger private file to a shareable document without compromising information.
- The function doesn’t require any browser extensions, Google Sheets integrations, or third-party apps to allow for data imports.
- There’s no equivalent function in Excel, so IMPORTRANGE is an invaluable tool to use exclusively in Google Sheets.
- You get results instantaneously.
- It reduces the risk of human error.
3 important Google Sheets functions to use alongside IMPORTRANGE
There are several other helpful formulas you can use in Google Sheets to improve your workflow when dealing with large amounts of. Let’s look at some of our favorites.
COUNTIF will show how many cells in a specific range meet the exact criteria you’re searching for. Instead of scrolling through rows and rows of data trying to find something, you can use this formula.
The syntax for COUNTIF is =COUNTIF(range, criterion)
You can use this to find sums above or below a certain amount: =COUNTIF(A1:A500, “<500”)
Or, find cells containing specific text: =COUNTIF(B1:B50, “July”)
VLOOKUP is one of the most helpful formulas to use when sifting through data. You can use it to find information that’s related, such as a specific staff member and their salary, or the number of sales a specific team made in the month of September. In short, it easily compares data points.
The syntax for VLOOKUP is =VLOOKUP(search_key, range, index, [is_sorted])
The SPLIT function is great to use if you’re working with sheets full of text. It allows you to easily separate text based on a specific character or string and will fragment the results in separate cells in the same row.
The syntax for SPLIT is =SPLIT(text, delimiter, [split_by_each], [remove_empty_text])
Formulas in Google Sheets may seem overwhelming at first, especially if you’ve never used them before. However, they’re invaluable tools that you should get comfortable with.
Once you understand their power, you’ll be able to work much faster and smarter, and soon enough others will come asking you for help with their data sheets!