How to Make a Pareto Chart in Excel Tutorials

How to Make a Pareto Chart in Excel

How to Make a Pareto Chart in Excel

A Pareto chart is a type of chart that contains both bars and a line graph, where individual values are represented in descending order by bars, and the cumulative total is represented by the line. The chart is named for the Pareto principle, which, in turn, derives its name from Vilfredo Pareto, a noted Italian economist. The Pareto principle suggest that 80% of results come from 20% of activities.

The left vertical axis is the frequency of occurrence, but it can alternatively represent cost or another important unit of measure. The right vertical axis is the cumulative percentage of the total number of occurrences, total cost, or total of the particular unit of measure.

The purpose of the Pareto chart is to highlight the most important among a (typically large) set of factors. In quality control, it often represents the most common sources of defects, the highest occurring type of defect, or the most frequent reasons for customer complaints, and so on.

This tutorial will show you how to make and edit a Pareto Chart in Excel

How to Build a Pareto Chart in Excel

Step 1: Select the data you want displayed in the Pareto chart

Use your mouse to select the data you want included. Excel will use the left most column for the largest groups or branches. The data may need to be reorganized to take advantage of this chart type.

Step 1: Select the data you want displayed in the Pareto chart

Step 2: Click the Insert Tab, and then Click the Statistic Chart button.

After selecting the data for the chart, click on the Insert tab and select Pareto choice from the charts group.

Step 2: Click the Insert Tab, and then Click the Statistic Chart button

Step 3: Select Pareto chart type.

When the Hierarchy chart type sub menu appears notice the two types: Treemap and Pareto. Treemap is the subject of another tutorial.

Step 3: Select Pareto chart type

Result: Your Pareto Chart will appear on your worksheet

You will now see your area chart appear in your worksheet. Now you can start adding chart elements and formatting to your chart. Continue reading for more details on adding chart elements and chart formatting.

Line Graph


How to Add Chart Elements to a Pareto chart in Excel

Step 1: Click on a blank area of the chart.

Use the cursor to click on a blank area on your chart. Make sure to click on a blank area in the chart. The border around the entire chart will become highlighted. Once you see the border appear around the chart, then you know the chart editing features are enabled.

Step 1: Click on a blank area of the chart

Step 2: Click on the Chart Elements button next to the chart

Once the chart name area is highlighted, you will see the Chart Elements button next to upper right hand side of the chart. The button looks like a plus sign. Doing this will open the Chart Elements window.

Step 2: Click on the Chart Elements button next to the chart

Step 3: Check the Chart Elements you would like to add from the Chart Elements window

Once you have opened the Chart Elements window, you will see a number of items you can select to add to your chart. Check the Chart Elements you would like to display and they will appear on your chart. You can click on the arrow next to each Chart Element option for some additional formatting options.

Step 3: Check the Chart Elements you would like to add from the Chart Elements window

Here are the available chart elements for a Pareto Chart:

  • Axis
  • Axis Title
  • Chart Title
  • Data Labels
  • Gridlines
  • Legend

Each of these chart elements can be formatted in a variety of ways. Please see our other tutorials on how to add and format each chart element.

How to Format a Pareto chart in Excel

Step 1: Right-Click on a blank area of the chart

Use the mouse to right-click on a blank area on your chart. On the menu that appears select the Format Chart Area option.

Step 1: Right-Click on a blank area of the chart

Step 2: Select the Format Chart Area option

On the menu that appears select the Format Chart Area option. Doing this will open the Format Chart Area panel on the right side of the workbook.

Step 2: Select the Format Chart Area option

Step 3: Use the Format Chart Area panel to make changes to the appearance of your chart

Once you have opened the Format Chart Area panel, you will see a number of items you can modify on your chart. Use these features to create a custom look for your Pareto chart.

Step 3: Use the Format Chart Area panel to make changes to the appearance of your chart

Here are the available chart elements for a Pareto Chart:

  • Fill
  • Border
  • Shadow
  • Reflection
  • Glow
  • Soft Edges
  • 3-D Format
  • Size
  • Properties

Please note: You can also right click on any of your chart elements and open a separate formatting panel for that particular item. The Format Chart Area panel will be the one you want to use when formatting the main structure of the chart.

How to Modify the Organization of a Pareto chart in Excel

Step 1: Right-Click on the Horizontal Axis of the chart

Use the mouse to right-click on the Horizontal Axis on your chart. On the menu that appears select the Format Axis.

Step 1: Right-Click on the Horizontal Axis of the chart


Step 2: Select the Format Axis

On the menu that appears select the Format Axis option.

Step 2: Select the Format Axis option

Step 3: Use the Format Axis panel to make changes to the appearance of your chart

Step 3: Use the Format Axis panel to make changes to the appearance of your chart

Topic #13

How to Make a Box and Whisker Chart in Excel




Thanks for checking out this tutorial. If you need additional help, you can check out some of our other free Excel Chart tutorials, or consider taking an Excel training class with one of our professional trainers.