Do Pareto Analysis In Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.

Do Pareto Analysis In Excel

Do Pareto Analysis In Excel

Learn to do Pareto Analysis and Create Pareto Chart in MS Excel. Pareto Analysis is a technique used for decision making based on the Pareto Principle, known as the 80/20 rule. It is a decision-making technique that statistically separates a limited number of input factors as having the greatest impact on an outcome, either desirable or undesirable. Pareto analysis is based on the idea that 80% of a project’s benefit can be achieved by doing 20% of the work or conversely 80% of problems are traced to 20% of the causes.

The Video above explains how to use Pareto Analysis in  MS Excel. The Text below is from a Microsoft Excel Support Blog

Create a Pareto chart

Applies To: Excel 2016 , Word 2016 , Outlook 2016 , PowerPoint 2016
A Pareto or sorted histogram chart contains both columns sorted in descending order and a line representing the cumulative total percentage. Pareto charts highlight the biggest factors in a data set, and are considered one of the seven basic tools of quality control as it’s easy to see the most common problems or issues.Example of a Pareto chart

Create a Pareto chart

  1. Select your data. Typically, you select a column containing text (categories) and one of numbers. A Pareto chart then groups the same categories and sums the corresponding numbers.Data used to create the example pareto chart
    Data used to create the example chart above

    If you select two columns of numbers, rather than one of numbers and one of corresponding text categories, Excel will chart your data in bins, just like a histogram. You can then adjust these bins.

  2. Click Insert > Insert Statistic Chart, and then under Histogram, pick Pareto.Art showing the Pareto chart accessed from the Insert tabYou can also use the All Charts tab in Recommended Charts to create a Pareto chart (click Insert > Recommended Charts > All Charts tab.

TIP: Use the Design and Format tabs to customize the look of your chart. If you don’t see these tabs, click anywhere in the Pareto chart to add theChart Tools to the ribbon.

Design and Format tabs for the Chart Tools

Configure bins

  1. Right-click on the chart horizontal axis, > Format Axis >Axis Options.Format Axis command on shortcut menu
  2. Use the information below to pick the options you want in the Format Axis task pane.Format Axis task paneBy Category    The default when both data and text are plotted. The text categories are plotted on the horizontal axis and graphed in descending order.

    TIP: To count the number of appearances for text strings, add a column and fill it with the value “1”, then plot the Pareto chart and set the bins to By Category.

    Automatic    This is the default for Pareto charts plotted with a single column of data. The bin width is calculated using Scott’s normal reference rule.

    Bin width    Enter a positive decimal number for the number of data points in each range.

    Number of bins    Enter the number of bins for the Pareto chart (including the overflow and underflow bins). The bin width will adjust automatically.

    Overflow bin    Check the box to create a bin for all values above the number in the corresponding box. To change this value, enter a decimal number into the box.

    Underflow bin    Check the box to create a bin for all values below or equal to the number in the corresponding box. To change this value, enter a decimal number into the box.

Scott’s normal reference rule:

Art showing formula for Scott's Reference RuleScott’s normal reference rule tries to minimize the bias in variance of the Pareto chart compared with the data set, while assuming normally distributed data.

Overflow Bin

Overflow formulaUnderflow Bin

Underflow formula

TIP: To read more about the Pareto chart and how it helps you visualize statistical data, see this blog post on the histogram, Pareto, and box and whisker chart by the Excel team. You may also be interested learning more about the other new chart types described in this blog post.

Formulas used to create histograms in Excel 2016

Our Other Services

Read Free Ebooks

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!

Buy Excel Course

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788

Create Best Looking Resume

At you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!

Download Excel Crash Course

This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.

Learn Photoshop Web Designing

You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .

Download Full list of Excel Shortcuts

This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.

Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on

Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.

List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.

Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You