Excel 100% Stacked Lines in a Combo Chart

Yes, you can combine percentage lines with another chart type in Excel, but the most dependable method is to calculate each line’s share yourself and add those values as regular line series. A native 100% Stacked Line chart normalizes its line series together; with only two series, their percentages always add to 100%, so the lines show complementary information rather than two independent trends.

What a 100% Stacked Line Chart Shows

A 100% Stacked Line chart displays how each series contributes to the total at each category, such as each month or year. Excel calculates each series’ share of the sum for that category and plots the result as a percentage. The series’ values at a category therefore add to 100%, provided the data can be meaningfully totaled.

For example, if product A sells 20 units and product B sells 30 units in January, their shares are 40% and 60%. In February, if they sell 35 and 15 units, their shares become 70% and 30%. The chart emphasizes the changing mix of sales, not the fact that total sales fell from 50 units to 50 units—or rose or fell in other months.

This is different from an ordinary line chart, which plots the raw values, and from a 100% Stacked Area chart, which fills the space between cumulative series boundaries. A 100% Stacked Line chart shows each series’ percentage as a line. It is useful for comparing composition over time, but it does not show the overall size of the total.

Why Two Stacked Lines Can Be Misleading

With exactly two nonnegative series, each category’s shares must be complements: if one is 40%, the other is 60%; if one rises to 70%, the other falls to 30%. Both lines contain the same underlying information. Showing both is valid, but it can make a chart look busier without adding insight. A single line for one share may be clearer, with the other share understood as the remainder.

There is a further limitation: percentages can look stable even when the totals change dramatically. A shift from 40% to 50% could occur while the total doubles, halves, or stays constant. If readers need both composition and scale, include a separate measure of the total, such as a column series, or provide totals in data labels or a nearby table.

Stacking also assumes the component values are suitable for a meaningful total. It is generally not appropriate to stack unrelated measures, such as temperature and revenue, or values that can be negative. If a category total is zero, its component shares are undefined; blanks, zeros, or negative values can also make the result confusing. Check the source data before choosing this chart type.

Can Excel Put a Native 100% Stacked Line in a Combo Chart?

Excel for Microsoft 365 on Windows offers combo charts that assign chart types to individual data series. The exact chart types available in the combo dialog depend on the selected data and Excel’s chart options. In some setups, the dialog may not offer the 100% Stacked Line subtype as a per-series choice. Even where a stacked-line option is available, its normalization behavior is not a reliable way to make a percentage line independent of other series in a mixed chart.

For a chart that combines a percentage trend with a different measure, calculate the percentages in worksheet cells and use those calculated values as ordinary line series. This makes the denominator explicit and gives you control over which series contribute to the percentages. It also avoids relying on a chart subtype whose normalization may not match the story you intend to tell.

In Excel, select the data and use Insert > Recommended Charts, or go to Insert > Insert Combo Chart if that option is shown. To change an existing chart, select it and choose Chart Design > Change Chart Type > Combo. Assign a chart type to each series in the dialog. If your required 100% Stacked Line choice is absent, use the calculated-percentage method below rather than substituting an unrelated stacked type.

Calculate the Percentages in the Worksheet

Suppose column A contains month names, column B contains product A’s units, and column C contains product B’s units. Put the share calculations in columns D and E. With the first month’s values in B2:C2, enter the following formulas and fill them down for the remaining months:

With product A’s units in B2 and both products’ units in B2:C2, the share formula for product A is =B2/SUM($B2:$C2). If January’s values are 20 and 30, the result is 0.4, which displays as 40% when the cell is formatted as a percentage.

With product B’s units in C2 and both products’ units in B2:C2, the share formula for product B is =C2/SUM($B2:$C2). For the same example, it returns 0.6, or 60%. Together, the two displayed shares total 100%. Format D:E as percentages using Home > Number and select Percentage, or use the percentage button in the Number group.

These formulas assume the values are nonnegative and the row total is not zero. If a row can total zero, decide how the chart should handle it—such as leaving the share blank—and adjust the calculation accordingly. Don’t silently treat a zero-total month as a valid 0% composition: that could suggest a meaningful share where none exists.

Build the Combo Chart Step by Step

Arrange the data with the time categories in column A, the original values in B:C, and the calculated shares in D:E. Include clear headers in the first row.
Select the category labels and the series you want to chart. For a chart of shares plus total units, add a total column as well. You can create it with a formula such as =SUM(B2:C2) when B2:C2 hold the two component values; for 20 and 30, it returns 50.
Choose Insert > Insert Combo Chart > Create Custom Combo Chart, or select the existing chart and use Chart Design > Change Chart Type > Combo.
Set the share series to Line and the total series to a suitable type, often Clustered Column. If Excel offers a 100% Stacked Line option but it does not behave as intended with the other series, keep the calculated shares as ordinary lines.
If the total and shares use different scales, select the total series’ Secondary Axis option. The share lines can then use the primary axis, while the total uses the secondary axis.
Click OK. Add a chart title and axis titles, then check that the legend identifies every series correctly.

A combo chart with percentage lines and total columns needs particular care with axes. Share values run from 0 to 1 in the worksheet even though they display as 0% to 100%. To make the primary axis easy to read, right-click it, choose Format Axis, and set its bounds to 0 and 1 if appropriate. Under Number, use a percentage format. The total series’ secondary axis should retain units, such as units sold, rather than a percentage format.

Choose the Chart That Matches the Question

Start with the question the chart is meant to answer. If the question is “How did the mix change?”, a 100% Stacked Line chart or calculated percentage lines may fit. If it is “How did the quantity of each product change?”, use ordinary lines or clustered columns with raw values. If the reader needs both the mix and the total, a combo chart can show percentage lines alongside a total column series, as long as the axes and units are clearly labeled.

For two components only, consider plotting one percentage line and showing the total separately. If the focus is the difference between the components rather than their shares, charting the raw values may be more informative. For three or more components, a 100% stacked chart can show how shares change, but many lines may overlap. Keep the number of series manageable and use direct labels or a clear legend.

A 100% Stacked Column chart can be a better choice when the categories are discrete periods and readers need to compare the composition at each one. A 100% Stacked Area chart emphasizes the changing composition continuously across a time axis. These are not interchangeable with a line chart in every situation: the chart type affects how boundaries and the space between observations are perceived.

Common Problems and How to Fix Them

The shares do not total 100%. Check that the denominator includes every component that belongs in the whole, and confirm that all component values use the same units. Rounding displayed percentages can make a total appear to be 99% or 101% even when the underlying values add to 100%.
The percentage line looks flat. Confirm that the chart uses the calculated share columns, not the original values. Check the axis bounds too: a narrow or inappropriate scale can hide variation, while an automatic scale can exaggerate small changes.
The total and percentage series are hard to compare. They measure different things. Put them on clearly labeled axes, or separate them into two aligned charts. A secondary axis helps display both, but it does not make units directly comparable.
The chart includes an unwanted series. Select the chart and use Chart Design > Select Data to review the series and category labels. Ensure the month column is being used as the horizontal category labels rather than plotted as values.
There are gaps or unexpected results. Inspect blanks, text stored in numeric cells, zero totals, and negative values. Decide deliberately whether missing observations should appear as gaps or be handled another way; don’t replace missing data with zero unless zero is genuinely what it means.

Excel for Mac and Excel for the Web

The steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also supports combo charts and calculated-percentage series, though the ribbon layout and wording can vary by release. Look for the chart design or chart type controls after selecting the chart, and use the combo option if it is available. The worksheet formulas shown work in current desktop Excel on both Windows and Mac.

Excel for the web supports many chart-editing tasks, but chart-type choices and formatting controls can differ from the desktop app. If the web interface does not expose the needed combo subtype or axis settings, open the workbook in desktop Excel. The calculated-share approach remains useful because the percentage logic is in the cells, not hidden inside a chart setting.

Frequently Asked Questions

Can I use two 100% stacked lines in an Excel combo chart?
Excel can combine chart types, but a native 100% Stacked Line subtype may not be available as a per-series choice in the combo dialog. Calculating the shares in cells and plotting them as regular line series is a reliable alternative.

Why do my two percentage lines move in opposite directions?
If the two series are the only components and each category totals 100%, their shares are complements. When one rises, the other must fall by the same percentage-point amount. Plotting just one line may be clearer.

Can I show percentages and raw values on the same chart?
Yes. Use a combo chart and, if necessary, put the raw-value series on a secondary axis. Label both axes with their units. A separate aligned chart may be clearer if the two scales make the combo difficult to interpret.

Should I use a 100% Stacked Line or a 100% Stacked Area chart?
Use a 100% Stacked Line chart when individual percentage trends are the focus. Use a 100% Stacked Area chart when you want filled regions that emphasize the changing composition over time. Both show shares rather than the total size.

What if a category total is zero?
A percentage share cannot be calculated when the denominator is zero. Decide whether that category should appear as a gap or be omitted from the percentage view, and make the treatment clear to readers rather than implying that its shares are valid zero percentages.

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.