Creating A Pareto Chart In Excel

Step by Step Guide to Creating a Pareto Chart in Excel

A Pareto Chart is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total is represented by the line. It's a useful tool for identifying your most significant problems so you can focus on their solutions. Here's a step-by-step guide to creating a Pareto Chart in Excel:

Step 1: Prepare Your Data
First, you need to have two columns of data. The first column should contain the different categories or causes, and the second column should contain their corresponding frequencies or counts.

Step 2: Sort Your Data
Next, sort your data in descending order based on the frequencies or counts. This will make the largest category appear on the left side of your chart.

Step 3: Calculate Cumulative Percentages
In a third column, calculate the cumulative percentage of the frequencies. You can do this by dividing the cumulative frequency by the total frequency, and then multiplying by 100.

Step 4: Create the Chart
Highlight all your data (including headers), go to the 'Insert' tab, and select '2D Column' from the 'Charts' group. This will insert a basic bar chart into your spreadsheet.

Step 5: Add a Secondary Axis
Click on one of the bars (this should highlight all of them), right-click and choose 'Format Data Series'. In the pane that appears, under 'Series Options', select 'Secondary Axis'. This will add a secondary vertical axis on the right side of the chart.

Step 6: Change Chart Type
With the bars still selected, go to the 'Design' tab, click on 'Change Chart Type', and select 'Line'. This will change the bars to a line, representing the cumulative percentages.

Step 7: Format Your Chart
Finally, you can format your chart to make it more readable. You can add a title, label the axes, adjust the scale of the secondary axis, and add a legend.

And there you have it! You've created a Pareto Chart in Excel. This chart will help you identify the most significant factors in your dataset, allowing you to focus your efforts on areas where you can make the most impact.