How to link tables in excel with word. Link or embed data in Office for Mac
Note: This article has served its purpose and will soon be phased out. To avoid the appearance of "Page not found" errors, we delete the links we know. If you have created links to this page, please remove them, and together we will ensure data consistency on the Internet.
When working with Office, there are several ways to exchange data between applications. One way is to copy and paste the data, but it can also be linked to a source so that it updates automatically, or embedded to update the data only manually by opening a file. In addition, using a linked object allows you to create a link to part of a file, such as multiple cells in an Excel worksheet.
Advantages and Disadvantages of Linking and Embedding
Word
On the menu Insert click item An object and select option From file.
Find and select the file you want to link to and click Parameters.
Check the box link to file ».
Advice: As an icon.
Click the button Paste OK if you are using Excel.
Changing related data
On the menu Edit select team Connections.
If the command Connections is unavailable, which means there is no associated data in the file.
In section Original file Open source.
On the menu Insert click item An object and select option From file.
Please select desired file and press the button Parameters.
Uncheck the box link to file .
Advice: If you want to display only an icon that you can click to view details, select the As an icon.
Click the button Paste if you are using Word, or the button OK if you are using Excel.
Modifying embedded data
Double-click the embedded object.
Make your changes and save them.
On the menu File select a command.
On the menu Edit select team Copy.
On the menu Edit select team Paste specialand then item Insert Link.
In field how Microsoft Excel sheet, and press the button OK.
PowerPoint
Embedding data from one file into another
Click the location where you want to embed the file.
On the menu Insert select item an object, and then - from file.
If you want to embed an Excel sheet, it must be the first in the workbook.
Click the button Pasteand then the button OK.
Modifying embedded data
Make the necessary changes.
Open the tab fileand press the button Refresh.
On the menu File select team Close and return to [filename].
Note that the changes are saved in the embedded file.
Copy data from a single file and paste it as a linked or embedded object
Select the data you want to insert (for example, a range of cells in an Excel worksheet).
On the menu Edit select team Copy.
Navigate to another file and click the location where you want to paste the copied data.
On the menu Edit select team Paste special.
In field Paste as select the type of object, for example Microsoft Excel sheet, and press the button OK.
Excel
Linking data between Word and Excel files
If the data is in an Excel file and the related information is in word file, you can link them to make updating easier. For example, suppose you want to link the latest sales revenue to a monthly status report. The report is a Word document and the sales revenue data is in an Excel sheet. After you link the document and sheet, the data in the status report will automatically update when the sales revenue information changes. It is also possible that instructions from a Word document need to be pasted into multiple Excel files. If you use data binding, changes to instructions are automatically reflected in all Excel files.
Click where you want to insert the link.
On the menu Insert click item An object and select option From file.
Select the file you want to link to and click Parameters.
Check the box link to file ».
Advice: If you want to display only an icon that you can click to view details, select the As an icon.
Click the button Paste if you are using Word, or the button OK if you are using Excel.
Changing related data
On the menu data select team change links.
In section Original file select the required file and press the button Open source.
Make the necessary changes.
Click the linked file. Please note that there are changes in the file.
Embedding data from one file into another
If you want to maintain a link between two files, and the main file does not need to be updated when the other changes, the data can be embedded. For example, you can embed data when you don’t want the status report to refresh when changes are made in the Excel worksheet, or you don’t want the Excel worksheet to reflect changes in instructions. Because all information is stored in a single file, data embedding is useful in situations where the network version of a file needs to be sent to users who do not have access to the embedded file.
Click the location where you want to embed the file.
On the menu Insert click item An object and select option From file.
Select the file to embed and click Parameters.
If you want to embed an Excel sheet, it must be the first in the workbook.
Advice: If you want to display only an icon that you can click to view details, select the As an icon.
Uncheck the box Link to file and press the button Pasteif you are using Word, or the OKif you are using Excel.
Modifying embedded data
Double click the embedded file.
Make the necessary changes.
On the menu file select team Refresh.
Note that the changes are saved in the embedded file.
Copy data from a single file and paste it as a linked or embedded object
On the menu Edit select team Copy.
Navigate to another file and click the location where you want to paste the copied data.
On the menu Edit select team Paste specialand then item Insert Link.
In field how select the type of object, for example Microsoft Word Document, and press the button OK.
Word
Linking data between Word and Excel files
If the data is in an Excel file and the related information is in the Word file, you can link the data to make it easier to update. For example, suppose you want to link the latest sales revenue to your monthly status report. The report is a Word document and the sales revenue data is in an Excel sheet. After you link the document and sheet, the data in the status report will automatically update when the sales revenue information changes. It is also possible that instructions from a Word document need to be pasted into multiple Excel files. If you use data binding, changes to instructions will be automatically reflected in all Excel files.
Open source.
Make the necessary changes.
Remember that the changes are contained in the linked file.
Embedding data from one file into another
If you want to maintain a link between two files, and the main file does not need to be updated when the other changes, the data can be embedded. For example, you can embed data when you don’t want the status report to refresh when changes are made in the Excel worksheet, or you don’t want the Excel worksheet to reflect changes in instructions. Because all information is stored in a single file, data embedding is useful in situations where the network version of a file needs to be sent to users who do not have access to the embedded file.
for example Sheet, and press the button Edit.
Make the necessary changes.
On the menu File select team Close and return to [filename].
Note that the changes are saved in the embedded file.
Copy data from a single file and paste it as a linked or embedded object
You can link to part of a file. For example, in a monthly status report, you might want to link to a specific range of cells in an Excel sheet rather than the entire sheet. For example, you can link to specific instructions from a Word document rather than the entire document.
Select the data you want to insert (for example, a range of cells in an Excel worksheet).
On the menu Edit select team Copy.
Navigate to another file and click the location where you want to paste the copied data.
On the menu Edit select team Paste specialand then item Insert Link.
In field how select the type of object, for example Microsoft Excel sheet, and press the button OK.
PowerPoint
Embedding data from one file into another
If you want to maintain a link between two files, and the main file does not need to be updated when the other changes, the data can be embedded. For example, suppose you want to incorporate the latest sales revenue information into your monthly status report. The report is powerPoint presentationand the sales revenue data is on an Excel sheet. Once the sheet is embedded in the presentation, the files remain linked.
Click the location where you want to embed the file.
On the menu Insert click item An object and select option Create from file.
Find and select the file you want to embed.
If you want to embed an Excel sheet, it must be the first in the workbook.
Click the button Pasteand then the button OK.
If you don't see the data from the embedded file, drag the resize handle until the file content appears.
Modifying embedded data
Click the embedded file.
On the menu Edit hover over the item Object [object type], eg Sheet, and press the button Edit.
Make the necessary changes.
On the menu File select team Close and return to [filename].
Note that the changes are saved in the embedded file.
Copy data from a single file and paste it as a linked or embedded object
Only part of a file can be embedded. For example, in a monthly status report, you might want to embed not the entire Excel sheet, but only a specific range of cells on it.
Select the data you want to insert (for example, a range of cells in an Excel worksheet).
On the menu Edit select team Copy.
Navigate to another file and click the location where you want to paste the copied data.
On the menu Edit select team Paste special.
In field Paste as select the type of object, for example Microsoft Excel sheet, and press the button OK.
Excel
Linking data between Word and Excel files
If the data is in an Excel file and the related information is in the Word file, you can link the data to make it easier to update. For example, suppose you want to link the latest sales revenue to your monthly status report. The report is a Word document and the sales revenue data is in an Excel sheet. After you link the document and sheet, the data in the status report will automatically update when the sales revenue information changes. It is also possible that instructions from a Word document need to be pasted into multiple Excel files. If you use data binding, changes to instructions will be automatically reflected in all Excel files.
Source File select the desired file and click Open source.
Make the necessary changes.
Remember that the changes are contained in the linked file.
Embedding data from one file into another
If you want to maintain a link between two files, and the main file does not need to be updated when the other changes, the data can be embedded. For example, you can embed data when you don’t want the status report to refresh when changes are made in the Excel worksheet, or you don’t want the Excel worksheet to reflect changes in instructions. Because all information is stored in a single file, data embedding is useful in situations where the network version of a file needs to be sent to users who do not have access to the embedded file.
for example Sheet, and press the button Edit.
Make the necessary changes.
On the menu File select team Close and return to [filename].
Note that the changes are saved in the embedded file.
Copy data from a single file and paste it as a linked or embedded object
You can link to part of a file. For example, you might want to link not to the entire Excel sheet, but only to a specific range of cells on it.
Select the data you want to insert (for example, several sentences in word document).
On the menu Edit select team Copy.
Navigate to another file and click the location where you want to paste the copied data.
On the menu Edit select team Paste specialand then item Insert Link.
In field how select the type of object, for example Microsoft Word Document, and press the button OK.
Anyone who has ever prepared financial statements in accordance with IFRS requirements knows how long it takes to copy tables from Excel to Word and format them further. Let's save ourselves a lot of time. Now you only need to insert the tables you need once - they will be updated automatically when they change.
So, we have prepared tables that will go into a document in Microsoft Excel. Let's say it's a profit and loss statement. Let's say this.
As you can see - the most that neither is Excel "evskiy. It has a bunch of advantages. First of all, it is convenient to work with it, it can contain the necessary calculations, formulas, it is convenient to track where the data comes from (high-quality audit trail), etc. The problem is that it is not in the Word file, and this problem is serious.
There are a couple of ways to insert it into Microsoft Word:
(1) insert it with a picture (advantages - cannot be changed, exact formatting is preserved; disadvantages - again, cannot be changed, it weighs a lot, if you insert 30-40 such tables, there may be problems with sending by e-mail, especially from the corporate mailbox), and
(2) insert it in text (advantages and disadvantages - exactly the opposite).
Both methods have a significant problem. When changing sources with tables, they must be reinserted. When preparing the reporting, which I wrote about at the beginning, it is possible that the tables will be updated with enviable regularity, say 5-10 times per preparation. To avoid wasting time updating tables, I suggest inserting linked tables.
How to insert a linked table from Excel to Word
(1) Highlight the Excel table you want to insert into the Word document and click copy,
(2) go to Word, put the cursor where you want to insert the table and click Link and keep original formatting.
If you are using an earlier version of the office (screenshot taken from Microsoft Office 2010), click Paste Special ... And select the option in the window that appears To tie, instead of Insert, which is the default.
The result looks like this.
You've probably noticed that you can link in different formats. Each of them has its own advantages and disadvantages. Choose based on your personal preference. The main thing is that the inserted table will be updated along with updating the tables in Excel (when you start Word, it will ask you to update external data - feel free to click Refresh). Now you will not forget to update one of the 40 tables inserted into the document!
If you want to learn how to use Excel in your daily work and save a lot of time at the same time, take a look at these books: Microsoft Excel 2010 by example and Microsoft Excel 2010. User's Bible.
Latest news
Advanced Excel: Why I Stop Using V ...
I already wrote about the fact that the VLOOKUP function (VLOOKUP) is probably the most useful function after simple arithmetic operations ...
How Excel recounts a workbook and why you should avoid volatility ...
If you often work with large files that do a lot of computations that depend on each other, oh ...
How to quickly plot a Waterfall graph
Excel lacks standard charts like Waterfall. Therefore, to create diagrams of this type, gis is usually used ...
Benchmark window for tracking results
It often happens that we work with large tables, which ultimately calculate 1-2 indicators, but depend on ...
Bullet chart for comparing planned indicators and actually ...
It often becomes necessary to compare the main financial and economic indicators with the planned ones, for example, for the purposes of ...
Tatsuo Horiuchi - 73 year old Excel painter
“I've never used Excel at work, but I've seen other people make pretty nice charts and graphs in it ...
How to raise a number to a power and extract the root
Did you know that for the fact that for exponentiation of a number in Excel there is a special character ^ (on the six in English ...
To link two Word documents, you can use the command Paste special(menu Edit) or the command File(menu Insert).
In cases where you need to insert a part of the document, you should use the command Paste special.
Since text in different documents can be formatted differently, in order for the linked text to be inserted in the format of the destination document in the list box howdialog box Paste specialyou must select an option Unformatted text.
When the cursor is inside the linked area of \u200b\u200bthe destination document, the entire area is grayed out. Although the linked text can be edited in the target document, changes made during editing are lost when the link is updated. A gray background reminds you that you should not edit the selected area. If the link area is not highlighted, select the command Parameters(menu Service) and on the tab Viewin the dropdown Field shadingselect the line " When selecting" or " Is always».
To see the names of the files associated with the inserted items in the target document, choose Parametersmenu Serviceand in the tab Viewactivate the checkbox Field codes... In this case, the document will display not the inserted information, but indications of the connection with the source files.
Information about the presence and nature of links is displayed in the Links dialog box (command Connectionsmenu Edit) (Figure 7.7).
Figure: 7.7. Command Dialog Box Connections
Links can be updated automatically if the corresponding button is selected. When installing the option On requestthe update takes place:
when opening or printing a destination document file;
when you press the fast key for updating fields F9.
Linking an Excel table and a Word document
The procedure for linking an Excel table and a Word document is the same as linking the two Word documents discussed above.
The most important point when linking a table to a document is choosing a format.
When choosing a format An objectthe table will be inserted as an object in Excel and can be edited in Excel by double-clicking on this object.
In order to insert a table as a table, and not as plain text, you should select the format RTF text... When doing this, you usually need to edit the column width and row height to make the table look neat.
If you choose the format Unformatted text, the table will be inserted not as a table, but as plain text, and the data of different cells will be separated by tabs.
When choosing formats Pictureand Rasterthe table is inserted as a picture and will be edited by means of Excel when you double-click on the picture. When linking an Excel chart to a Word document, you should always choose the format Picture.
In general, Word is well "prepared" for integrating an Excel spreadsheet into a document as an object. The standard Word toolbar has a button Add Exce tablel., which inserts an Excel table as an object into a Word document. After clicking this button and choosing the size of the table, a blank table will be inserted into the document.
When embedding objects, all actions are performed in the same way as for linking, only the option is activated Pasteinstead To tie... When a document is embedded, there is no connection with the original information, but the ability to edit the embedded document using the tools and means of the source application is retained.
A word processor with some basic spreadsheet capabilities makes a fantastic combination. You can easily embed Excel tables in a regular Microsoft Word text document.
The Excel spreadsheet implementation basically includes a tiny copy of the program. When editing an Excel section of a document, you have access to all elements excel controlas if you were using Excel as a standalone program. You can add standard text and numeric values \u200b\u200band, crucially, formulas that apply directly to the Excel mini-window.
To insert an Excel table into Word 2016, click the Insert tab at the top of the window, and then click the Table button. Click Excel Spreadsheet from the drop-down menu.
Here's an example. I filled the standard cells with values \u200b\u200band used one of the most common sum formulas for the cells in the last column. So for the “Total” value for “Product1” in cell E2, I use the formula “SUM (B2: D2)” to add all three values \u200b\u200bto the row and automatically get my grand total. You can use any excel formulawhich you like in this built-in version of the program.
Excel tables have basically an infinite number of rows and columns, but this is not practical when you are using this data as a table in a Word document. To change the number of rows and columns visible, click and drag the anchor points, the black squares that appear in each corner and in the middle of the box around the Excel table.
When you're done editing these values, you can click on any other part of the Word document, and the default formatting will revert to the base table, suitable for printing or distribution via readable formats such as PDF. Here you can adjust the width and height of the table to better match the formatting of the Word document without changing the number of visible columns or rows.
To start editing the spreadsheet again, double-click it to bring back the Excel controls.
It is also possible to embed an existing Excel file, which is convenient if you are trying to exchange data that you have already accumulated. In the "Insert" table, click the "Object" button (small square with a blue frame in the "Text" section):
In the window that appears, click "Create from File", then click "Browse" to navigate and open an Excel spreadsheet on your computer's drive. You also have a couple of other options. Selecting the Link to File option keeps the table you see in Word linked to the actual Excel spreadsheet if they are stored in the same locations as when they were linked. Changes you make anywhere are reflected elsewhere. You can also select the Icon View option to display the spreadsheet as a simple icon in a Word document, which you can click to open the spreadsheet in Excel.
When you're done, just click OK to insert the table.
Depending on the formatting of Word, you may need to resize or resize to see everything.
If it is necessary to embed a tabular block in Word, it is better to form it in Excel and transfer it to a text file. There are two ways to do this - simple data insertion and import with establishment of links. However, it is worth considering, in order to insert a table into a Word from Excel, you need to carefully read the procedure.
Copy and paste
If the table is small and the final version of its contents is the best way to move around by copying and simple paste. The algorithm of actions is as follows:
- using the option "Use final formatting styles" - the contents of the inserted range will automatically be styled in the same style as the text block, the values \u200b\u200bof the cells can be changed;
- by selecting the "Picture" parameter - the original design will be preserved, but the possibility of making adjustments to the cell area is lost.
Importing a table
If you need to transfer tabular data with the ability to make changes in the original file and update the values \u200b\u200bin Word, use another method:- In Excel, the required range is highlighted and copied.
- The copied object is inserted in Word:
- "Link and use final styles" - the style of the table is adapted to the last paragraph of the document, when changes are made in the source, the values \u200b\u200bin Word also change;
