A Pareto chart is a graphical representation of data in a clear cut manner; it clearly shows the relative importance between the groups of data presented. This graph contains vertical bar graphs and line graphs. It is also called a Pareto distribution diagram and the values are plotted in descending order of relative frequency.
How to create a Pareto chart in Excel?
The Pareto principle explains that 80% of the priority tasks are represented in just 20% of the given tasks. In other words, 80% of the outcome is derived from 20% causes. Now to understand and implement this principle better, it is best to create a Pareto Chart. In Microsoft Excel, a pre given template of Pareto Chart is not available. However you can still create it easily by following these easy steps:
Basic data entry
Type all the variables and their frequency in two columns in Microsoft Excel. Sort this data in a descending order of frequency i.e. from the highest to the lowest. You don’t need to physically sort it. Just select any frequency and then click on the button that reads ZA’. This button will automatically sort your data in a descending order.
Next to the frequency column, create a new column for cumulative frequency. To calculate cumulative frequency automatically, you will have to use a formula. The first value in this column will be the same as in the frequency column. The second value will be the sum total of the first value of cumulative frequency column and the second value of frequency column. Thus, you can establish a formula using the +’ symbol between the two columns mentioned above. This will automatically give you the second value. Now you can simply drag the formula to get the remaining values.
In the next column, you need to calculate the cumulative percentage. Divide the corresponding cumulative value with the total cumulative value and multiply the result by 100 to achieve the cumulative percentage. In this column, just calculate the percentage of the first value and drag down the formula to get the remaining values. The percentage should always be 100 in the last row.
Select a range and create a basic graph
To select the range for the graph, you need to press the control button and select the variable column, frequency column and the cumulative percentage column. After selecting the three columns, click on the insert menu and select 2-D columns in the column graph option. A basic bar graph with blue and red columns will be created on your sheet. The X-axis is in blue and Y-axis is in red color.
Change series chart type
In the graph, carefully click on the red columns to select them. Now right click and choose Change Series Chart Type option. On selecting this option, the change chart type window will open. In this window, select on the line with the markers option’ and click ‘Ok’. Now the graph will display blue bar graph and red line graphs together.
Format data series
Select the red line graph and right click on it. From the drop down menu, select on the Format Data Series Option. This action will open the Format Data Series window. In this window, on the left side the first option is the series options. The section on the right side will show you two options – Primary Axis and Secondary Axis. Select the Secondary Axis.
Correcting the Y-axis
On the chart you will see the Y-axis increasing, as it should in a Pareto Chart. But the only problem is that the Y-axis may show values up to 120%. If this happens, you need to correct it. To do so, right click on the axis values and click on Format Axis. In the Format Axis window, the left column will have Axis Option as the first option. Now in the right column, that is the Maximum’ option, click on fixed and enter the value as 1.0 and click on close.
Pareto Chart is ready
The Pareto Chart is ready and you can make it more appealing by adding a Chart Title, Axis Title, Legend and Data Labels. You can even change the colors used in the chart. The Pareto Chart in front of you will clearly display the most important factors of your data i.e. it will be the reflection of the 80/20 Pareto principle.
Quick Video Tutorial How to Create Pareto Chart in Excel 2010
Benefits of using Pareto Chart
The Pareto Chart is used by enterprises to solve varied problems. It is used to analyze the frequency or causes of problems; it also helps identify the problematic areas that need to be focused. This invaluable data helps in improving organizational efficiency and companies can allocate their resources in a systematic way. It is one of the simplest tools to improve processes in a factory or a manufacturing line.
This analysis also improves the decision making process in any enterprise. The chart clearly shows the problem areas that need the most attention. This reduces the time taken for the decision making process as a group consensus can be arrived in a quicker and faster manner.
It is also used by six sigma consultants and forms the basis of most six sigma projects. It helps them identify major and minor problems and gives them a definitive start point for the project.
Where to download Pareto Chart Template for Microsoft Excel
You can download directly from Microsoft Official Website => Download Template Here
Difference between Bar Chart and Pareto Chart
In a bar graph, the values on the X-axis do not follow any order whereas in the Pareto chart they are shown in a descending manner. Also, the Pareto Chart uses both bar graphs and line graphs. Thus, a bar chart just depicts the data graphically, whereas the Pareto Chart not only represents the data but also depicts the importance of each factor.
Pareto Chart can really improve the quality of business if you use it correctly. Enterprises can deploy this chart to identify major product defects, analyze consumer complaints etc. It helps you prioritize better and focus on important issues while ignoring the trivial factors.
The Pareto Chart is relatively simple to make and it gives you a multi-perspective analysis within minutes.