A Pareto chart is a tool commonly used in Six Sigma methodology to illustrate the root problems or causes of a situation. To read more about the history and use of a Pareto chart, check out this distribution from the United States Department of Energy.
Although Pareto charts can be easily created in several different project management applications, not everyone has access to software of this type.
Fortunately, Pareto charts can also be created in Microsoft Excel 2007, and we’ll explain how in this step-by-step guide.
To begin, input the data from your project research into a table, which we'll use to create a chart in Excel. This doesn’t have to be a “fancy” table, but it does need to contain information on Count, Percent of Total, and Cumulative Percent as shown in the screenshot below. We actually won’t use the Count column to create the Pareto chart, but it is needed to calculate the other figures so we’re including it in this example. (Click any image below for a larger view.)
After the data has been entered, select the columns containing the information related to the Problem, Percent of Total, and Cumulative Percent. If your data is in non-adjacent columns, like in our example here, hold down the Ctrl key in order to select multiple items at once.
With the data still selected, click on the Insert tab of Excel’s main toolbar. From the Charts group on this tab, select Column and then pick the first entry (Clustered Column) of the 2-D Column choices.
At this point, your chart should look like the one below. It doesn’t quite look like a Pareto chart yet, but don’t worry – we still have some changes to make!
Next, right-click on one of the bars representing the Cumulative Percent data. From the options menu that appears, choose Change Series Chart Type.
This option will let us represent the Cumulative Percent data as a line while leaving the Percent of Total as a bar representation. When the Change Chart Type window appears (see screenshot below), choose the first option in the Line category.
Now, things are starting to look a little better.
Continue on to the next page for the remainder of this tutorial on how to create a Pareto chart in Excel.