Making a Pareto Chart in Excel

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%    

Pareto Chart - Frequency

Pareto Chart - Percent

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.

Getting the Data Ready 01

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:

Getting the Data Ready 02

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.

Making the Chart 01

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.

Making the Chart 02

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

Making the Chart 03

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.

Making the Chart 04

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

Making the Chart 05

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

Making the Chart 06

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.

Making the Chart 07

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.

Making the Chart 08

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.

Making the Chart 09

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.

Making the Chart 10

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.

Making the Chart 11

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.

Making the Chart 12

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

FacebookTwitterLinkedInGoogle+EmailShare
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 *


× four = 28

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>