England Finance

Apr 14 2018

How to Make a Pareto Chart in Excel (Static – Interactive)

#pareto #chart #template #excel


#

Creating a Pareto Chart in Excel (Static Dynamic)

Pareto Chart is based on the Pareto principle (also known as the 80/20 rule), which is a well-known concept in project management.

According to this principle,

80% of the problems can be attributed to about

20% of the issues (or

80% of your results could be a direct outcome of

20% of your efforts, and so on..).

The 80/20 percentage value may vary, but the idea is that of all the issues/efforts, there a few that result in maximum impact.

This is a widely used concept in project management to prioritize work.

Creating a Pareto Chart in Excel

In this tutorial, I will show you how to make a:

  • Simple (Static) Pareto Chart in Excel.
  • Dynamic (Interactive) Pareto Chart in Excel.

Creating a Pareto Chart in Excel is very easy.

All the trickery is hidden in how you arrange the data in the backend.

NOTE: To make a Pareto chart in Excel, you need to have the data arranged in a descending order.

Creating a Simple (Static) Pareto Chart in Excel

Here are the steps to create a Pareto chart in Excel:

How to Interpret this Pareto Chart in Excel

This Pareto chart highlights the major issues that the hotel should focus on to sort the maximum number of complaints. For example, targeting first 3 issues would automatically take care of

80% of the complaints.

For example, targeting the first 3 issues would automatically take care of

80% of the complaints.

Creating a Dynamic (Interactive) Pareto Chart in Excel

Now that we have a static/simple Pareto chart in Excel, let s take it a step further and make it a bit interactive.

Something as shown below:

In this case, a user can specify the % of complaints that need to be tackled (using the excel scroll bar ), and the chart will automatically highlight the issues that should be looked into.

The idea here is to have 2 different bars.

The red one is highlighted when the cumulative percentage value is close to the target value.

Here are the steps to make this interactive Pareto chart in Excel:

  1. In cell B14, I have the target value that is linked to the scroll bar (whose value varies from 0 to 100).
  2. In cell B12, I have used the formula =B14/100. Since you cannot specify a percentage value to a scroll bar, we simply divide the scroll bar value (in B14) with 100 to get the percentage value.
  3. In cell B13, enter the following combination of INDEX. MATCH. and IFERROR functions:
    =IFERROR(INDEX($C$2:$C$10,IFERROR(MATCH($B$12,$C$2:$C$10,1),0)+1),1)
    This formula returns the cumulative value that would cover the target value. For example, if you have the target value as 70%, it would return 77%, indicating that you should try and resolve the first three issues.
  1. In cell D2, enter the following formula (and drag or copy for all cell D2:D10):
    =IF($B$13 =C2,B2,NA())
  2. In cell E2 enter the following formula (and drag or copy for all cell E2:E10):
    =IF($B$13 C2,B2,NA())
  3. Select the Data in Column A, C, D E (press control and select using mouse).
  4. Go to Insert Charts 2-D Column Clustered Column. This will insert column chart with 3 series of data (cumulative percentage, the bars to be highlighted to meet target, and remaining all other bars)
  5. Right-click on any of the bars and select Change Series Chart Type.
  6. In the Change Chart Type dialogue box, select Combo in the left pane and make the following changes:
    • Cumulative %: Line (also check the Secondary Axis check-box).
    • Highlighted Bars: Clustered Column.
    • Remaining Bars: Clustered Column.
  7. Right-click on any of the highlighted bars and change the color to Red.

You have created an interactive Pareto Chart in Excel.

Now, when you change the target using the scroll bar, the Pareto chart would update accordingly.

Do you use Pareto Chart in Excel?

I would love to hear your thoughts on this technique and how you have used it. Do leave your footprints in the comments section

Related Project Management and Charting Tutorials:


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *