Stacked Bar Pareto Chart in Excel: Build and Format

Charts & pivot tables · Updated November 20, 2026

To make a stacked Pareto chart in Microsoft Excel for Microsoft 365 on Windows, sort categories by their combined total, plot the component values as stacked columns, and add a cumulative-percentage series as a line on a secondary axis. Excel’s built-in Pareto chart does not create this stacked-column-and-line combination automatically, so you build it from a summary table and a combo chart.

What a stacked Pareto chart shows

A standard Pareto chart ranks categories from largest to smallest and overlays a line showing the running share of the total. It helps answer two questions: which categories contribute most, and how much of the overall result is accounted for by the leading categories? A stacked version adds a breakdown within each category. Each bar is divided into component values, while the cumulative line is calculated from each bar’s total.

For example, a quality team might record defects by type and production shift. The stacked segments show how many defects came from each shift, while the order of the bars and cumulative line show which defect types contribute most overall. The line must use the sum of all segments in each category—not one segment by itself.

In Excel, “bar” can mean a horizontal bar chart, while the conventional Pareto chart uses vertical columns. The most dependable combo-chart approach is therefore a stacked column chart with a line. The terms are sometimes used loosely, but the distinction matters when you choose chart types.

Prepare and sort the source data

Keep one row per category and one numeric column per component. Include a total column for sorting and a cumulative-percentage column for the line. Here is an illustrative defect table, with two shifts as the stacked components:

Defect Shift A Shift B Total Running total Cumulative %
Late 18 12 30 30 37.5%
Damage 12 8 20 50 62.5%
Missing 9 6 15 65 81.25%
Wrong item 5 5 10 75 93.75%
Other 4 1 5 80 100%

With category names in A2:A6 and component counts in B2:C6, calculate each row’s total in D2 with =SUM(B2:C2), then fill the formula down to D6. With totals in D2:D6, enter =SUM($D$2:D2) in E2 and fill it down to E6 to calculate the running total. With running totals in E2:E6 and category totals in D2:D6, enter =E2/SUM($D$2:$D$6) in F2 and fill it down to F6. Format F2:F6 as Percentage. In this example the grand total is 80, so the first cumulative value is 30/80, or 37.5%, and the final value is 100%.

Sort the entire table—not just the total column—by Total from largest to smallest. Select the data range, including headers, then use Data > Sort; choose Total as the sort column and Largest to Smallest as the order. Sorting only one column would separate category labels and component values from their correct rows. If your table has a header row, confirm that Excel recognizes it as headers in the Sort dialog.

Build the stacked columns and cumulative line

  1. Select the category labels and component columns, including their headers—for the example, A1:C6. Do not include the Total column as a stacked segment; that would count the same data twice.
  2. Choose Insert > Insert Column or Bar Chart > 2-D Stacked Column. Excel creates one column per category, with Shift A and Shift B stacked inside it.
  3. Add the cumulative percentages as another series. Select the chart, choose Chart Design > Select Data, and use Add in the Legend Entries (Series) area. Set the series name to the F1 heading, set the series values to F2:F6, and confirm. The category labels should still be A2:A6. If the dialog includes an unwanted blank or extra category, correct the category-axis label range there.
  4. With the chart selected, choose Chart Design > Change Chart Type > Combo. Set Shift A and Shift B to Stacked Column. Set Cumulative % to Line and select Secondary Axis for that line, then confirm. In the secondary-axis version of the dialog, the checkbox is beside the cumulative series.
  5. Check that the line rises from the first category to the last and ends at 100%. If it does not, inspect the series values and confirm that the source rows are sorted by total descending.

The combo chart uses the primary vertical axis for counts and the secondary vertical axis for percentages. Because these scales differ, plotting the percentage series on the count axis would make the line nearly invisible or misleading. Excel may set a suitable percentage scale automatically; if needed, right-click the secondary axis, choose Format Axis, and set its bounds to 0 and 1 when the values are stored as decimal percentages. If the axis uses percentage number formatting, it will display that range as 0% to 100%.

Format it so the ranking is clear

Give the chart a descriptive title, such as “Defects by Type and Shift.” Use a legend that identifies both stacked components and the cumulative line. Apply contrasting but restrained colors: the two segments need to be distinguishable, and the line should stand out without obscuring the bars. Add data labels selectively. Labels on every segment can clutter a chart with many categories, while labels on the cumulative line can be useful when readers need exact threshold values.

Keep categories in descending total order from left to right. If Excel reverses the category order, right-click the horizontal category axis, choose Format Axis, and review the option for categories in reverse order. Also check where the vertical axis crosses if reversing the categories moves the axis to an unexpected side. Axis titles can clarify that the primary scale is a count and the secondary scale is cumulative percent.

A common reference is the 80% point: in the example, the first three categories contribute 81.25% of all defects. Treat that as a reading aid, not a universal law. The “80/20 rule” is a rough pattern, not a requirement that every dataset must follow. The chart shows the actual proportions; it does not prove why the categories are high or prescribe what action to take.

Choosing stacked columns or horizontal bars

Stacked columns are generally the practical choice when the chart needs a cumulative line in the same visual. They align naturally with Excel’s common column-and-line combo chart workflow, and the category ranking remains easy to see. They work best when there are a manageable number of categories and the component labels fit in the legend.

Horizontal stacked bars can be easier to read when category names are long or there are many categories. However, combining a horizontal bar chart with a cumulative line is less straightforward in Excel than the standard column-line combination. The axes and series may not combine as expected, and the line can be difficult to align with the bar categories. For a strict horizontal layout, consider presenting a sorted horizontal stacked bar chart beside a separate cumulative-percent chart or table. If you attempt a custom combination, verify every category-to-value alignment and the percentage scale rather than assuming Excel has paired them correctly.

Common problems and fixes

  • The line is flat or nearly invisible: Put the cumulative percentage series on the secondary axis and confirm its values are numeric percentages, not text such as “37.5%” stored as text.
  • The line does not finish at 100%: Check that the last running total includes every category, the denominator includes all category totals, and no rows were omitted from the chart’s series range.
  • The columns are not stacked: In the Combo settings, assign each component series the Stacked Column chart type. Avoid selecting 100% Stacked Column unless you want every category normalized to the same height; that chart shows composition, not the magnitude needed for a conventional Pareto ranking.
  • Categories are in the wrong order: Sort the complete source table by the total column in descending order. Do not sort the chart’s component values independently.
  • The chart has a duplicate-looking segment: Remove the Total series from the stacked columns. Only the component columns belong in the stack; the total is for sorting and cumulative calculations.
  • The percentage axis shows decimals: Format the secondary axis as Percentage. Excel stores 37.5% as 0.375, so the underlying value can be correct even when the axis labels need formatting.

If the source table changes often, update the chart’s data ranges when new categories are added. Converting the source range to an Excel table with Insert > Table can make expanding data easier to manage, though you should verify that the chart includes newly added rows and that the sort order is still descending. A new category inserted into the middle of an already sorted list can change every later cumulative percentage.

How this differs from Excel’s built-in Pareto chart

Excel includes a Pareto chart type among its statistical charts. It sorts individual values and displays their cumulative percentage, making it useful for a simple list of categories and one measure. It is not the same as a stacked Pareto chart: it does not automatically split each category’s total into multiple stacked components. For multi-component data, the custom stacked-column-and-line combo gives you control over the segments, totals, sort, and cumulative series.

Use the built-in chart when one measure per category is enough and its formatting meets the need. Use the custom method when the audience needs to see how each category total is divided—for example, defect counts by shift, sales by product and region, or support cases by issue and channel. The custom chart takes more setup, but the summary table makes the calculations inspectable and easier to audit.

Frequently Asked Questions

Can Excel make a stacked Pareto chart automatically?
No. Excel’s built-in Pareto chart is designed for a single value per category, not multiple stacked components with a cumulative line. Create a stacked chart and add a cumulative-percentage line using a combo chart.

Should the cumulative percentage use category totals or individual segments?
Use each category’s combined total in the descending ranking. The segments explain the composition of that total; summing only one segment would produce a different measure and would not represent the Pareto share of all observations.

Should I use a 100% stacked column chart?
Usually not for a standard Pareto chart. A regular stacked column preserves total size, so larger categories appear taller. A 100% stacked column makes every bar the same height and emphasizes component proportions instead of the category totals being ranked.

Why does my cumulative line go down?
A cumulative percentage should not decrease when all values are nonnegative and the categories are included correctly. Check for negative values, a mismatched series range, or formulas that do not use a running total. Also confirm that the plotted line points follow the same category order as the bars.

Can I use this chart with percentages or time values?
Yes, provided the measure is meaningful to add across categories. For a Pareto ranking, sort by the contribution being analyzed and calculate cumulative share against the sum of those contributions. Do not add rates or averages as though they were counts; use an appropriate underlying total or a separate analysis for those measures.

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.