Paste Links for Data, Charts, and Formulas in Excel, Word, PowerPoint

01
of 02

Paste Links Between Excel and Word Files

Link Files in MS Excel and Word with Past Link
Link Files in MS Excel and Word with Past Link. © Ted French

Pasting Links Overview

In addition to simply copying and pasting data from one Excel file to another or to a Microsoft Word file, you can also create a link between two files or workbooks that will update the copied data in the second file if the original data changes.

It is also possible to create a link between a chart located in an Excel workbook and a PowerPoint slide or Word document.

An example is shown in the image above where data from an Excel file has been linked to a Word document that could be used in a report.

In the example, the data is pasted into the document as a table, which can then be formatted using all of Word's formatting features.

This link is created by using the paste link option. For paste link operations, the file containing the original data is known as the source file and the second file or workbook containing the link formula is the destination file.

Linking Single Cells in Excel with a Formula

Links can also be created between individual cells in separate Excel workbooks using a formula. This method can be used to create a live link for formulas or data, but it only works for single cells.

  1. Click on the cell in the destination workbook where the data is to be displayed;
  2. Press the equal sign ( = ) on the keyboard to start the formula;
  3. Switch to the source workbook, click on the cell containing the data to be linked;
  4. Press the Enter key on the keyboard - Excel should switch back to the destination file with the linked data displayed in the selected cell;
  5. Clinking on the linked data will display the link formula - such as =[Book1]Sheet1!$A$1 in the formula bar above the worksheet.

Note: the dollar signs in the cell reference - $A$1 - indicate that it is an absolute cell reference.

Paste Link Options in Word and Excel

When pasting a link for data, Word allows you to choose whether to format the linked data using the current settings for either the source or destination files. Excel does not offer these options, it just automatically applies the current formatting settings in the destination file.

Linking Data between Word and Excel

  1. Open the Excel workbook containing the data to be linked (the source file)
  2. Open the destination file - either an Excel workbook or Word document;
  3. In the source file highlight the data to be copied;
  4. In the source file, click on the Copy button on the Home tab of the ribbon - the selected data will be surrounded by the Marching Ants;
  5. In the destination file, click with the mouse pointer on the location where the linked data will be displayed - in Excel click on the cell that will be in the upper left corner of the pasted data;
  6. As shown in the image above, click on the small arrow at the bottom of the Paste button on the Home tab of the ribbon to open the Paste Options drop down menu
  7. Depending upon the destination program, the paste link options will differ:
    • For Word, paste link is located under Paste Options in the menu;
    • For Excel, paste link is located under Other Paste Options in the menu.
  8. Choose the appropriate Paste Link option;
  9. The linked data should appear in the destination file.

Notes:

  • If both files are open when data is updated in the source file, the destination file should update immediately.
  • If the destination file is closed when the source data is changed, the data in the destination cells will update the next time that file is opened.
  • If the source file is closed when the destination file is opened, an alert box may open indicating the document contains links to external files and asking you to Update/Don't Update the links.

Viewing the Link Formula in Excel

The way that the link formula is displayed varies slightly between Excel 2007 and later versions of the program.

  • In Excel 2010 and later versions of the program, if you click on the linked data in the destination file, a formula such as =[Book1]Sheet1!A1 appears in the formula bar above the worksheet;
  • In Excel 2007, the linking formula is displayed as: {=Excel.Sheet.12|Book1!'!Sheet1!R1C1'}  in the formula bar.

Notes:

  • In the 2007 formula, the absolute cell reference is written in the style R1C1 - which stands for row 1 column 1 and which is equivalent to the more common cell reference style SAS1
  • In both formulas, Book1 indicates the name of the source file

Viewing Link Information in MS Word

To view information about the linked data - such as the source file, the linked data, and the update method:

  1. Right click on the linked data to open the context menu;
  2. Select Linked Worksheet Object > Links... to open the Links dialog box in Word;
  3. If there is more than one link in the current document, all links will be listed in the window at the top of the dialog box;
  4. Clicking on a link will display information about that link below the window in the dialog box.
02
of 02

Paste a Link Between Charts in Excel and PowerPoint

Paste a Link Between Charts in Excel, Word, and PowerPoint
Paste a Link Between Charts in Excel, Word, and PowerPoint. © Ted French

Linking Charts with Paste Link in PowerPoint and Word

As mentioned, in addition to creating a link for text data or formulas, it is also possible to use paste link to connect a chart located in one Excel workbook with a copy in a second workbook or in a MS PowerPoint or Word file.

Once linked, changes to the data in the source file are reflected in both the original chart and the copy located in the destination file.

Choosing Source or Destination Formatting

When pasting a link between charts, PowerPoint, Word, and Excel allow you to choose whether to format the linked chart using the current formatting theme for either the source or destination files.

Linking Charts in Excel and PowerPoint

As shown in the image above, this example creates a link between a chart in an Excel workbook - the source file and a slide in a PowerPoint presentation - the destination file.

  1. Open a workbook containing the chart to be copied;
  2. Open the destination presentation file;
  3. In the Excel workbook, click on the chart to select it;
  4. Click on the Copy button on the Home tab of the ribbon in Excel;
  5. Click on the slide in PowerPoint where the linked chart will be displayed;
  6. In PowerPoint, click on the small arrow at the bottom of the paste button - as shown in the image - to open the drop down list;
  7. Click on either the Use Destination Theme or the Keep Source Formatting icons in the drop down list to paste the linked chart into PowerPoint.

Notes:

  • If the two files containing the linked charts are both open, changes to the source data should update immediately in both charts.
     
  • When opening a PowerPoint presentation containing linked data, an alert box may open indicating a potential security concern and asking you whether you wish to to update the linked data.
Was this page helpful?