Categorized | six sigma

Pareto Chart Table – A More Meaningful Analysis (Download & Template)

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.

Tabulating the Data

Let’s first tabulate the data for the chart. The issues get listed in descending order of occurrence along with the frequency of occurrence and their cumulative totals.


Adding Cause and Resolution

pareto-chart-cause-and-resolutionOnce we have listed down the issues, we add the cause of the issues and the resolutions to the chart table. The rightmost column is used to indicate the current state of the resolution – whether the issue has been resolved or not. We can use data validation to prepare a drop-down of the various types of issues. In this case, I’ve used the ‘P’ attributes – People, Process, Policies, Procedure, Price, Promotion, Place and Product – which are more commonly used in the services industry.

Building a horizontal pareto chart

The second step is to make a horizontal pareto chart so that the bars are aligned along the rows. Since the event descriptions and other details are listed rowwise, a horizontal pareto chart gives a better perspective than a vertical one. Building a horizontal pareto chart can be a bit tricky. Here are the steps:


Putting it all together

Adjust the pareto chart, remove unwanted chart elements, format the table and we are all set to tackle those nagging issues with style !

You can download a sample worksheet with the pareto chart table here or click on the button below:


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

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>

Comments and Trackbacks

  1. Prabhjeet wrote:

    Hi Ajay,

    I use your this site for improving my Excel,But I am not able to use multiple if function.
    I would like to use three if functions but getting error
    Can you help.

    best regards

  2. Ajay wrote:

    @Prahjeet – Check IF this helps you understand it better. rgds,

  3. Michael wrote:

    The bar chart portion seems to fall apart in 2007 for some reason. All of the bars are taking up the entire space (as if they were equal to the total). The legend is also hidden behind the bars.

  4. Ajay wrote:

    @Michael – I thought what was good in 2003 should work in 2007 and later. I know there are these nagging chart compatibility issues between the versions but then this was just a simple chart which I thought should come out fine.

    I’ll look into this and have it rectified. Thanks a ton!

  5. Ajay wrote:

    @Michael – I had a relook at this. One needs to turn on the secondary chart axis and then hide it by changing the label, line and fill colors to white. It’s a workaround but it works.


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel