When you want to analyze or compare two or more things, a **Pareto **chart can be a helpful tool. A **Pareto **chart can be helpful in many situations where a few items or factors make up a large proportion of something. In business, a **Pareto **chart is often used to compare different items in terms of profitability or cost. In this article, I will show you how to make a **Pareto chart in Excel** with some easy steps. So, without having any further discussion, let’s dive straight into it.

**Table of Contents**hide

## Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

## What Is a Pareto Chart?

A **Pareto **chart is a graphic tool for showing the relative probability of occurrence of various values. Values are ordered from greatest to smallest and presented as a **Histogram **in a **Pareto **chart. The **Pareto **principle commonly referred to as the **80/20 rule**, is a widely accepted idea in business that claims that **20%** of causes account for **80%** of effects. A visualization tool that can be used to illustrate this idea is the **Pareto **chart.

The **Pareto **chart is named for the **Italian **economist **Vilfredo Pareto**, who noted that **20%** of the population in Italy controlled **80% **of the country’s land. He also discovered that just **20%** of the pea plants in his garden produced the majority of the peas, which were **80%**. According to **Pareto**, this phenomenon was not exclusive to **Italy **or peas but rather was a generic rule that could be used in a variety of circumstances.

## Steps to Make a Pareto Chart in Excel

I will use the following **Sales Report** to show you how to make a **Pareto **chart in Excel.

In the dataset, the **Product **column consists of a list of product names. The **Sales **column consists of the corresponding sales amount for each product.

**📓 Note:** Here, the products are sorted by the largest to smallest sales amount. It is a must to sort the data in descending order.

### Step-1: Preparing Dataset to Make a Pareto Chart

To make a **Pareto **chart, you need to prepare your dataset first.

- First, calculate the sum of all the sales amount using the following formula in cell
**C13**.

`=SUM(C5:C12)`

Now you need to calculate the cumulative sum of all the sales amounts in the column, **Cumulative**.

To calculate the cumulative sum of the sales amount,

- At first, copy the amount of the first sale in cell
**D5**using the following formula:

`=C5`

- Now insert the following formula in cell
**D6**to calculate the first cumulative sum.

`=C6+D5`

- After that, press the
**ENTER**button.

- Drag the
**Fill Handle**button from cell**D6**to**D12**.

- Now you will get the
**Cumulative Sum**of all the sales amount in the column,**Cumulative**.

Now, you have to calculate the percentage of the cumulative sum amount.

To calculate the percentage of the cumulative sum,

- Insert the following formula in cell
**E5**.

`=D5/$C$13`

Here, **D5 **is the first cumulative sales amount. And **$C$13** is the summation of all sales amounts. Here I used an absolute cell reference to lock cell **C13**.

- Now press
**ENTER**.

- After that, drag the
**Fill Handle**from cell**E5**to**E12**.

After that, you will get the fraction of the numbers.

To convert all the fraction numbers into percentages,

- Select the range
**E5:E13**. - Go to the
**Home**tab. - From the
**Number**group, select**Percentage**.

Now all the cumulative sum of the sales amount is in percentage.

**Read More: How to Make a Pareto Chart Using Pivot Tables in Excel**

### Step-2: Inserting Pareto Chart

After preparing the data, the next step will be inserting a chart using the data. To insert a **Pareto **chart,

- Highlight the
**Product**,**Sales**, and**Percentage**columns. - Go to the
**Insert**tab. - From the
**Charts**group, select the**Recommended Charts**command.

Now **Insert Chart **dialog box appears.

- Select the first recommended chart.
- Then click
**OK**.

After that, you will see the following **Pareto **chart.

### Step-3: Adding Data Labels

To add data labels on the **Pareto **chart,

- Click on the orange percentage line to select it.
- Then go to
**Chart Design ➤ Add Chart Element ➤ Data Labels ➤ Below**.

This will add the percentages of the cumulative sum of the sales amount below the orange line.

## Significance of Creating a Pareto Chart

From the above **Pareto **chart, we can see that the sales of **Coconut**, **Matcha**, and **Strawberry **contribute about **80%** of the whole sales. This data representation supports the **80/20 **rule completely. Now you can easily understand which products are more important for profitability and which aren’t.

## Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.

## Conclusion

To sum up, I have discussed steps to make a Pareto chart in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website **Exceldemy** to explore more.