Contents
You may have heard of the Pareto Law or the 20/80 Principle. At the end of the 19th century, the Italian sociologist and economist Vilfredo Pareto discovered that the distribution of wealth in society is uneven and subject to a certain dependence: with an increase in wealth, the number of rich people decreases exponentially with a constant coefficient (among Italian households, 80% of income was in 20% of families). Later, this idea was developed in his book by Richard Koch, who proposed the formulation of the universal “Principle 20/80” (20% of efforts give 80% of the result). In practice, this law is usually not expressed in such beautiful numbers (read “The Long Tail” by Chris Anderson), but clearly shows the uneven distribution of resources, profits, costs, etc.
In business analysis, a Pareto chart is often built to represent this unevenness. It can be used to visually show, for example, which products or customers bring the most profit. It usually looks like this:
Its main features:
- Each blue column of the histogram represents the profit for the product in absolute units and is plotted along the left axis.
- The orange graph represents the cumulative percentage of profits (i.e. the share of profits on a cumulative basis).
- On a conditional border of 80%, a threshold horizontal line is usually drawn for clarity. All goods to the left of the point of intersection of this line with the graph of accumulated profit bring us 80% of the money, all goods to the right – the remaining 20%.
Let’s see how to build a Pareto chart in Microsoft Excel on your own.
Option 1. A simple Pareto chart based on ready-made data
If the source data came to you in the form of a similar table (that is, already in finished form):
… then we do the following.
Sort the table in descending order of profit (tab Data – Sorting) and add a column with the formula for calculating the accumulated percentage of profit:
This formula divides the total accumulated profit from the beginning of the list to the current item by the total profit for the entire table. We also add a column with a constant of 80% to create a horizontal threshold dashed line in the future chart:
We select all the data and build a regular histogram on the tab Insert – Histogram (Insert – Column Chart). It should look something like this:
The percentage series in the resulting chart should be sent along the secondary (right) axis. To do this, you need to select the rows with the mouse, but this can be difficult, since they are hard to see against the background of large profit columns. So it’s better to use the drop-down list on the tab to highlight Layout or Format:
Then right-click on the selected row and select the command Format Data Series and in the window that appears, select the option On the secondary axis (Secondary Axis). As a result, our diagram will look like this:
For the series Accumulated Profit Share and Threshold, you need to change the chart type from columns to lines. To do this, click on each of these rows and select the command Change Series Chart Type.
All that remains is to select the Threshold horizontal row and format it so that it looks like a cutoff line rather than data (ie, remove the markers, make the line dashed red, etc.). All this can be done by right-clicking on the row and selecting the command Format Data Series. Now the diagram will take its final form:
According to it, we can conclude that 80% of the profit is brought by the first 5 goods, and all other goods to the right of the potato account for only 20% of the profit.
In Excel 2013, you can do it even easier – use the new built-in combo chart type immediately when plotting:
Option 2: PivotTable and Pivot Pareto Chart
What to do if there is no ready-made data for construction, but only the original raw information? Let’s assume that at the beginning we have a table with sales data like this:
To build a Pareto chart on it and find out which products sell best, you will first have to analyze the source data. The easiest way to do this is with a pivot table. Select any cell in the source table and use the command Insert – Pivot Table (Insert – Pivot Table). In the intermediate window that appears, do not change anything and click OK, then in the panel that appears on the right, drag the source data fields from the top to the bottom areas of the layout of the future pivot table:
The result should be a summary table with the total revenue for each product:
Sort it in descending order of revenue by setting the active cell to the column Amount in the Revenue field and using the sort button От Я до А (From Z to A) tab Data.
Now we need to add a calculated column with accumulated interest earnings. To do this, drag the field again Revenue to the area Values in the right pane to get a duplicate column in the pivot. Then right-click on the cloned column and choose command Additional calculations – % of the running total in the field (Show Data As – % Running Total In). In the window that appears, select the field Name, on which the percentage of revenue will accumulate from top to bottom. The output should look like this table:
As you can see, this is almost a ready-made table from the first part of the article. It only lacks for complete happiness a column with a threshold value of 80% for constructing a cut-off line in a future diagram. Such a column can easily be added using a calculated field. Highlight any number in the summary and then click on the tab Home – Insert – Calculated Field (Home – Insert – Calculated Field). In the window that opens, enter the field name and its formula (in our case, a constant):
After clicking on OK a third column will be added to the table with a value of 80% in all cells, and it will finally take the required form. Then you can use the command Pivot Chart (Pivot Chart) tab Parameters (Options) or Analysis (Analysis) and set up the chart in exactly the same way as the first option:
Highlighting key products
To highlight the most influencing factors, i.e. columns located to the left of the intersection point of the orange accumulated interest curve with the horizontal cutoff line of 80% can be highlighted. To do this, you will have to add another column to the table with the formula:
This formula outputs 1 if the product is to the left of the intersection point and 0 if it is to the right. Then you need to do the following:
- We add a new column to the chart – the easiest way to do this is by simple copying, i.e. highlight column backlight, copy it (Ctrl + C), select the diagram and insert (Ctrl + V).
- Select the added row and switch it along the secondary axis, as described above.
- Series Chart Type backlight change to columns (histogram).
- We remove the side clearance in the properties of the row (right-click on the row Illumination – Row Format – Side Gap) so that the columns merge into a single whole.
- We remove the borders of the columns, and make the fill translucent.
As a result, we get such a nice highlight of the best products:
PS
Starting with Excel 2016, the Pareto chart has been added to the standard set of Excel charts. Now, to build it, just select the range and on the tab Insert (Insert) choose the appropriate type:
One click – and the diagram is ready:
- How to build a report using a pivot table
- Set up calculations in PivotTables
- What’s New in Charts in Excel 2013
- Wikipedia article on Pareto’s law