Tags |

# How to Create a Pareto Chart in Excel

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.

## 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.

## What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

1. sudarshan wrote:

These methods were extremely useful and helpfull.
Thank you.

2. Ajay wrote:

Hi,
Thanks Sudarshan and welcome to da TaB !

Regards,

3. manoj saini wrote:

4. Ajay wrote:

@manoj – glad you liked this tutorial.

5. pikesway wrote:

Great article. I have put together a template and some context on what a pareto chart is and how it can be used. The template is free so feel free to use as you will.
http://hubpages.com/hub/Pareto-Charts

6. Neelim Kumar wrote:

This is extremely good synopsis to learn pareto-chart concept.
Thank you so much……………..

7. Dipesh wrote:

These methods were extremely useful and helpfull.
Thank you.

8. Nana Jacquay wrote:

Thank you for your entry! Honestly I have never come across anything that great.

Thank you for your valuable information

1. Pareto Chart Table - A More Meaningful Analysis | Excel & VBA - da Tab Is On wrote:

[…] Pareto charts are one of the basic pillars of the six sigma methodology and are typically used to depict the frequency of occurrence of issues that affect ‘production’. Although one usually sees them being used as standalones, the humble pareto chart can very well be augmented in order to make it a much more meaningful tool for tracking and resolving pending issues & errors. Let’s look at one such approach that utilizes a combination of a horizontal pareto chart and a chart table. […]

2. How to create Pareto charts with Excel 2007 by Eileen Eva | Inside Technology 360 wrote: