Pareto Chart

Pareto charts are extremely useful for analyzing what problem needs attention first, the taller bars on the chart, which represent frequency and the line shows the cumulative frequency percentage.

The Pareto principle (also known as the 80/20 rule), states that, for many events, roughly 80% of the effects come from 20% of the causes.

Let’s take an example and understood, how can we create a Pareto chart in Excel?

In the screenshot above, we have a product complain database. As per the Pareto principle that 80% of the effects come from 20% of the causes. If we focus on 20% of the major complains then we can reduce the complains number.

For this, first, we will calculate the cumulative frequency –

1. Start with the equal sign (=) and select first frequency ( Ex: value 45) then press enter
2. Come to the next cell, again start with the equal sign (=) and select 2nd frequency plus last cumulative frequency value then press enter
3. Drag the cell downwards
4. Also, check that Last number of (CF) should not be greater than the total value of Frequency

Once we are done with the cumulative frequency, let’s derive the percentage –

= Cumulative Frequency/ (Total of Frequency)

The formula is displayed in the screenshot below:

Apply the formula and drag it downwards. (Refer the below screenshot)

How to create a Pareto chart?

• Go to insert Tab
• Select the 2nd column chart

• Insert a blank column chart
• when we insert a chart in our sheet, we get a new tab “Chart Tools” in the Ribbon tab. There are two tabs available further inside the chart tools,
  1. Design
  2. Format

Go to Design tab->> Click on “Select Data”

• Select the range which we want to highlight in charts.
  1. First, select the product and frequency
  2. To select another range along with the previous range, put a comma first “,” then select

• Click Ok

• As per the legend option, Blue colour bars are representing Frequency and orange colour bars are showing the Percentage of Cumulative frequency
• Just click on any one of an orange bar and it will select all by default
• Right click on a selected bar ->> Format data series

• We get two options-
  1. Primary
  2. Secondary
• Mark this Bar as a Secondary->> Design tab-> Change chart type -> Select Line chart

Now, the final chart looks like the one in the screenshot below:

Note: As per the 80/20 principle, first three products are covering 71% complains of all complains.

Add Data Labels on the chart-

• Select the Bars
• Right-click -> Add data Labels

Follow the same process for the line chart 

Format Axis-

Refer to the highlighted rectangle border in the screenshot above, the percentage is more than 100%. Follow the steps below to correct it out-

1. Right-click on the secondary axis
2. Format Axis
3. Set the maximum value is 1.0

And, the final Pareto chart will look like this-

Hope you enjoyed this post. We would love to hear your thoughts. Do leave your suggestions, feedback, and comments in the comment box.

 

Happy Learning 😊