A Pareto Chart is a type of bar chart in which various factors that contribute to an overall effect and are arranged in order of magnitude of their effect.

Sample Data Table:
Types of Errors Discovered During Surgical Setup

Error Type	Frequency	Percent	Cumulative %	Cut off
Wrong Supplier	67	36.8%	36.8%	0.8
Excess Count	45	24.7%	61.5%	0.8
Too Few Count	35	19.2%	80.8%	0.8
Wrong Size	10	5.5%	86.3%	0.8
Wrong Sterile Instrument Set	9	4.9%	91.2%	0.8
Missing Item	8	4.4%	95.6%	0.8
Damaged Item	6	3.3%	98.9%	0.8
Other	2	1.1%	100.0%	0.8
Total	182	100%

In this example presenting the data using a Pareto Chart displays how three (3) of the error types account for 80% of all errors: Wrong Supplier, Excess Count, and Too Few Count —

Getting the Data Ready

Step 1: Prepare the Data. In order to make the Pareto Chart in excel, first you must have the data ready. Once we have the values for each cause, in the example above Surgical Setup Errors we need to calculate the data for the percent column and the cumulative percentage column. A) To calculate the percentage column values divide each error type by the total of all the error types. B) To calculate the Cumulative Percentage column in cell D9 type +C9, then move down and in cell D10 type +D9+C10 in D11 type +D10+C11, and repeat this process through cell D16.

Step 2: Add Cutoff. Add a column in your data table titled “Cutoff” and insert the value .8 for all error categories. Your table should look like this now:

Making the Chart

Step 1: Select the frequency data by highlighting cells B8 through B16 above and click on the “Insert” tab above and then in the the “Charts” menu click on “Column” and under 2-D Columns select “Clustered Column.” (Hover over column boxes to view type). Click on the legend and then right click and select “Delete” to remove the legend.

Step 2. Add labels to the horizontal axis. Click on the chart and click on “Chart Tools” and “Select Data”. On the Horizontal “Axis Labels” side of the pop-up box click on “Edit” and highlight cells A9 through A16. Click “OK” and “OK” again.

Step 3: Resize the labels so that they are easy to read. Right click on the horizontal axis and select “Font” and change the size to a size that allows the labels to be viewed as shown here. In this example the size is 6. Click “OK.”

Step 4: Add the Percentages. Click on the chart and click on “Chart Tools” above and click on “Select Data”. In the “Select Data Source” pop up box in the left hand side under ‘Legend Entries” click on “Add” and in the popup box under “Series Values” highlight cells D9 through D16 and click on “OK” and then press “OK” in the “Select Data Source” pop up box. Click “OK” and “OK” again.

Step 5: Format percentages to display on the secondary axis. Right click on the red bars you just created and select “Format Data Series.” With “Series Options” selected on the left, in the “Plot Series On” box (on the right) click on “Secondary Axis.” Click “Close.”

Step 6: Format line. Right click on the red bars and select “Change Series Chart Type” and with Line Type selected on the left, click on the “Line with Markers” box on the left. (Hover over box to view line type). Click “OK.”

Step 7: Make sure the secondary axis is formatted correctly to 100%. Right click on the secondary axis and select “Format Axis” and under “Axis Options” under Maximum change the fixed number to 1 and under “Number” change decimal places to 0 and click on “Close”. (If you have trouble with this, make sure that the box “Linked to Source” is NOT checked off.

Step 8: Add a title. Add a title to the graph by double clicking on the graph and under “Chart Tools” under the “Layout” tab select “Chart Title” and click on “Above Chart”. Edit the text to say “Surgical Setup Errors by Type ” and change the font size by right clicking on the title, select font and select font size. In this example the font size is 10.

Step 9: Add the cut off line by double clicking on the chart and under “Chart Tools” click on “Select Data” and Click on “Add” under the “Legend Entries” side of the pop up box and under “Series Values” highlight cells E9 through E16 and then click on “OK” and in the “Select Data Source” box click on “OK” and “OK” again.

Step 10: Format the cutoff line. Right click on the line you just created and select “Format Data Series” and under “Marker Options” select “None”. Under “Line Color” select “Solid Line” and change the color to “Black”. Lastly, under “Line Style” change the width to 2pt. and change the “Dash Type” to “Round Dot”. Your graph should now look like this.

Step 11: Highlight 80% of errors. Change the color of the bars to grey for the categories Wrong Size to Other which will help to highlight the 3 error types that account for 80% of the errors. Double click on the graph and then double click on the bar for “Wrong Size” and click on the bar one more time to highlight only that bar now right click and select “Format Data Point” and under “Fill” select ‘Solid Fill” and change the color to grey. Follow these steps to change the bar color for the remaining bars. Now your chart should look like this.

Step 12: Final touches. We like to keep things as simple as possible, so we want to simplify the line by eliminating the boxes on it. Right click on the line and select “Format Data Series.” Select “Marker Style and either make the markers smaller or select “No Marker” like we did. Use the “Chart Layout” and “Axis Titles” to the primary and secondary axis. And remember, just like the chart title, if you can “click it” you can edit it. We simply added labels and then clicked inside the labels to edit the text. You can also click on the labels and drag them to the position you want. We place the labels on top of the axis so that they are easy to read. If your chart shifts, simply click anywhere in the chart until “Plot Area” comes up and then you can drag and resize your chart to the proportions that look best for displaying the data. We also lightened the grid lines by simply clicking on them and then right clicking, select “Format Gridlines” and changed the color to a very soft gray.

 
Read more about Pareto Charts in my July 2012 newsletter entitled Pareto’s Perceptive Percentages.