FREE TRAINING EVENT: Avoid the Top Five Mistakes of Managing Remote and In-Person Teams. Click here to register.

Creating a Pareto Chart in Excel

The Pareto Principle, named for Italian economist Vilfredo Pareto, suggests that 80% of problems can be traced to as few as 20% of root causes.  This can be valuable, even vital information when you are trying to figure out which of many problems to tackle first, or in a complicated troubleshooting environment.

For example, you have just been asked to lead a struggling project team to get them back on track. You ask the team members what their main obstacles have been in terms of meeting objectives and expectations. They make the list, and you analyze it to figure out what the root causes are for each of the problems are that they are experiencing, looking for commonalities.

You apply a ranking to each problem cause based on the frequency that it occurs. In looking at the numbers, you discover that lack of communication between the project workers and the project stakeholders is the root cause of 23 of the main problems the team faces, while the next largest issue, access to necessary resources (computer systems, equipment, etc.), scored only an 11.  Other issues drop into the single digits. You realize that by fixing the communication problem you can eliminate a huge percentage of the problems, and by fixing the resource access problems you can clear nearly 90% of the team’s hurdles. Not only have you figured out how to help your team, you’ve just performed a Pareto analysis.

But all that paperwork probably took you some time. Using a Pareto chart in Microsoft Excel could have sped the process up for you considerably.

Pareto charts are a combination of a line graph and a bar graph. They are unique because they typically have one horizontal axis (the x-axis) and two vertical axes. This chart is useful for prioritizing and sorting data.

I’m here to walk you through the steps to get your data prepped for a Pareto chart then how to create the chart itself. If you have already formatted your data for a Pareto chart and you can skip down to part two.

In today’s problem scenario, we have a company that reimburses employee expenses regularly. We want to know where we are spending the most on and how we can reduce that cost by 80% with a quick Pareto analysis. We can find which reimbursements account for 80% of cost and prevent high costs in the future through policy changes, quotes on bulk pricing and a discussion of employee expenses.

Part One: Build Your Pareto Chart Data

1. Set up your data. We have 6 reimbursement categories and the claims amounts in our table.
2. Sort your data from largest to smallest amount. Be careful to highlight your data in columns A and B to sort accurately.
3. Use the SUM() function to add your Amount range. In this example, cells B3 through B8 should be added to get our total. SHORTCUT – To sum up a range of values, select the cell B9 and type ALT + “=”. The total here is \$12,250.

4. Create a Cumulative Amount column. Start with the first amount, 3750 or B3. Each amount builds on the one before it. In C4, type “=C3+B4” then press Enter.
5. To automatically fill the rest of the column, double click the Autofill Handle.

6. Next, create a Cumulative Percent column.  You can use the Amount total and each cumulative amount to build this column. In the function bar for D3, type “=C3/\$B\$9” and Enter. The “\$” figures create an absolute reference so that the Amount total (B9) does not change when you drag the formula down.
7. Now either double click on the Auto Fill Handle to fill in your data or click and drag the handle down your column of data.
8. You now have what you need to start your Pareto chart!

Part Two: Create Your Pareto Chart in Excel

1. Highlight your data (from B2 to D8 in this example).
2. Quick Tip: Press ALT and F1 on your keyboard to automatically create a chart from the data.
3. Right click in the Chart Area and select Select Data. The Select Data Source dialog box appears. Select Cumulative Amount and choose Remove. Then choose OK.
4. Click in the chart and use your keyboard’s arrow keys to toggle between areas on your chart. When Cumulative % is highlighted on the x-axis, hover and right click Change Chart Series Type. It’s hard to see right now but you should see it along the x-axis.
5. The Change Chart Type dialog box appears and pick a line graph.

6. You now have a bar chart with a flat line graph along the x-axis. In order to get a curve to our Cumulative % line, we need the other vertical axis.
7. Right click on the Cumulative % line and choose Format Data Series. The Format Data Series dialog box appears.
8. Choose Secondary Axis under Series Options then click Close.
9. Now you have a Percentage Axis and a complete Pareto chart! Our findings: training fees, hardware and office supplies make up the bulk of our expenses.

Now that you have the step-by-step tips to setup and create a Pareto chart in Excel, give it a try. You can apply the Pareto analysis to identify your biggest problem areas and make a huge impact by addressing them.

Monthly Archives

• Nitha says:

• Alvin Goh says:

this is just what i needed, million thanks. Easy yet powerful!

• Doug Lamb says:

Excellent example. Simple steps that were easy to follow.

• Dan says:

Very nice example and simple explanation! Thank you for a job well done!!!

• Luis says:

Excellent! Really easy to follow! Good job and thanks!

• Pathma says:

very simple methods, easy to follow. Very satisfied indeed.

• D.G. says:

Nice step by step, thanks for sharing!

• Ayyaj Shaikh says:

Good, step by step example

• Tejas says:

Really helpful… Steps explained is simple manner…. Thanks a lot

• Mariya Santhosh says:

Its Very Very Easy method for the end Users.

Mariya Santhosh

excellent info with examples.. ..thanx for providing such info

• Anwar says:

That was very useful. Very easy to understand. Thanks a lot..!

• Sohail Mumtaz says:

I had taken good understanding of making Pareto Chart by following your steps. A bundle of thanks for providing easy tips.

• Hassan says:

Fabulous! Very well explained, taking the learner through the nitty-gritties. Thanks so much and appreciate your struggles. Well done!

• Shubham Suryawanshi says:

Perfect way. Like that.

• Justine says:

extremely easy and helpful thanks !

• Rupesh Welekar says:

Very nice explained with example…

• Jack Reno says:

Thanks very much, super helpful and easy to follow!

• Pragal says:

Well explained and made it so simple. Thank you so much, as that’s what I needed.

• shantanu says:

one of the best examples

• John Smith says:

Very good……

• Hashitha says:

Excellent..well understood

• Srikanth says:

Very good tutorial , easy to understand.

• Ashish Sarode says:

Thank you! Thank you very much.

• bath says:

thanks for sharing this- very useful!

• sv says:

many thanks for sharing this very simple steps

• Kulandairaj says:

Very Nice. Thank you

• Vijay says:

Really helpful… Steps explained is simple manner…. Thanks a lot

• MOHIT SHARMA says:

• Devicharan says:

Thanks for the tutorial, I learnt it.

• Darwin says:

Thanks a lot. You answered my problem!

• Nick Joe says:

Thanks a lot . That was true excellent job

• Neeraj Sharma says:

Thanks for ur valuable guidance

• NitinMore says:

It is nicely documented, to learn easily!!!
I like it. Thanks!

• Dave says:

very good – easy to follow – even for me! 🙂

• Koshila says:

Perfect & clear example. Thank You!

• Rajani says:

Very helpful.. Thank you so much for posting

• Iris C says:

Thank you so much!
This is a great help in answering my assignment.
God bless!

• Mukul Namdeo says:

Thanks a lot, to explain the Pareto in easy way.

• Renu Sekhar says:

I would say its flawless..Very nicely explained..

• Sravanthi says:

Wonderful explanation.Thanks a lot

• Emilie says:

Excellent – thanks!

• Tim Graham says:

At last… something well explained that actually works. Great instructions

• Tushar Patel says:

Thank you so much, super helpful and Very easy to follow

• Deepa says:

It’s really very good

• preeti says:

thanks and very useful

• Geeta says:

Very helpful.Thank you so much for posting

• Kathy says:

One of the easiest and best Excel examples I have ever seen. Immediate help to me.

• Shristi says:

very useful thanks guys

• Emma says:

Thanks. Great help to me too.

• Ken says:

This tutorial worked out very well. Thank you for the instructions.

Thanks,Very easy to understand

• Joseph Isaac says:

Very simple way of presentation and easy to learn

• Prramaan says:

Very impressive

• Saravanan says:

excellent. this is easy way to acess

• Ridhima Sodhi says:

very well explained 🙂

• Balu says:

It is very easy to understand, excellent

• Sudheer says:

Thank you So much….i am very new to Pareto but it was awesome with your explanation!

• j.o.r. says:

Your explanation was very easy to follow. Thank you very much!Cheers!

• Vidya Sagar Reddy says:

Thank you so much very easy explanation about pareto chart….. very helpful to me.

• Geeta laxmi says:

Very useful. Thanks