How to Use a Pareto Chart in Excel (2 Suitable Examples)

Wondering how to use the Pareto chart in Excel. Do not worry, we are here for you. In this article, we will describe 2 ways to use a Pareto chart in Excel.

A Pareto Chart is a graphical tool that combines a vertical bar chart with a horizontal line chart. The purpose of this chart is to display the magnitude of different issues in a given dataset, arranged in descending order of importance.

Here, we will show how you can use a Pareto chart using Excel 2016 and higher versions of Excel. Also, we will show how you can use a Pareto chart using Excel 2013 and older versions of Excel.

In the following image, you can see the overview of using Pareto chart in Excel. Now, let’s dive in with us to find out how to use a Pareto chart in Excel.

Introduction to Excel Pareto Chart

The Pareto chart, sometimes referred to as a sorted histogram chart, comprises columns arranged in descending order and a line chart displaying the cumulative percentage.

It is derived from the Pareto principle and serves to highlight the most prevalent issues in a given dataset. This chart is effective in identifying the key areas that require attention and improvement.

The Pareto chart is readily available in Excel 2016 and higher versions. However, it is also possible to create in Excel 2013 and older versions of Excel.

Below we are providing a typical Pareto Chart.

The above Pareto chart is displaying important elements in a dataset and shows their relative significance to the overall total.

Further in the article, we will describe how you can use Pareto chart in Excel.

What Is Pareto Analysis?

Pareto analysis is a problem-solving technique. The Pareto principle is the basis of Pareto Analysis. It is named after Italian economist Vilfredo Pareto. This analysis uses the Pareto chart to observe the most important issues in a dataset.

The Pareto principle is also known as the 80/20 rule. This principle states that for most of the events, approximately 80% of the impacts resulted from 20% of the causes.

Below I am describing a few real-life examples of the Pareto principle:

In the economy, the wealthiest 20% of the world’s people control roughly 80% of global income.

According to the reports of medicine, 20% of people use 80% of healthcare resources.

In software, 20% of bugs are responsible for 80% of mistakes and crashes.

Pareto Analysis includes the following steps:

Define the problem
Collect data
Create a Pareto chart
Analyze the chart
Prioritize actions
Monitor progress

Usages of Pareto Chart

Pareto Chart is widely used in the following cases:

Controlling Quality: A Pareto chart is a tool used in manufacturing to figure out which defects happen most frequently during the production process. By using this chart, companies can determine which issues to tackle first to reduce defects and improve the overall quality of their products.
Examining Customer Complaints: A Pareto chart is a helpful tool to examine customer complaints and determine the most common types of problems that customers face. This data can then be used to enhance customer service and tackle the most important issues.
Analyzing Sales: A Pareto chart can show which products or services a business sells the most and which customers buy them the most. This information can help the business concentrate on marketing those products or services and improve how many they sell.
Inspecting Employee Performance: A Pareto chart is a helpful tool to study the errors that occur frequently among employees. By using this chart, we can recognize which types of mistakes happen most often. This information can be beneficial to identify areas where employees need more training. By improving these areas, we can enhance the overall performance of employees.
Analyzing Website Traffic: Using a Pareto chart can help you examine the traffic on your website and pinpoint the primary sources of visitors. This data can be beneficial in directing your marketing efforts toward the sources that generate the most traffic. Additionally, this information can assist in improving the performance of your website by identifying areas that require attention.

How to Use a Pareto Chart in Excel: 2 Examples

In the following article, we will describe how you can use the Pareto chart in Excel. We will inspect Customer Complaints through the Pareto chart. For this, we will use Excel 365.

Further, we will describe how you can use Pareto charts for Sales analysis. For this, we will use Excel 2013.

So let’s go through the following part of the article.

1. Inspecting Customer Complaint Using Pareto Chart in Excel 2016 and Newer Versions of Excel

In the following dataset, you can see that we have Complaint Type and Count columns. We have 10 types of complaints.

Next, using this dataset, we will insert a Pareto chart. After that, using the Pareto chart we will inspect which two complaints (20% of the complaint type) are responsible for 80% of the total complaints.

We can insert the Pareto chart in two ways, from the Histogram chart group, and from the Recommended Chart group.

We will show you both. So let’s go.

1.1. Using Histogram Chart from Insert Tab

Here, we will insert the Pareto chart from the Histogram chart group.

First of all, we will select the entire dataset >> go to the Insert
Then, click on the Histogram chart group >> select Pareto

Therefore, you can see the Pareto chart.

Here, we have edited the Chart Title, and axis font according to our needs.

Analysis Result: The orange line on the chart indicates that around 80% of the complaints come from only two out of ten types of complaints, which are Overpriced and Too Noisy. This means that the Pareto principle is applicable here.

1.2. Use of Recommended Chart Group

This is an alternate way to insert a Pareto chart.

In the first place, select the entire dataset >> go to the Insert
Select Recommended Charts.

At this point, an Insert Chart dialog box will appear.

From All Charts >> click on Histogram.
Select the Pareto chart >> click OK.

As a result, you can see the Pareto chart.

2. Analyzing Sales Data Using Pareto Chart in Excel 2013 and Earlier Versions of Excel

Now, we will analyze Sales data using Pareto charts. For this, we have taken the following dataset which has a Customer Name and Sales column. We have 10 customer names.

Next, using this dataset, we will insert a Pareto chart. After that, using the Pareto chart we will inspect which two customer’s sales among the 10 customers are responsible for 80% of the total sales.

Here, we will use Excel 2013. Excel 2013 and earlier versions of Excel have no built-in Pareto chart. Thus, this method is especially helpful for those who are using lower versions of Excel.

Let’s go through the steps below to analyze Sales data using a Pareto chart.

First of all, we have to sort the data in descending order.
To do so, we will select the Sales column >> go to the Home
Then, from the Sort & Filter group >> select Sort Largest to Smallest.

In the Sort Warning dialog box, select Sort.

Then, we add a Cumulative%
We type the following formula using the SUM function in cell D5.

=SUM($C$5:C5)/SUM($C$5:$C$14)

After that, press ENTER.

Thus, you can see the result in cell D5.

We drag down the formula with the Fill Handle tool to other cells as well.

Now, we will insert a Pareto chart.

We will select the entire dataset >> go to the Insert tab >> click on Recommended Charts.

At this time, the Insert Chart dialog box will appear.

Moreover, from the All Charts group >> select Combo > choose the Custom Combination.
After that, mark the corresponding axis of the Line chart as the secondary axis.
Finally, click OK.

Therefore, you can see the Pareto chart.

Analysis Result: The orange line on the chart indicates that around 80% of the sales come from only two out of ten customers, who are Smith and Sara. This means that the Pareto principle is applicable here.

How to Format a Pareto Chart in Excel

If you want to make your Pareto chart more eye-catching and reader-friendly, then you might customize your Pareto chart. There is no fixed rule or way to customize a chart. You can customize your Pareto chart in any format you want.

Here, we will show you some easy and quick ways to customize the Pareto chart. So let’s follow the steps below.

First, we will change the chart style.

To do so, we will click on the chart >> go to the Chart Design
From the Chart Styles group >> select a Chart Style.

Therefore, you can see a chart with an eye-soothing chart style.

Next, we will change the fill color of the columns.

To do so, we will select the columns >> go to the Format tab.
Then, from the Fill Color group >> select a color.

Thus, we have formatted the Pareto chart and make the chart more eye-soothing.

Advantages of a Pareto Chart in Excel

Below we are providing some of the advantages of using a Pareto chart.

A Pareto chart helps to find the main reason for a problem and solve it, making the organization more efficient.
By using this chart, you can turn business problems into facts and create plans to address them.
This makes problem-solving easier and improves decision-making.
The Pareto chart in Excel gives a simple and clear picture of the information, making it easier to comprehend and examine. It enables you to detect patterns and tendencies that may not be obvious from just looking at the original data.
The Pareto chart is helpful in making things better because it allows you to keep track of how your improvements are working over time.

Limitations of Pareto Chart in Excel

A Pareto chart has some limitations as well. Here, we are addressing some limitations of the Pareto chart.

The Pareto chart alone cannot provide a complete understanding of the root cause of a problem. In cases where a problem has multiple aspects, creating multiple Pareto charts can help identify issues at each level.
The Pareto chart is based on frequency distribution, so it cannot be used to calculate statistical values such as mean, standard deviation, etc.
Additionally, the Pareto chart does not provide information about the severity of the problem or how much improvement can be made by implementing changes to the process.
Pareto charts assume that each group or factor is not related to the others. However, in real life, some factors can be linked or influenced by each other.
Pareto charts are most useful when the data is in distinct categories that can be counted or measured. They may not work well with continuous data that falls on a continuous scale.

Things to Remember

Before creating a Pareto chart, it’s important to group similar problems together. It’s recommended to keep the number of groups below ten.
The data should be arranged from the most frequent to the least frequent. We also need to figure out the total percentage of the frequencies.
The pareto analysis only looks at past data and doesn’t predict the future. So, it’s important to keep updating the data to improve the process continually.
We can make multiple Pareto charts for each problem, and then analyze the sub-issues in each chart. We can repeat this process for as many levels as needed.

Frequently Asked Questions

1. What is 80 20 Pareto in Excel?

The 80 20 Pareto principle is a concept in statistics that suggests that 80% of the results come from only 20% of the causes. This is also known as the “Pareto analysis” or the “80/20 rule.”

2. What Type of Data is Best Suited for a Pareto Chart?

Pareto Charts are typically used for data that can be grouped into categories, such as customer complaints, product defects, or types of errors. They are not ideal for data that is continuous or measured on a scale, such as time or temperature. However, you can still use a Pareto Chart for continuous data by grouping it into categories or ranges.

3. What is the Difference Between a Histogram and a Pareto Chart?

A Pareto chart is like a histogram, but instead of showing the bins in order of size, they are arranged from the most frequent to the least frequent.

Download Excel File

You can download the Excel file from the link below and practice the explained methods.

Use Pareto Chart.xlsx

Conclusion

In this article, we extensively describe how you can use a Pareto chart in Excel. We describe what is Pareto analysis and the basic introduction of a Pareto chart.

Here, we show the use of a Pareto chart for inspecting customer complaints and for sales data analysis, and we use two different versions of Excel to describe these two uses.

In addition to this, we describe the advantages and limitations of the Pareto chart.

We hope this article will be beneficial to you. If you have any queries or suggestions, please let us know in the comment section. You can visit our website Exceldemy for various Excel-related articles.

<< Go Back to Excel Pareto Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!