What is a Pareto Chart
How Can I Create Pareto Charts in Excel?
Pareto charts combine a sorted bar graph with a cumulative line graph. Often, two or three "big bars" represent most of the problem (80/20 rule).
I really like the QI Macros. The Pareto chart alone is worth the purchase price.
- Mike Dean, Ellwood Texas Forge
Creating a Pareto Diagram in Excel Requires Some Wizardry
If you've ever tried to create a Pareto chart in Excel, you know how complex and time consuming it can be. Here are the steps:
- Sort the data in descending order,
- Calculate the cumulative percentage,
- Create a column chart of the sorted data
- Create a line graph of the cumulative percentage and put it on a second Y Axis.
- Adjust the scale of both axes;
- Change the color of the bars.
This can take quite awhile, especially if you don't do it very often. When you are done you will have a "Pareto" that looks like the one to the right.
Excel's plot area is gray; the bars have spaces between them; the cumulative line starts in the middle of the first bar instead of bottom left corner. And you can't read the cumulative percentages off of the line.
Why Struggle? QI Macros Can Create a Pareto Chart in 3 Easy Steps
- Point - select your data in an Excel worksheet
- Click – on the QI Macros menu and choose Pareto
- Analyze and Improve –the QI Macros will do the math and draw the diagram for you. Any bar that represents more than 20% of the total will be shaded.
If your data looks like the table below (a single column of text, dates or numbers) just make sure the column has a heading. Then just click on the heading (cell C1) and select Pareto from the QI Macros Menu:
QI Macros will count the occurrence of each phrase and draw a Pareto chart of the result in one step.
QI Macros Pareto Diagram Features
Notice how the bars touch; bars over 20% are highlighted with color; axes are scaled correctly; cumulative percentages are shown on the line graph.
QI Macros is Compatible with Excel 2003, 2007, 2010, 2013, 2016, 2011/2016 (Mac)
I have purchased your QI Macros and now amaze my friends and co-workers with the ease and speed of my Pareto charts and other stats.
— Bob Miller, Training Manager, SETECH, Inc