Importing Excel spreadsheet data into to Word
July 6, 2008 12:05 AM Subscribe
How can I make readable Word tables out of a large Excel spreadsheet?
Here's a question for the Excel/Word gurus.
I have created some data in Excel that I need to put into a Word document (.docx) in table form, preferably so that I can continue to edit the Excel spreadsheet and have the Word tables dynamically update. I tried using "Paste Special" to insert one of my worksheets as an Excel Object, but the spreadsheet is too wide to fit in my portrait-oriented Word document. What is the best way to make this data fit?
I have thought of changing some of the pages in my document to "landscape", so that all the table fields can fit. However, it seems I can only change the page orientation for the "rest of the document" to landscape, and I want to change only the middle of my document.
Also, inserting as an Excel Worksheet is quite unappealing aesthetically. The table looks grainy, and when I export to .mht, the object is converted to an image file, so that I cannot select the text.
Another, closely related question: in some situations, I want to make a bunch of tables out of one spreadsheet. In other words, let's say I have the following Excel spreadsheet:
Name | Age | Sex
----------------------
John | 12 | Male
Jane | 13 | Female
I want to convert this to two side-oriented tables of the following format:
Name | John
Age | 12
Sex | Male
Name | Jane
Age | 13
Sex | Female
Ideally, I would like to put these two tables in different places in my document, and have them automatically update when the spreadsheet changes. This seems very similar to the Mail Merge feature, except I want the output to be in my document, not to other formats.
Any ideas? Thanks!
Here's a question for the Excel/Word gurus.
I have created some data in Excel that I need to put into a Word document (.docx) in table form, preferably so that I can continue to edit the Excel spreadsheet and have the Word tables dynamically update. I tried using "Paste Special" to insert one of my worksheets as an Excel Object, but the spreadsheet is too wide to fit in my portrait-oriented Word document. What is the best way to make this data fit?
I have thought of changing some of the pages in my document to "landscape", so that all the table fields can fit. However, it seems I can only change the page orientation for the "rest of the document" to landscape, and I want to change only the middle of my document.
Also, inserting as an Excel Worksheet is quite unappealing aesthetically. The table looks grainy, and when I export to .mht, the object is converted to an image file, so that I cannot select the text.
Another, closely related question: in some situations, I want to make a bunch of tables out of one spreadsheet. In other words, let's say I have the following Excel spreadsheet:
Name | Age | Sex
----------------------
John | 12 | Male
Jane | 13 | Female
I want to convert this to two side-oriented tables of the following format:
Name | John
Age | 12
Sex | Male
Name | Jane
Age | 13
Sex | Female
Ideally, I would like to put these two tables in different places in my document, and have them automatically update when the spreadsheet changes. This seems very similar to the Mail Merge feature, except I want the output to be in my document, not to other formats.
Any ideas? Thanks!
Do you have to use Excel and Word? Can you use Access instead?
This strikes me as one of the classic points where the things you want to do strain the boundaries of what spreadsheets are for. You might be better off storing your data in a database, where you can build any reporting structures you like.
posted by bcwinters at 5:27 AM on July 6, 2008
This strikes me as one of the classic points where the things you want to do strain the boundaries of what spreadsheets are for. You might be better off storing your data in a database, where you can build any reporting structures you like.
posted by bcwinters at 5:27 AM on July 6, 2008
What is the best way to make this data fit?
Landscape view, margin adjustments, font size, table cell padding adjustments.
-You can have a mixed portrait-landscape-portrait document by putting section breaks before and after the section you want to be landscape. Then with the cursor in that middle portion, go to Page Setup and change the orientation to landscape. I'm on a Mac with slightly different and feature-restricted Office 08, but for me doing that only changed the orientation of the active section. I seem to remember there being a good many more options in Page Setup when I was on Windows but you ought to be able to do the same thing. You just may have some kind of dropdown or other selector that says what all to apply this change to.
-You can adjust the margins in this section independently. You could probably take them down to .25" though sometimes your printer will complain when you go to print. They did in the old days sometimes, selfishly calling for .32 or something. Maybe not these days.
-Smaller font. Yeah, I bet you thought of this one.
-Cell padding/margins. Word tables have a default amount of space between cell data and the lines but you can adjust it in Table Properties. That can save a fair bit of space if you have a lot of columns.
Linking to Excel
I can't tell whether you're having trouble linking the data dynamically to Excel or if you just don't like how it looks. Google tells me it's possible to make dynamic links but I can't get it to work in Mac + Office 08. Various googles say copy the excel cells and do Paste Special: html into Word. For me, that does basically the same as paste special: formatted text. There's a plain table but no link. If I instead do Paste As Hyperlink, not paste special anything, it does create a link but changes to the excel sheet don't reflect in Word as advertised, even after opening and closing both. You may have better luck with the original instructions if you're on PC.
Custom-assembled tables
I don't know how to do this from within Word, i.e. how to link a single Word table cell to a single Excel spreadsheet cell and then link its neighbor Word table cell to a different, non-contiguous location in the spreadsheet, but I have a feeling it could be done if you google/help deeper in the cross-linking lore. However what might be easier is to add another worksheet to your spreadsheet and create the custom mini tables there - - and then paste those custom tables into Word. It's easy to link cells across sheets in Excel (e.g., "=Sheet1!A2") and you can arrange the table just how you like in Excel using linked copycat data and only then paste special: html into Word. So it'll be a double link, assuming my unsuccessful html linking step above works for you. Word would draw its data from a secondary sheet that was drawing it from the main sheet, and you would only ever have to change data in the main sheet. This way you can avoid cell by cell manual assembly in Word.
posted by Askr at 6:50 AM on July 6, 2008
Landscape view, margin adjustments, font size, table cell padding adjustments.
-You can have a mixed portrait-landscape-portrait document by putting section breaks before and after the section you want to be landscape. Then with the cursor in that middle portion, go to Page Setup and change the orientation to landscape. I'm on a Mac with slightly different and feature-restricted Office 08, but for me doing that only changed the orientation of the active section. I seem to remember there being a good many more options in Page Setup when I was on Windows but you ought to be able to do the same thing. You just may have some kind of dropdown or other selector that says what all to apply this change to.
-You can adjust the margins in this section independently. You could probably take them down to .25" though sometimes your printer will complain when you go to print. They did in the old days sometimes, selfishly calling for .32 or something. Maybe not these days.
-Smaller font. Yeah, I bet you thought of this one.
-Cell padding/margins. Word tables have a default amount of space between cell data and the lines but you can adjust it in Table Properties. That can save a fair bit of space if you have a lot of columns.
Linking to Excel
I can't tell whether you're having trouble linking the data dynamically to Excel or if you just don't like how it looks. Google tells me it's possible to make dynamic links but I can't get it to work in Mac + Office 08. Various googles say copy the excel cells and do Paste Special: html into Word. For me, that does basically the same as paste special: formatted text. There's a plain table but no link. If I instead do Paste As Hyperlink, not paste special anything, it does create a link but changes to the excel sheet don't reflect in Word as advertised, even after opening and closing both. You may have better luck with the original instructions if you're on PC.
Custom-assembled tables
I don't know how to do this from within Word, i.e. how to link a single Word table cell to a single Excel spreadsheet cell and then link its neighbor Word table cell to a different, non-contiguous location in the spreadsheet, but I have a feeling it could be done if you google/help deeper in the cross-linking lore. However what might be easier is to add another worksheet to your spreadsheet and create the custom mini tables there - - and then paste those custom tables into Word. It's easy to link cells across sheets in Excel (e.g., "=Sheet1!A2") and you can arrange the table just how you like in Excel using linked copycat data and only then paste special: html into Word. So it'll be a double link, assuming my unsuccessful html linking step above works for you. Word would draw its data from a secondary sheet that was drawing it from the main sheet, and you would only ever have to change data in the main sheet. This way you can avoid cell by cell manual assembly in Word.
posted by Askr at 6:50 AM on July 6, 2008
Response by poster: Thanks so much, guys. And special thanks to Askr for the in-depth reply.
One question: I can migrate the data to an Access database, as bcwinters suggested; does anyone have guidance on what I would do from there to get my desired individual tables from each database row? (With minimal VBA knowledge.)
If the database route doesn't work, I'm either going to do a mail merge and paste the static text to the appropriate places in my document, or create my new tables in Excel and paste as Excel object.
posted by lunchbox at 10:59 AM on July 6, 2008
One question: I can migrate the data to an Access database, as bcwinters suggested; does anyone have guidance on what I would do from there to get my desired individual tables from each database row? (With minimal VBA knowledge.)
If the database route doesn't work, I'm either going to do a mail merge and paste the static text to the appropriate places in my document, or create my new tables in Excel and paste as Excel object.
posted by lunchbox at 10:59 AM on July 6, 2008
The trick is to avoid embedding an object when you mean to link. When an object is linked, information can be updated if the source file is modified. Linked data is stored in the source file. When you embed an Excel object, information in the Word file doesn't change if you modify the source Excel file.
To link:
1. Open both the Word document and the Excel worksheet with the data that you want to use to create a linked object or embedded object.
2. Switch to Excel, and then select whatever (the entire worksheet, a range of cells, or the chart) that you want in the Word document.
3. Press "Copy" or CTRL+C.
4. Switch to the Word document, navigate to the right spot and then click where you want the information to appear.
5. On the Home tab, in the Clipboard group, click the arrow under Paste, and then click Paste Special.
6. In the As list, select Microsoft Office Excel object.
7. Click Paste to insert an embedded object, or click Paste link to insert a link to the object.
If you have a linked table, you need to practice good file hygiene to avoid losing an important earlier version of your document, e.g., if you're using this trick to make it simple to create monthly reports. There are ways to
In general, to import an Excel table into Word and have it hold its format and contents accurately without creating a table mess: Microsoft explains how here.
1. Mark the range you wish to copy in Excel,
2. Hit "Copy"
3. Switch to Word, navigate to where you want the table and then hit "Paste Special."
4. In the "As" list, choose "Picture (Windows Metafile)" and insert.
The results will be at 100 percent if they fit given your existing margins, page size, etc. and will be shrunken to fit same if they are too big. You can treat them like a "picture." This solution makes for a pretty document but will not address your linking issues.
posted by carmicha at 11:08 AM on July 6, 2008
To link:
1. Open both the Word document and the Excel worksheet with the data that you want to use to create a linked object or embedded object.
2. Switch to Excel, and then select whatever (the entire worksheet, a range of cells, or the chart) that you want in the Word document.
3. Press "Copy" or CTRL+C.
4. Switch to the Word document, navigate to the right spot and then click where you want the information to appear.
5. On the Home tab, in the Clipboard group, click the arrow under Paste, and then click Paste Special.
6. In the As list, select Microsoft Office Excel object.
7. Click Paste to insert an embedded object, or click Paste link to insert a link to the object.
If you have a linked table, you need to practice good file hygiene to avoid losing an important earlier version of your document, e.g., if you're using this trick to make it simple to create monthly reports. There are ways to
In general, to import an Excel table into Word and have it hold its format and contents accurately without creating a table mess: Microsoft explains how here.
1. Mark the range you wish to copy in Excel,
2. Hit "Copy"
3. Switch to Word, navigate to where you want the table and then hit "Paste Special."
4. In the "As" list, choose "Picture (Windows Metafile)" and insert.
The results will be at 100 percent if they fit given your existing margins, page size, etc. and will be shrunken to fit same if they are too big. You can treat them like a "picture." This solution makes for a pretty document but will not address your linking issues.
posted by carmicha at 11:08 AM on July 6, 2008
oops - an editing error above. The link I provided above and here is about how to preserve an earlier version of a word document after you've changed the linked worksheet. The last set of numbered instructions is for importing an Excel table into a Word document while preserving its formating. Sorry about that.
posted by carmicha at 11:12 AM on July 6, 2008
posted by carmicha at 11:12 AM on July 6, 2008
Response by poster: I found the answer to my question.
In the Word document:
"Insert > Quick Parts > Field"; select "Database", and choose the data. This allows you to do exactly what I am doing.
posted by lunchbox at 11:37 AM on July 21, 2008 [1 favorite]
In the Word document:
"Insert > Quick Parts > Field"; select "Database", and choose the data. This allows you to do exactly what I am doing.
posted by lunchbox at 11:37 AM on July 21, 2008 [1 favorite]
This thread is closed to new comments.
Sorry: kinda lame answer, but it would achieve some of your goal.
posted by IAmBroom at 4:47 AM on July 6, 2008