Add a Pareto Line to an Excel Bar Chart

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, add a cumulative-percentage column beside your category counts, then use Insert > Combo to plot the counts as columns and the cumulative percentage as a line on a secondary axis. Sort the counts from largest to smallest first; otherwise the line will not show a Pareto ranking.

What a Pareto line shows

A Pareto chart combines two views of the same categories. The bars show the value for each category, usually arranged from largest to smallest, while the line shows the running total as a percentage of all values. The line therefore ends at 100%, and it helps reveal how quickly the largest categories account for the whole.

For example, a support team might count requests by reason. If the top two reasons make up 65% of all requests, the line reaches 65% above the second bar. That does not prove those causes are the only ones worth addressing; it gives a ranked summary that can help guide further investigation. The familiar 80/20 idea is a possible pattern, not a rule that every dataset must follow.

Excel distinguishes a Pareto chart from an ordinary bar or column chart. Its built-in Pareto chart is a statistical chart that creates the descending bars and cumulative line from category data. If you already have a chart, or want more control over formatting and calculations, building a combo chart with your own cumulative-percentage series is a flexible alternative.

Prepare and sort the source data

Start with one row per category and one numeric value per row. Remove blank category rows and check that values are numbers rather than text. Then sort the entire table by the count or amount column, largest to smallest. Keep each category attached to its corresponding value while sorting.

Here is a small example of defect reports:

  • Scratches: 40
  • Loose fittings: 25
  • Mislabels: 15
  • Missing parts: 12
  • Other: 8

The total is 100. Sorting matters because the cumulative line adds values in the order shown. If the categories are alphabetized instead, the line still calculates correctly as a running total, but it no longer describes the cumulative contribution of the largest causes first.

Calculate cumulative percentages

Assume category names are in A2:A6, counts are in B2:B6, and the rows are already sorted from largest to smallest. Type C1 as Cumulative %, then enter the following in C2:

=SUM($B$2:B2)/SUM($B$2:$B$6)

Copy the formula down through C6. The fixed total range keeps the denominator at all five counts, while the first range expands as the formula is filled downward. For the example, the results are 40%, 65%, 80%, 92%, and 100%. Select C2:C6 and choose Home > Number > Percent Style, or use Ctrl+Shift+% on Windows. The Mac shortcut is Control+Shift+% in many Excel configurations; if it differs on your keyboard or locale, use the Percent Style button on the Home tab instead.

Check the final percentage before charting: it should be 100% when every category is included and the denominator covers all the counts. If you are rounding displayed percentages, the visible intermediate values may not add neatly, but the underlying values should still calculate from the original numbers.

Build the combo chart in Excel for Windows

  1. Select the category names and both numeric series, including their headings. In the example, select A1:C6. Including the cumulative percentage column gives Excel the three series it needs.
  2. Choose Insert > Combo > Create Custom Combo Chart. If Combo is not shown in the Insert ribbon, use Insert > Recommended Charts, open All Charts, and choose Combo.
  3. In the custom chart dialog, set the count series to Clustered Column and the cumulative percentage series to Line. Turn on Secondary Axis for the cumulative percentage series.
  4. Choose OK. Excel creates columns for the counts and a line plotted against a second vertical axis.

The secondary axis is important because the counts and percentages use different scales. Without it, a percentage line such as 40% to 100% may be compressed near the bottom of an axis scaled for counts up to 40, or otherwise appear misleading. The secondary axis gives the percentage series its own scale.

In current Excel for Mac, the usual route is Insert > Recommended Charts > All Charts > Combo, followed by the same series and secondary-axis choices. Ribbon layouts can vary slightly by update. In Excel for the web, chart options may also differ; if the custom combo controls are not available in your browser version, create or edit the chart in the desktop app.

Format the axes and make the chart readable

Format the right-hand vertical axis, which belongs to the line, as a percentage. Right-click that axis and choose Format Axis. In Axis Options, set the minimum to 0 and maximum to 1 if Excel has not already used a 0-to-100% scale; under Number, choose Percentage. Excel may show axis bounds as decimal values even when the labels are formatted as percentages, so 1 represents 100%.

Give the chart a descriptive title, such as Defect Reports by Cause. Add axis titles when readers might not know what the bars measure or what the line represents. You can select the chart and use Chart Design > Add Chart Element to add titles and data labels. A legend is useful when the series names are not self-explanatory. Consider labeling the line at each point only when the chart remains uncluttered; five points are easy to read, but dozens can become crowded.

Keep the category names in descending-value order and use a clear scale for the columns. Do not force the two axes to share a scale: the secondary axis exists precisely because counts and percentages are different units. If the chart has many categories, consider grouping minor categories into an honestly defined Other group, or use a larger chart area and readable labels rather than shrinking the text until it is illegible.

Read the line without overinterpreting it

Each point on the line is the sum of that category and all categories before it, divided by the total. In the example, the point above Loose fittings is 65% because 40 plus 25 equals 65, and the full count is 100. The point above Mislabels is 80%. The line is not a second measure of each individual category; the bars provide that information.

A steep early rise means a small number of top-ranked categories represent a large share of the total. A slower rise means the values are more evenly spread. If you use a threshold such as 80%, treat it as a reading aid rather than a universal cutoff. The appropriate point for action depends on the cost, consequences, and practical causes of the categories, not just the shape of the chart.

Also remember that the chart summarizes the data supplied. It does not establish why a category is frequent, whether categories were consistently recorded, or whether one category is more important than another. Check the definitions, time period, and data collection before drawing operational conclusions.

Common problems and fixes

  • The line is flat or missing: Check that the cumulative column contains numeric percentages, not text such as the characters “40%”. Confirm that the series is set to Line and that it has been included in the chart selection.
  • The line looks too low: It may be using the count axis. Open Chart Design > Change Chart Type, choose Combo, and assign the cumulative percentage series to the secondary axis.
  • The line does not finish at 100%: Check that the final formula includes every category and that the denominator covers the same complete set of counts. A total range that omits rows or includes an unintended value will change the result.
  • The bars are not in Pareto order: Sort the source table by its values, largest to smallest. Sorting only the displayed chart labels or only one column can detach category names from their counts.
  • Categories appear in reverse order: Select the category axis, open Format Axis, and check Categories in reverse order. Excel may reverse the visual order depending on chart layout; adjust the axis setting while confirming the largest bar is where you intend it to be.
  • There are too many tiny bars: Keep the complete data if detail matters, but consider a clearly labeled Other category for a summary view. Recalculate the cumulative percentages from the grouped data so the line still ends at 100%.

Combo chart or Excel’s built-in Pareto chart?

Use a custom combo chart when you want to add the line to an existing chart, control the cumulative calculation, or update a chart based on a prepared table. It is also useful when the data represents amounts other than frequencies, provided the values are meaningful to add and compare as a share of a total.

For a new chart from raw category-and-count data, try Excel’s built-in option: select the data, then choose Insert > Insert Statistic Chart > Pareto. Excel creates the descending columns and cumulative line automatically. The built-in chart is convenient, but it gives you less direct control over the calculation series than a worksheet-based combo chart. It is not the same as selecting Pareto from the custom combo settings.

One related point: Excel combo charts pair a line with column series, not a conventional horizontal bar series in the same way. If your existing chart uses horizontal bars, a practical approach is to create a column-based combo chart from the same source data. The Pareto convention is normally descending vertical columns followed by a cumulative line.

Frequently Asked Questions

Can I add a Pareto line to a chart I already made?
Yes. Add a cumulative-percentage column to the chart’s source data, then use Chart Design > Select Data to include that series. Choose Chart Design > Change Chart Type, select Combo, set the new series to Line, and assign it to the secondary axis.

Does Excel have an automatic Pareto chart?
Yes. In Excel for Microsoft 365, select the category and value data and choose Insert > Insert Statistic Chart > Pareto. Excel builds the descending bars and cumulative percentage line. The exact menu placement can vary by platform or ribbon layout.

Should the cumulative line always end at 100%?
It should when all categories are included in the calculation and the total is the sum of those same category values. If the last point is not 100%, inspect the formula ranges, blank or excluded rows, and any categories left out of the chart.

Can I use a Pareto chart for percentages or costs?
You can use any nonnegative values whose sum gives a useful total, including costs or counts of events. The line then shows each category’s cumulative share of that total. Explain the measure clearly, and do not treat the ranking as proof of cause or importance.

Why does my Pareto line use a second axis?
The bars show raw values, while the line shows proportions from 0% to 100%. A secondary axis lets both series remain readable without implying that a count and a percentage have the same units.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.