Excel table is useful as you can organize and analyze data quicker. You can convert…

# How to create a Pareto chart in Excel

The Pareto chart in Excel contains both columns and a line graph. The line graph on the chart is called the Pareto curve and shows the most significant data factors. It is named after the Italian economist Vilfredo Pareto. One of the most important aspects is the 80-20 rule, meaning that 20% of the company’s products or customers generate 80% of its total sales. So, how do you create a Pareto chart in excel 2016 or later? Some people also know it as the whale curve of cumulative customer profitability. This article will teach you how to create a Pareto chart in Excel in steps.

## Table of contents

## Creating a Pareto chart in Excel 2016

Before you start to make a Pareto chart, you need to insert some data. The example you are going to read is about customer profitability. Using the 80-20 rule, the company gets an overview of which customers are profitable and non-profitable.

### How to create a Pareto chart in steps

Let’s say that a company has 15 customers that generate its total sales, and you want to find out if the 80-20 rule applies here. In this example, we have entered 15 customers in **column A**, and the sales data for each customer in **column B**. Follow these steps below, and you will be able to create a Pareto chart in Excel.

**1**– First, you have to **select **the data in range cell A1:B16. Do **not **include the total number.

**2 **– Then click on **Insert** in the top bar.

**3 **– Go to charts and click on the histogram **figure**.

**4 **– Select the **Pareto **chart.

The Pareto chart will look like the example below. As you can see, the 80-20 rule applies here, as the first 20% of the customers generate about 80% of the total sales.

## Building a Whale curve of customer profitability

Excel automatically builds a histogram with a Pareto curve, but that is not the same as a whale curve. A **whale curve** is a graph of cumulative customer profitability and has a very different shape.

A whale curve shows:

- 20% of products generate about 80% of sales/profit or

- 20% of customers generate about 80% of sales/profit.

### 1 – Sort data and rank by profit

In order to create the whale curve, you need to have the profitability data of your customers. Now we have entered the profit data for the same 15 customers as the previous example.

First, you have to sort your data by descending profit. You can select the data and click on the Z-A button. It is supposed to rank the columns in alphabetic order, but sometimes it will not sort the data correctly.

In this case, the safest way to do this is to sort the data by **profit**. Remember **not **to include the total with the selection. Select the data from range A1:C16 and **right-click** on the screen > Then click on **Sort **> Now click on **Custom Sort**.

A new box called **Sort **appears on your Excel sheet. Here you have to change the Column and sort by **Profit**. Change the Order from **Largest to Smallest**. Now click on **OK**.

Now you have ranked the customers in the columns from most profitable to least profitable. The example of your data is changed and should look like the picture below.

### 2 – Calculate cumulative profit

Now you have to calculate the cumulative profit, which means that you have to add the profit of each customer. In cell D2, you have to write the same number as C2. Then you can write the formula for cell C3 and drag it down in the rest of the column. You only have to add up two numbers, as you can see in the formula bar.

First, you **click **on cell D3, which is highlighted in the picture > Then you write the **formula **in the formula bar > Take the mouse cursor to the edge of the right-down corner of cell D3 > The mouse cursor changes to a **black +**. Now **drag **it down until cell D16.

After that, you have to calculate the cumulative percentage. You are going to calculate the cumulative profit of each customer divided by the total profit.

First, you **click **on cell E2, which is highlighted in the picture. Then you write the **formula **in the formula bar > Then you take the mouse cursor to the edge of the right-down corner of cell E2 > The mouse cursor changes to a **black +**. Now **drag **it down until cell E16.

### 3 – Create a whale curve in all Excel versions

You can insert a whale curve in all Excel versions because you have to calculate the cumulative profit in percentage. Look at the picture below and follow the steps.

**1 **– First, you have to **select **the cumulative profit in cell range D1:D16.

**2 **– Then go to **Insert **in the top bar.

**3 **– Click on the line charts **figure **in Charts.

**4 **– Select **Line with Markers**.

Now you have created a whale curve in Excel, and the example should look like the picture below. You can customize the title by double-clicking on **Chart Title** and write the text you like.

As you know, 20% of 15 = 3 customers. In this example, the first three customers earn 88% of the profit, which means that the 80-20 rule applies here. You can also see that up to 80% of the customers provide this company a profit, while the last three customers give them a loss.

Read more: How to create a bar chart in Excel »

## Conclusion

You have learned how to create a Pareto chart in Excel 2016 or later. You can also make a whale curve customer profitability by using a line graph with markers. Creating a Pareto chart in Excel 2016 is easy and can show you the most significant factors in the data. All Excel versions can make a whale curve by calculating the cumulative profit of a company’s customers. It will show you which customers are the most and least profitable for the company.

## This Post Has 0 Comments