Excel Stacked Bar Chart With Two Data Sets Explained

Charts & pivot tables · Updated November 19, 2026

To make an Excel stacked bar chart with two data sets, arrange categories in the first column and the two data series in the next columns, select the range, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. Excel will draw one bar per category, with one series forming each segment. If you mean two separate stacked bars for each category, that requires a different data arrangement or a chart workaround.

What a stacked bar chart shows

A stacked bar chart combines the values of multiple series into one horizontal bar for each category. Each series appears as a colored segment, and the full bar represents the sum of those values. For example, if a region has 42 new customers and 28 returning customers, its bar has two segments and a total length of 70 customers. The chart makes both the parts and the overall total visible.

The wording can be confusing because people use data set to mean different things. In Excel chart terminology, a series is a column or row of values plotted together, while a category is a point along the chart’s category axis. If your two data sets are two components you want to add together within each category, a standard stacked bar is appropriate. If they are two independent scenarios or groups whose totals should be compared side by side, a regular stacked bar will combine them rather than keep them separate.

A bar chart uses horizontal bars, which is often helpful when category names are long. A stacked column chart uses the same stacking idea but displays vertical columns. Both are available as chart types in Excel; choose the orientation that makes labels and comparisons easiest to read.

Arrange the worksheet data

Use one row per category and one column per data series. Keep the column headings in the first row and category names in the first column, and avoid blank rows or columns inside the range. For instance, a small sales table could look like this:

Region New customers Returning customers
North 42 28
South 35 45
West 50 30

Here, each region is a category, and the two customer types are series. The North bar totals 70 customers, the South bar totals 80, and the West bar totals 80. Use meaningful, distinct series names because Excel uses those headings in the legend. Values should be numeric; text such as a dash in place of a missing value may be interpreted differently from a truly empty cell.

Check what the numbers represent before stacking them. Stacking is meaningful when the values are parts of a whole and can sensibly be added, such as units sold by product type or hours divided by work activity. It can mislead when the series overlap, use incompatible units, or represent separate measures such as revenue and profit. In those cases, use separate charts or another chart type rather than implying that the values form a total.

Create the stacked bar chart in Excel

  1. Place the headings, categories, and values in a continuous rectangular range, as in the example.
  2. Select the entire range, including the headings. If the data is an Excel table, selecting a cell in the table is usually enough for Excel to use its related data.
  3. On the ribbon, choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. Choose Stacked Bar, not 100% Stacked Bar, if you want the axis to show actual totals.
  4. Check that the category labels and series are assigned correctly. If the bars show regions as series or customer types as categories, select the chart and use Chart Design > Switch Row/Column.
  5. Replace the default chart title with a concise description, and make sure the legend identifies both series.

Excel may choose a chart layout based on the shape of the selected range, so inspect the result rather than assuming the orientation is right. To edit the source range or add a series, select the chart and use Chart Design > Select Data. In the Select Data Source dialog, you can change the chart data range, add or remove a series, and edit category-axis labels. On Excel for Mac, the ribbon layout and dialog appearance can differ, but the chart commands are available from the Insert and Chart Design tabs.

Choose between stacked and 100% stacked

A standard stacked bar keeps the original values and uses bar length to show the sum. It answers questions such as which regions had the most customers overall, and how much of each region’s total came from each customer type. The axis remains in the units of the source data.

A 100% stacked bar scales every category to the same full length. Each segment shows its share of that category’s total, so it is useful for comparing composition when the category totals differ. In the example, North is 60% new and 40% returning; South is 43.75% new and 56.25% returning. The 100% chart makes those proportions easier to compare, but it hides the difference between a total of 70 and a total of 80. Consider showing the totals in labels or a separate table if both the percentages and totals matter.

Neither version is automatically better. Use standard stacking when absolute amounts and totals are important. Use 100% stacking when relative shares are the main point. Avoid treating a 100% chart’s equal-length bars as evidence that category totals are equal: the chart deliberately normalizes them.

Format the chart so the comparison is clear

Formatting should help readers distinguish the series and understand what the bar lengths mean. Select the chart and use Chart Design > Add Chart Element to add or adjust items such as a chart title, axis titles, legend, and data labels. A useful title can state both the measure and the period, such as Customers by Region and Type, rather than simply Stacked Bar Chart.

  • Use a consistent, high-contrast color for each series. Keep the same series colors across related charts so readers do not have to relearn the legend.
  • Keep the legend visible unless direct labels make it unnecessary. With only two series, a legend is usually easy to scan.
  • Add data labels selectively. Labels on every segment can clutter a chart with many categories; labels are most useful when exact values matter and there are few bars.
  • Use axis titles or a note to identify units, such as dollars, orders, or hours. Do not rely on a title alone if the unit could be ambiguous.
  • Sort categories in a way that supports the comparison, such as descending by total, when that ordering is meaningful. For horizontal bars, Excel may place the first category at the bottom; axis formatting can change the category order if needed.

To change spacing, right-click a bar and choose Format Data Series, then adjust Gap Width in Series Options. A smaller gap makes bars look thicker, while a larger gap separates categories more clearly. Keep in mind that segment labels and series colors should remain legible if you resize the chart or paste it into a report.

When you need two separate stacked bars per category

A standard stacked bar chart does not create two independently stacked bars side by side for each category merely because the worksheet has four data columns. Excel will normally stack all four series into one bar per category. This is a common source of confusion: two data sets can mean two segments in one stack, or two groups that each contain their own stack.

For two groups, each with two components, one practical workaround is to create two rows for each category and use a second category field for the group name. For example, create columns for Region, Group, A component 1, A component 2, B component 1, and B component 2. Give each region two rows: one for Group A with values only in its two component columns, and one for Group B with values only in its two component columns. Leave the other group’s value cells empty. Select the whole range and insert a stacked bar chart. Each row becomes a bar, and the region and group fields can form a multi-level category axis. Review the plotted values and labels carefully, since the chart will contain four series and may need manual color adjustments so matching components look alike across groups.

This approach can work when the goal is to compare two stacks for each category, but it takes more setup and the legend may be less intuitive. For a report where the two groups must be unmistakably distinct, two aligned charts with the same axis scale can be clearer. Another option is to chart one group at a time and place the charts side by side. If the intended comparison is simply two totals rather than component makeup, a clustered bar chart may be more appropriate than stacking.

Common problems and how to fix them

  • The chart has one bar or the wrong categories. Confirm that the category labels and series headings were included in the selected range. Use Chart Design > Switch Row/Column if Excel interpreted rows and columns opposite to what you intended.
  • The bars show percentages instead of the source totals. You may have selected 100% Stacked Bar. Change the chart type through Chart Design > Change Chart Type and choose Stacked Bar.
  • The bars look like one solid color. Confirm that both value columns are numeric and that both series appear in the legend. In Chart Design > Select Data, verify that both series are included and their ranges are correct.
  • A segment is missing or unexpectedly small. Check for blank cells, text-formatted numbers, zeros, and values outside the selected range. A blank is not the same as a zero, and a text entry that looks like a number may not plot as expected.
  • Labels are crowded or overlap. Remove labels from some or all segments, enlarge the chart, or show values in a table beside it. Adding more labels does not always make a chart easier to interpret.

When a chart looks wrong, inspect the source range before changing colors or formatting. Most structural problems come from selection boundaries, row-versus-column orientation, or data that does not represent additive parts.

Frequently Asked Questions

Can I make a stacked bar chart with exactly two data series?
Yes. Put categories in one column and the two series in adjacent columns, select the data, and insert a Stacked Bar chart. Excel displays one bar per category with two segments, provided both series contain plottable values.

What is the difference between stacked bar and clustered bar?
A stacked bar places series segments end to end in one bar per category, showing a combined total and its parts. A clustered bar places series bars next to one another, making direct comparisons between series easier but not combining them into one total.

Can I show both the segment values and the total?
Data labels can show the segment values, but a normal stacked chart does not automatically label each bar with its total. You can calculate totals in the worksheet and add them as a separate annotation or use labels sourced from cells where that option is available, taking care not to suggest that the total is another segment.

Should I use a bar chart or a column chart?
Use a bar chart when long category names need room or a horizontal ranking is easy to scan. Use a stacked column chart when vertical bars fit the page or time categories run along the horizontal axis. Both can show stacked series; the direction changes, not the underlying meaning.

Why are my two data sets appearing in the same stack?
That is the expected result when you choose a standard stacked chart: every selected series is stacked for each category. If you intended two separate stacks per category, use a group-aware data layout such as the two-row workaround above, or use two aligned charts.

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.