How to Make a Pareto Chart in Excel in 10 Seconds [2019 Tutorial]

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

A Pareto chart is one of the best ways to emphasize the major factors to anything that can be counted and categorized.

the 80 20 rule

The Pareto principle, which this chart depicts, states that 80% of results came from 20% of the action.

This principle has lots of application in business as well as in personal life.

Fortunately, you can make one out of your data in less than 10 seconds with the built-in Pareto chart type (even faster if you have quick hands 😉).

Be advised that Microsoft Excel 2013 versions and below don’t have a built-in Pareto chart type.

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

When to Use a Pareto Chart?

In general, there are 2 uses for this type of chart:

  • To show the relative portion or percentage of each factor to the total
  • To emphasize the most significant factors in the data

Before using a Pareto chart, make sure your data can be categorized and the percentage of each has value to you.

Kasper Langmann, Co-founder of Spreadsheeto

There are lots of charts available in Excel especially with the addition of 6 new charts in Excel 2016. Make sure this chart is the best one that serves your purpose.

If you’re not sure, ask yourself these questions:

  • Are you looking to illustrate the largest issues you (or your business/team) is facing?
  • Do you need to know which areas you should focus your efforts on?
  • Do you want to know which issues contribute the most to your problems?

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Pros and Cons of Pareto Charts

This chart isn’t a one-size-fits-all kind of chart. It comes with its own advantage and disadvantages.

Here are its advantages:

  • Improve problem-solving by turning problems into laid-out facts
  • Increase efficiency by putting an emphasis on the few specific factors that contribute the most to the problem
  • Enhance decision making by taking into consideration the factors with the greatest impact

Here are its disadvantages:

  • Limited only to showing observable qualitative data
  • Not useful for statistics computation
  • Offers no additional insight about a specific factor
  • Need additional Pareto charts to provide more insight
  • No forecast analysis since the data is based on historical data

Practice caution in using a Pareto chart. Make sure your data is accurate. One small mistake could result in misinterpretation and misjudgment.

Kasper Langmann, Co-founder of Spreadsheeto

Without further ado, let’s jump right away into how you can create this type of chart.

Making a Pareto Chart

To start, let’s pretend that you’re someone who wants to improve your own time management.

You feel like you’re wasting too much of your time but you can’t exactly pinpoint what. So your first step was to record the amount of time you spent last week on an activity that’s more than an hour.

Here’s the result:

data overview for time management exercise of pareto chart

Because you’re the type who likes to view data in a chart, you decided to use a Pareto chart.

The first thing you should do is select the range that contains your data including the headings. In this case, it’s A2:B12.

Then, select the ‘Insert’ tab from the tab list.

insert tab on microsoft excel 2019 tab list

After that, click on the ‘Insert Statistic Chart’ and select ‘Pareto’ from the Histogram group.

Congratulations! You now have a Pareto chart!

pareto chart in microsoft excel 2019

Pro Tip: Show the Data Labels

Data labels are helpful in this type of chart since they show the individual value of the factor at a glance.

To show the ‘Data Labels’, simply click on the chart and click the ‘Chart Elements’ symbol. Then, tick the box beside ‘Data Labels’.

Conclusion

Creating a Pareto chart in earlier versions of Excel wasn’t easy. There are computations involved which makes it a bit complicated. In addition, you also had to manually place the data in decreasing order.

Fortunately, Microsoft added a built-in Pareto chart in Excel 2016.

Now, it’s possible to make one in 5 seconds. 👍

Kasper Langmann, Co-founder of Spreadsheeto
2019-10-08T12:38:05+00:00