A Pareto chart plots the distribution of data in columns by frequency, sorted in descending order. A line showing cumulative percentage is plotted on a secondary axis. Starting with Excel 2016, the Pareto chart is a built-in chart type. In this worksheet, I’ve got a list of 100 reported issues classified by type. There are six types total, listed in column E. To plot this data in a Pareto chart, we first need a summary count by type. You might think a pivot chart would work well in this case, but at the moment a Pareto chart is not a pivot chart option. So to get the counts we need, I’ll use formulas. I’ll first convert this data to a table. Then I’ll use the COUNTIF function to get a count for each type. Now we can plot the data in a Pareto chart. To start off, select any cell in the summary. With suitable data, you’ll find the Pareto chart listed in Recommended charts. You’ll also find the Pareto chart under the Statistic icon to the right, in the Histogram area. Excel will build the Pareto chart automatically. The vertical axis is a value axis, scaled to fit the data. In the Format Task Pane, you’ll find limited options for Minimum, Maximum, and display units. The horizontal axis is a category axis by default. Like the built-in histogram chart, the only option for columns is gap width. However, you can select and format columns individually if you like. You can easily display the number of items in each column using data labels. By default, gridlines are lined up with the units on the primary vertical axis. The built-in Pareto chart uses a newer chart engine in Excel and still has some limitations. You can’t set the chart title with a formula. You can’t specify units for either vertical axis. And you can’t add data labels or markers to the Pareto line. If you need these features, your best bet is to build a Combo Chart manually.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
