Excel for Papers Crib Sheet

Using Excel to male charts suitable for insertion into papers.

  1. Before starting, work out in your Illustrator doc how big you want the graph to be; make labels for each axis (9 pt is good, 12 pt for categories, eg bar chart X axis) and align them to their respective margins, then align guides to the inner margins this defines, and draw an unstroked box into the space using smart guides; round the size of this box down to the nearest mm to get your target size; leave the box there, and call it the bounding box
  2. Select data (not labels or anything) and make a graph
  3. Pick graph type
  4. Verify data
  5. For bar graphs:
    1. Ensure all titles are clear
    2. Leave both X and Y axes selected
    3. Deselect all gridlines
    4. Deselect 'Show legend'
    5. Make chart
    6. Format X axis, set 'tick mark labels' and both 'tick mark type' options to 'none'
    7. Format Y axis, set 'tick mark labels' to 'none'
    8. Format plot area, set fill colour to 'automatic' (should be white; if not, pick white!)
    9. Format data series, set fill colour to black (or whatever) and deselect 'shadow'
    10. Format both the axes and set their line weight to something finite and suitable - 1 pt seems to be good for a graph ~10 cm high
  6. Finish all graph-specific steps before the next step, because this somehow destroys it as a graph (oh no, hang on, this seems to be about selection mode)
  7. Select the graph as a graphics object (click right near the edge), and use the size controls in the formatting palette to set the size (this tends to nudge your entered values a bit, but you may be able to get the right value by entering something a bit off to one side, bizarrely); remember you're in cm here, and Illustrator is probably in mm, so shift the decimal point when copy-and-pasting
  8. Copy and paste into Illustrator
  9. Recall that the graph is fractionally smaller than we want it
  10. Centre-align the graph over the bounding box drawn previously
  11. Ungroup the graph (releasing three subgroups: the framing boxes, the axes, and the bars), deselect the framing boxes, and regroup; delete the framing boxes
  12. Drag out guides to align with the ticks on the Y axis; centre-align the Y axis labels to these guides
  13. For a bar chart:
    1. Drag out guides to the edges of the bars; drag a third in between each pair, and distribute space; centre-align the X axis labels to the bar centre guides (could probably use the direct selection tool to do this without the edge guides, aligning-to-centre directly to the bar)