How do I get Word and Excel to work together?

February 5, 2009 10:07 PM

I need MS Excel and Word to play together nicely. I can do what I need to in Word, and I can do what I need to in Excel; it's the Relationship that's Complicated. Maybe I need workflow advice? Basic knowledge? Please, please keep reading.

I'm putting together a quarterly report for an ongoing evaluation of a county youth program. We're currently in Q2. We get lots of quantitative data, run it through SPSS and Excel to make lovely charts and graphs (like the average number of tutoring hours for females across tutoring agencies, that kind of thing). Then, my job is to write up a narrative description of what's going on, and put it together in ONE document.

This is the problem.

So, the final document will have a couple paragraphs of text, and then a chart that I've copied over from Excel, then more text, and on and on. Writing up the text and charts takes 20% of my time. Formatting, moving, adjusting, placing, chasing paragraphs around takes up the other 80%. It seems to me that with two programs that are part of a "Suite," it can't possibly be that hard.

Here's how I currently do it:
1) Write paragraph of text in Word.
2) Go to line after paragraph.
3) Copy chart from Excel.
4) Post Special...Microsoft Excel Chart Object
5) (Because now the text looks all goofy from how it looked in Excel) Right click > Format Object... > Layout > Click 'Square' and 'Center'
6) Then I have to go back above the chart and start typing the next paragraph, but I have to move the chart up manually to push the new text below the chart. [This is where it starts to suck.]

Now, god forbid that, after I have all my text-chart-text-chart sequences done, I have to go back and add a sentence or a paragraph, or move (or, *gasp* resize slightly) a chart, because then my charts will suddenly move behind each other, or on different pages, or they'll move to above the top margin on a page and become Completely Immovable.

So, I need some advice on how you create a document in Word that has 20+ pages of text and 25+ charts pasted in from Excel in a way that you can then make adjustments to the document and not have everything completely screwed up. What's your Word+Excel workflow?

posted by cheeken to computers & internet (14 comments total)

Do you need the Excel charts to be editable from within Word?

If you don't, then why not paste them as plain "Picture" (I think that's what it's called, the one that says something like "of all the options, this takes up the least amount of room and displays the nicest in Word"). You may have to do some formatting changes in the Excel chart to make it look good when you paste it as a picture, but that's pretty easy with some experimentation.

As for the layout, unless you need text to flow around the chart on the left and right side, just set the picture wrapping style to "In line with text", which will place the chart on its own line within the paragraph. Then use the text formatting to center it, either directly or using styles.

No matter what you do, make sure to turn on paragraph formatting marks so that you can see what you are doing. They will allow you to see exactly where in the document the anchor for the picture is set, so that you can easily see where you need to insert text that will go above/below the chart.
posted by gemmy at 10:21 PM on February 5 [1 favorite has favorites]

Word just sucks for the relationship between text and images. On one long report, I'd tried all the advanced options, showed the paragraph marks, etc. etc., and finally, when I selected the picture and hit the "up" arrow, and the picture moved down, I realized it was not my fault. Could you get your office to buy you some page layout software? They should once they find out it would make you five times as productive.

Unlike gemmy, I don't like "in line with text." I go to advanced options and I generally use "top and bottom."

I have to move the chart up manually to push the new text below the chart

Hmm, why not make a fake new paragraph (hit return twice and typing "ajajajajajaja" below) below before inserting your image in between the two paragraphs?

I too would try just pasting it normally and not as an Excel object.

You might also look at the checkboxes under Format Picture -> Layout -> Advanced Layout -> Picture Position tab -- checkboxes there like "move object with text," "lock anchor," and "allow overlap" are worth some experimentation. (Then again, I was using these when I realized that in Microsoft Word's world, up = down, so they don't solve every problem.)
posted by salvia at 10:35 PM on February 5

Do you need the Excel charts to be editable from within Word? If you don't, then why not paste them as plain "Picture"

I was going to say that I've always had to Paste Special because when I tried to just copy them as pictures, the text was always smooshed and looked awful.

BUT, I just tried it, and on a fluke, after I copied the chart into Word, if I 'right-click > Edit Picture, all the text turns out like it should. That this happens actually makes no sense to me, but I checked it in Print Preview, and the text smooshiness is gone.

Hmmm....this may just work. Also, I always forget how helpful formatting marks are, so thanks for the reminder.
posted by cheeken at 10:46 PM on February 5

Here's what I do:

1) Write paragraph of text in Word.
2) Go to line after paragraph.
3) Copy chart from Excel.
4) Paste Special --> Picture (Enhanced Metafile)
5) Then I double click the mouse below the chart and start typing the next paragraph.
posted by Jasper Friendly Bear at 10:48 PM on February 5

Agreed with gemmy that you may try pasting charts as pictures, then you don't need to adjust the formatting of charts.

An alternative way is to use "PowerPoint + Excel" rather than "Word + Excel". You can write down your analysis report in PowerPoint, which is more user-friendly when dealing with the combination of text and charts. Change the PowerPoint page setup as "portrait" and you will get an output file just looks like a doc. If anyone don't like to open file with PPT, you can print it as PDF.
posted by yezimary at 10:49 PM on February 5

@Jasper Friendly Bear

I've attempted the double-click method before, and it worked for a bit, until I had to move a chart to a previous page, and found it ended up at the end of the document instead. What do you do when you have to move things around?
posted by cheeken at 11:04 PM on February 5

Moving charts around is a pain. Usually I end up doing the following:

1) In Word, I right click on the chart and choose format picture
2) From the dialog box, I select the layout tab.
3) On the layout tab, for the wrapping style, I choose "In Front of text" and hit ok.

This will mess up your text, but your chart will now become free floating.

I then move the chart with the mouse to where I want it to be. And then I go back to layout tab dialog box and choose "In line with text" as the wrapping style. This will make the text go around the newly placed chart.
posted by Jasper Friendly Bear at 11:13 PM on February 5

If you save the excel spreadsheet as a webpage (it's an option in the 'file' menu) it apparently spits out all the graphs as gif files in the same folder as the xls. I haven't done it but I've seen my boyfriend do it and his graphs look exactly as they do in the spreadsheet, no smooshy text and stuff (which I have also experienced). Then you can just insert or paste them straight into word being confident that the formatting won't change.

It's definitely easier to position pictures rather than embedded excel objects and this although still annoying, and this might at least make it easier to keep the graphs looking how you like? I generally do the same as Jasper Friendly Bear for getting things to sit where I want them.
posted by shelleycat at 11:55 PM on February 5

Another tip for layouts in Word is to insert a table where you want your picture or chart to be.

Once you've finished your normal paragraph - use the table wizard to insert a table after the paragraph. This table should be the width of the entire page. If the chart only takes up half the width of the page then split the table into 2 cells - you have the chart in one, and (if you like) accompanying text to the left or the right in the other cell. Once you're done with the chart and text, exit the table and just start writing again normally.
posted by Mave_80 at 2:27 AM on February 6 [1 favorite has favorites]

The method I just oultined should avoid your problem when you add text after having already inserted the chart etc, since the table will move with everything else on the page and the picture will be anchored within the table...
posted by Mave_80 at 2:28 AM on February 6

There is an easier approach. Insert the image in a TABLE. You can edit within the table and the text automatically moves before and after the table for editing. The table is a nice way to constrain the image and still get to edit the image if necessary.

When you need to fake layout control in MS Word, the table function is your friend.

Ha, you could even use Publisher with the insert word text function if you want a more "pretty" report. Be aware that backwards/forward compatibility is an issue.

How big can the documents get with using tables and text? Hundreds of pages. The PDF conversion can be a little tricky so always review before distribution in case the style sheet of the document went slightly weird on you.
posted by jadepearl at 4:39 AM on February 6

Nthing using the Paste Special->Picture (Enhanced Metafile) option and sticking your charts in tables. Word sucks.
posted by mullacc at 9:13 AM on February 6

I'm not sure if this is functionally any different from Paste Special-ing into Word, but if you hold down Shift when you click the Edit menu in Excel, you're presented with the option to "Copy as Picture...". That might help.
posted by losvedir at 10:43 AM on February 6

Thanks all, I'm gonna give the tables a shot. It makes sense that it would keep the image in line with the text. I'll see how it goes!
posted by cheeken at 10:31 AM on February 7