How to Make a Pareto Chart Using Pivot Tables in Excel

We can easily create a Pareto Chart for a normal range or table. But the Pareto Chart command of Excel doesn’t work for a Pivot Table, we have to do it differently then. I’ll show those simple ways to make a Pareto chart using excel pivot tables in this article with vivid illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


What Is a Pareto Chart?

A chart that contains both the columns sorted in descending order and a line showing the cumulative total percentage is called a Pareto or sorted histogram chart. Pareto charts are used to highlight the highest factors in a dataset. Among the seven basic tools of quality control, it is considered one of them. Because it is quite quick and easy to find out the most common problems or issues from the Pareto Chart.


Easy Steps to Make a Pareto Chart Using Pivot Tables in Excel

Let’s get introduced to our dataset first. It shows five machines’ daily produced products.

To make a Pareto Chart, we’ll have to make a Pivot Table from a data range first.

Steps:

  • Select any data from the dataset.
  • Next, click as follows: Insert > PivotTable. Soon after you will get a dialog box.

  • It will select the data range automatically. Mark your desired worksheet option. I marked New Worksheet.
  • Then just press OK.

How to Make a Pareto Chart Using Excel Pivot Tables

After a while, a new worksheet will open up with the PivotTable Fields on the right side.

  • At this moment, mark the headers from the fields.

How to Make a Pareto Chart Using Excel Pivot Tables

Then the Pivot Table will look like the image below.

How to Make a Pareto Chart Using Excel Pivot Tables

  • For a Pareto chart, we need to sort the data of the Sum of Units in descending order. So right-click on any data then click as follows from the context menu to sort: Sort > Sort Largest to Smallest.

How to Make a Pareto Chart Using Excel Pivot Tables

  • After sorting, we’ll copy the Sum of Units column to calculate the cumulative percentage for the Pareto chart. We won’t need to use the Copy command here rather we can do it more smartly and quickly using PivotTable Fields.
  • Drag the Units header in the Sum of Units field.

How to Make a Pareto Chart Using Excel Pivot Tables

Now see, the column is copied with a serial number. It’s one of the advantages of using Pivot Tables.

How to Make a Pareto Chart Using Excel Pivot Tables

Now we’ll find the cumulative percentage in the new column.

  • Right-click on any data of the new column and select as follows: Show Values As > %Running Total In.

How to Make a Pareto Chart Using Excel Pivot Tables

  • Later, select the base field and press OK.

How to Make a Pareto Chart Using Excel Pivot Tables

Our Pivot Table is ready to create a Pareto chart now.

  • Select any data from the Pivot Table and click as follows: PivotTable Analyze > Tools > PivotChart. Consequently, you will get a dialog box soon named- Insert Chart.

  • After that, click the Combo option and mark the Sum of Units2 as Secondary Axis.
  • Finally, just press OK.

Then you will get a Pareto chart like the image below.

How to Make a Pareto Chart Using Excel Pivot Tables


How to Customize a Pareto Chart in Excel

After inserting a Pareto chart, applying some customizations gives a better look of your chart and gets easier to understand. In this section, I’ll show some major and most common customizations for the Pareto chart.


Hide All Field Buttons

After creating a chart from the Pivot Table, the pivot field buttons are also available on the chart by default. But if you want you can hide them all.

Steps:

  • Right-click on any field button and select Hide All Field Buttons from the context menu.

How to Make a Pareto Chart Using Excel Pivot Tables

Now see, the field buttons are gone and the chart is looking clean.


Change Design

Excel has a lot of chart styles, so you can choose one of them which suits your dataset most. There are two ways to do it.

Steps:

  • Click on the Chart Styles icon beside the chart.
  • Then select your desired chart design from the Style section.

How to Make a Pareto Chart Using Excel Pivot Tables

  • Or, click anywhere on the chart and then select the design from the Design ribbon. I chose Style 9.

How to Make a Pareto Chart Using Excel Pivot Tables

Now have a look, it’s looking better.


Change Color

After choosing any design you can change the colors of your chart too. It will help you to stay in any design with customized colors. For this operation, excel has two ways too.

Steps:

  • Firstly, click on the Chart Styles icon beside the chart and then select a color template from the Color section.

How to Make a Pareto Chart Using Excel Pivot Tables

  • Or, select the Pareto chart and click as follows: Design > Change Colors.
  • After that, select a color template from the list. I selected Colorful Palette 4.

How to Make a Pareto Chart Using Excel Pivot Tables

Looking pretty cool, right?


Format Axis

You can change the axis according to your requirement like you can change the minimum and maximum bounds for your Pareto chart.

Steps:

  • Right-click on the axis that you want to change the format. I’ll change the format for the percentage axis.
  • Then select Format Axis from the context menu. Or, you can double-click on the axis to open the Format Axis field at once.

How to Make a Pareto Chart Using Excel Pivot Tables

  • Next, from the Axis Options field set the minimum and maximum bounds. I set 0 for minimum and 1 for maximum. 0 means 0% and 1 means 100%.
  • Then just hit the ENTER button.

How to Make a Pareto Chart Using Excel Pivot Tables

Now the axis of the Pareto chart is formatted according to the new bounds.


Change Axis Alignment

The horizontal data labels are looking quite messy as the names are longer. So if we align them in a vertical direction then it will look pretty clear.

Steps:

How to Make a Pareto Chart Using Excel Pivot Tables

Here’s our final output.


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to make a Pareto chart using Excel Pivot Tables. Feel free to ask any questions in the comment section and give me feedback. Visit ExcelDemy to explore more.

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo