IMPORTRANGE formula: an easy guide

Introduced in version 8.1 of ONLYOFFICE Docs, the IMPORTRANGE function is a tool for data integration. This feature allows you to import a range of cells from one Excel sheet to another, facilitating data merging and centralized reporting. Let’s take a closer look at the components of this feature and how to apply it.

What is IMPORTRANGE?

IMPORTRANGE function is one of the text and data functions. It allows you to pull data from one spreadsheet into another using a simple formula. It is particularly useful for collaborative projects, data analysis, and creating dashboards that require data from various sources.

Examples of using the IMPORTRANGE formula

IMPORTRANGE can significantly streamline your workflow by aggregating data from different spreadsheets into a single, centralized document.

Market research data aggregation. You are working on market research data, where different departments manage separate spreadsheets for various metrics such as customer satisfaction, market trends, and competitor analysis. Using IMPORTRANGE, you can easily consolidate all these metrics into a single dashboard, allowing for comprehensive analysis and strategic decision-making.

Financial reports. Consider generating monthly financial reports where each department maintains its own spreadsheet. With IMPORTRANGE, you can import financial data from each department into a master financial report, making the creation of consolidated financial statements seamless and efficient, thus aiding quick financial analysis.

Inventory management across locations. Each warehouse might have its own inventory spreadsheet. IMPORTRANGE enables you to consolidate inventory data from all these locations into a single central spreadsheet, providing a unified view of stock levels and facilitating better inventory management and replenishment planning.

Academic performance tracking. Teachers often maintain separate grade sheets for different subjects. With IMPORTRANGE, you can combine grades from various subjects into a single report card for each student. This simplifies the tracking of academic performance across multiple subjects and helps to generate comprehensive student reports efficiently.

Syntax

The syntax of the IMPORTRANGE function is simple and looks as follows:

IMPORTRANGE(“spreadsheet_url”, “range_string”)

Where the arguments correspond to:

• spreadsheet_url: The URL of the spreadsheet the data is imported from. It should be enclosed in quotation marks.
• range_string: The range of cells you want to import. Also enclosed in quotation marks.

Now let’s discover how to apply the IMPORTRANGE formula in Excel sheets using the ONLYOFFICE Spreadsheet Editor.

How to add the IMPORTRANGE formula in Excel sheets

Let’s explore the scenario of using a formula to centralize and analyze sales data.

You are responsible for managing sales data from several regional offices. Each office maintains its own sales spreadsheet, and you need to combine this data into a central dashboard to analyze overall sales performance and track key metrics.

1. Prepare your Excel file

Open your sales report spreadsheet or create a new one. Here, we will reflect data from separate reports for each region.

2. Add the IMPORTRANGE formula

Click on the cell or group of cells where you want to import data from another sheet.

Go to the Formula tab on the top toolbar -> Text & Data -> IMPORTRANGE. Alternatively, click Function and enter the formula name in the search bar.

3. Enter the formula arguments

In the window that appears, in spreadsheet_url enter a link to the sales data file for the North region.

Note that when importing data from your Excel sheet into another one, sometimes you may need to authorize access permissions. Otherwise, you will see the error #REF!

In range_string, select the cell or range of cells to import.

In our case, the formula is following:

=IMPORTRANGE(“https://example”,”D12″)

Once you have entered the arguments, you can check the Function result. If the result is not displayed or gives an error, check that the arguments you entered are correct.

If the result is correct, click OK to add the IMPORTRANGE formula to the Excel sheet.

Now, data from the North region sales report is reflected in your sales report.

4. Check how the formula works

Now, the data is visible in your report. However, colleagues can change the data in the source table. Let’s see how it will work.

The cell from which data is imported into the overall sales report contains the sum of the total number of transactions.

Therefore, as soon as colleagues in the sales department change the number of transactions in at least one row, the total amount will automatically change.

The new data will automatically appear in your sales report.

This example demonstrates how to use IMPORTRANGE to import sales data from multiple spreadsheets into a general sales report for further analysis. Customize the formulas and links to suit your specific data and requirements.

Add IMPORTRANGE formula with ONLYOFFICE Spreadsheet Editor

Try importing data from other Excel sheets in the ONLYOFFICE editors, format the imported data according to your requests, analyze it with conditional formatting, pivot tables, and filters, and use the AI assistant.

TRY ONLINE NOW

Discover more new features of ONLYOFFICE Docs v8.1: