How To Create Pareto In Excel 2007

Preparing to Get Started

A Pareto chart is a tool commonly used in Six Sigma and other project management methodologies to illustrate the root problems or causes of a situation. Although Pareto charts can be easily graphed in several different project management applications, not everyone has access to software of this type. Fortunately, these analysis tools can also be created in Microsoft Excel, and we'll explain how in this step-by-step guide.

We'll be using Excel 2010 to make our chart, but the same steps work in Excel 2007. You can also use this same technique to create a Pareto chart in older versions of Excel—the tools are just in different locations. Excel 2007 and 2010 both have the "new" Microsoft Office ribbon, while previous versions of the software still use toolbar menus.

Initial Table of Data

Note: Before getting started, you may also want to take a look at Performing a Pareto Analysis in Excel to get a better idea about how a Pareto chart is used.

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 above. 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. Plus, some people may choose to display the Count instead of the Percent of Total on the chart.

Another Optional Column: In the screenshot, the last column is labeled Horizontal Line Value and 80.00 has been entered for every value. This information will be used to graph a horizontal line at the 80% cut-off mark on the final chart in our example—but it really is completely optional.

First Steps in Chart Creation

After the data has been entered into the table, select the columns containing the information related to the Problem, Percent of Total, Cumulative Percent and Horizontal Line Value (optional). 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, as shown in the image to the right.

First Chart

This initial graph should look like the one shown to the left. It may not look much like a Pareto chart yet, but don't worry! We still have a few adjustments to make to get our formatting right.

Before going on to the next step, you may want to take a few seconds to adjust the size and placement of the chart in the worksheet. While this isn't completely necessary, it may make the components easier to see and work with in later steps. Plus, you can always resize again at the end of the process if you like.

Make It Look Like a Pareto Chart

Change Chart Type

The problem with our initial chart above is that everything is graphed as a column, but we want the Cumulative Percent and Horizontal Line Value series to show up as line graphs. So, the next thing we're going to do is fix that real quick.

Right-click on one of the bars representing the Cumulative Percent data. From the options menu that appears, choose Change Series Chart Type. See the figure to the right.

Select Line Chart

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 left screenshot), choose the first option in the Line category.

After clicking OK, do the same thing for the Horizontal Line Value data, if you chose to include that information in your graph.

Now, things are starting to look a little better. The large image above shows that our graph is finally starting to take on the appearance of a Pareto chart.

Adding the Final Touches

The Pareto chart we have now is functional, but it still could use a bit of "prettying up." Here are a few additional things we can do—each of these modifications is optional, so just pick the ones that suit you.

Modifying or Getting Rid of the Legend

To change the position of the legend—or to get rid of it altogether—make sure the chart is selected and then click on the Layout tab in the Chart Tools grouping. Click Legend from the Labels grouping, and choose the style you like. For this case, I am going to pick None, because I think the chart is pretty self-explanatory without a legend—and I personally prefer graphs with as little "clutter" as possible.

In the next few slides, we'll discuss some other modifications—including how to add a dual axis—and describe how to make the Pareto chart look more appealing.

Options for Modifying the Pareto Chart

Adjusting the Vertical Axis (y-Axis) Values

By default, Excel chose to extend the values on the vertical axis from 0 to 120. However, our values here are never going to be over 100%, so we can manually change these default values to make a little more sense.

To do this, right-click over the y-axis and select Format Axis (see first image below). In the pop-up window that appears, select the Fixed option for both Minimum and Maximum, entering 0 for Minimum and 100 for Maximum. Click Close when done, and the graph will be updated.

Now, your Pareto chart should look like the large image shown above.

Note: If you want to use a dual axis so that the cumulative percentage and horizontal line graphs don't skew the look of your graph, go forward to the slide titled Update: Adding a Secondary Axis for Cumulative Percentage.

Move the Columns Closer Together

Many people also like to adjust the gaps between the columns in the Pareto chart—some even prefer to remove the gaps completely so that all columns "touch."

If you'd like to make this adjustment, right-click on any of the columns and select Format Data Series (see first screenshot below). A new window will appear, allowing you to adjust the Series Overlap and Gap Width values. One nice thing—any changes you make here will show on the chart as you make them, so you can play around a little with the sliders to get a result that looks nice to you. When done, just click Close to return to your chart.

Format Data Series

Adjust Gap and Overlap

After Adjusting Gaps

Add Title, Colors and More

Now, we have a Pareto chart that gives a good representation of our data. All that's left to do is add a title and play around with the various chart formatting options in Excel to spruce up the overall design. This is actually my favorite part of the whole process, since Excel 2010 has a lot of great design tools. Use the options on the Design, Layout and Format tabs of the special Chart Tools grouping to try out a few different looks.

If you'd like to download the data table and Pareto chart created in this tutorial, you can do so at this link: Excel 2010 Pareto Chart.

Want to make the Pareto chart look even better? The next couple of slides will show how to add a secondary axis to the chart.

Update: Adding a Secondary Axis for Cumulative Percentage

A few people have asked about adding a secondary axis for the cumulative percentage graph so that the entire chart doesn't have to be scaled to 100 percent. Here are the steps for that, starting with the already formatted chart shown in the previous section.

Format Data Series for Cumulative Percentage

Choose Secondary Axis

Step 1:** First, right click the cumulative percentage curve and select **Format Data Series**. This will call up the Format Data Series window. On the **Series Options** tab, click on the radio button next to **Secondary Axis**.

Click Close to apply the changes and return to your chart.

Note: If you added the Horizonal Line to your Pareto chart, do the same thing for that graph so that it will also be associated with the secondary axis.

Formatting the Secondary Axis

Format Secondary Axis

** Fix Maximum Value

Step 2:** Now, right click on the secondary axis and select **Format Axis** (see screenshot to the left). In the **Axis Options** tab of the Format Axis window, change the Minimum and Maximum to **Fixed** (as opposed to Auto). Enter 0 for Minimum and 100 for Maximum.

Click Close to return to exit this window and return to your main Excel spreadsheet.

Step 3: Similarly, right click on the primary axis and adjust the Maximum value based on the Percent of Total graph. This will vary, depending on your data. For instance, the largest individual percentage in the example I am using is 30.67 percent, so I am going to change the Maximum to 45. You can play around with this option to get the look you like.

Pareto Chart With Two Axes

The image to the right shows the new Pareto chart with both a primary and a seconday axis. If you'd like to label these two axes (or apply any other formatting), make sure the entire chart is selected and go to the Layout tab in the Chart Tools grouping. Here, you can label one or both axes—you can also label the horizontal axis if you think doing so would add value to your presentation.

Additional Resources: There are several other Excel project management templates available in the Media Gallery, including this collection of Six Sigma layouts. You can also find step by step instructions for creating a Gantt chart in Excel here on the Project Management Channel. Feel free to download any of these materials and modify them to fit your own project needs.

If you have any other questions or suggestions, please leave a note in the comments!

If you're looking for more sample forms and downloadable templates, check out Bright Hub's resource guide: Over 50 Free Project Management Templates and Sample Forms.

References

Microsoft Excel Official Site, http://office.microsoft.com/en-us/excel/

All screenshots taken by author.

This post is part of the series: Pareto Charts and Analysis

Looking for tips on how to perform a Pareto analysis or how to create a Pareto chart in Excel? Check out some of the other articles in our Pareto series.

When Do You Need to Use a Pareto Chart?
Using Excel for Pareto Analysis
Creating Pareto Charts With Microsoft Excel
Using Pareto Analysis on Surveys
Problem Solving for Projects with Pareto Analysis