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 »
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.