WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Pareto Chart in Excel

Pareto Chart in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

How to Create a Pareto Chart in Excel? (Step by Step)

You can download this Pareto Chart in Excel Template here – Pareto Chart in Excel Template
  • Step #1 – Collect the Raw Data including the Category (cause of a problem) and their Count

PARETO Chart Example - 1-1

  • Step #2 – Calculate the percentage of each category and further compute the cumulative percent

The percent will be calculated using the formula =(C3/$C$13) *100, applying throughout the other cells.

PARETO Chart Example - 1-2

Cumulative Percentage

It is the method of calculating the frequency distribution and will be calculated successively by adding the percent with other frequencies. So, the formula will be =D6+C7. After sorting the values from largest to smallest, we calculate the cumulative percentage for each of the categories.

PARETO Chart Example - 1-3

  • Step #3 – Select category, count, and cumulative percent Range together as shown below

PARETO Chart Example - 1-4

Go to the Insert tab in Excel and select a 2-D column bar graph.

PARETO Chart Example - 1-5

Now the Pareto chart created is shown below:

PARETO Chart Example - 1-6

  • Step #4 – Select the cumulative percent bars and change the series chart type to Line

The red bars are the cumulative percentage bars, select any one of the bars and change the series, select Line from the change chart type.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

PARETO Chart Example - 1-7

Now the Pareto chart will look like shown below:

PARETO Chart Example - 1-8

  • Step #5 – Right-click on the cumulative total line (in red) and choose format data series.

PARETO Chart Example - 1-9

  • and select the secondary axis in excel

PARETO Chart Example - 1-10

Select Secondary axis and close the Format Data Series window

Now the Pareto chart will look like as shown below

PARETO Chart Example - 1-11

  • Step #6 – Click on the right-hand axis and select format axis,

PARETO Chart Example - 1-12

then under the axis option tab, select maximum to set it to be fixed, and set the value to 100

PARETO Chart Example - 1-14

In Axis Options, select the Maximum from Auto to fixed and enter a value 100 manually and close the format axis window

Finally, the Pareto chart will look like

PARETO Chart Example - 1-15

PARETO Chart Example - 1-15

The chart above shows that 80% of the effects come from 20% of the causes.

Advantages

  • The Pareto chart highlights the major cause of the problem that hampers a process
  • It helps to rectify the major problems and thus increases organizational efficiency. Once the big hitters in a process are discovered using this technique, one can move ahead for the resolutions, thus increasing the efficiency of the organization
  • It also enhances problem-solving skills as it enables you to sort out business-related issues into strong facts. Once you’ve vividly laid out these facts, you can start the planning important to take care of the issues.
  • It improves decision making in a process.
  • It helps the organizational team to focus on the input that will have a greater impact in accordance with the 80/20 rule.

Disadvantages

  • The Pareto chart doesn’t provide any insight into the root cause of the problem.
  • A single cause or a reason category may further have other factors involved, so to find the major impact at each level of the problem, we have to create many Pareto charts. So, lower levels of the Pareto chart are often required.
  • Pareto chart is based on the frequency distribution; hence it cannot be used to calculate the mean, standard deviation, and other statistical values may often require.
  • Pareto chart can’t be utilized to compute how awful the issue is or how far changes would bring a procedure back into specification.

Things to Remember

  • Before creating a Pareto chart, it is necessary to categorize the issues, and it is considered to be a good practice to keep the categories less than 10 in numbers.
  • It is based on the past data, so for continuous improvement of a process, it is necessary to revamp the data on a periodic basis because the Pareto analysis is based on the historical data and doesn’t provide the forecast analysis.
  • Always create the secondary y-axis with percentage descending in increments from 10 to 100.
  • It helps in providing an easier way to make a distinction before and after Pareto analysis to verify the process changes had the desired outcome.
  • We can create multilevel Pareto charts for each issue and can further perform another Pareto analysis on the sub-level issues and so on.

Pareto Chart in Excel Video

Recommended Articles

This has been a guide to Pareto Chart in Excel. Here we discuss its uses and how to make Pareto Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Types of Charts in Excel
  • Create Area Chart in Excel
  • How to Create Pie Chart in Excel?
  • Histogram Chart in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Pareto Chart in Excel Template

Coursera IPO Financial Model & Valuation Free Download