Pareto Chart in Excel

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Create a Pareto Chart in Excel? (Step by Step)

You can download this Pareto Chart in Excel Template here – Pareto Chart in Excel Template

Below are the steps to create a Pareto chart in Excel.

  1. Collect the raw data, including the category (cause of a problem) and their count.


    PARETO Chart Example - 1-1

  2. Calculate the percentage of each category and further compute the cumulative percent.


    The percent can be calculated using the formula =(C3/$C$13) *100, applied throughout the other cells.

    PARETO Chart Example - 1-2

    Cumulative Percentage

    It is the method of calculating the frequency distribution and will be calculated successively by adding the percent with other frequencies. So, the formula will be =D6+C7. After sorting the values from largest to smallest, we calculate the cumulative percentage for each category.

    PARETO Chart Example - 1-3

  3. Select a category, count, and cumulative percent range, as shown below.


    PARETO Chart Example - 1-4

    Go to the “Insert” tab in Excel and select a “2-D Column” bar graph.

    PARETO Chart Example - 1-5

    Now, the Pareto chart created is shown below:

    PARETO Chart Example - 1-6

  4. Select the cumulative percent bars and change the series chart type to “Line.”


    The red bars are the cumulative percentage bars, select any of the bars, change the series, and select “Line” from the “Change Chart Type.”

    PARETO Chart Example - 1-7

    Now, the Pareto chart will look like the one shown below.

  5. Right-click the cumulative total line (in red) and choose “Format Data Series.”


    PARETO Chart Example - 1-9

    Select the secondary axis in excel

    PARETO Chart Example - 1-10

    After selecting the “Secondary Axis,” close the “Format Data Series” window.

    Now, the Pareto chart will look like as shown below.

    PARETO Chart Example - 1-11

  6. Click on the right-hand axis and select the “Format Axis.”


    PARETO Chart Example - 1-12

    Then, under the “Axis” option tab, select “Maximum” to set it to be fixed and set the value to 100.

    PARETO Chart Example - 1-14

    In the “Axis” options, select the “Maximum” from “Auto” to “Fixed.” Then, enter a value of 100 manually and close the “Format Axis” window.

    Finally, the Pareto chart will look like the one shown below.

    PARETO Chart Example - 1-15

    PARETO Chart Example - 1-15

The chart above shows that 80% of the effects come from 20% of the causes.

Advantages

  • The Pareto chart highlights the major cause of the problem that hampers a process.
  • It helps to rectify the major issues and thus increases organizational efficiency. Furthermore, once the big hitters in a process are discovered using this technique, one can move ahead with the resolutions, thus increasing the organization’s efficiency.
  • It also enhances problem-solving skills as it enables you to sort out business-related issues into strong facts. Then, once we have vividly laid out these facts, we can start the important planning to take care of the issues.
  • It improves decision-making in a process.

It helps the corporate team focus on the input that will have a greater impact per the 80/20 rule.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Limitations

Things to Remember

Pareto Chart in Excel Video

 

Recommended Articles

This article is a guide to Pareto Chart in Excel. We discuss its uses and how to make a Pareto chart in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *