BLOG – PRODUCTIVITY TOOLS AND MORE…

Create a Pareto Chart in Excel in 2 steps – Easy Tutorial

Mar 27, 2022 | Microsoft Excel Tutorial

Pareto Chart In Excel – Step-by-Step Tutorials

 

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.

Create an Excel Pareto Chart - Select Data - 1 column for "causes" + 1 column for "impacts"

STEP 2 – Select Pareto Chart in the ribbon

Insert > Insert Statistical Chart > Pareto. The Pareto Chart you get is then ready to be customized!

Insert - Statistical Chart - Pareto Chart
Pareto Chart in Excel - Default Excel Pareto Chart

That’s it! Congratulations! 

Pareto Chart In Excel In 2 Steps: Video Tutorial

OPTIONAL STEP: Customize your Pareto Chart in Excel

There are 2 ways to customize your Pareto Chart in Excel: chart design menu in the ribbon + right-click menus in the chart

1 – In the ribbon, several design options are available under the Chart Design menu:

Chart Design in the Excel ribbon

2 – In the Pareto Chart, the menus you get by right-clicking provide you as well with different design options:

Design options when right-clicking in the chart

How to create a Pareto Chart in Excel 365?

The 2-step Excel Pareto Chart Tutorial above works for Office 365, i.e., Excel 365. Alternatively, read more details on the Microsoft website here:

>> Pareto Chart in Office 365 <<

How to create a Pareto Chart in Excel 2013?

To create a Pareto Chart in Excel 2013, follow the tutorial below:

>> Pareto Chart  in Excel 2013 <<

How to create a Pareto Chart in Excel 2010?

To create a Pareto Chart in Excel 2010, follow the tutorial below:

>> Pareto Chart  in Excel 2010 <<

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:

  >> Download the Free Excel Pareto Chart Template <<

(Pro:  no need to sort the data input by descending effect – Con: no target line)



Download this free template to make an Excel Pareto Chart with a target line:

  >> Download the Free Excel Pareto Chart Template With A 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 Analysis with Pareto Chart – Microsoft Templates <<

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 really matters by emphasizing the most critical drivers of a phenomenon. 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 the existence of a Pareto distribution within your data.

Example of Pareto Chart In Excel

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.

Why Do People Draw A Target Line In Pareto Charts?

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 ‘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

Excel Pareto Chart Tutorial - 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. To automate a Pareto Analysis in just one-click like shown in the DEMO section of this website,  you can use an Excel Pareto Analysis Add-In. 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 hence 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, it takes only two steps to build a Pareto Chart. 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.  To make a Pareto Chart with Google Sheets, please refer to this tutorial here. For more details about the Pareto Principle, please refer to the FAQ by clicking here.

0 Comments

Trackbacks/Pingbacks

  1. Parkinson's Law - Why We work better with tight deadlines - 1 Explanation - […] tasks required. A Pareto Analysis is usually done via spreadsheets: Pareto Analysis in Excel, Pareto Chart in Excel, or…
  2. How to build a Pareto Chart in Google Sheets in 2 Easy Steps - […] Pareto Chart or Pareto Diagram is not a built-in chart type in Google Sheet unlike Excel. It remains easy…
  3. Best Microsoft Excel Guide Books – Top 3 for Data Analysis - […] Excel tutorials, articles and resources related to the Pareto Analysis and Pareto Chart topics, we encourage you to check…
  4. The Powerful Pareto Analysis or 20/80 Rule in Excel: FAQ - One-click Excel Pareto Analysis Add-In - […] Excel Pareto Chart […]

How to build a Pareto Chart in Google Sheets in 2 Easy Steps

This easy step-by-step tutorial shows how to build a Pareto Chart in Google Sheets. Download now the Free Google Sheets Pareto Chart template for immediate use.

Time Management: 1 Simple Eisenhower Matrix to Get it Right

The Eisenhower Matrix or Urgent-Important Matrix is a highly effective time management tool that uses 4 quadrants to prioritize tasks and increase productivity

Optimize Easily your Well-being with the 80/20 rule Approach

Happiness, the feeling of positivity, really is the foundation of productivity. - Miguel McKelvey Everyone deserves to have a happy and fulfilled life. Take control now of your well-being by applying the simple yet...

3 Tricks To Turn The Shiny Object Syndrome To Your Advantage

The shiny object syndrome is bad for productivity. Focus is key. Execution is everything. How do you stay focus and execute with all these new ideas in mind?

Why Your Best Time management Tool is Useless? 1 explanation

The Best Time Management Tools provide optimal task planning. That’s a great start, but that’s not enough. Learn here how to make your ambitions come true.

The Powerful Pareto Analysis Approach and 20/80 Rule: FAQ

All about the Pareto Principle, Pareto Analysis, 80/20 Rule, Pareto Law, and the Law of the Vital Few. Free Microsoft Excel & Google Sheets Tutorials & Templates

Work Smarter – Get Help Right Now From an Excel VBA Expert

No time to learn VBA for your Excel project? Get it done: Ask a Microsoft Excel VBA Expert. Live Excel Help, Online Excel Assistance, Overnight Project Delivery

Pareto Efficiency / Pareto Optimality: 1 Perfect Allocation

Pareto Efficiency / Pareto Optimality is the highest efficiency level resulting from an optimal allocation where any change to it would make something worse off

Murphy’s Law: 1 Gloomy View On How Things Usually Turn Out

Murphy’s Law is the adage that “Anything that can go wrong will go wrong.” People give Murphy’s Law relevance when things go awry as a reason to why it happened

Laborit’s Law / Law of the Least Effort: 1 clear explanation

Laborit’s Law or the Law of Least Effort suggests that humans prefer to carry out simple tasks that give immediate satisfaction to avoid stress or inconvenience

Illich’s Law or Law of Diminishing Returns: 1 simple concept

Illich’s Law or Law of Diminishing Returns suggests that optimal productivity is reached with an appropriate balance between working time and resting time.

Pin It on Pinterest