How to Make a Deviation Graph through Time

A graph displays a deviation relationship when it features how one or more sets of quantitative values differ from a reference set of values. The graph does this by directly expressing the differences between two sets of values. (Stephen Few — Show Me the Numbers — Designing Tables and Graphs to Enlighten (2012) 2nd Edition)

(Please see example in Excel Worksheet.)

Data:

Step 1: Add Variance Column: Type “Variance” in cell D35 and press enter. Type “+B36-C36” in cell D36 and press enter. Click on cell D36 and drag formula down to cell D47.

Step 2: Add % Variance Column: Type “% Variance” in cell E35 and press enter. Type +D36/C36″ in cell E36 and press enter. Click on cell E36 and drag formula down to cell E47.

Step 3: Make the Line Chart: Highlight cells E36 through E47 click “Insert Tab” click “Line” and select “2D Line”.

Step 4: Click on the Primary Horizontal axis and right click select “Format Axis” and under Axis Options change “Axis Labels” to “Low”.

Step 5: Click on legend and press “DEL” key.

Step 6: Click on the chart and under the “Design Tab” in Chart Tools menu click “Select Data”. On the right hand side of pop up box under “Horizontal (Category) Axis Labels click “Edit” and highlight cells A36 through A47 click “OK” then click “OK” again.

Step 7: Click on horizontal grid lines and press “DEL” key.

Step 8: Add Title: Click on chart and click “Layout” tab under “Chart Tools” click “Chart Title” and select “Above Chart”.

Step 9: Edit Title: Click on “Chart Title” and highlight the title and type “Emergency Department Visits Percentage Change vs Last Year“. Change the font size if necessary. For this example we chose 11.

This entry was posted in Excel Tips & Tricks, Graphs. Bookmark the permalink.