Stacked Bar Chart Data in Excel: Setup and Tips

Charts & pivot tables · Updated November 20, 2026

To make a stacked bar chart in Excel, put each category in its own row, each data series in a separate column, and select the full table before choosing Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. Excel then draws one horizontal bar per category and divides it into colored segments for the series.

What a stacked bar chart shows

A stacked bar chart compares totals across categories while showing how each total is composed. For example, a row might represent a product and its segments might represent sales from three regions. Segment lengths encode the regional values; the whole bar represents the sum for that product.

The chart is most useful when readers need to compare overall amounts and see the parts contributing to them. It is less effective when the main task is comparing one particular series across many categories, because most segments do not start at a shared zero point. A segment in the middle of each bar is harder to compare accurately than a bar in a regular bar chart.

Excel offers both horizontal stacked bars and vertical stacked columns. The underlying data arrangement is usually the same; the difference is the direction in which categories and values are plotted. A horizontal chart is often a good fit for long category names, while a stacked column chart can suit a small number of time periods.

Arrange the data before inserting the chart

Use a simple rectangular table with one header row. Put category names in the first column and the numeric series in the columns to their right. Each numeric column becomes a differently colored series, and each data row becomes a bar. Avoid blank rows and columns inside the selected range, since they can cause Excel to interpret the data incorrectly.

For example, a table could have Product in A1, North in B1, South in C1, and West in D1. Rows 2 through 5 could contain four product names and each region’s sales. Select A1:D5, including the headers. Excel uses the first column for category labels and the top row for series names.

  • Use descriptive, distinct column headings; they become the chart legend labels.
  • Keep values numeric. Text that looks like a number may not be plotted as expected.
  • Use consistent units, such as dollars throughout or units throughout, rather than mixing measures.
  • Include zeros where a category has no value for a series if the row and column structure needs to remain complete.

If your data is laid out in the opposite direction, with series down rows and categories across columns, Excel may still chart it, but the result may not match your intended grouping. You can change the interpretation after insertion with Chart Design > Switch Row/Column.

Create a stacked bar chart in Excel

In Microsoft Excel for Microsoft 365 on Windows, select the complete table, including its headings. Then use these steps:

  1. Choose Insert on the ribbon.
  2. Select Insert Column or Bar Chart in the Charts group.
  3. Under 2-D Bar, choose Stacked Bar. Do not choose 100% Stacked Bar unless you want to show proportions rather than original amounts.
  4. Check that the category names appear beside the bars and that the legend lists the intended series.
  5. Use Chart Design > Add Chart Element to add a chart title, axis titles, or data labels if they help explain the result.

Excel normally creates the chart on the same worksheet as the selected data. To move it, use Chart Design > Move Chart. On Mac, the chart choices are also on the Insert tab, though the ribbon layout can vary slightly by version. Excel for the web supports creating charts, but some detailed formatting controls may be more limited than in the desktop app.

Choose between stacked and 100% stacked

A standard stacked bar chart preserves the values and displays the combined total as the full bar length. Choose it when the totals matter—for example, comparing the number of orders received by different departments while also showing each order type.

A 100% stacked bar chart rescales every category to the same total length. Each segment represents that series’ share of the category total, so the chart highlights composition rather than absolute size. It is useful when comparing percentages across groups of different sizes, but it hides differences in the totals themselves. A category with 20 units and one with 200 units both fill the same width.

Use a regular clustered bar chart instead when comparing individual series values is more important than showing the total composition. In a clustered chart, series sit beside one another rather than stacking. That gives each series a common baseline, which makes direct comparisons easier, though the category total is less visually immediate.

Example: sales by product and region

Suppose a worksheet lists four products and sales in three regions. The rows are products, and the columns are North, South, and West. A stacked bar chart creates four bars. Each is split into three regional segments, with the total bar length showing combined sales for that product. The legend identifies which color represents each region.

If Product A has 40 units in North, 25 in South, and 35 in West, its complete bar represents 100 units. Product B might have 60, 20, and 20, also totaling 100. The two bars have equal lengths, but their segment patterns differ. That makes the chart good for seeing both equal totals and different regional mixes. If you instead need to determine precisely which region sold more across all products, a small summary table or clustered chart may communicate that comparison more clearly.

For a useful chart title, name both the measure and the grouping, such as Sales by Product and Region. If the values are in thousands or represent a particular period, state that in an axis title or subtitle. Context prevents a chart from being interpreted as raw counts when it actually shows a scaled measure.

Add labels and make the chart readable

Data labels can show values inside or beside segments. Select the chart, then use Chart Design > Add Chart Element > Data Labels and choose a position. Labels are helpful when there are only a few categories and the segments are large enough to contain readable text. With many short segments, labels collide or become too crowded; the axis and legend may be clearer without them.

To change what a label displays, right-click a label and choose Format Data Labels. In the Format Data Labels pane, select options such as Value or Series Name. In current Microsoft 365 desktop Excel, Value From Cells can use worksheet cells as custom label text; it is useful for showing carefully prepared labels, but the source cells need to be maintained as the data changes.

Excel’s standard stacked chart shows the segment values, but it does not automatically put the sum at the end of every bar as a total label. If totals are important, calculate them in a separate column and use a deliberate labeling method, such as custom labels from cells where available, or display totals in a nearby table. Check the output after edits, because custom labels may not update in the way readers expect if the linked range changes.

Keep the series colors consistent and distinguishable. Order the series thoughtfully because the first series is generally nearest the axis and later series stack outward. Use a legend with the same wording as the table headers, and remove unnecessary gridlines or decorative effects that compete with the data.

Common problems and how to fix them

  • The series and categories are reversed: Select the chart and choose Chart Design > Switch Row/Column. Check the legend and category axis afterward to confirm the change.
  • The chart includes the wrong cells: Select the chart and use Chart Design > Select Data to inspect the chart data range. Include the headings and intended rows, but exclude notes or summary rows that should not be plotted.
  • Category labels appear in an unexpected order: Excel plots categories according to the source order, but a horizontal chart can display them in a direction that looks reversed. Right-click the category axis, choose Format Axis, and check Categories in reverse order under Axis Options.
  • Some values do not appear: Look for numbers stored as text, blank cells, or an incorrect selected range. Correct the source cells and check the chart again.
  • One segment dwarfs the others: Confirm that all series use the same unit and scale. A very large value may be valid, but a misplaced total, percentage, or value in different units can distort the whole chart.
  • The chart is difficult to read: Reduce the number of series or categories, widen the chart, simplify labels, or switch to a different chart type. Stacking many thin segments makes comparisons difficult.

Negative values need special care. Excel can plot negative and positive values in a stacked chart on opposite sides of the zero line, but a mix of signs can make category totals and segment relationships less intuitive. If positive and negative contributions are central to the story, test the chart with the actual data and explain the zero baseline clearly.

When to use another chart

Use a stacked bar chart when the audience needs to see both category totals and the parts that make them up, and when the series are meaningfully additive. If the components are not parts of a coherent total—for example, unrelated measurements with different units—stacking them is misleading.

Consider a clustered bar chart for direct comparisons between series, a 100% stacked chart for relative composition, or a line chart for a trend over many time points. A table can be better than any chart when readers need exact values for many categories. One practical approach is to pair a chart with a compact data table so the visual pattern and precise numbers are both available.

Frequently Asked Questions

What data layout is best for a stacked bar chart in Excel?
Put one category per row in the first column, one series per column to the right, and series names in the header row. Select the headings and all relevant values before inserting the chart.

How do I switch the rows and columns in an Excel stacked bar chart?
Select the chart and choose Chart Design > Switch Row/Column. This changes which table dimension Excel treats as categories and which it treats as series.

What is the difference between stacked bar and 100% stacked bar?
A standard stacked bar preserves and compares actual totals. A 100% stacked bar makes every category the same length and compares each series’ share of its category total.

Can Excel show totals on stacked bars?
Excel does not automatically add a total label to each full bar in the usual stacked chart. You can show segment labels and use a separate total column, or create custom labels from cells in supported desktop versions.

Why does my stacked bar chart show the wrong series?
The selected range or data orientation may be wrong. Use Chart Design > Select Data to inspect the range, and try Chart Design > Switch Row/Column if Excel has assigned categories and series in the opposite direction.

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.