Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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

    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 tab

    You 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 the Chart 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 pane

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

Formulas used to create histograms in Excel 2016

Scott’s normal reference rule:   

Formula for Automatic option

Scott’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   

Formula for Overflow bin option

Underflow Bin   

Formula for Underflow bin option

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

    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. For details, see "Configure bins" on the Windows tab.

  2. On the ribbon, click the Insert tab, and then click Statistical chart icon (the Statistical chart icon), and in Histogram section, click Pareto.

    Pareto chart on the ribbon

    Tip: Use the Chart 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 display them on the ribbon.

See Also

Create a waterfall chart

Create a histogram

Create a box and whisker chart

Create a treemap chart in Office

Create a sunburst chart in Office

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×