Specifications for Excel figures for CDI

Graphs (figures) for publication in CDI need to be formatted for consistency and to meet desktopping requirements.

Page last updated: 12 February 2013

Overview l Excel figure format l Footnotes and titles l Format worksheet margins l Format Plot Area l Format Chart Area l Line Chart l Chart colours l Chart inserts l Text boxes

Overview

Graphs (figures) for publication in CDI need to be formatted for consistency and to meet desktopping requirements. For example, thin axes lines may not show up clearly when converted to desktopping requirements. The following format requirements have been worked out to provide the optimum desktopping output.

Graphs should be created in MS Excel.

The preference is to have all figures in ONE Excel file. The data MUST be included on a separate sheet.

Worksheets should be named appropriately, e.g. Figure 1, Figure 1 data. Preferably, only the relevant worksheets should be included in the file. Worksheets can be copied to a new Excel file unless data is linked to formulas in other worksheets.
Top of page

Excel figure format

All charts should be created 'As a new sheet' not as an object in a data sheet (with the exception of chart inserts).

If editing a chart that has been created as an object in a sheet you can covert it to a new sheet by the following.

From Chart Menu, Select Location\As new sheet (MS Excel 2003 and earlier), or

Select chart, Right mouse click > move chart (MS Excel 2007 and later)

Footnotes and titles

Footnotes and headings should be in the document text, NOT in the Excel chart.

Format worksheet margins

  1. From the File menu, Select Page Setup\Margins
  2. Change all - Top, Bottom, Right and Left margins to '0'.
  3. Ensure page size is A4 NOT Letter
Top of page

Format axis

Patterns\Weight: use 3rd (second thickest) line (Excel 2010 Line style width 2 pts)
Axis: custom
Tick mark labels: next to axis
Tick mark type: Major = outside, Minor = none

Scale: Varies depending span of x axis, always make number of categories between tick marks and number of categories between tick mark labels the same. When using percentages make sure that the maximum axis scale is 100. If x , axis is in years the number of categories is usually '6'. The year should only be used for the first category for each year. e.g., Jan 1996, Jul, Jan 1997, Jul …..
Font: Arial
Font style: Regular (Do not use bold for anything)
Font size: 18 point.
(Unselect the 'Auto scale' box)
Number: For date fields format as text then type the corresponding year next to Jan e.g. "Jan 1996". This will ensure years are properly centred on the month.
Don't use decimal points if all fields are a whole number
Alignment: The X axis text is always horizontal with the exception of age group figures or unless the number of categories shown mean the text doesn't fit horizontally or are too closely spaced.
Text: Don't use leading zeros. E.g. 1 Jan, NOT 01 Jan,

Format Axis Title

Patterns: Border = None, Area = None
Font: Arial ( sentence case i.e. 1st letter in upper case then other letters in lower case)
Font style: Regular (Do not use bold for anything)
Font size: 26 point.
(Deselect the 'Auto scale' box)
Top of page

Format Legends/other text on figure


Patterns: Border = None, Area = None
Font: 22 point, Arial, regular; no box around legend
Use normal case (only capitalise the first word)
As much as possible – do not use abbreviations unless footnoted. (Deselect the 'Auto scale' box)

Format Plot Area

Patterns: Border = None
Plot area colour: None
Top of page

Format Chart Area

Patterns: Border = None
Chart area colour: None
Gridlines: None

Chart colours

Do not use the same colour for any two series.

The preference is for the following order (left to right for bar charts and bottom to top for stacked charts; and line graphs): dark blue, red, green, (yellow – bar charts only), light blue, brown, pink, gold, dark green.

Note: for combined line and bar charts, use light colours for the bars (e.g. yellow) then dark colours for the lines (e.g. dark blue, red)
Top of page

Format chart

Histograms: Use a gap width of 0 (under format column group) (e.g. age/sex charts)
Stack charts: Use gap width of 50 and an overlap of 100
Bar charts: Use a gap width of 50 (under format bar group)
Bar colours: See above:

Line charts

Style: Use 1st 3 options in reverse order (solid or dashed lines) i.e. the solid line should be the bottom line. (Excel 2007 or later-dash type: solid line; long dashed line (6th option), short dashed line (4th option)
If 1–3 series used, use a triangular marker on the solid line.
        If more than 3 series, repeat the three style options and use markers on lines 4 onwards in the following order: triangle; filled asterix; diamond; asterix; square
Weight: Use 4th of 4 options (the thickest option) (Excel 2010: Line style width 3 pt)
Colours: If using markers use the same colour option as the plot line. Don't use smoothing
Top of page

Chart inserts

Chart insert options are as above with the following exceptions.

Format axis

Font: Font size: 16 point. (Unselect the 'Auto scale' box)
Top of page

Format Axis Title

Font: Font size: 20 point.

Format Legends/other text on figure

Font: 18 point, Arial, regular; no box around legend
Top of page

Text boxes

Font: Font size: 20 point.