Excel Stacked Column Chart With Two Data Sets Explained

Charts & pivot tables · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, place the two data sets in adjacent columns beside the same category labels, select the table, then choose Insert > Insert Column or Bar Chart > 2-D Column > Stacked Column. Each column in the chart represents one category, and its two colored segments show the values contributed by the two data sets.

How a stacked column chart works

A stacked column chart combines multiple series into one vertical column per category. The height of each segment represents one series value; the full column represents the sum of the series for that category. With two data sets, each column will normally have two segments, one for each series. For example, a chart of sales by month could show one segment for online sales and another for store sales.

This chart is useful when readers need to see both the total and how its parts contribute. It is less suitable when the main task is comparing one series across categories: the lower segment shares a baseline, but the upper segment starts at a different height in each column. That makes the upper values harder to compare precisely. A clustered column chart is often clearer for that purpose.

Arrange the two data sets in a shared table

Use one row per category and one column per data set. Put a heading at the top of each series column; Excel uses those headings in the legend. A simple example is monthly sales, shown in thousands of dollars:

Month Online Store
January 12 8
February 15 10
March 9 14

Excel will show total column heights of 20, 25, and 23, respectively. These totals are useful only if the two series use comparable units and the values are meant to be added. Do not stack percentages that describe different bases, or quantities that cannot sensibly be combined; the resulting column height would imply a meaningful total when there may be none.

  • Use clear, distinct series headings such as Online and Store rather than generic labels such as Series 1 and Series 2.
  • Keep the category labels consistent across both data sets. If one source has a category the other lacks, decide whether its value is genuinely zero or simply unknown before entering a zero.
  • Keep the data in a single rectangular range where possible. A blank cell, a text value in a numeric column, or a misplaced heading can make the chart incomplete or confusing.

Create the chart in Excel for Microsoft 365

  1. Arrange the headings and values as in the table above, with categories in the first column and the two series in the next columns.
  2. Select the complete range, including the headings. For the example, that means selecting the Month, Online, and Store columns and all three months.
  3. Choose Insert > Insert Column or Bar Chart > 2-D Column > Stacked Column. Select the ordinary Stacked Column option, not 100% Stacked Column.
  4. Check the result: the horizontal axis should list the categories, the legend should identify the two series, and each category should have one column with two segments.

If Excel puts the months in the legend and creates one column per series instead, select the chart and choose Chart Design > Switch Row/Column. This changes how Excel interprets the selected range; it does not alter the worksheet data. If labels or values are still wrong, check that the selected range includes the intended headings and that the first column contains the category names.

On Excel for Mac, the same chart type is available from the Insert tab’s column or bar chart controls, though the ribbon layout can differ by release. Excel for the web also offers column charts, but some formatting controls may be arranged differently or be less extensive than in the desktop app.

Choose between stacked and 100% stacked

Use Stacked Column when the actual totals matter. In the sales example, January’s total is 20 and February’s is 25, so the chart visibly shows February’s taller total. Segment sizes also show each series’ contribution, although comparing the upper segments across months takes more effort.

Use 100% Stacked Column when the question is about proportions rather than totals. Each column is normalized to the same height, so its segments show the share of the total represented by each series. In January, Online is 12 of 20, or 60%, and Store is 40%; in February, Online is 15 of 25, also 60%, while Store is 40%. The two columns would therefore have identical segment proportions even though their actual totals differ. This chart type does not communicate the difference between 20 and 25 as column height.

A common misconception is that 100% stacking is simply a more polished version of stacking. It answers a different question and hides total size. Choose it only when relative shares are the point, and consider adding labels or a separate total series if the underlying totals also need to be communicated.

Make the chart easier to read

Give the chart a title that names the measure and period, such as Monthly Sales by Channel. If a reader might not know what the numbers mean, add an axis title or include the unit in the chart title. Avoid relying on color alone to explain the data: use useful legend labels and choose colors with enough contrast. Keep the series colors consistent if you use the same categories in other charts.

To add labels, select the chart and use Chart Design > Add Chart Element > Data Labels, then choose a position such as Center or Inside End. Labels can make individual segment values easier to read, but too many labels can crowd a chart with many categories. If you want totals above the columns, do not assume that ordinary data labels will automatically show the sum of stacked segments; they usually label the component series. You may need to calculate totals in the worksheet and use a separate label technique, or show totals in a table beside the chart.

For a cleaner layout, select a segment and open Format Data Series to adjust its fill or the gap width between columns. Keep the gap wide enough to distinguish categories, but not so wide that the columns look unrelated. Remove unnecessary gridlines or a redundant legend only when the remaining chart still makes each value understandable.

When the data sets are in separate tables

If the two series are stored in separate places, first align them by category in a compact chart-source table. This is usually safer than selecting disconnected ranges, especially when the categories appear in a different order or one table is missing a month. The chart depends on category position: the second value in one series must correspond to the same category as the second value in the other.

You can build a separate summary range without changing the original records. Include one category column and one value column for each series, then create the chart from that summary. If you turn the summary range into an Excel Table using Insert > Table, adding rows to the table can make chart maintenance easier because the table expands as data is appended. Review the chart after structural changes, particularly if rows or columns have been inserted into the source.

Common problems and what to check

  • Only one series appears: Confirm that both value columns were included in the selected range and that each has a heading. Use Chart Design > Select Data to inspect the plotted series.
  • The chart shows categories as series: Try Chart Design > Switch Row/Column. Also verify that the source is laid out with one category per row and one series per column.
  • A value is missing or treated oddly: Check for text in a numeric cell, an unexpected blank, or a formula that returns an empty string. The right treatment depends on whether the value is zero, unavailable, or intentionally omitted.
  • Totals look too large or do not make sense: Make sure the series are additive and use compatible units. For instance, stacking a percentage with a dollar amount has no meaningful interpretation.
  • Negative values appear below the baseline: Excel plots negative values below zero rather than stacking them with positive values in the ordinary upward direction. When the series contain both positive and negative amounts, explain that convention and check whether the resulting net visual is appropriate for the comparison.
  • The upper series is hard to compare: That is a limitation of stacking, not necessarily a formatting error. Use a clustered column chart when direct comparison of the two series is more important than showing their combined total.

Stacked versus clustered columns

A stacked chart uses one column per category and places the series on top of one another. A clustered chart puts the series side by side within each category. With two series, the stacked chart emphasizes each category’s combined amount and its composition; the clustered chart makes it easier to compare the two values directly because both columns start from the same baseline.

For example, use stacked columns if a manager wants to compare total monthly sales and see the online/store mix. Use clustered columns if the main question is whether online sales exceeded store sales in each month. A line chart may be a better choice when the goal is to emphasize change over many time periods, particularly if there are many categories. No chart type is universally best; choose based on the comparison the reader needs to make.

Frequently Asked Questions

Can I make a stacked column chart with exactly two data sets?
Yes. Select the category labels and both value series, including their headings, then insert a Stacked Column chart. Excel creates one segment per series for each category.

What is the difference between stacked and 100% stacked?
A regular stacked chart uses the actual values, so column heights show totals. A 100% stacked chart makes every column the same height and shows each series as a proportion of its category total.

Can the two data sets have different category labels?
They can, but they should be aligned in a shared source table before charting. Put each category on its own row and place the matching value for each series in the appropriate column; decide carefully whether a missing value means zero or unknown.

How do I show the total above each stacked column?
Standard data labels generally show the values of the individual series, not the sum. Add or calculate a total for each category and use an appropriate separate label method, or place the totals in a nearby table. Verify the displayed total against the source values.

Why does Excel show the wrong series or category order?
Excel may have interpreted rows and columns in the opposite direction from what you intended. Select the chart and use Chart Design > Switch Row/Column, then check the headings and category labels in Chart Design > Select Data.

Final checks before sharing

Before sending the workbook, verify that the chart title states what is measured, the legend names both data sets, and the category labels are readable. Confirm that the chosen chart type communicates the intended comparison and that its totals are meaningful. If readers need exact values, pair the graphic with a data table or labels rather than asking them to estimate values from column heights.

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.