In Microsoft Office Excel, it is possible to use data not only from an open worksheet, but also to use data from other Excel files. In addition, if there are several Excel files with different information, for the convenience of organizing and analyzing information, you can create a separate document that will generalize and make the necessary calculations with the information of these documents.
For this purpose, Excel links to cells in another file are used, which represent the address of a cell or range of cells in another worksheet.
Link Types
The type of link depends on what values the link will indicate when moving it:
- A relative link shows the value of a cell that is at the same distance from itself. When moving such a link one column to the right, its value will correspond to the value of the cell located one column to the right of the original one. Microsoft Office Excel usually uses this type of link by default .
- An absolute reference shows the value of the same cell in the source data. It is created by some adjustment of the finished formula. To do this, the $ symbol is set in the link before the column and row number. If the dollar symbol is set only before the column / row number, then with the movement of the link its values will change within the specified column / row of the source data.
- A mixed link includes both ways of using the source data.
When creating an Excel link to a cell in another file, use the name to refer to the cells used. Using the name makes it easier to use the values of the required cells. Such links do not change when moving the source data or the links themselves, since the name refers to a specific range of cells.
The process of creating a cell reference in another file
How is it going?
- We open the source Excel file in which the work is carried out, and the second, the data of which must be used in the current file.
- In the source, select the cell in which you want to create a link, that is, use the values of another document.
- Enter the equal sign in the cell. If it is necessary to perform calculations with the link data, then a function or other values are introduced that must precede the link value.
- Go to the second file with the data we need, select the sheet of the document containing the cells that you want to reference.
- Select the cell or group of cells to the data of which you want to create a link.
- If the formula involves the introduction of additional functions or data, then go to the original document and supplement the formula, after which we complete the operation by pressing Ctrl + Shift + Enter.
- In the source file we look through the final formula, if necessary, adjust it, and then press Ctrl + Shift + Enter again.
Appearance of Excel cell link in another file
What does it look like?
- Link to the value of one cell: = [Source.xls] Sheet1! A1
- Link to cell range value: = [Source.xls] Sheet1! A1: B5
They will have this appearance if the file whose data is used for the link is opened during operation.
In order to use the data of the file that is currently closed, the full path to the file must be specified in the link:
= D: \ Folder \ [Source.xls] Sheet1! A1
Updating data in a file
If both documents used are open, and the source values of the cells that are used in the Excel link to the cell in another file are changed, the data is automatically replaced and, accordingly, the formulas are recalculated in the current file containing the link.
If the data in the cells of the source file changes when the document with the link is closed, then the next time you open this file, the program will display a message asking you to update the links. You should agree and select the "Update" item. Thus, when opening a document, the link uses the updated data of the source file, and the recalculation of formulas with its use will occur automatically.
In addition to creating links to cells in another file, Excel also implements many different features that make the process much more convenient and faster.