Learn how to create a Pareto Chart in Excel.
Step 1: Data Preparation
- Open Excel and input your data. Let’s say you have two columns: one for categories (e.g., types of defects) and another for their respective frequencies (e.g., number of occurrences).
- Ensure your data is sorted in descending order based on frequency.
Step 2: Calculate Cumulative Percentages
- In a new column, calculate the cumulative percentage for each category. To do this, divide the frequency of each category by the total frequency and then multiply by 100.
- In the cell adjacent to your first data point, input a formula like
=B2/SUM(B:B), assuming your frequency data is in column B. Then drag this formula down to apply to all data points. - Format this column as percentage.
Step 3: Create a Pareto Chart
- Select both the categories and the cumulative percentage column.
- Go to the “Insert” tab on the Excel ribbon.
- Click on “Insert Statistic Chart,” then choose “Histogram,” and select the option for “Pareto.”
- Excel will generate a chart combining a column chart with a line chart. The columns represent the frequency of each category, while the line represents the cumulative percentage.
Step 4: Customize the Chart
- Right-click on the secondary axis (the one representing cumulative percentage) and choose “Format Axis.”
- Under the “Axis Options,” change the maximum bound to 100%.
- Format your chart as desired, including titles, axes labels, and gridlines, to make it more presentable and understandable.
Step 5: Interpret the Pareto Chart
- Analyze the chart. The bars on the chart should be in descending order of frequency, and the cumulative percentage line should show the cumulative contribution of each category to the total.
- Identify the point where the cumulative percentage line starts to bend sharply. This point indicates the “vital few” categories that contribute the most to the total, according to the Pareto Principle.
