How to Use the IMPORTRANGE Function in Google Sheets

Have you ever needed to reference a range of cells in another Google Sheets file? Look no further! In this article, we’ll explore how to easily connect two files and keep imported information up to date using the IMPORTRANGE function in Google Sheets.

Using the IMPORTRANGE formula in Google Sheets

Here are the steps to use the IMPORTRANGE formula in Google Sheets:

1. Type “IMPORTRANGE” in a cell or go to the Insert tab (or the Functions icon) -> Function -> Web -> IMPORTRANGE.
2. Open the Google Sheets file you want to reference.
3. Copy the file’s URL.
4. Insert the URL into the formula, enclosing it in quotation marks.
5. Type the sheet name and cell range into the formula, also in quotation marks.
6. When you see “#REF” in the cell and a note saying “You need to connect these sheets,” click on Allow Access in the note.

The general syntax of the formula is as follows:

URL_du_fichier: You need to insert a URL in this argument. The URL should be enclosed in quotation marks, like this: “URL”.

Plage_de_cellules: This argument requires the sheet name and the cell range. Both should be combined with “!”. You can also use named ranges instead of row and column indexes. For example, if you want to reference the cell range B2:F17 in a sheet called “Exemple”, the argument should be “Exemple!B2:F17”.

Let’s take a more concrete example. Suppose you want to reference data with the following assumptions:

URL: https://docs.google.com/spreadsheets/d/1BmOfn6fpSZFQ-Ld2Ec65e8ZKX9ccFWggfWov5u37TNQ/edit#gid=2029313979

Feuille: Consolidated P&L Dashboard

Plage: A16:F24

With these assumptions, the IMPORTRANGE formula would look like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BmOfn6fpSZFQ-Ld2Ec65e8ZKX9ccFWggfWov5u37TNQ/edit#gid=2029313979", "Consolidated P&L Dashboard!A16:F24")

Once you’ve allowed access and waited for the formula to load, you can see the imported cell range values, as shown in the image below:

Here are a few key points to note:

1. Leave enough space for the formula to expand the imported values.
2. The formula imports values without formatting, row height, or column width. So, if needed, you’ll have to copy and paste the formatting from the source file.
3. If an update is made in the selected cell range in the source file, the IMPORTRANGE function immediately reflects the change, ensuring that the imported information is always up to date.

Now that you know how to use the IMPORTRANGE function in Google Sheets, you can easily connect and update information between multiple files. If you want to explore more advanced features of Google Sheets, don’t hesitate to visit Crawlan.com.

Now it’s your turn to play and explore all the possibilities offered by Google Sheets!