Pareto Analysis: Identify 20% of Root Problems


Setting priorities is one of the main management functions of an organization. If the managers do not prioritize their tasks and organizational objectives, the organization will head towards the wrong direction and eventually collapse.

A Pareto Chart can be used when it comes to identifying the facts needed for setting priorities. Pareto charts clearly illustrate the information in an organized and relative manner.

This way, management can find out the relative importance of problems or causes of the problems. When it comes to prioritizing the causes of the problem, a Pareto chart can be used together with a cause-and-effect diagram.

The Pareto Principle

Pareto charts are created based on the Pareto principle. The principle suggests that when a number of factors affect a situation, fewer factors will be accountable for the majority of the affectation.

This is almost the same as the 80/20 theory, which says that 80% of the impact is made by 20% of causes.


How to Create a Simple Pareto Chart in Excel

  1. Type and list the number of tickets raised in production under their specific category:pareto_principle_2
  2. Sort this data in descending order by selecting cell B and clicking Data > Sort Largest to Smallest icon. Your values in column B are in descending order now.
  3. Then calculate the Cumulative Frequency by entering this formula =B2 into the cell C4 in this case, and press Enter key.
  4. In cell C5, type this formula =C2+B3, press the Enter key, and select cell C3. Then drag the fill handle to the range that you want to contain this formula, and all the Cumulative Count values in column C will have been calculated. See screenshots:
  5. Calculate the Cumulative Percentage. For example, in cell D2, input this formula =C2/$C$12 (the cell C4 indicates the number of the first Ticket Count and the cell C11 contains the total number of complaints) and then drag the formula down to fill the range you want to use.
  6. Now that your data is complete and ready to create a Pareto chart, hold down the Ctrl key, select data in column A, column B, and column D, and then click Insert > Column > Clustered Column as shown in the following screenshot:
  7. And you will get a chart as follows:
  8. Then select one red bar (Cumulative Percentage) and right click, then choose Change Series Chart Type from the context menu as shown in the following screenshot:
  9. Then select the red line, right click, and choose Format Data Series. In the Format Data Series dialogue box, select Series Options and check Secondary Axis in the right section. See screenshots:
  10. Once you close the dialogue box, you will see that the secondary axis has been added to the chart. Select the percentage axis and right click to choose Format Axis.
  11. In the Format Axis dialogue, check the fixed radio button beside Maximum, and set the number to 1.0 in the text box. See screenshot:


This shows that almost 80% of the tickets in production are purchased because of general inquiry and product issue, which is almost 20% of the total tickets in the categories. Now to reduce the flow of new tickets to production, we need to create a strategy to target these two vulnerable areas.

Alok Awasthi

Alok Awasthi

Team Lead Technical Services

Alok Awasthi is a Team Lead – Technical Services for 3Pillar Global. He has almost 8.5 years of experience in L2/L3 production support, application support management, problem management/incident management, and customer relationship management. He also has a strong knowledge of UNIX and UNIX Shell Scripting, as well as SQL and Oracle.

Leave a Reply

Related Posts

Take 3, Scene 17: The Value of Data Analytics Dan Greene and Adi Chikara join us for this episode of Take 3 to discuss the value of Data Analytics for making sense of the massive amounts of data t...
Take 3, Scene 13: Redux Your React On this episode of Take 3, we take a look at the React development library and how it pairs with the Flux pattern Redux. Vlad Zelinschi and Cassian Lu...
Take 3, Scene 14: The Present and Future of Angular 2, Part ... On this two-part episode of Take 3, Cassian Lup and Andrei Tamas join us all the way from Romania to discuss the newest iteration of the AngularJS fra...
Take 3, Scene 14: The Present and Future of Angular 2, Part ... On the second part of this two-part episode of Take 3, we continue our conversation with Cassian Lup and Andrei Tamas on the newest iteration of the A...
Spell Check and Autocorrect with Conditional Probability A client of mine wanted to reduce the time it took for his vendors to upload an inventory list to his system. The system currently matches the product...

Free product development tips delivered right to your inbox