Publication-quality plots with Excel

February 28, 2012

Microsoft Office tools are not the best choice for scientific publications, but sometimes you have to use them. Some journals actually require Word documents, and many of my collaborators simply don’t do LaTeX. The process outlined below is they best way that I’ve found to make high-quality plots and charts with Microsoft Excel. High-quality graphics with consistent size, aspect ratio, typeface, and font size will make your publications look much more professional. You can do this with Excel, but it requires some effort. You will need to install the free Bullzip PDF Printer before you begin. 1. Create and format the chart correctly in Excel. It’s better to insert a “floating” chart in an existing worksheet, as shown below, rather than creating a new worksheet that contains only the chart. Set the size of the chart by right-clicking near the edge of the chart and selecting Format Chart Area. Then select Size and set the actual size and aspect ratio that you want (try 3 inches by 4 inches). If you want a set of plots to have consistent formatting, it is very important that they are the exact same size in Excel! If the finished plots must be different sizes but have the same formatting, make the outer dimensions for all the charts big enough to accommodate the largest plot. Then shrink the plot within the frame, leaving whitespace which will be cropped out at a later step. Also set correct font sizes, line widths, colors, etc.

2. Select the chart and print. This will print the chart, by itself, on its own page. When the Excel print dialog opens, select “Bullzip PDF Printer” instead of your normal printer and press Print. The Bullzip dialog will open and allow you to choose options for printing to a file. Choose a TIFF file in the General tab and set the output file name. Then go to the Image tab, select tiff24nc (24-bit RGB) and set the resolution to at least 600dpi. I have not gotten the other TIFF formats to work correctly. Press the Save button when finished. The resulting file will be quite large because it is uncompressed.

3. Open the saved file in an image editor such as the GIMP. You will see that the chart has been printed on a large white page. Use the crop tool to eliminate the extra whitespace around the chart. Then choose “Save As” to save the image. After you choose a file name, another dialog will appear to select TIFF options. Choose “LZW” compression–it’s lossless, which is good for plots and line art.

Here is a link to the finished TIFF image. This method is much more reliable and consistent than copying and pasting plots from Excel into Word or Powerpoint.

Comments

Comment #1 by anthony

anthony - May 5, 2013

Thanks for the great write up. I have been using this method, however the plots that come out of GIMP never seem to be the same size as they are set in excel. Say I want a 3"x4" plot to go into Microsoft Word - I make my Exel plot this size, go through the method outlined, and then the TIFF file inserted into Word is much bigger than 3"x4"? Any way to fix this? Thanks,

Comment #2 by Chris

Chris - Jan 3, 2014

If you’re forced to use Word, I think that copying from Excel and pasting into Word is the best solution since it retains the vector graphics that Excel produces. If you do want a standalone image to use in Word, you can copy the plot into Powerpoint, right click on the plot, and export it as an enhanced metafile (EMF), which also retains the vector image. If you’re able to use LaTeX to create your document, a PDF or EPS file is probably the preferred format. I find that printing to a PDF file from MS Excel will retain the vector graphics. One caveat: You have to make sure that you select the paper size to match your figure size, and turn the page margins to zero inches. Once the image is a PDF, it’s straightforward to convert to an EPS with ghostscript if desired. If desired, the PDF/EPS files can be modified somewhat in Inkscape (FOSS software). The TIFF rasterization method above is acceptable, especially if you don’t know where the images will be used. However, it generates files which are quite large and which scale poorly.

Comment #3 by Craig

Craig - Jan 5, 2014

Chris, thank you for the suggestions. I developed my method based on TIFF files because I had bad experiences copying plots from Excel and pasting into Word. Word changed the format of the graphics, such as changing the width of the white space around the plot. That won’t work for submitting an article to a journal that requires specific dimensions for graphics. Further, most journals want the images uploaded as separate files, rather than embedded in a Word document.

Technical Writing