Pareto Chart In Excel – Step-by-Step Tutorials
Follow this Excel Pareto Chart tutorial to turn data into visual insights: a Pareto Chart graph shows the significant elements in a data set to determine relative importance. From there, you can prioritize and decide accordingly.
In a hurry? Download Excel Pareto Chart Templates here.
“A good business strategy is an operational execution driven by a Pareto Analysis”
Table of Contents
- 1 Pareto Chart In Excel – Step-by-Step Tutorials
- 1.1 How to create a Pareto Chart in Office 365, Excel 2016, or Excel 2019 in 2 steps?
- 1.2 How to create a Pareto Chart in Excel 365?
- 1.3 How to create a Pareto Chart in Excel 2013?
- 1.4 How to create a Pareto Chart in Excel 2010?
- 1.5 How to draw a Target Line in an Excel Pareto Chart?
- 2 Pareto Chart in Excel – Free Templates for Download
- 3 Pareto Chart In Excel – Overview
- 4 Pareto Chart In Excel – Tutorial Conclusion
How to create a Pareto Chart in Office 365, Excel 2016, or Excel 2019 in 2 steps?
STEP 1 – Select your data input
One column for the “causes” and one for their “impacts.” There is no need for the data to be sorted.
STEP 2 – Select Pareto Chart in the ribbon
Insert > Insert Statistical Chart > Pareto. The Pareto Chart you get is then ready to be customized!
That’s it! Congratulations!
Pareto Chart In Excel In 2 Steps: Video Tutorial
OPTIONAL STEP: Customize your Pareto Chart in Excel
There are two ways to customize your Pareto Chart in Excel: via the chart design menu in the ribbon or the right-click menus when the cursor is in the chart
1 – In the ribbon, several design options are available under the Chart Design menu:
2 – In the Pareto Chart, the menus you get by right-clicking provide you as well with different design options:
How to create a Pareto Chart in Excel 2013?
To create a Pareto Chart in Excel 2013, follow the tutorial below:
How to create a Pareto Chart in Excel 2010?
To create a Pareto Chart in Excel 2010, follow the tutorial below:
How to draw a Target Line in an Excel Pareto Chart?
The fastest way to build an Excel Pareto Chart with a target line is simply using the free template below. Alternatively, you can follow this YouTube tutorial to get an Excel Pareto Chart with a target line.
Pareto Chart in Excel – Free Templates for Download
Download the free template used as an Excel Pareto Chart example in this tutorial:
(Pro: no need to sort the data input by descending effect – Con: no target line)
(Pro: target line – Con: need to sort the data input by descending effect)
Pareto Analysis Table combined with a Pareto Chart – Template from the Microsoft Office website:
Pareto Chart In Excel – Overview
Why build a Pareto Chart in Excel?
A Pareto Chart or Pareto Diagram helps turn your data into clear visualizable actionable insights. It highlights what matters by emphasizing a phenomenon’s most critical drivers. A Pareto Chart is an excellent tool to illustrate and justify a shortlist of the essential tasks in a project management context. It can be combined with other project prioritization methods, such as Eisenhower’s Urgent-Important Matrix.
A Pareto Distribution is observed when the Pareto Principle holds. The Pareto Principle (or “20-80 rule” or “Law of the Vital Few”) states that, for many events, roughly 80% of the effects come from 20% of the causes. In short, a Pareto Chart is a root cause identifier chart that may illustrate a Pareto distribution within your data.
Example of Pareto Chart In Excel
As an example, the Pareto Diagram or Pareto Chart above shows that 80% of the customer complaints ensue from only 20% of the products.
To help identify the most important causes (“Vital Few”) from a mass of insignificant data points (“Trivial Many”), you might create a cut-off line on your Pareto chart: this line is the ‘Target Line.” The target line is 80% when assuming a perfect Pareto Distribution. In real life, the target value is adjusted to make a relevant separation between the most important causes (“Vital Few”) from the least important ones (“Trivial Many”) as the frontier between the two can be different to 80%.
Example of an Excel Pareto Chart with a target line
How To Choose Between A Pareto Chart (Diagram) And A Pareto Analysis (Table)?
If the data set underlying your Pareto Analysis comprises limited “causes or drivers” to analyze, a Pareto Chart (diagram) is undoubtedly the way to go.
On the contrary, if the data set has many “causes or drivers,” then crunching the data with a direct Pareto Analysis (table) in a sheet is more appropriate. Indeed, a Pareto Chart with too many data points will be scattered and not yield clear, visualizable, and actionable insights.
A Pareto Analysis can be done manually, as explained in this Pareto Analysis Excel tutorial. You can use an Excel Pareto Analysis Add-In to automate a Pareto Analysis in just one click, as shown in this website’s DEMO section. An Excel Add-in is particularly useful for Excel users who frequently have to do Pareto Analysis in Excel. The Excel Pareto Analysis Add-In also helps to save time when the data set to analyze is defined with active filters in Excel. These filters can stay in place when using the Add-in, avoiding additional time-consuming data preparation steps such as building a temporary data set from the relevant subset of data, etc.
“The great thing in life is efficiency. If you amount to anything in the world, your time is valuable, your energy precious. They are your success capital, and you cannot afford to heedlessly throw them away or trifle with them.” ― Orison Swett Marden
Pareto Chart In Excel – Tutorial Conclusion
With Microsoft Excel 2016 and more recent versions, building a Pareto Chart takes only two steps. Since it is swift and easy, we advise you always to look at the data you work with by making a Pareto Chart. You may find insightful results that could lead to better time or budget management in your business operations. Please refer to this tutorial here to make a Pareto Chart with Google Sheets. For more details about the Pareto Principle, please refer to the FAQ by clicking here.