Chapter 40

Integrating Office Application Information

IN THIS CHAPTER

Sending data between Excel and Word

Copying tables between Excel and Word

Exchanging outlines between Word and PowerPoint

Using the Outlook Address Book in Word

Sharing data between Word and OneNote

In some ways, using the Office suite is like using a single multipurpose program. Features from the different programs mesh together almost seamlessly. For example, when you create a chart in a Word 2013 document, the data also lives in Excel 2013, almost as if Excel were an extension of Word.

In this chapter, you explore the ways in which Excel, PowerPoint, Word, Outlook, and OneNote communicate with each other. Some things are perfectly intuitive, and others aren’t. The casual PowerPoint user might never stumble on how to send outlines back and forth with Word. Do you ever wonder about the array of picture options available to you when copying images between Word and other programs? Which format should you use, and what are the consequences of using this one or that? How can pasting a 40K picture into a Word file add 900K to its size? In this chapter, the focus is on the less intuitive, to get you over some hurdles and stumbling blocks, and to make sense of some of those little mysteries that can make using Word seem like a struggle.

Excel

Although sharing work between Word and Excel often works well, differences in how the two programs operate can produce confusing results. This can be addressed by becoming aware of those differences, and working in a way that accommodates them and smoothes the way. This section looks at Word and Excel and ways to share text, data, tables, and graphics.

Using Excel content in Word

Word offers a variety of ways to share and exchange content with Excel:

• Clipboard: Copying content to the Clipboard, and then using Paste or Paste Special to insert the contents into Word or Excel. Commandment: When in doubt, use Paste Special.
• Chart: Using Office 2013’s Chart feature to create a chart inside Word using Excel’s facilities.
• Object: Using Insert ⇒ Text ⇒ Object ⇒ Create New or Create from File to embed all or part of an Excel worksheet into a Word document.

A common method that also works is drag and drop. You can select data or other content in Excel and drag it into Word. Make sure you press and hold the Ctrl key while dragging. If you do not see the plus with the mouse pointer as shown in Figure 40.1, the selection will be cut from Excel rather than copied. By default, the Excel data becomes an embedded object in the Word document, and you can double-click it there to display Excel tools for making changes. If you want the data to become a table in Word instead, use the Clipboard as described next.

FIGURE 40.1 Press Ctrl when dragging from Excel to Word to copy the range.

Read on to learn more about using the Clipboard, Chart, and Insert ⇒ Object features.

Clipboard

Excel’s Clipboard works slightly differently from the Clipboard in most other Office programs. When you select cells in an Excel worksheet, they are highlighted. At this point, they are merely highlighted and cannot be moved or otherwise acted upon. You need to copy (or cut) the selection to the Clipboard, by pressing Ctrl+C or Ctrl+X, or right-clicking and choosing Copy or Paste. As in some other Office applications, you also can use the tools in the Clipboard group of Excel’s Home tab to cut, copy, and paste a selection. After you cut or copy a selection of cells in Excel, an animated border appears around the selection, as shown in Figure 40.2.

FIGURE 40.2 The animated marquee around a selection in Excel shows you that you have cut or copied it.

If you press Esc in Excel or double-click elsewhere (or perform any of a dozen or more other actions in Excel), the animated border disappears and you cannot paste the selection. Even if the data has actually been copied to the Clipboard, you still can’t use the Paste button unless the animated border still appears in Excel. Dismissing the animated border also disables Paste Special, which is a helpful tool to use to determine how to paste Excel data.

With the selection active in Excel, click where you want the data to appear in Word. If you simply click the Paste button in the Clipboard group of the Home tab, the cut or copied Excel selection pastes into Word as a Word table. As shown in Figure 40.3, the Paste Options button appears. You can click it to reveal options for how to paste the data. Move the mouse pointer over a paste option to see a description of what it does and a Live Preview of it on the pasted data. From left to right, the Paste Options for pasting Excel data and the key you can press to apply each are:

FIGURE 40.3 With a straight paste, the Excel data appears as a table, but you can use Paste Options to control how the pasted data appears.

• Keep Source Formatting (K)
• Use Destination Styles (S)
• Link & Keep Source Formatting (F)
• Link & Use Destination Styles (L)
• Picture (U)
• Keep Text Only (T)

You also can use the Paste Special dialog box to paste and control how Word formats the data and whether the data stays linked to the source data in Excel. This method provides a few different format choices than the Paste Options button, but the downside is that you don’t see a Live Preview of the pasted data. To use Paste Special, click the Paste button’s down arrow (the bottom half of the button), and choose Paste Special (or press Ctrl+Alt+V). The Paste Special dialog box, shown in Figure 40.4, appears. Notice that the default is HTML format.

FIGURE 40.4 When using Paste Special to paste a selection of cells from Excel into Word, you’ll have several options regarding how to paste.

At the end of the day, both HTML and RTF retain both formatting and table structure, but there are differences. They might seem subtle, or they might seem substantial, depending on your needs. There also are differences among other options that might seemingly appear similar. Different Paste Special options are as follows:

• Microsoft Office Excel Worksheet Object: Inserts the selection as a complete mini-worksheet, complete with Excel editing if you double-click the object.
• Formatted Text (RTF): Inserts formatted text as a table, retaining the cell, column, and row formatting in effect in the Excel file. This option often misinterprets cell shading and other colors.
• Unformatted Text: Inserts plain text with no attributes. Tabs are used to separate text that originated in different cells.
• Bitmap: Inserts a .bmp picture file.
• Picture (Enhanced Metafile): Inserts an .emf picture file that is essentially identical in appearance to the Windows Metafile but is slightly smaller in size.
• HTML Format: Retains text formatting, but doesn’t retain all of the table formatting. This usually results in a table that is smaller in width than the RTF table. This option inserts cell shading and colors more accurately than RTF.
• Unformatted Unicode Text: Usually, this yields the same result as unformatted text. Unicode goes well beyond ASCII and ANSI and provides for many more characters and languages. If you find that linguistic information is being lost when pasting as unformatted text, then switch to unformatted Unicode text.

Click the Paste link option button to the left of the list of formats if you want copied Excel data to be linked to its source. This means that any changes you make to the Excel data will appear in Word when you reopen the Word file. Click OK to finish the paste.

Chart

If you simply paste a chart from Excel to Word, the chart is pasted as an unlinked, embedded chart. Once the chart is in Word, when you click it, the Chart Tools contextual tabs — Design and Format — appear. If you need to change the data, choose the Chart Tools ⇒ Design tab, and then click Select Data or Edit Data in the Data group of the tab, as shown in Figure 40.5.

FIGURE 40.5 Clicking Edit Data in the Chart Tools ⇒ Design tab selects and opens the data in Excel.

When you make changes to the data, the chart in Word is updated automatically to reflect the data changes. If there’s a chance that you’ll need to undo changes, leave the Excel window open. As long as it remains open, Ctrl+Z will work if you want to undo a change. If you close the Excel window with the data, changes to the chart and data are saved automatically, and Ctrl+Z will no longer undo changes you might have made.

When you copy graphics such as charts from Excel to Word with Paste Special, the rules change a bit. Right-click the chart or graphic and choose Copy. This time, you don’t get the dashed selection because you’re not copying cells — so it’s a bit simpler, and once something has been copied to the Clipboard, the Paste Special options remain available. Switch to Word, click the Paste Special arrow or press Ctrl+Alt+V, and you’ll see the options shown in Figure 40.6.

FIGURE 40.6 When you’re copying graphics such as charts from Excel to Word, most of the formats presented are picture formats.

The first option, Microsoft Excel Chart Object, inserts the chart as an embedded object, which I’ll discuss more in the next section. To find out about any of the other formats — various picture formats — click the format to see a description of it under Result at the bottom of the dialog box. For example, Figure 40.6 shows the Bitmap format selected and its description. The different picture formats each result in a different appearance for the pasted object in the Word document, so you might want to experiment to see which format gives you the best appearance in your document considering how it’s going to be presented — online versus on paper.

Object

A third way to use Excel data in a Word document is as an object. In Word, choose Insert ⇒ Text ⇒ Object. The Object dialog box appears. To use an existing Excel worksheet, click the Create from File tab. To create a new Excel object, stay on the Create New tab. Each tab is described next.

Create from File

On the Create from File tab, click the Browse button to navigate to the target file. Choose Link to file and/or Display as icon, according to your needs, and click OK.

Create New

In the Create New tab of the Object dialog box, select the desired type of Excel object, as shown in Figure 40.7. Choose Display as icon, if desired (click Change Icon, if appropriate), and then click OK. Use Excel’s tools to create the desired object, as shown in Figure 40.8, and then click outside the object (or close Excel, depending on the object type).

FIGURE 40.7 The Change Icon button appears only if Display as icon is checked.

FIGURE 40.8 Creating an Excel worksheet object in Word

From worksheet to table

As noted earlier, directly pasting formatted Excel data into Word creates a new table automatically. Sometimes, however, you need to insert data into a table that already exists. Typically, two problems can occur. First, sometimes the pasted cells don’t go exactly where you want them to go. Second, no matter what you do, the formatting in the table never ends up exactly as you want.

To handle the first problem, the dimensions (rows and columns) of the source must be identical to the destination and the destination cells must be selected. For example, if you are pasting a selection of cells that contains 5 rows and 4 columns, then the destination must also be 5 × 4, and you must select the destination cells. If you try to paste in the top-left cell (which seems logical, right?), Word will paste the entire selection into that cell, so you end up with a table within a table.

There is no perfect way to handle the second problem. Even if you choose the setting File ⇒ Options ⇒ Advanced ⇒ Pasting from other programs to Match Destination Formatting or Keep Text Only, something in the formatting will be messed up — usually the spacing.

Your best bet, assuming you’re using a style, is to choose Keep Text Only from either Paste Options or the Paste Special button, and then reapply the style to the pasted cells. Alternatively, if there are table cells that contain the correct formatting, use the Format Painter to reformat the pasted cells as desired.

Using Word content in Excel

Going from Word into Excel isn’t quite as tricky as going from Excel into Word, although the setup of the Word content matters more in terms of how it pastes into the destination cells in Excel.

Clipboard

If you simply copy content from Word and paste it into Excel — using the Paste button or Ctrl+V — the setup of the original data controls how it is distributed in the destination cells in Excel. When you paste text that includes one or no paragraph marks, Excel inserts all of the text into the selected cell. If the selection contains multiple paragraphs, it is inserted into consecutive cells in the target column. For example, if the Clipboard contains three paragraphs and you paste into cell A1 (Row 1 Column 1), the three paragraphs are inserted into cells A1 (Row 1 Column 1), A2 (Row 2 Column 1), and A3 (Row 3 Column 1), respectively.

When pasting all or part of a table or text delimited with tabs into Excel, the cells are inserted into separate cells matching the original selection in Word. (Point to the table and then click the table move handle that appears to select the entire table or drag over cells to select them before clicking Copy or pressing Ctrl+C.) You only need to select the cell in the upper-left corner of the destination range. For example, to copy a 5 × 4 table from Word to Excel, select the table and copy or cut it to the Clipboard. In Excel, right-click in the upper-left cell of the 5 × 4 area where you want the table to appear and choose Paste or press Ctrl+V. By default, any formatting or shading from the original table appears when you paste to Excel.

Paste Options (also available via the Paste Special arrow) are a bit more limited, as shown in Figure 40.9. In this case, the two options are Keep Source Formatting (K) or Match Destination Formatting (M).

FIGURE 40.9 You also can choose Paste Options when pasting from Word to Excel.

Notice in Figure 40.9 that you may need to make adjustments to column widths and number formatting. Columns A and F in the pasted data in the figure need to be widened. In the case of column A, the column is too narrow to display the date information, so pound signs appear instead. In the case of column F, the numeric values appear in scientific notation because the column is too narrow. Double-clicking the right border of the column heading (beside the column letter) AutoFits the column to the appropriate width. Also, the dollar values shown would have their decimal points vertically aligned with Accounting or Currency number formatting applied. Use the tools in the Number group of Excel’s Home tab to work with the number formats for pasted data.

When you paste a picture from Word into Excel, it is inserted into Excel’s drawing layer rather than into cells. Note that Excel does not have text wrapping options for graphics, because cell text can’t wrap around them.

Drag and drop

Unlike when copying via drag and drop from Excel to Word, when you do so from Word to Excel, you also need to press and hold Ctrl while dragging. Otherwise the text will be moved from Word. When dragging and dropping a table, drag from within the table; attempting to drag via the table move handle doesn’t work. And if you drag and drop a table without using Ctrl, an empty table remains in Word after you move the contents to Excel.

Object

You can insert a new or existing Word document into an Excel file as an object. In the destination file in Excel, click the cell where you want it to reside. Click the Insert tab, click the Text button to display the Text group if needed, and click Object. Click the Create from File tab and, as described earlier, use the Browse button to select the file to insert. Select Link to file and Display as icon, as needed, and then click OK. The inserted document looks like a picture object on the worksheet, but if you double-click it, you can edit its contents.

PowerPoint

In some ways even more than other Office applications, Word and PowerPoint were meant to work together. That’s because PowerPoint uses heading levels that are similar to Word’s Heading styles. When creating a PowerPoint presentation, for example, it’s a simple matter to convert a Word outline into a PowerPoint presentation (or at least the basis for one), or to use a PowerPoint presentation as an outline for a Word document.

Converting a Word outline to a PowerPoint presentation

Converting a Word document outline into a PowerPoint presentation is simple — as long as you’ve used Word’s Heading styles for your outline or created it in Outline view, which automatically applies the heading styles. Unfortunately, PowerPoint is not able to extract just the outline from a Word document with body text under the headings, so you would have to strip out the body text manually yourself if the document has already been written. Also keep in mind that you need to have saved and named the file. PowerPoint opens the outline from a saved file rather than using a copy type of process.

To convert a Word outline into a PowerPoint presentation, in PowerPoint start a new PowerPoint presentation (Ctrl+N). In the Slides group of the Home tab in PowerPoint, click the New Slide button arrow, and click Slides from Outline, as shown in Figure 40.10. In the Insert Outline dialog box, find the document containing your outline, select it, and click Insert.

FIGURE 40.10 You can use a Word outline to create a PowerPoint presentation.

Converting a PowerPoint presentation to a Word document

You can also go in the other direction, using a PowerPoint presentation as a starting outline for a Word document. In PowerPoint, choose File ⇒ Save As. After selecting the save location from Backstage or clicking Browse and entering a name if needed in the File name text box, open the Save as type drop-down list and click Outline/RTF — near the bottom of the nonalphabetized list — and then click Save.

In Word, choose File ⇒ Open ⇒ Computer ⇒ Browse, navigate to the .rtf file you just created, and open it. Then switch to Outline view. The top level for each slide was assigned Heading 1, the next level Heading 2, and so on.

Using tables from Word

If a table already exists in Word, you can copy it into PowerPoint. PowerPoint will convert the Word table to a PowerPoint table. From that point on, it is a part of the presentation, and maintains no relationship to Word. You can edit its text directly in PowerPoint.

To paste a table from Word to PowerPoint, copy it to the Clipboard (Ctrl+C) in Word, and then paste it onto a slide in PowerPoint (Ctrl+V). The resulting table appears in the center of the slide.

A pasted Word table is placed into a content placeholder on the slide if an appropriate one is available. Here are the basic rules for what goes on:

• If the slide has an appropriate content placeholder that is empty, the table is placed into it but retains its own size and shape.
• If the slide does not have an appropriate empty content placeholder, the table is inserted as a free-floating object, unrelated to any placeholders.

Word’s table feature is somewhat more robust than PowerPoint’s. If you want to maintain all the Word capabilities in the table, paste the table as a Word object instead of doing a regular paste. Follow these steps:

You can also use the Paste Options button that appears immediately after you paste the table. Click the third icon: Embed. The resulting table is an embedded object and cannot be edited directly using PowerPoint’s table feature. To edit the object, you must double-click it to open it in Word.

Integrating Excel cells into PowerPoint

If you need calculating capabilities in a table, consider embedding Excel cells into the slide instead of using a traditional PowerPoint table:

You’ve just created an embedded Excel object. It does not exist outside of this PowerPoint file; it’s a mini-Excel spreadsheet that you use just for this one presentation. If you want to embed content from an existing Excel file, copy and paste it as in the previous section on Word tables.

Outlook

Outlook 2013 contains a number of tools for creating and formatting messages that greatly resemble the tools for creating and editing documents in Word 2013. This integration of functionality in both programs should make it comfortable for you to use both programs together. You’ve already seen in Chapter 10, “Data Documents and Mail Merge,” how to use your Outlook Address Book contents for a mail merge. Read on to learn other ways that Word and Outlook interact.

Using the Outlook Address Book in Word

One of the more conspicuous relationships between Word and Outlook is in the use of the Outlook Address Book for addresses in Word documents — especially letters and envelopes. For example, in the Create group of the Mailings tab in Word, click Envelopes or Labels, and then click the Insert Address button (see Figure 40.11), which opens the Select Name dialog box.

FIGURE 40.11 You can access the Outlook Address Book using the Insert Address button.

The Select Name dialog box appears, as shown in Figure 40.12. If you have multiple address books set up, click the Address Book drop-down arrow and choose the one you want. Note that the Search option enables you to search the Name only or More columns. When you use Name only, the dialog box displays only names that start with what you type.

FIGURE 40.12 Insert addresses for Word envelopes or labels via the Address Book.

Alternatively, click More columns, type what you’re looking for, and click Go. This search feature searches for occurrences of the search text anywhere in any contact field. If that still gives you too many hits, click Advanced Find. Use the Find dialog box to search for names containing text you type. When you find the person or business whose address you want, select it and click OK.

Once the address you need is selected in the Select Names dialog box, click OK, finish selecting settings in the Envelopes and Labels dialog box, and then click OK again.

Access Imports and Exports

The External Data tab in Access 2013 is divided into two groups (Figure 40.13): Import & Link and Export. You can easily import data from an Excel workbook file by choosing External Data ⇒ Import & Link ⇒ Excel, and then following the steps presented in the Get External Data dialog box. Use the Text File button in that group to import a delimited file that you’ve created in Word and saved as a plain text file.

FIGURE 40.13 Access by design can import and export a variety of file formats.

On the Export group side, you can select a table or query in the Access Navigation pane, and then export it as Excel or a Text File, or use the More button to select other Export formats. If you have a table or query that includes mailing address information selected in the Navigation pane, click Word Merge in the Export group to start the Microsoft Word Mail Merge Wizard (Figure 40.14). Choose whether to merge to an existing document or create a new one, and then use the choices on the Mailings toolbar to set up the merge as described in Chapter 10.

FIGURE 40.14 Use a mailing list from Access in a Word mail merge.

Other Access objects can be exported to differing formats. For example, if you’ve selected a report in the Navigation pane, you can use the PDF or XPS choice in the Export group to save to that file format to share information with other users.

OneNote

Until you get in the swing of using OneNote for note taking and project tracking, you might still do some of that work in Word. You also might have documentation previously generated in Word that you want to transfer to OneNote, or OneNote information that you want to incorporate in a more formal document in Word. Transferring information between the two applications is a breeze, making it easy for you to choose the note taking tool that’s appropriate for any given situation.

Printing from Word to OneNote

When you want to incorporate information from a Word document into OneNote, you “print” from Word to OneNote. Start by opening the document to print in Word. To send only a portion of the document to OneNote, select the information to transfer.

Choose File ⇒ Print. In the Backstage view, open the Printer drop-down list, and click Send to OneNote 2013, the choice shown in Figure 40.15. If you selected part of the document to send, open the first Settings drop-down list, which initially shows Print All Pages, and click Print Selection. Then click the Print button near the top.

FIGURE 40.15 Printing a file from Word to OneNote

OneNote opens, and the Select Location in OneNote dialog box appears, as shown in Figure 40.16. Select one of the notebooks under Recent Picks, or use the folder tree under All Notebooks to choose a different destination. Then click OK. Word inserts the information as a page image on a new page tab in the specified notebook.

FIGURE 40.16 Selecting a Notebook destination for the “printed” Word information

Sending from OneNote to Word

You can send a notebook page from OneNote to Word. The notebook page’s information — notes, links, pictures, and more — are converted to an appropriate format and placed in a new Word document with as many pages as needed to include all the information. After selecting the page to send, choose File ⇒ Send, and then click Send to Word.

Summary

In this chapter, you’ve learned several ways to exchange data between Word and Excel. You’ve also seen how to convert Word outlines into PowerPoint presentations, and how to create a Word outline using a PowerPoint presentation. Additionally, you’ve looked at several ways that Outlook and Word stay in contact. You should now be able to do the following:

• Insert Excel content in a Word document and vice versa using a variety of different methods.
• Use the Clipboard and drag and drop to copy or move content between Word and Excel.
• Exchange outlines between Word and PowerPoint.
• Paste a Word table or Excel cells into PowerPoint.
• Use the Outlook Address Book tool to insert a contact’s address in Word envelopes or labels.
• Identify what file formats Access can import and export, including how to use a table or query of addresses in a merge.
• Print information from Word to OneNote or convert a OneNote page to a Word document.