How to Make a Pareto Chart Using Pivot Tables in Excel

What Is a Pareto Chart?

A Pareto chart combines columns sorted in descending order with a line representing cumulative total percentages. It’s a powerful tool for highlighting the most significant factors in a dataset.

Dataset Overview

Imagine you have a dataset showing daily production from five machines.

To create a Pareto Chart, start by making a Pivot Table from your data range.

Select any data from your dataset.
Click on Insert > PivotTable.

A dialog box will appear, automatically selecting the data range. Choose your desired worksheet option (e.g., New Worksheet) and click OK.

A new worksheet will open with the PivotTable Fields on the right side.
Mark the headers from the fields to set up your Pivot Table.

The Pivot Table will look like the image below:

For the Pareto chart, sort the Sum of Units column in descending order.
Right-click on any data point and choose Sort > Sort Largest to Smallest.

Instead of using the Copy command, let’s do this more efficiently using PivotTable Fields.
Drag the Units header into the Sum of Units field.

Now you’ll see a new column with serial numbers (thanks to Pivot Tables).

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

Calculate the cumulative percentage for this new column:
- Right-click on any data point in the new column.
- Select Show Values As > %Running Total In.

Choose the base field and press OK.

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

Select any data from the Pivot Table.
Click on PivotTable Analyze > Tools > PivotChart.
In the dialog box named Insert Chart, choose the Combo option.

Mark Sum of Units2 as the Secondary Axis and press OK.

Voilà! You’ll have a Pareto chart.

Read More: How to Use Pareto Chart in Excel

Customizing a Pareto Chart

Hide All Field Buttons

Hide all field buttons:
- Right-click on any field button and select Hide All Field Buttons.

Change Design

Change the chart design:
- Go to Chart Styles.
- Select your desired chart design from the Style section.

Or click anywhere on the chart and select a design from the Design ribbon.

Adjust Color

Go to Chart Styles or select the Pareto chart and click on Design.
Select Change Colors and select a color template from the list.

Format Axis

To customize the axis, such as changing minimum and maximum bounds, follow these steps:

Right-click on the axis you want to format (e.g., the percentage axis).
Select Format Axis from the context menu.
Alternatively, you can double-click on the axis to open the Format Axis field directly.

In the Axis Options section, set the minimum and maximum bounds. For example, set 0 for the minimum (representing 0%) and 1 for the maximum (representing 100%).
Press ENTER.

Now your Pareto chart’s axis is formatted according to the new bounds.

Change Axis Alignment

If your horizontal data labels appear messy due to longer names, consider aligning them vertically for clarity:

Follow the first two steps from the previous section to open the Format Axis field.
Click on the Size & Properties icon.
From the Text direction dropdown, select Rotate All Text 270°.