Saving Excel charts for publications

Saving Excel charts for publications

We’ll discuss two ways to save a high resolution graph from Excel: without a plug-in and with a plugin.

Using only standard tools it’s possible to copy the graph in Excel and paste the graph as a graphic and that the graphic will have 150 DPI. To verify the DPI, set the size of the Excel graph to something, say 8×4.5, which is the HD or 1920×1080 aspect ratio, copy the graph, and paste it as a raster graphic, perhaps into MS Paint. The resolution is will be HD. To calculate DPI = width / width of graphic, perhaps 1922 / 8 = 150.25 (update these numbers). If the DPI is not exactly 150, perhaps 150.15, the graph is probably surrounded by a border, which seems to add to the graph’s size. In that case, change the graph’s Format Chart Area and select Border/NoLine. Repeat this process to verify DPI is indeed 150. Now, to achieve a graphic with 1920×1080, simply set the chart size to 1920/150 = 12.8″ and height to 1080 / 150 = 7.2″ (This method should work for metric as well, I assume there’s a DPmm unit.) Now export the single graph, and past into a graphic editor. The resolution should be 1920×1080.

Related to this approach, I like to use Greenshot as a clipboard tool for making screenshots and capturing sections of screenshots and optionally saving to the clipboard or files. From its icon in the taskbar, there’s an option for saving the contents of the clipboard to a file.

While simple, a big drawback of this approach is 12.8×7.2″ charts are huge. Also, performing a copy-paste-save-rename operation for each graph takes extra time, especially if the charts are later updated. Instead, I prefer to use Daniel’s XL Toolbox. With this Excel plugin, a chart can be selected and exported with the “Export selection” option or all the charts (and optionally all other graphic items including embedded images) in a workbook can be exported with the “Batch Export” option. Each have options for controlling the final resolution and DPI. In Excel, I size the charts to 8×4.5″ and then export with 1200DPI. The resulting PNG image files have excessive resolution (9600×5400), but I use the default 300 DPI, the exported charts look blocky especially in the text. The exported charts at lower resolution (and higher DPI than Excel) look poorer than Excel. However, high DPI exports look nice. The high DPI exports also have a reasonable choice of font size.

Combine the convenience of one-click export of a single or all charts make this donation-ware open-source plugin well worth considering. Besides the chart exports, Daniel’s XL Toolbox includes a number of analyses that I need to explore. I currently pay for Analyse-It, which is reasonably priced, but I use it infrequently, and would like to avoid maintenance fees.