Open In App

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

Improve
Improve
Like Article
Like
Save
Share
Report

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.

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:

Cumulative Percentage

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

Select 2-D clusterer chart option

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

2-D clustered chart

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

Change series type

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

Line Chart

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

Series Options

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

Format Axis

The Static Pareto Chart is ready.

Static Pareto Chart

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.

Format table for dynamic pareto chart

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.

Customize Ribbon

Click on INSERT

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

Scroll Bar

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

Horizontal Scroll Bar

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

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)

Formula

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:

Column Manipulations

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

Final Step Dynamic Pareto Chart

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.

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-

Format Axis

The Dynamic Pareto Chart is ready.



Last Updated : 19 Jun, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads