How to Create a Pareto Chart in MS Excel 2010


Pareto Analysis is a simple technique for prioritizing potential causes by identifying the problems. The article gives instructions on how to create a Pareto chart using MS Excel 2010.

Part 1

 on How to Create a Pareto Chart in MS Excel 2010

1
Make a list of all of the data elements/work items that you need to prioritize using the Pareto principle. This should look something like this. If you don't have data to practice, then use the data shown in the image and see if you can make the same Pareto chart, which is shown here.

 on How to Create a Pareto Chart in MS Excel 2010

2

 on How to Create a Pareto Chart in MS Excel 2010

3
Use formulae similar to what is shown in the figure. Now your table should look like this.

 on How to Create a Pareto Chart in MS Excel 2010

4
Ensure that the total should be same as the last value in Cumulative Frequency column. Now your data table is complete and ready to create the Pareto chart. Your data table should look like this.

 on How to Create a Pareto Chart in MS Excel 2010

5

 on How to Create a Pareto Chart in MS Excel 2010

6
Right Click in the Chart area and Select Data.

 on How to Create a Pareto Chart in MS Excel 2010

7
Then put a comma (,) and select column E1 to E9. This is one of the important step, extra care need to be taken to ensure correct data range is being selected for the Pareto.

 on How to Create a Pareto Chart in MS Excel 2010

8
Frequency is shown as Blue bars and Percentage is shown as Red bars.

 on How to Create a Pareto Chart in MS Excel 2010

9
Click on “Change Series Chart Type” to “Line with Markers”. Following screen should appear.

 on How to Create a Pareto Chart in MS Excel 2010

10
Percentage bars are now changed to line-chart.

 on How to Create a Pareto Chart in MS Excel 2010

11
Now, Format Data Series pop-up will open, where you need to select "Secondary Axis".

 on How to Create a Pareto Chart in MS Excel 2010

12
The only problem with this Pareto Chart is the fact that the secondary Y-axis is showing 120%. This needs to be corrected. You may or may not face this issue.

 on How to Create a Pareto Chart in MS Excel 2010

13
Right click and click on "Format Axis" option shown as you right click. Go to Axis Options in the "Format Data Series" dialog box and Change the value for "Maximum" to 1.0.

 on How to Create a Pareto Chart in MS Excel 2010

14
However, you can still go ahead and Add some final touch to your Pareto to make it more appealing. Go to Chart Tools --> Layout. You can add Chart Title, Axis Title, Legend and Data Tables, if you want.