How to Make a Deviation Graph

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 Time 1

Step 1: Add Variance Column: Type “Variance” in cell D5 and press enter. Type “+B6-C6” in cell D6 and press enter. Click on cell D6 and drag formula down to cell D10.

How to Make a Deviation Graph Time 2

Step 2: Make the Bar Chart: Highlight cells D6 through D10 click “Insert Tab” click “Column” and select “2D Clustered column”.

How to Make a Deviation Graph Time 3

Step 3: 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 A6 through A10 click “OK” then click “OK” again.

How to Make a Deviation Graph Time 4

How to Make a Deviation Graph Time 5

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 Time 6

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

How to Make a Deviation Graph Time 7

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

Step 7: Edit Bar Colors: Click on each positive value bar and change the bar color to red: Select the Administration bar by clicking on twice so that only that bar is highlighted then right click and select “Format Data Point”. Under “Fill” select “Solid Fill” and click on the color icon and change to red. Repeat this step for the Central Supply bar and Radiology bar.

How to Make a Deviation Graph Time 8

How to Make a Deviation Graph Time 9

Step 8: Click on each negative value bar and change the bar color to grey: Select the Nursing bar by clicking on twice so that only that bar is highlighted then right click and select “Format Data Point”. Under “Fill” select “Solid Fill” and click on the color icon and change to grey. Repeat this step for the Pharmacy bar.

How to Make a Deviation Graph Time 10

Step 9: 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 Time 11

Step 10: Edit Title: Click on “Chart Title” and highlight the title and type “Full Time Equivalent (FTE’s): Variance from Budget”. Change the font size if necessary. For this example we chose 12.

How to Make a Deviation Graph Time 12

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 *


8 − one =