The Pareto principle states that about 80% of the effects come from 20% of the causes in many real-world events.
You can use this to, say, identify 80% of your best-selling categories, or conversely the worst performing categories/items. The uses are limitless, really. And they don’t have to be only about $$$.
Graphically, it’s a combination chart: clustered columns and with a percent line on a secondary axis. Here, I’ll show what type of data you need to start with, how to create the chart, and how to tell its story.
Preparing the Data Set
Let’s start with a small data set:
It’s about revenues by book categories. The first thing we want to do is find the total revenue. Using which we’ll calculate the percent of cumulative revenues for each category. Therefore, in-between we’ll need to calculate the cumulative values for each.
The final table should look like this:
Important to note that the last row of Cumulative should be exactly = Sum of revenues. And the last value of Percent row should be 100%. If any of these is off, the conclusion will be erroneous! You want to double-check the formulas and correct any error. The formulas themselves are extremely basic as shown above (formulas in burgundy) and you would simply drag them down to cover all records.
Creating the Chart
The chart can be inserted from Insert->Chart menu and either finding it in Recommended Charts (Excel does a good job of picking it usually), or as I do, go to Histogram and choose Pareto in that category. Now your chart should look like this:
The Series for this chart includes REVENUE and Percent (in the above example data set), and the Horizontal Axis covers all the categories labels. Do not include the Sum row or the Cumulative numbers as the column was auxiliary for Percent column.
Reading the Chart
The relationship between the percent line and the bars are key. Since here, we’re interested in 80% of the best-selling book categories, I highlighted those to tell the story. It’s clear that Romance, Baby books, Sports, Fiction, Sci-Fi, Fashion, and True Crime categories account for 80% of the revenues.