Excel Pareto Chart: Instructions & Template

The Pareto Principle is also known as the 80-20 rule, which is a general principle referring to the observation that 80% of outcomes come from 20% of causes. You might sometimes hear specific instances of the Pareto Principle, for example:

  • 80% of results come from 20% of the work
  • 80% of the land is owned by 20% of the people
  • 80% of sales are attributed to 20% of customers
  • 80% of wealth belongs to 20% of the people

The Pareto Principle is named after Vilfredo Pareto who observed in Italy in the 19th Century, that 80% of the land was owned by 20% of the people. He then developed the principle further by observing 20% of the pea pods in the garden contained 80% of the peas. More information about the history of the Pareto principle can be found on Wikipedia.

Pareto Charts

Pareto Charts are used within Six Sigma to aid in the identification of root causes. A Pareto Chart will help you find the most important factors amongst a large set of factors. Essentially, Pareto analysis can be used to help you identify the 20% of the causes resulting in 80% of the problems. A Pareto Chart is a combination of a bar chart and a line graph, with individual values represented by the bars in descending order, and the line representing the cumulative total of the bars in percentage terms. This will be more obvious when you look at the following example:

Excel Pareto Chart Diagram

The Pareto Chart above shows the issues which might occur in an online store. As can be seen, the left-hand vertical axis shows the number of times a particular category of issue has occurred. The right-hand vertical axis shows the percentage of all issues a particular issue represents. On the horizontal axes we have the various categories of issues themselves. This sounds complicated but it’s self explanatory once you look at the chart above.

The Pareto Chart is one of the 7 Basic Tools of Quality. These are all graphical tools which can be very useful in identifying issues related to quality.

There are a couple of pointers you should keep in mind when examining Pareto Charts:

  • Look for a break point in the cumulative percent curve. This point occurs where the line begins to flatten out. Issues which occur before this point are the most important. Issues which occur after this point are less important. In the example above I would say that the break point is at “Comments”, meaning that “Registration”, Browse”, and “Search” are the categories most in need of attention.
  • If you can’t determine a break point, then this could mean your issues are speadevenly across the categories. In this case you’ll need to use judgement as to which categories are the most important to resolve first.
  • Try not to have catch-all categories such as “General”, “Other”, or “Miscellaneous”. Doing this introduces the risk you will not be addressing the most important category of problem first, particularly if your catch-all category has a large number of items within it.

Creating an Excel Pareto Chart

Before we get into the instructions to create your Excel Pareto Chart there are a few things you need to do in advance:

  • Decide how you want to categorize your issues. Note that it is considered good practice to have less than 10 categories.
  • Keep a count of the number of issues in each category. Do this by examining each issue and adding it to the most appropriate category. You might find yourself changing category names at this stage once you get into the detail of examining issues.

Now that we have collected the raw data, it’s time to go through the instructions to create our Excel Pareto Chart:

STEP 1: Collect Raw Data in Table Format

The first step is to collect your data into a table similar to the one shown below.

pareto chart data diagram

You can create this table yourself, or simply download and modify the Excel Pareto Chart Template I’ve provided by clicking the link.

STEP 2: Create Basic Table

To begin creating your Pareto Chart in Excel, select the Category column, the Count column, and the Cumulative Percent column as shown in the diagram below.

pareto data selection graphic

Hold down the Ctrl key to help you in selecting the columns. Notice that you do not select any data elements from the TOTALS row. Now that you have the right columns selected it’s time to create the table by selecting the Insert tab in Excel, the Column button, and then choosing Cluster Column. This is shown below:

pareto cluster diagram

Your Pareto Chart will now look like this:

excel pareto chart start graphic

STEP 3: Create A Basic Pareto Chart

Now that we have our basic diagram, it’s time to make it look more like a Pareto chart. To do this right click on any one of the Culminative Percent bars in the diagram. Select Change Series Chart Type and then select Line as shown below

excel pareto chart choose line diagram

Once you have done this you’re diagram will look as follows:

Excel Pareto Chart Basic

STEP 4: Add a Second Axes

You chart should now be beginning to look like a Pareto Chart, but it will still have just one axes. Now it’s time to fix this. Do this by right-clicking on the Cumulative Total line and choosing Format Data Series. Now select the Secondary Axes as shown below:

Excel Pareto Chart Secondary Axes

Once you close the pop-up window, you should now see the secondary axis as shown below:

Excel Pareto Chart with x and y Axes Picture

STEP 4: Make it Pretty

Your Excel Pareto Chart is just about done. In fact, you can stop after step 3 if you’re just using the Pareto Chart for yourself. However, if you’ve intending to show the chart to anyone else then by following this step you can make it easy for others to understand too.

The first thing to do is to get our percent axis to finish at 100% and not 120%. To do this, right click on the right-hand axes and select Format Axes. Now, under the Axes Options tab select Maximum to set it to be Fixed, and then manually set the value to 100 as shown in the diagram below:

Excel Pareto Chart Scale Axes

This will result in your Pareto Chart looking as follows:

Pareto Chart Scaled Axes

We are almost there. We just need to label our axes and we’re done. To do this select your chart, choose the Layout tab, and select Axes Titles as shown in the picture below:

pareto chart label axes

Congratulations! Your Excel Pareto Chart is complete, and should look as follows:

Excel Pareto Chart Graphic

Pareto Chart Conclusion

The Pareto Principle is also known as the 80 20 Rule, and it refers to the fact that 80% of outcomes result from 20% of causes. A Pareto Chart is a graphical tool allowing you to understand which categories result in the most issues, enabling you to target the most troublesome areas first. This can be done at an organization level, a project level, or a product level, amongst others. Creating an Excel Pareto Chart requires a little bit of Excel wizardry which is why I’ve provided the step-by-step instructions above. However, as a short cut you can simply download the Excel Pareto Chart Template. 

Related Posts Plugin for WordPress, Blogger...

{ 35 comments… read them below or add one }

Brent April 12, 2011 at 4:55 pm

Wow, this really helped me to complete an assignment for my IT Project Management class. Thanks so much!

Denis April 13, 2011 at 12:16 pm

Hi Brent,

Glad you found the information useful :-)

Denis

Stevo April 14, 2011 at 5:29 pm

This is just awesome, especially the template provided. Thank you so much! You really helped with my IT assignment

Denis April 15, 2011 at 1:48 am

Hi Stevo,

Thanks for your comment. It’s good to know people are finding this information useful :)

Denis

Ed May 12, 2011 at 11:04 am

Thanks for the info, it is extremely useful. I had an interview for a promotion at work, and I knew that I would be asked to do a pareto chart using the new microsoft office package. Thanks to this website I was fully prepared and confident. I easily landed the job since none of the other applicants were able to create a pareto! Thanks again!!

Denis May 12, 2011 at 12:33 pm

Hi Ed,

I think it’s amazing that my site helped you to land that job! Congratulations! And best of luck in your new role.

Denis

Gary August 11, 2011 at 6:27 am

Great job!!.. I never created the chart before and it was very helpful

Shida August 18, 2011 at 1:08 am

Thanks for the information. I’ve used it for my data collection. For my first data very helpful. But for my second data, graph of cumulative percent did not appear. I didn’t know why. i’ve follow the instruction.

Himanshu August 25, 2011 at 7:54 am

Excellent explanation that is quite simple to understand but covers the steps to prepare “Pareto” graph in excel completely – Awesome!!

Raj Pradhan August 31, 2011 at 10:34 pm

This is a really wonderful information. it helped me to prepare a seminar on Pareto Chart topic. thanks a lot.

Denis September 1, 2011 at 12:28 am

Thanks Raj – I’m happy you found it useful :)

Denis

John September 29, 2011 at 1:47 pm

Shida,

I just read this and had the same issue. The problem was that my “count” values were way to high to see the small number of precentages.. my QTYs were in the thousands. If you click beside the column… it is there and then you can manipulate the percentages.

Matt October 7, 2011 at 7:46 am

Shida, sometimes the % data can be hidden if your left scale is too large. You can temporarily increase one of your % data points to a number high enough to appear, or you can temporarily adjust the left scale to 100. You just need to be able to see the % data so you can switch it to the secondary axis.

Freddy November 16, 2011 at 3:18 am

Thx for great instructions.

I had the same issue with hidden %, due to large numbers. It can be solved with make both series as a line, and change series 1 back to column.

Rishabh February 13, 2012 at 1:27 pm

wow!! a wonderful guide..thanks a lot :)

Andrew May 9, 2012 at 4:59 am

Really helpful guide, I am using it to analyse construction data. I have worked out how to put a horizontal line on the chart to signify 80%, is there any way to create a drop line where the 80% line intersects the accumulative curve to illustrate the 20/80 categories.

Many thanks

Andrew

Lean May 24, 2012 at 2:06 am

Very Helpful!! you just secured my promotion. Thanks

Deepak Aggarwal July 2, 2012 at 6:24 am

Thank you vey much for the wonderful help…it’s really great and I have learnt today that how to make Pareto in Excel…thanks a ton once again. !!

Mohammad July 24, 2012 at 3:23 am

Thanks a lot. That was very useful to me. As i am in Quality Profile.

Andrew September 5, 2012 at 7:16 pm

Terrific instructions. Thank you very much.

Santanu Kumar Das September 11, 2012 at 6:47 am

Very useful.

Ronald Duggs September 25, 2012 at 7:54 am

Thank you! I was lost and this really helped me.

Vard October 3, 2012 at 3:09 am

I guess the line must begin from the top of first bar cause it is the cumulative curve.
BR
Vard

John October 29, 2012 at 10:18 pm

This is amazing! Your instructions were as clear as can be, just as if you were right here holding my hand. Thank you so much for posting this Pareto Chart in Excel tutorial. Excellent indeed.

John.

Toki November 23, 2012 at 5:53 pm

Thank’s a lot for this.

Very clear and informative.

Mel November 29, 2012 at 8:02 am

This is awesome! Your guide helped me to create this pareto chart for my Six Sigma project.
Have a wonderfull day!

Louis Hemmi December 7, 2012 at 8:54 am

This is the most complete and thorough treatment of the subject of Pareto charts in Excel that I’ve seen.

I did a Pareto a few years ago, but your documentation is much more instructive than what I found previously.

Sushil January 3, 2013 at 8:23 am

THanks for very much useful content… appreciated…

AV January 14, 2013 at 1:20 am

Its really helpful..

Julius Mjelwa January 20, 2013 at 7:02 am

Pareto charts are very useful in analysis of data and easy to understand

Santy January 25, 2013 at 11:30 am

Hi,

Really Aweosme. Loved the way the steps have been clearly descirbed. Thanks a ton
:)

Kumar Saurav February 25, 2013 at 1:56 am

Thanks a lot ,This is what we called step by step pareto Analysis.

Regards
Kumar Saurav

Reena Rawal March 25, 2013 at 1:46 am

Wonderful! great help! It has help me a lot. Many Thanks!

Sara June 20, 2013 at 10:18 am

Very useful! Thank you

arup roy July 3, 2013 at 5:06 am

thanks for this grate help.this is help me too much.

Leave a Comment

Previous post:

Next post: