« A Bit of Shameless Self-Promotion | Main | Charting Data in PowerPoint »
In a recent post, I said that it's possible (and easy) to size Excel charts perfectly for Word with no trial and error. A very talented Excel MVP, Jon Peltier, posted a comment asking for my take on this topic. I'm very glad he asked (this is one of my favorite little timesavers... shared by Investment Banking document production operators everywhere).
If you have anything to add to the info that follows, Jon, please do! (That goes for everyone, of course!)
When you use a Word table cell as a placeholder for your Excel chart, you can use the dimensions of the table cell to calculate the size for the chart (in almost no time at all!) Here's what you do ...
- Say, for example, that your Word table cell is 4.2 inches wide and 2 inches tall. (see the downloadable sample files below, if you'd like to try this without taking time to set up a table and chart first).
- Now, place your Excel chart (if it isn't already) on it's own sheet in the Excel workbook. Then, on any worksheet, perform these simple calculations:
(Note: the calculations assume your chart sheet is a landscape, letter size page -- that is, 11 inches wide by 8.5 inches tall. If it's not, substitute below for your paper size and orientation.)
=(width of the chart sheet -minus- width of the Word table cell)divided by 2... which in this case is:
= (11-4.2)/2
(result for this case: 3.4 )
=(height of the chart sheet -minus- height of the Word table cell)divided by 2...which in this case is:
= (8.5 - 2)/2
(result for this case: 3.25)
Enter the results of the first calculation (3.4) as your chart sheet's LEFT and RIGHT margins (do this in File, Page Setup), and the results of the second calculation (3.25) as the chart sheet's TOP and BOTTOM margin. When you do, the chart will instantly become a perfect fit to your Word table cell!
It's quite likely that your chart will need a bit of reformatting if the new size is much different than the original. So, if you'll be adding text boxes or other drawing objects to the chart -- you'll save time if you do so after it's resized. Same goes for custom font formatting on the chart.
Tip: Best thing you can do to make reformatting fast and easy? ZOOM IN TIGHTLY before editing. Excel's view accuracy increases substantially when you zoom.
I always suggest turning off Auto Scale for chart fonts before resizing (your text might appear to take over the whole chart after sizing when you do this ... but it only takes a second to select the chart area and change the font size). That's just personal preference because I don't like it when the text on my chart disappears into oblivion :) To turn off Auto Scale -- select the chart area (because font formatting you apply to the chart area affects all parts of the chart), then go to the Font tab of the Format Chart Area dialog box and deselect the Auto Scale check box.
When you are delighted with your beautiful newly-sized Excel chart, just copy it and paste into Word as a PICTURE (this is the most secure way to paste any Excel object -- to be sure that the recipient sees what you want them to see ... and to be sure that you're only sharing the content you want. In case you didn't know ... Excel objects pasted into Word as embedded objects actually embed the ENTIRE workbook from which they originated.)
IMPORTANT: Once it's pasted as a picture, be sure that the picture's layout is In Line With Text -- available from Format, Picture, Layout. That way it will sit snugly and securely in the Word table cell you've created for it. And, when a graphic is in line with text, you can format it with font and paragraph formatting just as easily as you do text characters. If you'd like more information on in line with text vs. using graphics with text wrap in Word ... check out my earlier post To Float or Not to Float
Okay - here are those sample files. These files were formatted using the tools in my book (the MODD tools). In case you have the MODD tools, the Word document download tells you in the text of the page what tools were used to create the page and size\place the chart. But, you don't need MODD to use the instructions in this post or the sample files -- it's all Word and Excel functionality :) MODD just does a lot of the work for you... Here are the files:
Posted by Stephanie
TrackBack URL for this entry:
http://www.arouet.net/cgi-bin/mt/mt-tb.cgi/27
Thanks for signing in, . Now you can comment. (sign out)
(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)
Comments
Stephanie -
You flatter me. No better way to encourage a reply.
You've outlined a nice procedure and you've described it clearly. It would be readily converted to automation.
If I'm just using pictures of the charts, I just use embedded Excel chart objects, size them appropriately, and I use Excel's Copy Picture method (hold Shift when selecting the Edit menu), then just paste in the target application (as often as not PowerPoint for my recent clients). Most of the time I encourage pictures of charts, for security and stability. If the chart updates, copy and paste another picture, eh? Especially if it's done in VBA anyway.
If the chart still has to be a live chart (editable as an Excel OLE object) you have to use the chart sheet method. Otherwise it may resize itself to match an integral number of rows and columns in tha parent worksheet. There are ways to copy less than the huge workbook when copying a chart. You can copy the chart, and use paste link in the target app (which unfortunately didn't work through automation in PowerPoint until 2003). You can copy the chart sheet and a worksheet with just the chart's data into a new workbook; this way you're pasting just the chart and its own data. You could copy the chart sheet only into a new workbook; this way it retains its links to the original data, and as long as it's on the same machine (or presumably on the same network) the chart will stay updated. You'll be sorry, though, if you update a chart with broken links.
These techniques that I describe, and that closely match yours, were related to me mostly by PowerPoint MVP Brian Reilly. He referred to it as "Bovey's Method" after Excel MVP Rob Bovey (appspro.com). Rob's the guy who wrote the XY Chart Labeler, which is a handy tool to add data labels from the worksheet to data points on the chart.
You are correct to warn readers about the font autoscale feature. I don't like how it works, and I dislike having all the fonts in a chart be 9.5 points. I'm funny that way. I have a description and links on my site that describe how to change the Windows registry to turn this off by default. Be sure to sharpen that axe before hacking around in the registry.
Since one of your factors is calendar quarter, I'd be more inclined to use a line chart. The lines help convey the passage of time. I've made a mess of a sample, if you're interested.
Posted by: Jon Peltier | November 30, 2004 09:20 AM
Thanks for the reg tip with AutoScale, Jon!
The only thing in your comment that I MUST take issue with is your note at the end about the sample chart I provided. Just because the x-axis uses time periods, doesn't mean that the reason I'm using the chart is to convey trends over time. If, for example, I want to emphasize the differences between individual periods -- then the size of individual columns can be far more impactful than using a line. :)
Posted by: Stephanie Krieger | December 5, 2004 04:04 PM
Stephanie,
Your instructions on how and where to, essentially, copy and paste a spreadsheet into a Word or PowerPoint document are all well and good. However, as I learned in a Spring 2005 graduate course w/ presentations, the data on such spreadsheets have become so small that, even in the PowerPoint presentation projected onto a screen, it cannot be read.
The more practical answer is to summarize the important spreadsheet data in the PowerPOint program. For the few coworkers and audience members who truely have a concern for the details, have a "handout" ready of the original data.
Crispin L. Fowler
Posted by: Crispin L. Fowler | October 12, 2005 11:04 AM
Hi, Crispin,
You have a good point, but it's a separate topic.
You are assuming that a worksheet is huge. First of all -- the instructions in this article are for copying charts, not worksheets. But, for copying worksheets -- which I have also provided info on in another post ... don't assume that the entire worksheet is included. If you have a very large worksheet that will need to be reduced to the point that it can't be seen well -- you're quite right to summarize it.
But, people commonly copy just a single table from Excel to Word or PowerPoint that will resonate very nicely in a document or presentation.
When it comes to the effectiveness of the presentation, only you can judge if there is too much information to make it effective on the page. For some tips on creative effective presentations -- as opposed to the technical steps of copying content between programs -- see my article on Microsoft At Work entitled 12 Tips for better presentations. http://www.microsoft.com/atwork/getworkdone/presentations.mspx
Thanks for posting!
Stephanie
Posted by: Stephanie | October 12, 2005 11:10 AM