IMPORTRANGE in Google Sheets - Simplified Explanation with Video Tutorial!
IMPORTRANGE is a powerful formula that is designed to automatically sync data from one spreadsheet to another spreadsheet.
Official Formula:
=IMPORTRANGE(spreadsheet_url, range_string)
Formula Explained:
- The formula itself is IMPORTRANGE.
- The spreadsheet URL (website) is the URL you want to pull the data FROM.
- The Range String is the name of the Tab of the spreadsheet you're pulling the data FROM followed by an "!" then the range you want to sync.
For example: Let's say you want to sync data from cells A1 to N30 from the "Data" tab in a spreadsheet called "Data Sync" that has a URL of "https://docs.google.com/spreadsheets/d/1w4MoZ-Yf5DnAHfU0DFYHhzuWBSd3iaZwBNvc2y3hAgL/edit".
You'll use the following formula:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1w4MoZ-Yf5DnAHfU0DFYHhzuWBSd3iaZwBNvc2y3hAgL/edit","Data!A1:N30")
Notice that the name of the spreadsheet doesn't matter. Only the URL, Tab Name, & Range.
Use Case idea's:
- Let's say you work in Human Resources and have a master roster showing which employees are active, inactive, department, etc. Instead of having a roster on each spreadsheet you work work on, you can simplify maintenance by using a single master roster and using IMPORTRANGE to bring live data in whichever spreadsheet you're working with.
- Let's say you work with the Customer Success Team where they log churn risk in a spreadsheet to provide additional context for weekly meetings. Your project is to analyze churn reasons to identify area's of improvement. Instead of having to do the analysis in the live spreadsheet that everyone has access to, you can use IMPORTRANGE to bring the live data into the spreadsheet where you're completing your analysis.
- Let's say you're a Manager. Your team has individual task trackers and you want to compile all of the individual task trackers into a single team task tracker view. You can use IMPORTRANGE coupled with VSTACK & QUERY to compile the individual task trackers into a team tracker.
Pro Tip!
Instead of typing out the URL and/or the range, you can point to a cell that contains those values.
For example:
- A6 = https://docs.google.com/spreadsheets/d/1w4MoZ-Yf5DnAHfU0DFYHhzuWBSd3iaZwBNvc2y3hAgL/edit
- B6 = Data!A1:N30
The new formula would be =IMPORTRANGE(A6,B6). This formula will work the same exact way as the example above since the values match identically. It's just A LOT cleaner and easier to maintain.