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)

Data:

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.

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

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.

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

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.

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

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.