Pareto Chart in Excel

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 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 will be calculated using the formula =(C3/$C$13) *100, applying 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 of the categories.

    PARETO Chart Example - 1-3

  3. Select category, count, and cumulative percent Range together as shown below


    PARETO Chart Example - 1-4

    Go to the Insert tab in ExcelInsert Tab In ExcelIn excel “INSERT” tab plays an important role in analyzing the data. Like all the other tabs in the ribbon INSERT tab offers its own features and tools. Under Insert Tab we have several other groups including tables, illustration, add-ins, charts, Power map, sparklines, filters, etc.read more 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 one of the bars and change the series, select Line from the change chart type.

    PARETO Chart Example - 1-7

    Now the Pareto chart will look like shown below:

    PARETO Chart Example - 1-8

  5. Right-click on the cumulative total line (in red) and choose format data series.


    PARETO Chart Example - 1-9

    and select the secondary axis in excel

    PARETO Chart Example - 1-10

    Select Secondary axis and 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 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 Axis Options, select the Maximum from Auto to fixed and enter a value 100 manually and close the format axis window

    Finally, the Pareto chart will look like

    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 problems and thus increases organizational efficiency. Once the big hitters in a process are discovered using this technique, one can move ahead for the resolutions, thus increasing the efficiency of the organization
  • It also enhances problem-solving skills as it enables you to sort out business-related issues into strong facts. Once you’ve vividly laid out these facts, you can start the planning important to take care of the issues.
  • It improves decision making in a process.
  • It helps the organizational team to focus on the input that will have a greater impact in accordance with the 80/20 rule.

Limitations

Things to Remember

Pareto Chart in Excel Video

 

Recommended Articles

This has been a guide to Pareto Chart in Excel. Here we discuss its uses and how to make Pareto Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

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