Close this search box.

Frequency Pareto

The Frequency Pareto diagram contains how frequently a category (e.g., problem) occurred in a data set. The diagram can be easily updated with new data. This page shows you how to make a frequency Pareto diagram. The data can be downloaded at this link. This page contains the following:

Data Entry

Data are entered into a worksheet and can be in columns or in rows based on how the categories are entered. Consider the case where you are tracking the number of returns by product. In this case, product represents the categories and returns represents the frequencies (the numeric data). You can enter the data in columns (categories are listed in one column) or in rows (categories listed in one row). The frequencies can be summed already or the program will sum the frequencies for each category. For example, you might be tracking the product returns by product by week. It is recommended that you enter 0 when there is no frequency for a category because this helps ensures the Pareto diagram can be updated correctly with new data (see Updating the Frequency Pareto diagram below).

The figure below shows the various options for entering the data into a worksheet. The data does not have to start in A1. It can be anywhere on the spreadsheet. The categories range does not have to be adjacent to the frequencies range.

Creating a New Frequency Pareto Diagram

The following steps show how to create a new frequency Pareto diagram. The data in the upper left-hand corner of the figure above will be used.

1.Select the categories and frequencies range on the worksheet. For example, if the data starts in cell A10, select cells A10 to B15.

2. Select “Frequency” on the “Pareto” panel in the SPC for Excel ribbon

The input screen for the Frequency Pareto diagram is shown. Note that the categories and frequencies are already filled in based on the ranges selected on the worksheet. These can be changed at this point if the ranges are not correct. The only item required to create the Pareto diagram at this point is to enter the name of diagram. This is name that will be on the worksheet tab containing the Pareto diagram. In this example, the name “Product Returns” is used as the name of the diagram.

3. Once you have entered the information, select OK and the Pareto diagram will be generated on a new diagram sheet. An example based on the data above is shown below. Note that it is on the worksheet tab “Product Returns”.

Options for the Frequency Pareto Diagram

The input screen above had the “Add Titles/Dates/Category Axis” option button. If you select that button, you will get the input screen below.

The input screen above also had the “Vital Few Color/Bar Gap” option button. If you select this option, you will get the input screen below.

Updating a Frequency Pareto diagram with New Data

After you have added additional data to the worksheet, you can easily update the Pareto diagram as shown below. When a Pareto diagram is updated, the formatting on the diagram, the diagram title and the axis labels are maintained.

How SPC for Excel Finds New Data to Update the Frequency Pareto Diagram

The program looks for new data depending on how the original data was entered.

Categories in one column, frequencies summed option

You have added new data (in blue) to the original data as shown below. Products F and G have been added. The program searches from the first data cell (cell A11 in this case) down the column to find the last cell with an entry before an empty cell. This is the range that is used to update the diagram. Note that the original data for returns for products A – E could have been updated as well. The program will use whatever is now in the range from A11 to B17.

Categories in one column, frequencies not summed option

You have added new data (in blue) to the original data as shown below. Week 5 is new data for existing product and products F and G have been added. The program searches two directions for new data. First it searches from cell A23 down to find the last product entry before a empty cell. Then it searches from cell B23 across the row to find the last entry (week 5) before a empty cell. This is why it is recommended that you put 0 in for categories that have no frequency. The new range for the Pareto diagram is contained from A23 to F29.

The program handles the categories in one row in a similar fashion.

Change the Options for a Frequency Pareto Diagram

To change the options for a Pareto diagram (e.g., to change if a cumulative line is added), do the following:

Note: When you change the options, the program does not add any new data to the diagram. You have to run “Update diagrams” to add new data.

Scroll to Top