- Click the cell that contains the data you want to link to, and then click Copy on the Standard toolbar.
- Click the cell you want to link from, and then click Paste .
- Click Paste Options and then click Link Cells.
Create a link between cells in different workbooks
- Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).
- In the destination workbook, click Save .
- Select a cell or cells you want to link from.
- If you are creating a new formula, type = (an equal sign).
If you are entering the link elsewhere in the formula, type the operator or function that you want to precede the link.
-
On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.
- Select the cells you want to link to.
- Complete the formula. When you finish entering the formula, press ENTER.
Link or embed content from another program using Object Linking and Embedding (OLE)
OLE (Object Linking and Embedding) is supported by many different programs, and is used to make content created in one program available in another. For example, you can insert a Microsoft Word document into Microsoft Excel. To see what types of content you can insert, click Object on the Insert menu. Only programs that are installed on your computer and that support OLE objects appear in the Object type box.
The main differences between linked objects and embedded objects are where the data is stored and how the object is updated after you place it in the destination file.
Embedded objects are stored in the workbook they are inserted in, and are not updated. Linked objects remain as separate files, and need to be updated.
Linked and embedded objects in a document
Create a link to an existing document
-
Click in the worksheet where you want to place the linked object.
-
On the Insert menu, click Object.
-
Click the Create from File tab.
-
In the File name box, type the name of the file, or click Browse to select from a list.
-
Select the Link to file check box.
-
To display the content, clear the Display as icon check box.
If you want to display as an icon, select the Display as icon check box.
Note You cannot use the Object command on the Insert menu to insert graphics and certain types of files. To insert a graphic, point to Picture on the Insert menu, and then click From File.
-
Click in the worksheet where you want to place the embedded object.
-
On the Insert menu, click Object.
-
If the document does not already exist, click the Create New tab. In the Object type box, click the type of object you want to create.
If the document already exists, click the Create from File tab. In the File name box, type the name of the file, or click Browse to select from a list.
-
Clear the Link to file check box.
-
To display the content, clear the Display as icon check box.
To display as an icon, select the Display as icon check box.
Insert part of an existing document as a linked or embedded object
-
From a program other than Microsoft Excel, select the information you want to copy as a linked or embedded object.
-
Click Copy .
-
Switch to the worksheet you want to place the information in, and then click where you want the information to appear.
-
On the Edit menu, click Paste Special.
-
To paste the information as a linked object, click Paste link.
To paste the information as an embedded object, click Paste. In the As box, click the entry with the word "object" in its name. For example, if you copied the information from a Microsoft Word document, click Microsoft Word Document Object.
Note You can't paste worksheet cells or a chart as a linked or embedded object in a Microsoft Excel workbook. However, you can use the Copy Picture command (hold down SHIFT and click the Edit menu) to create a link to cells on this or another sheet
Create a reference between cells on the same or different worksheets
The following formulas contain relative references to and names of other cells. The cell that contains the formula is known as a dependent cell when its value depends on the values in other cells. For example, cell B2 is a dependent cell if it contains the formula =C2.
Example formula | What it does |
---|---|
=C2 | Uses the value in the cell C2 |
=Sheet2!B2 | Uses the value in cell B2 on Sheet2 |
=Asset-Liability | Subtracts a cell named Liability from a cell named Asset |
- Click the cell in which you want to enter the formula.
- In the formula bar , type = (equal sign).
- Do one of the following:
- To create a reference, select a cell, a range of cells, a location in another worksheet, or a location in another workbook. You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.
- To create a reference to a named range, press F3, select the name in the Paste name box, and click OK.
- Press ENTER.
Link to a name in another workbook
You can create descriptive names to represent a cell or range of cells in another workbook.
Link to a defined name in another workbook
-
Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).
-
In the destination workbook, click Save .
-
Select a cell or cells you want to link from. If you are creating a new formula, type = (an equal sign).
If you are entering the link elsewhere in the formula, type the operator or function that you want to precede the link.
-
On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.
-
Press F3, and select the name you want to link to.
Define a name that refers to cells in another workbook
-
Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the source workbook).
-
In the destination workbook, point to Name on the Insert menu, and then click Define.
-
In the Names in workbook box, enter the name for the link.
-
Delete the contents of the Refers to box, and keep the cursor in the box.
If the name is a formula, enter the formula, and position the cursor where you want the link. For example, type =SUM(), and position the cursor between the parenthesis.
-
On the Window menu, click the name of the source workbook, and then click the worksheet that contains the cells you want to link to.
-
Select the cell or range of cells you want to link to.
-
In the Define Name dialog box, click Add.