Linking excel and word tables. Link or embed data in Office for Mac
If you need to embed a table block in Word, it is better to form it in Excel and transfer it to text file. There are two ways to do this - a simple insertion and import of data with the establishment of links. However, it is worth considering that in order to insert a table into a Word from Excel, you need to carefully read the procedure.
Copy and paste
If the size of the table with the final version of its contents is small, the best way to move is to copy and paste. The algorithm of actions is as follows:
- using the "Use final formatting styles" option - the contents of the inserted range will automatically be formatted in the same style as the text block, cell values 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.
Table import
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 desired range is selected and copied.
- In Word, the copied object is inserted:
- "Link and use target styles" - the style of the table adapts to the last paragraph of the document, when changes are made in the source, the values in Word also change;
Very often, when solving various office tasks, we store and process data either in Excel spreadsheets or in text editor word. Moreover, most of these data are interconnected. For example, an Excel table or its fragment is copied and pasted into Word.
The advantages of this approach are obvious: the user is not bound in the choice of data storage media.
However, there is also a significant disadvantage, namely: if the data in the source table changes, then we must make sure that these data are corrected in all related documents. Therefore, the topic of copying, embedding and linking data from various documents is very relevant. In this video tutorial, I want to tell you in what three ways we can copy data from Excel to Word, as well as how to establish a relationship between this data.
The resulting table is the most ordinary Word table. We can format or edit it as we like: change or add data. In this case, the original Excel table is not changed in any way. Similarly, if I make any changes in the original table, nothing will change in the copied table. Simple, but not very convenient.
The second way to copy data involves pasting into word document Excel spreadsheet as a complete object. This time, when copying, we will use the data paste special mode. As before, the data table in our two documents is not linked, which is inconvenient. However, working in text word editor, we get a full-fledged Excel spreadsheet at hand, into which you can enter formulas, create graphs, and everything will work.
The last, third way of copying data involves linking them in a special paste process. By appearance the appeared table is no different from the previous ones, however, in fact, it is a completely different object, different from the previous ones. This time, we no longer have a table in front of us, but a link to the original data source. If with original Excel data something happens, we will immediately see these changes in a Word document. To do this, the associated data must be updated in a timely manner. Closing the received Text Document, let's continue editing the Excel spreadsheet. Then, when reopening a text document, Word warns us that the document contains related data that depends on an external source and prompts us to update it.
To link two Word documents, you can use the command Special insert(menu Edit) or command File(menu Insert).
In cases where you need to insert a part of the document, you should use the command Special insert.
Since text in different documents may be formatted differently, in order for the linked text to be inserted in the format of the destination document in the list box How dialog box Special insert you must select an option Unformatted text.
When the cursor is inside the linked region of the target document, the entire region is selected in gray. Although linked text can be edited in the target document, changes made during editing will be lost when the link is updated. The gray background reminds you that the selected part should not be edited. If the connection area is not highlighted, select the command Options(menu Service) and on the tab View drop down list field shading select the line " When highlighting" or " Is always».
To see in the target document the names of the files that the inserted elements are associated with, select the command Options menu Service and on the tab View activate the checkbox Field codes. In this case, the document will not display 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 Connections menu Edit) (Fig. 7.7).
Rice. 7.7. command dialog box Connections
Links can be updated automatically if the corresponding button is selected. When setting the option On request update happens:
when opening or printing a destination document file;
when you press shortcut key update fields F9.
Linking an Excel spreadsheet and a Word document
The procedure for linking an Excel spreadsheet and a Word document is the same as linking two Word documents discussed above.
Most important point when linking a table to a document is to choose the format.
When choosing a format An object the table will be inserted as an Excel application object and can be edited in Excel by double-clicking on this object.
To insert a table as a table and not as plain text, select the format RTF text. This usually involves editing the column widths and row heights 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, with the data of different cells separated by tabs.
When choosing formats Picture and Raster the table is inserted as a picture and will be edited by means of Excel when you double-click on the picture. When binding Excel charts with 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. There is a button on the standard toolbar of Word. Add Excel Table l., which is used to insert an Excel table as an object into a Word document. After clicking this button and choosing a table size, a blank table will be inserted into the document.
When embedding objects, all actions are performed similarly to linking, only the option is activated Insert instead of To tie. When a document is embedded, there is no connection with the source information, however, the possibility of editing the embedded document with the tools and means of the source application remains.
20.1 Theory
There are many different ways sharing data programs Microsoft office. For example, Word provides several ways to paste Microsoft data Excel to Word document. Can be copied and pasted spreadsheet Excel to Word document. You can also insert a spreadsheet into a Word document as a linked object or an embedded object.
The main differences between linking and embedding (embedding) objects are where the data is stored and how the data is updated after it has been placed in the document.
A linked object is an object (for example, a spreadsheet) created in one file and inserted into another file with support for linking between files. The linked object can be updated at the same time as the source file is updated. The linked object is not part of the file in which it is inserted.
An embedded object is an object (such as a spreadsheet) that is embedded in a file. Once embedded, the object becomes part of the file. Double-clicking an embedded object opens it with the program in which it was created. Any changes you make to an embedded object are reflected in its containing file.
Linking and embedding can be done both using the clipboard and using the "Paste Object" dialog box, which is called by the Object command on the Insert menu.
To fulfill this laboratory work learn how to share data between Microsoft Office programs. Implementation using the "Insert Object" dialog box is described in the lecture course and in the recommended sources of information.
20.2 Purpose of work
Acquisition of practical skills in working with Microsoft Excel and Word programs in the process of creating documents, copying, linking and embedding a Microsoft Excel object (spreadsheet) into a Word document.
20.3 Statement of the problem
To complete the lab, do the following:
- create a Word document in accordance with the attached sample shown in Figure 20.1;
- create a blank book, complete the worksheet according to the attached sample shown in Figure 20.2, and perform the necessary calculations;
- Insert the created Excel spreadsheet into a Word document in the following ways:
- as Word tables;
- as an embedded object;
- as an associated object.
After inserting an Excel table into a Word document, delete the data in cell G5 in all tables and update the data.
Rice. 20.1
Rice. 20.2
20.4 Work step by step
20.4.1 Turn on the PC
Click the button power on the system block PC.
20.4.2 Start Microsoft Word and Excel
20.4.2.1 Run Microsoft Word using the Main Menu command.
After starting Microsoft Word, the application window "Document 1 - Microsoft Word", which displays a blank text document based on the Normal template. Create a document according to the attached template shown in Figure 20.1 and save it.
20.4.2.2 Without closing Word, start Microsoft Excel.
After starting Microsoft Excel, the application window will open, displaying a blank workbook "Book 1" with three worksheets. Create a new workbook and complete the worksheet according to the attached template shown in Figure 20.2. Perform all calculations (fill in rows and column "Total" and row "Total") in the spreadsheet and save the file (workbook).
20.4.3 Insert the generated Excel spreadsheet into a Word document in various ways
20.4.3.1 Insert the generated Excel spreadsheet into a Word document as a Word table.
- go to your Word document, select where to insert the table, and click the Insert button on the toolbar.
20.4.3.2 Insert the generated Excel spreadsheet into a Word document as an embedded object.
To do this, follow these steps:
- select a range of table cells;
- click the Copy button on the toolbar;
- go to the Word document, select the place to insert the embedded object;
- set the switch to Insert;
20.4.3.3 Insert the generated Excel spreadsheet into a Word document as a linked object.
To do this, follow these steps:
- select a range of table cells;
- click the Copy button on the toolbar;
- go to the Word document, select the place to insert the linked object;
- select the Paste Special command from the Edit menu;
- set the switch to the Link position;
- In the "As:" list, select Microsoft Sheet Office Excel(object) and click OK.
20.4.4 Delete the data in cell G5 in all tables inserted in the Word document and update the data
20.4.4.1 Delete the data in cell G5 (number 128) of the Word table created by the copy method and update the data in the entire table.
For this:
- recalculate the data in the entire table.
20.4.4.2 Delete the data in cell G5 (number 128) of the Word table created by the object embedding method and update the data in the entire table.
For this:
- double click on the embedded object (table) to open it with Excel programs in which it was created;
- delete the number 128 in cell G5;
- click outside the embedded object field to return to Word.
20.4.4.3 Delete the data in cell G5 (number 128) of the Word table created by the object linking method and update the data in the entire table.
Do the following:
- double-click on the associated object (table), an Excel application window will open with an open workbook;
- delete the number 128 in cell G5;
- data in the entire table will be automatically recalculated;
- close Excel application to return to Word.
20.4.5 Shutdown
Report to the teacher about the work done. After permission to shut down, close application programs Microsoft Word and Excel, after which you can start passing tests on the work performed.
