Understanding IMPORTRANGE in Google Sheets

A spreadsheet’s power comes from its ability to perform calculations that reference many different pieces of data within the spreadsheet.

While you can easily reference values from different cells and sheets with cell reference, what if you need data that is housed in a completely different spreadsheet?

With Google Sheets’ IMPORTRANGE formula, you can import data across multiple spreadsheets.

Or as Google succinctly explains, the IMPORTRANGE formula “imports a range of cells from a specified spreadsheet.”

Also read: Easily Import Your Financial Data from Tiller Into Another Google Sheet 

How to Use the IMPORTRANGE Formula

The formula for IMPORTRANGE is as follows:

=IMPORTRANGE("spreadsheet_url", "range_string")

The first parameter – spreadsheet_url – is found in the search bar of your browser.

Simply copy the url, and paste it into the IMPORTRANGE function:

The second parameter – range_string – is the data you want to bring in from another spreadsheet.

1. Go to the sheet with the data you wish to export
2. Note the name of the sheet and the individual cells you want to import.

To correctly reference this data, use the following syntax:

"Name_of_the_sheet!cell_ID"

The first time you use the IMPORTRANGE function in a particular spreadsheet, you will get a #REF! error prompting you to give permission to access the referenced spreadsheet.

About Granting Access With IMPORTRANGE

• Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE.
• The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission.
• Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet.
• The access remains in effect until the user who granted access is removed from the source.

IMPORTRANGE and Your Budget

IMPORTRANGE can be particularly helpful with budgeting. For example, it can be used to:

• plan income and expenses for an entire year
• create a cash flow report to account for periodic expenses (insurance premiums, birthday gifts, and back-to-school shopping)
• track periodic income (work bonuses, tax refunds, and side hustle income) that you might otherwise forget to include in your budget

By adding columns for your budgeted cash flow and actual cash flow, you can see how closely you adhere to your budget.

Instead of manually transferring budgeted and actual data from your budget spreadsheet to your cash flow report, you can use IMPORTRANGE.

However, there are a couple of steps you must take to organize your data in a way that the IMPORTRANGE formula can use. Using the Tiller Budget template as an example:

1 – Unhide the Budgets History sheet

A Tiller spreadsheet contains a hidden sheet called Budgets History. To unhide it, go to “View” → “Hidden Sheets” → Budgets History.

This sheet lists your budgeted and actual income and expenses for each budget period.

2 – Transpose the data

The data in the Budgets History spreadsheet is horizontal, but you need it to be vertical to import it into a vertical range in your Cash Flow Report. To change the orientation of the data, copy the cells with the budget category names and budget data you need.

Then open a new tab and choose “Edit” → “Paste special” → “Paste transposed” to reorient the data.

3 – Import your data

Use the IMPORTRANGE formula to bring in the data:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qv9Lj5g-cuEqUPXXa30-hDHiowWlUvAKU2gzwxmnSHc/edit#gid=146353117","Sheet1!B1:B3")

If you import at the end of every budget period, you’ll be able to foresee potential cash flow shortfalls, allowing you to adjust your budget before your cash flow becomes an issue.

Other Budget Applications

Using IMPORTRANGE to update a cash flow report is just one of many budget applications:

1. If you and your significant other split your finances, use IMPORTRANGE to combine your budgets and analyze your household income and expenses.
2. If you need to manually import bank data into your Tiller spreadsheet, you can use IMPORTRANGE rather than copying and pasting each column.
3. If you’re new to Tiller, but you’ve been using a spreadsheet to track your budget, use IMPORTRANGE to bring your budget data into your Tiller spreadsheet.

Regardless of your particular use of IMPORTRANGE, you will save a bunch of time otherwise spent copying and pasting or manually entering data from multiple spreadsheets.