How to make a Pareto Chart using Excel Pivot Tables

I recently started working in a PDCA to solve a problem regarding customer complaints.

As you know, the PDCA applied to problem solving involves writing the problem statement based in facts and I had to enter the last twelve months data – rows and rows of information.

I needed the historical data to create a Pareto chart and prioritize the focus of the PDCA and now, in the stage of “Check” I have to monitor the data and then validate if the solutions proposed have been effective.

This means that I have to continuously enter new data and update Paretos and Run charts, so I tried to do it in the easiest and fastest way possible so the charts I was showing to my colleagues were accurate.

Because this is an approach widely used in many other contexts, I want to share this tool that can help in saving time and prevent errors while entering data and refreshing pivot tables and pivot charts in Excel.

I hope it is useful for you.

I decided to use an example involving an imaginary downtime problem.

Automating tasks is important and helpful, so I converted range data into a table. This allows me to enter new data by dragging the last row of the table and makes the update of pivot tables easier.

I’m using Excel 2013 but I had tested the method described in Excel 2007 and it works the same.

To see the details in the images click on them for a zoom in.

 

Enjoy!

 

Enter data in a spread sheet

pareto 01

Convert Data Table into a Table with Total Row

1. Select all data and then INSERT>Table>Create Table>OK

pareto 02

a. Result

pareto 03

2. Click on the table then on TABLE TOOLS>DESIGN and click on Total Row

pareto 04

Add a % of Total Column

1. Click in the cell beside the table headers and type “% of Total

pareto 05

2. In the first cell enter a formula to calculate percentage (in this particular case is “=B2/C13”)

pareto 06

a. Result

pareto 07

3. Select the % of Total column and give the number format for %

pareto 08

Create the Pivot Table

1. Click on the table and then INSERT>PivotTable>PivotTable>OK

pareto 13

a. Result

pareto 14

2. Drag “Downtime Cause” to the section Row Labels, “Time [min.]” and “% of Total” to the section “∑ Values

pareto 15

3. Click on the Pivot Table, on any value of “Sum of Time [min.]” then DATA>Sort & Filter>Sort Largest to Smallest

pareto 16

4. Click on “Sum of % of Total” and then Value Field Settings…>Number Format>Percentage>OK

pareto 17

pareto 18

pareto 19

5. Back in the Value Field Settings dialog box, click on Show Value As>Running Total In>OK

pareto 20

Note: To ensure that the cumulative column is well calculated, make sure that the Base field is the one in the Rows section, if it is not so, you will have a N/A in the Pivot Table.

pareto 21

a. Result

pareto 22

6. Change the cell names to match your analysis (just click on the cell and type)

pareto 23

Create the bar chart

1. Click on the Pivot Table and then PIVOTTABLE TOOLS>ANALYZE>PivotChart>OK (chart type must be Column)

pareto 24

a. Result

pareto 25

2. Two slow clicks on the % Cumulative series legend and then right-click Change Series Chart Type…>Combo> Clustered Column – Line on Secondary Axis>Line (in roll down menu)

pareto 26

pareto 27

Note: if you are using Excel 2007 the Combo option is not available and you have to change % Cumulative series for Line with markers and then manually set up the secondary axis.

3. Click on Line with markers and then OK

pareto 28

pareto 29

a. Result

pareto 30

4. Right-click on the values of the secondary Axis and then Format Axis… and change Major units to 0.2

pareto 31

pareto 32

5. To hide the gray field buttons, right-click on any of the buttons and then Hide All Field Buttons on Chart

pareto 33

a. Result

pareto 34

6. Click on the chart and then PIVOTCHART TOOLS> DESIGN> Chart Layout> either Quick Layout or Add Chart Element and format the chart as you need, adding titles, axis titles, etc.

pareto 35

a. Result

pareto 36

And this is your Pareto chart.

© 2013 Rocío Muñoz Zárate

6 thoughts on “How to make a Pareto Chart using Excel Pivot Tables

  1. Buddhi Raj Sharma says:

    I like this. Can u send me in rni_solutions@yahoo.com. Thanks

  2. Tabeth Sikireta says:

    Thanks for these notes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s