A Pareto Chart can be used to communicate the most important factors out of a larger group of factors. It does this by ranking the data points from largest to smallest. More information on Pareto Charts can be found here.
As can be seen, the columns diminish in height from left to right, and there is a cumulative series plotted against the secondary axis. From this chart you can see that items F, E, and A account for nearly 70% of all items. This can be useful if decisions are to be made about the priority of addressing problems, or for showing the relative importance of sales in different markets.
Another variation of this chart is to present it in “Waterfall” style (see also Waterfall Charts).
Creating a Pareto chart in QuickChart is simple. Just create a table of the factors in any order (as shown below), and click on the Pareto button on the Excel ribbon toolbar.
The factors will automatically be re-ordered and the cumulative series added.
The single-click Pareto chart is not “live”, however, because the actual data being charted is plotted has been re-ordered and an additional series created. This means that it won’t update with changing data. However, to achieve this you can use the “=QC.PARETO(…)” formula.
The array formula “=QC.PARETO(…)” will take the same input data and create an output with all of the required series to make the Pareto chart. The output height of the array formula will be the same height as the input data but with an extra column.
There are four parameters for the formula:
- The list of names for each factor
- The values to be plotted
- Is the cumulative series to be a total rather than a percentage? (TRUE/FALSE)
- Is the chart to be a standard Pareto Chart rather than a Waterfall (TRUE/FALSE)
Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.
The Settings dialog box is accessed through the Pareto drop-down menu on the Excel tibbon toolbar.
There are just two options in the chart settings.
- Standard or Waterfall Pareto Chart
- Show the Cumulative series as a percentage or as a total of absolute values
If you are using the “=QC.PARETO(…) Formula, you should ensure that these settings are the same as the relevant parameters in the formula in order to get the output that you expect.