Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Create a Pareto Chart in Excel for Business Analysis

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 268

PostPosted: Wed Feb 01, 2017 11:28 am    Post subject: Create a Pareto Chart in Excel for Business Analysis Reply with quote

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).





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.





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)






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






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






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 Smile)





Good work ahead guys Smile



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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group