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.

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.

How 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 in Excel, 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.

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.

Then the Pivot Table will look like the image below.

• 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.

• 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.

Now see, the column is copied with a serial number. It’s one of the advantages of using 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.

• Later, select the base field and press OK.

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.

Read More: How to Use Pareto Chart in Excel

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.

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.

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

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.

• 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.

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.

• 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.

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:

• Follow the first two steps from the immediate previous section to open the Format Axis field.
• Next, click on the Size & Properties icon and select Rotate All Text 270° from the Text direction dropdown box.

Here’s our final output.

Download Practice Workbook

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

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.

Related Articles

• How to Create a Stacked Pareto Chart in Excel
• How to Create Pareto Chart with Cumulative Percentage in Excel
• How to Create Dynamic Pareto Chart in Excel
• How to Interpret Pareto Chart in Excel

<< Go Back to Excel Pareto Chart | Excel Charts | Learn Excel