What is a Pareto Chart?
Pareto Charts, named after the Italian economist Vilfredo Pareto, are a unique and powerful tool for data analysis and decision-making. A Pareto Chart is a combination of a bar and a line charts, essentially using both in a single visualization. This chart type is especially useful for comparing the importance of items against other factors. Ideally suited for tracking indicators like acceptance metrics or production quality, Pareto charts are one of the key visualizations in Microsoft Excel.
In the typical application of this chart, bars are meticulously arranged in a descending order, serving as a visual manifestation of frequency. Simultaneously, the accompanying line meticulously captures the cumulative total percentage, providing a nuanced insight into the data landscape.
Vilfredo Pareto made a groundbreaking observation in the late 19th century that has since transformed various fields of study and practice. He discovered what is now famously known as the Pareto Principle or the 80-20 rule. This principle emerged from his analysis of wealth distribution in Italy, where he noticed a consistent pattern: approximately 80% of the country's wealth was owned by just 20% of the population. Intrigued by this disparity, Pareto delved deeper and found that this distribution was not unique to wealth alone but seemed to be a recurring pattern in different aspects of life and society. His work paved the way for a principle that posits that, in many scenarios, approximately 80% of effects come from 20% of the causes, a concept that has profound implications in economics, business, and beyond.
Rooted in the Pareto principle, this chart leverages Excel references to embody the core tenet that 80% of effects emanate from 20% of causes. An illustrative example of this principle is the revelation that the wealthiest 20% of the global population exerts control over a staggering 82.7% of the world's income. The 80/20 rule underscores the potency of Pareto Charts in discerning patterns and relationships within data.
Pareto Chart Excel Basics
A Pareto chart mainly consists of 2 objects:
- Bars with values in descending order
- A line for cumulative total percentage
These requirements demand data sorted accordingly to be displayed on two types of graphs.
Creating a Pareto Chart in Excel
Creating a Pareto chart in Excel varies depending on the version you're using. In newer versions of Excel (2016 and later), the process is streamlined. However, in older versions of Excel, building a Pareto chart requires manually creating a column chart for your data, calculating the cumulative percentages separately, and then adding these as a secondary axis in the form of a line chart. This method ensures users of any Excel version can perform a Pareto analysis, albeit with varying levels of manual input.
Excel 2016
Excel 2016 includes a Histogram tool that is equipped with the capability to create Pareto charts, a feature designed to facilitate quick and efficient generation of these charts. To demonstrate how you can leverage this tool, we have prepared a sample workbook. This workbook serves as a practical example to guide you through the process of creating a Pareto diagram using your own data. To get started and follow along with the instructions, you can easily download our sample workbook by clicking on the download button provided below. This hands-on approach will help you understand and utilize the Pareto chart feature in Excel 2016 effectively.
Assume that you have a data table like below.
Begin by selecting the set of values to be used in the visualization, just like you would when creating any other chart.
Next, go to INSERT > Charts in the Ribbon, and click Histogram. From the list of options, select Pareto.
Your Excel Pareto chart will be created with values sorted automatically. You can always customize your Pareto chart, just like in other charts by going to CHART TOOLS > DESIGN in the Ribbon.
Pareto Charts in Excel 2013 and Before
Unfortunately, Excel 2013 and its predecessors lack a native function specifically for crafting Pareto graph. However, users are not without options, as there exists a practical workaround to this limitation. It's important to recognize that a Pareto graph is fundamentally a combination chart that displays values in a sorted order. With this understanding, we can revisit our previous example and apply this concept. By manually creating a combination chart and arranging the data in a descending order, you can effectively mimic the structure and functionality of a Pareto chart. This approach allows users of Excel 2013 and earlier versions to still benefit from the analytical insights provided by Pareto analysis, despite the absence of a dedicated feature for it in these older Excel versions.
We begin by sorting the data.
- Click a cell in the values column and click the Sort Largest to Smallest icon under DATA > Sort & Filter.
- If your data is defined as a Table, click the down triangle ▼ symbol and click Sort Largest to Smallest.
Next step is calculating the cumulative total percentages. To do this, we need to divide the running totals by the actual total. In our example, these values are placed in range C3:C22. Select the cell D3 and enter the following formula:
=SUM($C$3:C3)/SUM($C$3:$C$22)
Copy down the formula for the rest of the values. The last value should be equal to 1 - representing 100%. Format the cumulative total values as percentages and give the new column a name for a better view.
Now, we can create the Pareto diagram. Begin by selecting your data. Unlike Excel 2013, Excel 2010 and previous versions don't have a definition for a combo chart (but they are supported). Since these versions don’t have the Combo Chart feature, you need to create a bar or line chart first. The next steps will be selecting one of the series and turning it into the other type, and selecting the Secondary Axis option.
Go to INSERT > Charts > Combo > Clustered Column – Line path to create a combo chart. Excel automatically sets the first selection as columns, and the second as line.
At this point, both series are using the same axis, whereas the cumulative total percentage values are supposed to be displayed on another axis. Right-click on the chart area and click the Change Chart Type option.
Check the Secondary Axis option for the cumulative total values and click OK to apply your settings.
Our Excel Pareto graph is ready! You can apply a few more design tweaks to finalize. Below are a few ideas.
- Remove title and legend.
- Set cumulative axis' Maximum value to 1.
- Set Gap Width to remove gaps between columns.
Practical Applications of Pareto Analysis
Below is the final chart using our sample data. Pareto Charts are an invaluable tool in various sectors for optimizing decision-making processes and identifying key areas of focus. In the business world, these charts are frequently used to analyze customer feedback or sales data. By highlighting the most significant factors contributing to a particular outcome, such as the majority of complaints stemming from a few key issues or the bulk of profits coming from a select range of products, businesses can prioritize their efforts and resources effectively. The visualization provided by Pareto diagrams simplifies complex data, making it easier for management to identify trends and make informed strategic decisions.
In the realm of quality control and process improvement, Pareto analysis is instrumental. They help in pinpointing the most common sources of defects or problems within a production process. By focusing on the 'vital few' causes of quality issues, organizations can significantly enhance their operational efficiency. This is particularly relevant in manufacturing, where addressing the primary causes of defects can lead to substantial improvements in product quality and a reduction in waste and rework. Additionally, Pareto diagrams are used in project management to identify and address the most critical issues affecting project timelines or budgets. By targeting the most impactful problems, project managers can ensure smoother project execution and better resource allocation.
In conclusion, creating a Pareto chart in Excel can be accomplished with ease in newer versions, thanks to the integrated feature that automates the process. For users of these versions, it's as simple as selecting the data and using the built-in Pareto chart option under the 'Histogram' section. However, for those working with older versions of Excel, a more hands-on approach is required. This involves manually creating a column chart, calculating cumulative percentages, and then adding these as a secondary axis in line chart format. Regardless of the version, the key steps remain the same: organize data in descending order, calculate cumulative percentages, and visually represent these two aspects through a combination of column and line charts. With these steps, users across different versions of Excel can effectively create and utilize Pareto diagram for their data analysis needs.
