Find out how to create a Pareto chart in Excel. A Pareto chart can be created in Microsoft Excel as a combination of a bar and a line chart. A Pareto chart (also known as the Pareto graph) is a combination of two data series, one set of points representing a set of independent values and the other representing the cumulative sum of those values. The independent values are typically represented by vertical bars in which the values are arranged in a descending order with the most important category to the extreme left. The second set of values, often represented as a line, is the cumulative percentage sum of the first set of values. You can download an excel worksheet outlining steps on how to create a Pareto chart here.
The chart was named for Vilfredo Pareto. According to wiki, the Pareto chart is one of the fundamental tools of quality control and management. Apart from the Pareto chart, other tools in the quality control universe include the histogram, control charts, check sheets, flowchart, scatter diagrams and the cause & effect diagram.
How to create a Pareto Chart in Excel
Layout the data in the proper format
Remember that in a Pareto chart you will typically have two series. Series A will represent the individual values in the series as a bar chart. Series B will show the values in a cumulative manner as a line graph. So while the first series in ordered in a descending manner, the second one is ordered in an ascending manner.
Insert the bar chart
Once you have the data laid out as shown in the figure below, simply select both the series and click on menu “Insert” -> “Chart” and choose the bar graph. You will end up a chart with two series of bars. From thereon, creating a Pareto chart in excel is just a step away
Convert the series representing the cumulative sum to a line chart
Right click on the second series and click “Chart Type” and select the “Line Chart”. This will convert the second series into a line chart. You will end up with a line chart that starts from the middle of the first bar, rises up with an ever decreasing slop and finally ends up to a point above the last bar.
Format the cumulative data series and change axis to the secondary axis
The final step in creating a Pareto chart in Excel is to double click on the cumulative series represented by the line chart and on the Axis tab, click on the “Plot series on “ and click “Secondary Axis”. This will change the Y-axis for the cumulative graph series to the rightmost axis. This step helps separate both the chart series for greater visual clarity.
Dress up the Pareto chart
An important step in this exercise is to clean up the chart for “chart junk”. Remove the default coloring of the chart area, remove the chart border for the bars in the first series and provide proper coloration. Your Pareto chart will come out looking something similar to the one shown below.