Mahfuza Anika Era
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.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
3 Suitable Ways to Create Pareto Chart with Cumulative Percentage in Excel
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 (2 Smart Ways)
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.
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.
