Monday, September 30, 2013

Publication Quality Figures in Excel

Whoever at Microsoft thought that the cartoon-ish format defaults for Excel graphs clearly doesn't understand the professionalism required for journal quality figures.  It's actually so bad that when I see scientific data presented in default Excel graphs, I instantly begin to question the validity of the work.  If anyone at Microsoft happens to read this post, please do some homework and compare the quality of Excel figures to let's say OriginLab or GraphPad.

Besides the visuals, Excel also needs to be able to export figures at fixed dimensions as a variety of formats.  PNG is probably the most viable since it works with both word documents and PDFLaTeX.

Fortunately, all hope is not lost.  Many of us have access to Excel but not other graphing programs (cost!).  Follow along and before you know it, your figures will be publication worthy!

First off, you'll need the free add-on Daniel's XL Toolbox This add-on allows for export of high quality figures with set dimensions with a variety of formats.  Great Job on this addon!

Download my template.  Click on the link.  Then in the new window select File>Download or click the down arrow button at the top left.

You can replace the data and the figure will automatically update itself.  A couple key tricks:

1)  Format Chart Area and fix the height and width.  I prefer 1.8 x 3" for fitting two figures side by side.
2)  Everything needs to be black and white (or grayscale if acceptable)
3)  I prefer time new roman for the font 10 pts
4)  Axis titles are italicized (any variable is italicized)
5)  The real trick is making sure everything lines up
 - Notice the cells behind the figure are very small.  Under chart tools>Format>Align select snap to grid.  Now when you drag an axis or label, it will always snap the the cell boundaries.  The smaller the cells, the more control over positioning.

This will get you started.  If you're starting from scratch, you'll most likely have to set the line width for the axis and data.

Once you're happy with the figure, click XL Toolbox>Export>Export for publication. Select the format and check the height and width.  This often isn't correct.  I don't know if this is a bug or what.  To fix it:  go back to the figure go to Format Chart Area>Size and click Lock Aspect Ratio.  Sometimes when it's locked you'll need to unlock it, sometimes when it's unlocked you'll need to lock it.  Either way, this fixes things.

Here's the final product

Update:  Use 1000dpi and use the PRINT and select Adobe PDF command from Word or else the images seem to be downgraded.  I used the max dpi (2400) and it couldn't handle it in Word 2013.

No comments: