Create a Pareto Chart in Excel for Business Analysis

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 291
Joined: Tue Sep 25, 2007 12:41 pm

Create a Pareto Chart in Excel for Business Analysis

Post by Admin » Wed Feb 01, 2017 11:28 am

Create a Pareto Chart in Excel for Business Analysis or Quality Control:


Pareto chart, named after the Italian economist Vilfredo Pareto, who noticed that 80% of Italy's land was owned by 20% of the population while at the University of Lausanne in 1896 . Hence comes the 80/20 rule.


So it can happen that, 80% of the unrest or problems you are facing in your factory or business comes from 20% of the workers or from 20% of the issues.

80% failure of the output or productivity is occurring from 20% of the issues.

Or something positve: 80% of your company's profit comes from 20% of customers.

80% of your company's revenue comes from 20% of its products.

So now you know how important is the Pareto Chart for quality control or for manegerial purposes.



To create the Pareto Chart, the first thing you need to do: arrange your data in descending order.

In the example, I have arranged the revenue data in descending order in Column B. Now I will calculate cumulative percentage in Column C. May be you will need to format Column C to have the percentage format (click on the heading of Column C to select the whole column > right mouse click > click on Format Cells... > from Category choose Percentage, leave decimal places up to 2 > click OK).


Image


Now in cell C3 enter

=SUM($B$3:B3)/SUM($B$3:$B$8 )


Copy this down the column. Now you have the cumulative percentage.


Image


So my data is ready now to start plotting the Pareto Chart.



Create the Chart:

Select your data (A2:C8).

Go to Insert > Charts > here from 2-D Column select the first one (Clustered Column)


Image



Now right click on the cumulative (%) bar from the legend > select Change Series Chart Type...



Image


Change Chart Type window opens > from Line group select the first one > click OK.


Image



The cumulative (%) bar is now changed to line > right click on it > click on Format Data Series...

The Format Data Series window opens. Here from Series Options click on Secondary Axis > click Close.


The Pareto Chart is ready so far. Now do the final polishing – like adjusting the values for the vertical axes (click on any of the axes > right mouse click > click on Format Axis.... > adjust the maximum and major unit values etc. as per your preference), giving a title to your chart (you can do the title from Chart Layouts that you will get under Design tab > select any layout of your choice :-))


Image


Good work ahead guys :-)



Read on Wikipedia: Pareto Principle







*Recommended:

Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.


For comments of suggestions, please contact us: info@shamskm.com

ChristinaKMunos
Posts: 1
Joined: Sat Aug 03, 2019 2:41 pm

Re: Create a Pareto Chart in Excel for Business Analysis

Post by ChristinaKMunos » Sat Aug 03, 2019 2:54 pm

Can you give me some business related tips because I have recently opened a business of resume writing and I want some about my website https://www.resumesservicesreview.com/ also because I don't know how to run a business and a website. Please help me with this. Thanks.

Post Reply