Pasting Excel Data within Word's Page Margins

by Allen Wyatt
(last updated January 22, 2020)

6

Jerald uses Excel quite a bit in conjunction with Word. He creates his financial models in Excel and then pastes the Excel data into a Word document before printing. Every time he pastes the data, the "table" in Word extends beyond the right margin. Jerald wonders if there is something he can do, in Excel, to ensure that the data will stay within the page margins in Word.

There are a few ways you can approach this, with most of the solutions occurring on the Word side of the process. One option is to, in Word and before pasting, create a single-cell table that is the full width of the margins on the page. You can then paste the Excel data into that single cell, which results in Word creating a table within a table. Since the table is bounded by the outer single-cell table, it won't extend beyond the margins of the page.

You can also, within Word, choose from different available pasting options for the table. When you copy the table in Excel and switch to Word, click where you want the data inserted. Then, display the Home tab of the ribbon and, in the Clipboard group, click the down arrow under the Paste tool. From the resulting options you should notice six options in the Paste Options area:

  • To keep the data as an Excel table object, click either Keep Source Formatting or Link & Keep Source Formatting. (Note that linking is the only difference between these two options.)
  • To have the data adapt to the styles available in Word, click Use Destination Styles or Link & Use Destination Styles. (Again, note that linking is the only difference between these two options.) This option should fit the data within the margins as a Word table, with data wrapped within cells as necessary.
  • To paste the data as a static picture, click Picture. This option is very similar to pasting any other picture in Word, which means that it will not overrun the margins of the page. Instead, the "picture" is resized to fit within the margins.
  • To paste the data as unlinked text, click Keep Text Only. This results in each row of your Excel data as an individual paragraph with tabs between each column in each row. You can then select this pasted data and covert it to a Word table that extends from margin to margin.

A third option is to try modifying a deeply buried setting in Word. Follow these steps, again within Word:

  1. Display the Word Options dialog box. (In Word 2007 click the Office button and then click Word Options. In Word 2010 or later versions display the File tab of the ribbon and then click Options.)
  2. At the left side of the dialog box choose Advanced.
  3. Scroll through the options until you see the Cut, Copy, and Paste section.
  4. Make sure the Use Smart Cut and Paste option is selected.
  5. Click the Settings button, next to the Use Smart Cut and Paste option. Word displays the Settings dialog box. (See Figure 1.)
  6. Figure 1. The Settings dialog box in Word.

  7. Make sure the Adjust Formatting when Pasting from Microsoft Excel option is selected.
  8. Click OK to dismiss the Settings dialog box.
  9. Click OK to dismiss the Word Options dialog box.

If your table extends beyond the right margin after you paste it, you could also follow these steps after the pasting:

  1. Right-click the table handle to display a Context menu. (The table handle is at the top-left corner of the table; it looks like a small box containing a four-headed arrow.)
  2. Choose AutoFit | AutoFit to Contents. Word adjusts the columns widths, as necessary, to fit the data within the margins of the page.
  3. Manually adjust column widths, as desired.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13288) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Emoticons in Word

Like to add a smiley or two to your writing? Word makes it easy through creative use of the AutoCorrect feature.

Discover More

Changing the Reference in a Named Range

Define a named range today and you may want to change the definition at some future point. It's rather easy to do, as ...

Discover More

Criteria-Based Counting in a Filtered Column

The filtering capabilities of Excel are excellent, providing you with great control over which records in a worksheet are ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Changing References in a Lot of Defined Names

Need to change some cell references in your defined names? Changing one or two is easy; changing dozens is a good deal ...

Discover More

Quickly Entering Dates and Times

Excel provides keyboard shortcuts for a variety of purposes. This tip examines two such shortcuts, designed to allow ...

Discover More

Displaying a Hidden First Row

If you hide the first rows of a worksheet, you may have a hard time getting those rows visible again. Here's a simple way ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven more than 2?

2020-10-14 06:33:06

Wayne

I am trying to copy / paste an XL sheet with hundreds of rows and over 30 columns. Here is the problem - many cells (rows and columns) are merged in the XL sheet. There are also many blank cells that seem to hinder pasting into Word.

When pasting into Word, I can make this fit into a letter-size width page - but - the data re all mis-placed in the cells, due to the complex merging in the XL sheet.

Other than manually un-merging all the merged cells in the XL sheet - Is there a way to retain the cell merges on the Word side?


2020-01-30 19:22:24

Peter Atheron

Sateesh,
I'm not a typist but I suspect that the data in Word is badly formatted (too many tabs). Some years ago a typist was complaining that her lovely looking word-processed data would not sort properly and this was the problem. try sorting the data in Word and see if it sorts correctly. If not then you will have to delete the excel tabs. In the future get the typist to enter the data into a table.
Deleting Tabs in Word.
Place the cursor behind the first word,, hold SHIFT and press the right arrow key until you reach the next word. Then press Tab. You have to do this for every word on every row. When it is finished you can elect the whoe data set and format the tabls by clicking on the Ruler.
As I said before I'm not a typist, but when I do the Show/Hide button (Ctrl + *) is always active so that I can mke sure the formatting is regular.

(see Figure 1 below)
(see Figure 2 below)

Figure 1. Data In Word

Figure 2. Results when copied to Excel


2020-01-29 02:39:24

sateesh

I have been working on importing the data from word to excel and could not able to get the data in proper alignment.
there are 25 pages in the word document and it is containing the tables and paragraphs i have to export the data as it is in word to Excel.
is it possible that what ever data exist in the word document and that has to be exported to worksheet by page wise.
could you plz suggest me about query.


2020-01-09 09:35:40

Brian

The single cell table option was brilliant. Solved my problem in seconds.


2019-06-05 09:56:19

David Steinberg

Dear Allen,

I have an Excel worksheet that has about 21 columns with more than 800 rows. Some of the columns are quite wide.

I want to convert the spreadsheet to Word, but have the rows become vertical instead of horizontal.

Is this possible to do?


2016-12-12 09:23:44

Rocco

Just what I needed to know. Other sites didn't help me like yours did.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.