How to Create a Pareto Chart in MS Excel

Step-by-Step Guide

1. 1

   Step 1: Identify and List Problems.

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

   Step 2: Arrange different Categories in Descending Order

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

   Step 3: in our case “Hair Fall Reason” based on “Frequency”.

4. 4

   Step 4: Add a column for Cumulative Frequency.

5. 5

   Step 5: Calculate total of numbers shown in Frequency and add a column for Percentage.

6. 6

   Step 6: Go to Insert-->Column and select the 2-D Column chart.

7. 7

   Step 7: A blank Chart area should now appear on the Excel sheet.

8. 8

   Step 8: Select Column B1 to C9.

9. 9

   Step 9: your Pareto Chart should look like this.

10. 10

    Step 10: Select one of the Percentage bars and right click.

11. 11

    Step 11: Now your chart should look like this.

12. 12

    Step 12: Select and right click on the Red Line chart for Percentage and Click on Format data series.

13. 13

    Step 13: Secondary "Y" axis will appear.

14. 14

    Step 14: Select the Secondary Y-axis.

15. 15

    Step 15: Your Pareto is complete and should look like this.

Detailed Guide

About the Author