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:

How to Make a Deviation Graph through Time 1

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.

How to Make a Deviation Graph through Time 2

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.

How to Make a Deviation Graph through Time 3

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

How to Make a Deviation Graph through Time 4

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

How to Make a Deviation Graph through Time 5

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

How to Make a Deviation Graph through Time 6

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.

How to Make a Deviation Graph through Time 7

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”.

How to Make a Deviation Graph through Time 8

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.

How to Make a Deviation Graph through Time 9

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

Leave a Reply

Your email address will not be published. Required fields are marked *


− one = 2