Reveal your data set’s heaviest hitter with a Pareto chart type

 

by Kara Hiltz

Application: Microsoft Excel 2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201804/pareto.zip

Pareto charts, which are histogram charts sorted in descending order, are often used to determine what data has the biggest impact. In Excel 2016, you can now choose the Pareto chart type to create one of these charts in seconds.

To create a Pareto chart, we’ll:

  • Enter the data we need for our Pareto chart example.
  • Create the Pareto chart.
  • Format the Pareto chart to increase its visual appeal and readability.

In a previous issue, we demonstrated how to use Excel 2016’s new histogram chart type. The Pareto chart type is related to a histogram because it also categorizes data. But unlike the histogram chart, the Pareto chart type, also sorts the categorized data bars and adds a line to represent the cumulative total percentage of data, as shown in Figure A.

In this article, we’ll explain when you might use a Pareto chart over other chart types, go through the steps it takes to create a Pareto chart, and then give you a few suggestions for formatting the Pareto chart. Let’s get started.

IME18402A

A:

The Pareto line gives you a cumulative percentage of your data set based on each additional category as you move from left to right.

When to use a Pareto chart

Pareto charts aren’t always applicable to a data set. Generally speaking, you’ll use a Pareto chart when you have data that falls into two or more categories and you want to determine which of these categories contributes most to your data set.For this reason, many data sets that deal with quality control are good candidates for a Pareto chart.

In our example, we’ll use survey results to find out how customers are hearing about our company. Our data set includes responses to 300 surveys asking customers to select an option that matches how they heard about the company. We have categorized the responses for each choice, as shown in Figure B.

Download: If you’d like to follow along with our example, just download and extract the file surveyresults.xls from the URL given at the beginning of the article.

IME18402B

B:

You should list your Pareto chart’s categories in the leftmost column.

Create a Pareto chart

Once your categorized data set is ready in Excel, it takes just a few clicks to create a Pareto chart, which combines a sorted bar chart with a line.

To insert a Pareto chart:

  1. Select any cell within your data set.
  2. Go to the Insert tab and, in the Charts area, click on the Insert Statistical Chart icon insert statistical [...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage