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.
1Make 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.
3Use formulae similar to what is shown in the figure. Now your table should look like this.
4Ensure 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.
6Right Click in the Chart area and Select Data.
7Then 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.
8Frequency is shown as Blue bars and Percentage is shown as Red bars.
9Click on “Change Series Chart Type” to “Line with Markers”. Following screen should appear.
10Percentage bars are now changed to line-chart.
11Now, Format Data Series pop-up will open, where you need to select "Secondary Axis".
12The 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.
13Right 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.
14However, 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.