How to make References to other files in Excel with formula

When calculating data in Excel, you may often find yourself in a situation when you need to pull data from another worksheet or even from a different Excel file. Can you do that? Of course, you can. You just need to create a link between the worksheets (within the same workbook or in different workbooks) by using what is called an external cell reference or a link.

External reference in Excel is a reference to a cell or a range of cells outside the current worksheet. The main benefit of using an Excel external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.

How to Reference to Other Files in Excel Using Cell References

You can pull data from one Excel sheet to another by using the relevant cell references. This is a simple way to get data from one sheet into another.

To pull data from another sheet by using cell references in Excel:

1. Click in the cell where you want the pulled data to appear.

2. Type =(equals sign) followed by the name of the sheet you want to pull data from. If the name of the sheet is more than one word, enclose the sheet name in single quotes.

3. Type! followed by the cell reference of the cell you want to pull.

4. Press Enter.

5.The value from your other sheet will now appear in the cell

6. If you want to pull across more values, select the cell and hold the small square in the bottom-right corner of the cell

7. Drag down to fill the remaining cell

There is an alternative method that saves you from having to type in the cell references manually.

To reference data from other files without typing the cell reference manually:

1. Click in the cell where you want the pulled data to appear.

2. Type = (equals sign) and then open the sheet from which you want to pull data.

3. Click on the cell containing the data that you want to pull across. You’ll see the formula change to include the reference to this cell.

4. select a cell in Excel

5. Press Enter and the data will be pulled into your cell.

Referencing to Other Files Workbook in Excel

external references to another workbook are displayed in two ways, depending on whether the source workbook is open or closed.

1. External reference to an open workbook

When the source workbook is open, an Excel external reference includes the workbook name in square brackets (including the file extension), followed by the sheet name, exclamation point (!), and the referenced cell or a range of cells. In other words, you use the following reference format for an open workbook reference:

[Workbook_name]Sheet_name!Cell_address]

For example, here's an external reference to cells B2:B5 on sheet Jan in the workbook named Sales.xlsx:

[Sales.xlsx]Jan!B2:B5

If you want, say, to calculate the sum of those cells, the formula with the workbook reference would look as follows:

=SUM([Sales.xlsx]Jan!B2:B5)

2. External reference to a closed workbook

When you reference another workbook in Excel, that other workbook does not necessarily need to be open. If the source workbook is closed, you must add the entire path to your external reference.

For example, to add up cells B2:B5 in the Jan sheet from Sales.xlsx workbook that resides within the Reports folder on drive D, you write the following formula:

=SUM(D:\Reports\[Sales.xlsx]Jan!B2:B5)

As is the case with creating an Excel formula that references another sheet, you don't have to type a reference to a different workbook manually. Just switch to the other workbook when entering your formula, and select a cell or a range of cells you want to refer to. Microsoft Excel will take care of the rest:

Did you learn how to reference other files in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.