Pareto charts are used to identify the issues to focus on first in order to make improvements. This article walks readers through a few simple steps on how to create a Pareto chart with a cumulative percentage in Excel. Let’s follow the steps carefully to create a perfect Pareto chart.
Pareto Chart with Cumulative Percentage in Excel: 3 Suitable Ways to Create
We have covered 3 methods of creating a Pareto chart with a cumulative percentage for different versions of Excel. We have applied The SUM function here to compute the cumulative percentage. Later, some formatting is done to make the chart ideal.
Method 1: Using Excel 2016-365 Versions to Create a Pareto Chart with Cumulative Percentage in Excel
Suppose we have some information regarding the complaints of the patients of a hospital. The number of complaints about an issue is considered as frequency.
Steps:
- First, select any cell of the data table.
- Then, click on Insert.
- Later, select the Recommended Charts.
- Now, after selecting the Recommended Charts option, a new dialog box will show up.
- Press on the All-Charts option which is marked here.
- Afterwards, select Histogram from Recommended Charts.
- Next, select the Pareto option from the Histogram which is marked in the picture.
- Finally, hit the OK.
- Accordingly, after selecting the OK option, a Pareto chart with Cumulative Percentage is created automatically with Excel 2016-365 Versions.
Read More: How to Create a Stacked Pareto Chart in Excel
Method 2: Applying Excel 2013 Version to Create a Pareto Chart with Cumulative Percentage
We have the same dataset as the previous method. Now, we need to determine the cumulative percentage first.
Step-01: Calculating Cumulative Percentage
To calculate the cumulative percentage, we will apply the SUM function.
- First, enter the Formula in Cell D5. This formula will quickly compute the cumulative percentage.
=SUM($C$5:C5)/SUM($C$5:$C$13)
- Next, click on ENTER and you can see the result in cell D5.
- One thing to be noticed here is that the data type of the Cumulative Percentage column is Percentage.
- In conclusion, copy the formula down to cell D13 by using the AutoFill.
- As a result, we will get the Cumulative Percentage of the whole column.
Step-02: Drawing Pareto Chart
In this step, we will insert Combo Chart so that we can format this later to a Pareto Chart.
- Initially, select any cell of the table. We have selected C5.
- Secondly, click on the Insert.
- Press on the Recommended Charts from options.
- Consequently, a dialog box will pop up and from that Select All Charts.
- Now, click on the Combo option from Recommended Charts.
- At the bottom Cumulative Percentage should be in Line Chart Type.
- Finally, put a tick on the blank marked box. Which defines that Cumulative Percentage will be the Secondary Axis.
- Now, press the OK button to create the chart.
- Therefore, we got the Pareto Chart.
Step-03: Formatting Axis
To look polished, this chart needs some formatting. Firstly, let’s format the Axis.
- In the beginning, right-click on the secondary axis marked in the picture.
- Options will appear like this.
- Then, select Format Axis.
- In the Axis Options, you will see that Bounds Maximum value is 2 by default.
- 2 means it will show up to 120%.
- So, reset 2 to 1.0 as we want the cumulative percentage up to 100%.
- Consecutively, we have formatted the Axis and it looks like the image below.
Step-04: Formatting Bar Width
Secondly, we will format the bar width to make the chart look better.
- To begin, right-click on the Column bar marked below.
- After getting this box, select Format Data Point.
- The Gap Width is set 219% by default. You have to change it to 5%.
- Reset the Width to 5%.
- At the end of this, the Pareto Chart will look like this.
- In conclusion, by following these 6 steps, we have created a Pareto Chart with cumulative percentages in Excel 2013.
Method 3: Creating Excel 2010 Version to Create a Pareto Chart with Cumulative Percentage
Imagine, we have a dataset of complaints regarding the medical services of a hospital. We need to calculate the cumulative percentage by applying formulas.
Step-01: Preparing Data Set with Cumulative Percentage
As we will insert a chart so let’s prepare the dataset now.
- First and foremost, enter the formula in cell D5.
=SUM($C$5:C5)/SUM($C$5:$C$13)
- Next, hit the ENTER button and you will get the result.
- With the help of AutoFill, duplicate the formula down to cell D13.
- Therefore, we computed the cumulative percentage for all the cells.
Step-02: Inserting Columns
Here, we will insert 2-D column and later we can convert it to Pareto Chart by Formatting.
- Initially, select any cell of the data table. We have selected cell C5.
- Then, click on Insert.
- Later, choose the column icon from the tab which is pointed in the image.
- Among the options pick a 2-D Column.
- From the 2-D column choose the first one.
- The chart will appear like the snapshot shown below.
Step-03: Changing Series Chart Type
The chart requires some formatting, which will be covered in this step.
- In the first place, select the cumulative percentage column.
- In case, your cumulative percentage bar is too small to hover the mouse over, widen the chart by dragging the corner borders.
- Right-click on the column and this box will be displayed. Choose Change Chart Type from the options.
- Subsequently, this new window will pop up. Click on the Combo option from the Recommended Charts.
- Now, put a tick mark beside the Cumulative Percentage. This means the secondary axis will be the cumulative percentage.
- Finally, hit OK.
- Correspondingly, Cumulative Percentage has converted into line.
Step-04: Formatting Axis
Now, we need to Format the Axis to set the Maximum Bound value to 1.0.
- Initially, choose the Secondary Axis pointed in the image.
- Afterwards, right-click on this Axis.
- Later, from the options select Format Axis.
- In Axis Options, under Bounds, Maximum value is 1.2 which means 120% is the maximum value for the vertical Axis.
- As 1.2 is not okay for Pareto Chart, change this to 1.0 which denotes the highest value to be 100%.
- The chart will appear as the following one.
Step-05: Formatting Data Point to decrease column width
We will decrease the Column Width to make it look like an ideal Pareto Chart.
- To start, select any Column.
- Then, right-click on the column and choose Format Data Point from the box.
- In Primary Axis, Gap Width is showing 219% which is too much.
- Therefore, swap this with 5%.
- Thus, decreasing the gap width, and your Pareto chart is ready.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
In this article, we have demonstrated how to make a Pareto chart with a cumulative percentage steps-by-steps. By following the steps, you can make a Pareto chart according to your preference very quickly. In case you face any problem regarding the article, please leave a comment here. We will try to solve the problem.
