Making the Chart

Step 1: Create your chart. Select cells E5 through G6 and select the “Insert” tab and in the “Charts Section” click the down arrow under “Bar” and select the first selection under 2-D bar for “Clustered Bar” chart type.

Step 2: Delete the legend. Click the legend and press delete.

Step 3: Format rows and columns. Click the chart and select from “Chart Tools” tab and click “Switch Row/Column”.

Step 4: Overlap the data. Click the green bar and right click – a pop up box “Format Data Series” appears. Select “Series Options” (left side of the box) and in the right hand side under “Series Overlap” section move the arrow over to 100% overlapped and in the “Gap Width” section move the arrow over to “No Gap”. Click “Close”.

Step 5: Rearrange the bars. Click the green bar and under chart tools tab select “Select data”. Click the entry (Best, Worst, Average) and use the up and down arrows located in the top right section on the “Legend Entries” box to move the entry up or down until you have “Best” on the top, “Average” and “Worst” at the bottom of the list. Click “OK”.

Step 6: Change the color of each of the bar sections to the same color in graduated sections. Right click on the blue section and select “Format Data Series”. Click on “Fill” in the left hand box inside the pop up window and then select “Solid Fill” in the right hand box and go down to the color icon and select the down arrow and select the darkest blue. Click “Close”. Repeat this step with the next two sections (red then green) selecting a lighter shade of blue each time to create a graduated color scheme.

Step 7A: Add National Average data (above in cells C5 and C6). Click the chart and then click the Chart Tools” tab at the top of the screen. Click “Select Data” and click the “Add” under “Legend Entries” to add the National Average data. Under “Series Name” click the “spreadsheet icon” and click cell C5 above. Click on the spreadsheet icon on the right hand side of the box and under “Series value”, click the “spreadsheet icon” and click cell C6 above. Click the spreadsheet icon on the right hand corner of the box and click ‘OK”.

Step 7B: Add Hospital Actual data (above in cells B5 and B6). Click the chart and click the Chart Tools” tab at the top of the screen. Click “Select Data” and click the “Add” under “Legend Entries”. Click “Add” and under “Series Name” click the “spreadsheet icon”. Click cell B5 (Actual) above, click the spreadsheet icon on the right corner of the box. Under “Series value” click the “spreadsheet icon” and then cell B6 above. Click the spreadsheet icon on the right corner of the box. Click “OK”.

Step 8: Click the dark blue section of the graph and right click. A pop box will come up then select “Change Series Chart Type”. Select “XY Scatter” in the left hand section of the pop-up box. Select “Scatter with only markers”, which is the first selection under “XY Scatter” in the right hand section of the pop-up box. Click “OK”.

Step 9: Click on the purple section of the graph and right click. A pop box will come up, select “Change Series Chart Type”, select “XY Scatter” in the left hand section of the pop-up box, select “Scatter with only markers”, which is the first selection under “XY Scatter” in the right hand section of the pop-up box. Click “OK”. You will end up with two X’s on the left hand axis of the graph.

Step 10: Start process to form lines for chart. Select the top X representing the 79 value by clicking on it. Click the “Chart Tools” tab at the top of your screen and click “Select Data”. Click “Hospital Actual” under the “Legend Entries” section of the pop up box and then click “Edit” in the “Series Name” box. Select “spreadsheet icon”, select B5 and click the icon to the right. In the “Series X Values” box click the “spreadsheet icon” and select cell B6. Click the icon to the right and then in the “Series Y Values” box highlight what is in the box and type the number 1. Click “OK”.

Step 11: Select the remaining X representing the 76 value on the left axis by clicking it. Click the “Chart Tools” tab at the top of your screen and click “Select Data”. Click “National Average” under the “Legend Entries” section of the pop up box and then click “Edit”. In the “Series Name” box select “spreadsheet icon”, select C5 and click the icon to the right. In the “Series X Values” box click the “spreadsheet icon” and select cell C6. Click the icon to the right and in the “Series Y Values” box highlight what is in the box and type the number 1. Click “OK”.

Step 12: Center the data points. If data is not centered from top to bottom, right click the secondary axis and select “Format Axis”. Select “Axis Options” from the left hand side of the pop up box. Change the “Minimum Value” to “Fixed” by clicking the “Fixed” button and type in the number 0. Change the “Maximum Value” to “Fixed” by clicking the “Fixed” button and type the number 2. Change the “Major Unit” to “Fixed” and type the number 5. Click “Close”.

Step 13: Format into Bullet Graph. Click the first data point, select “Layout tab”, select “Error Bars” and select “Error Bars with Percentage”. You will end up with something that looks like a cross over the “X”.

Step 14: Create Vertical Line. Click the horizontal part of the cross and press the delete key which will leave you with vertical line over the “X”.

Step 15: Click the chart, click the “Layout Tab” in the first section click the down arrow next to “Chart Area” and select “Series “National Average” Y Error Bars”. Click “Format Selection” and a pop up box comes up. In the left hand side of the pop up box click “Vertical Error Bars” and in the right hand section, under “Display…Direction” click “Both” and “Display…Direction”, click “No Cap”. Under “Error Amount” click “Percentage” and change percentage to 30. Click “Line Style” in the left hand side under “Width”, use the up arrow to change the “Width” to 3pt. to make a nice heavy line. Click “Close”.

Step 16: Create Horizontal Line. Click the second data point, select Layout tab, select “Error Bars” and select “Error Bars with percentage”. You will end up with a cross over the “X”.

Step 17: Click the vertical part of the cross and press the delete key, which will leave you with horizontal line over the “X”.

Step 18: Click the chart and click on the Layout Tab. Under “Current Section”, click the down arrow next to “Chart Area” and select “Series “Hospital Actual X Error Bars”. Click on ‘Format Selection” a pop up box comes up. In the left hand side of the pop up box click on “Horizontal Error Bars” and in the right hand section, under “Display…Direction” click on “Minus”. Under “Display…End Style”, click on “No Cap”. Under “Error Amount” click on “Percentage” and change Percentage to 100. Click on “Line Style” in the left hand side and under “Width” use the up arrow to change the “Width” to 6 pt. to make a nice heavy line. Click “Close”.

Step 19: Clean up Chart Junk. Select Markers from each of the lines by clicking the marker, right click and select “Format Data Series”. Select “Marker Options” and select “None”. Click “Close”. Click the bottom axis and right click. Select “Format Axis”. Set the “Minimum” to “Fixed” and enter the number 0, and “Maximum” to “Fixed” and enter the number 100. Right click the secondary axis and press delete. Click the primary axis and press delete. Right click the chart and select “Format Chart Area”, select “Border Color” and click “No Line”. Click “Close”.