**Watch Video – Creating a Pareto Chart in Excel**

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 it, ~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.

In this tutorial, I will show you how to create:

- A Simple Pareto Chart in Excel.
- A Dynamic Pareto Chart in Excel.

Creating a Pareto Chart in Excel is very easy. All the trickery is hidden in how you arrange the data.

Let us take an example of a Hotel for which the complaints data could look something as shown below:

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

**Follow Along.. Download the Example File**

## Creating a Simple Pareto Chart in Excel

Here are the steps:

- In the column adjacent to the data (# of complaints), enter the below formula:

=SUM($B$2:B2)/SUM($B$2:$B$10)*This is to get the cumulative percentage values so that all the complaint numbers add up to 100% in the end.* - Select the entire data set (A1:C10), go to Insert –> Charts –> 2-D Column –> Clustered Column. This will insert a column chart with 2 series of data (# of complaints and the cumulative percentage).
- Right click on any of the bars and select Change Series Chart Type.
*[If you are using Excel 2010 or 2007, you need to select and right-click on any of the orange bars (of cumulative %) and then select Change Series Chart Type].* - In the Change Chart Type dialogue box, select Combo in the left pane and make the following changes:
- # of Complaints: Clustered Column.
- Cumulative %: Line (also check the Secondary Axis check box).
*[If you are using Excel 2010 or 2007, it will be a two-step**process. First change the chart type to a line chart. Then right click on the line chart and select Format Data Series and select Secondary Axis in Series Options]*

- Your Pareto Chart in Excel is ready. Adjust the Vertical Axis values and the Chart Title.

**How to Interpret this Pareto Chart**

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

## Creating a Dynamic Pareto Chart in Excel

Now that we have a simple Pareto chart in Excel, let’s take it a step further and make it a bit dynamic. Something as shown below:

In this case, a user can specify the % of complaints that need to be tackled (using the 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 it:

- In cell B14, I have the target value that is linked to the scroll bar (whose value varies from 0 to 100).
*See Also:*How to create a Scroll Bar in Excel. - 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.
- 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.

- In cell D2, enter the following formula (and drag or copy for all cell – D2:D10):

=IF($B$13>=C2,B2,NA()) - In cell E2 enter the following formula (and drag or copy for all cell – E2:E10):

=IF($B$13<C2,B2,NA()) - Select the Data in Column A, C, D & E (press control and select using mouse).
- 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)
- Right click on any of the bars and select Change Series Chart Type.
- 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.

- Right-click on any of the highlighted bars and change the color to Red.

That’s It! You have created a Dynamic Pareto Chart in Excel. When you change the target using the scroll bar, the chart would update accordingly.

**Try it Yourself.. Download the Example File
**