How to Create a Pareto Chart in Excel (Static And Dynamic)?

A Pareto Chart is a type of chart that contains both, a line chart and a bar chart where the cumulative total is represented by the line chart. They are generally used to find the defects to prioritize, in order to observe the greatest overall improvement. The chart is named for the Pareto principle, which, in turn, derives its name from the noted Italian economist, Vilfredo Pareto.

How to Create Pareto Chart?

This section focuses on discussing two types of Pareto chart:

1. Static Pareto Chart.
2. Dynamic Pareto Chart.

Let’s start discussing each type of Pareto chart in detail.

1. Static Pareto Chart: A static Pareto Chart is a simple chart that shows all the data and there exists no option for the user to view data corresponding to particular values. Below are the steps to create a static Pareto chart:

Step 1: Creating the data table of an e-commerce retailer’s user complaints.

Note: Arrange the data in descending order if it isn’t.

Step 2: Create another Column under C and title it as Cumulative Percentage. Then, select the first box under this column and paste the following formula and apply it to all corresponding cells.

=SUM($B$2:B2)/SUM($B$2:$B$10)*100

The result will look something like this:

Step 3: Select the entire dataset and go to Insert. Find 2D Clustered Chart under the Column Charts section:

Step 4: Click on it. The following chart will appear:

Step 5: Select all the red bars by clicking any red bar once. Then, right-click and select Change Series Chart Type.

Step 6: From Line, select the simple Line Chart and hit Enter.

Step 7: Select the Line Chart and right-click on it and select Format Data Series and make the following changes and click Close.

Step 8: Right-click on the Right-hand side Axis and select Format Axis and make the following changes:

The Static Pareto Chart is ready.

2. Dynamic Pareto Chart: Let’s see how to create a Dynamic Pareto Chart where the user can adjust the values and see the results for those values respectively.

Step 1: Make the following new columns and rows alongside your existing data.

Step 2: Go to Developer –> Insert from the ribbon menu. If the Developer Tab is not visible, simply right-click on any of the Items from the ribbon, e.g., Insert, View, Home, etc. and select Customize Ribbon. Then, find a checkbox with Developer and check it.

Click on INSERT

Step 3: From Form Controls, select Scroll Bar and click anywhere on your worksheet.

Step 4: Resize it to make it a horizontal scroll bar that would look something like this:

Step 5: Right-click on this bar and select Format Control.

Make the following changes

Step 6: Go to cell B12 and enter the formula =B14 there. Then, select the B13 cell and enter:

=IFERROR(INDEX($C$2:$C$10,IFERROR(MATCH($B$12,$C$2:$C$10,1),0)+1),1)

Step 7: In cell D2, enter the following and apply it to the entire column.

=IF($B$13>=C2,B2,NA())

Similarly, for cell E2, 

=IF($B$13<C2,B2,NA())

The final result will be as follows:

Step 8: Select the entire Data from Column A, C, D, and E and go to Insert –> Charts –> 2D Column Charts –> Clustered Column.

Step 10: Now, select all the blue bars by clicking on any of the blue bars once. Then, right-click and select Change Series Chart Type.

Step 11: From Line, select the simple Line Chart and hit Enter. Then, right-click on the Right-hand side Axis and select Format Axis and make the following changes-

The Dynamic Pareto Chart is ready.

jaygala260

Follow

Improve

Article Tags :

• Computer Subject
• Excel
• Microsoft Office
• News
• ExcelGuide

+1 More