How to get presentable graphs and figures from Excel

Excel's default settings for graphs often result in something a bit ugly, and getting those graphs out of Excel can be a finicky process. Font sizes get screwed up, the top and right tickmarks are usually missing by default, and so forth...

So, here's my quick reference for getting Excel plots to look the way I want them to look, so that they will show up as intended when used with a proper typesetting system such as LaTeX.

This seems to work for Excel 2007/2010 and XP/2003

Get the chart on its own page

Create it on a new sheet by itself. Or, if it's on a normal worksheet, right-click the chart, choose "Move Chart" (07/10) or "Location" (XP/03), and put it on a new sheet.

Page size and aspect ratio

When looking at the chart, adjust the page size (in 07/10, "File > Print", look for page size under "Settings")(inXP/03, "File > Page Setup"). Default should be "Letter, Landscape". If you want to change the aspect ratio, leave the width at 11 inches and set a custom height.

Alternatively, set the custom page size for the chart to exactly the dimensions at which it will be eventually printed. (This may not work correctly with some PDF converters, though; you might get a small graph in the middle of a large white page instead. Test it on your own version to be sure.)

Add ticks on the top and right axes

You need at least two data series to do this. (If you have only one series, add a dummy series.) Right-click a data point on the second series and choose "Format Data Series". Then:

(07/10) Under "Series Options", choose "Plot Series On Secondary Axis". Then switch to the "Layout" ribbon, click "Axes" and select "Secondary Horizontal Axis > Show Default Axis".

(XP/03) Choose "Axis", then "Plot Series On Secondary Axis". Then right click on the plot, select "Chart Options", "Axes", then "secondary axis - value (X) axis".

Format the axes

Right-click each of the four axes in turn, choose "Format Axis", and set the following parameters. Use identical settings for both Y axes, and use identical settings for both X axes.

Scale- set min/max/units to whatever you like. "Auto" will screw up if your data series have different ranges.

Set "Major tick mark type" to "inside". For the secondary axes (right and top), set "Horizontal axis crosses" to the same value as the Y-axis "Maximum", and set "Axis labels" to "None".

Set "Number" to whatever you like and leave "Fill" set to "Auto" or "None". Set "Line colour" and "Line style" to give a solid black line.

Fiddle with the "Alignment" if you want, but the defaults are usually OK.

Gridlines

(07/10) Right-click a gridline and choose "Format Gridlines". Set the line colour to "No Line" unless you have good reason to keep them. To get them back, right-click the axis and choose "Format Major Gridlines".

(XP/03) Right-click the plot, choose "Chart Options", set "Gridlines" off unless you want them.

If there's a perimeter border or a background fill

Right-click outside the axes and choose "Format Chart Area". Turn off the border and fill if they're on. Right-click inside the axes for "Format Plot Area" and set "Area" or "Fill" to "none".

Font sizes to use on an 11” wide graph

If you're going to set your graphs to 11" wide in Excel, and let the typesetting software (eg. LaTeX) scale the resulting graphs to fit the column width, choose the graph's font size according to this chart so that it will end up at the correct size in the final printed copy.










 

Queen's Theses

Layout

\textwidth

(Margins 1.5" L, 1" R)

0.24\textwidth

(4 per row)

0.32\textwidth

(3 per row)

0.49\textwidth

(2 per row)

0.6\textwidth

(1 per row)

0.8\textwidth

(1 per row)

Col. Width (Inches)

6

1.44

1.92

2.94

3.6

4.8

Pixels @ 300 dpi

1800

432

576

882

1080

1440

If you want this font size (pt)…

then for the column width above, you should set this font size on an 11" wide Excel graph (pt).

8

15

61

46

30

24

18

10

18

76

57

37

31

23

12

22

92

69

45

37

28

14

26

107

80

52

43

32










 

Journals

Layout

2 col,

1/2" margins

2 col,

3/4" margins

2 col,

1" margins

1 col,

1/2" margins

1 col,

1" margins

Col. Width (Inches)

3.5

3.375

3

7.5

6.5

Pixels @ 300 dpi

1050

1012.5

900

2250

1950

If you want this font size (pt)…

then for the column width above, you should set this font size on an 11" wide Excel graph (pt).

8

25

26

29

12

14

10

31

33

37

15

17

12

38

39

44

18

20

14

44

46

51

21

24

Exporting to PDF

(07/10) With the figure’s sheet open, go to “File > Save As”. Change “Save as type” to “PDF” and set “Optimize for” to “Standard”. The result can be embedded directly in PDFLaTeX.

(XP/03) Tim’s method: Requires Acrobat Pro. Select the plot. Under “Adobe PDF”, “Change Conversion Settings”, “Advanced Properties” select a suitable resolution for the PDF. Under “Page Setup, “Chart”, click on the “scale to fit page” button (this keeps the original proportions of the graph). Then go to “Print”, select Adobe PDF as your printer, select “Properties” and choose “High Quality” under the options and remove the check in the “do not send fonts…” box. Finally, print the file to PDF.

Exporting to Images

Newer versions of Excel and Windows sometimes allow you to paste an Excel chart into Paint or another image editor. It's hard to control the dimensions this way, though.

Look at http://peltiertech.com/export-chart-as-image-file/ or http://www.vbaexpress.com/kb/getarticle.php?kb_id=449 for ideas on how to control the export of Excel graphs to images using VBA. PNG format works best. Never use JPG for graphs. Alt F11 opens VBA from inside Excel. The important command to remember is: Activechart.export c:\graphs\graph.png

Topic: 

Technology: 

Comments

Any errors?

Matthew's picture

If you find something wrong in this, please leave a comment and I'll fix it if need be.

A similar cheat sheet for Matlab is coming soon.

Add new comment