Stacked Bar Chart in Excel: Build It from a Table

Charts & pivot tables · Updated November 20, 2026

To make a stacked bar chart in Excel, arrange categories in the first column and the values for each series in separate columns, select the data, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. If your data is an Excel Table, the chart can also expand as you add rows. A stacked bar chart compares totals across categories while showing how each series contributes to each total.

What a stacked bar chart shows

A stacked bar chart uses horizontal bars, with each bar representing a category and each colored segment representing one data series. The segments sit end to end, so the full length of a bar represents the sum of its component values. For example, a department’s bar might combine online, in-store, and phone sales. You can compare total sales between departments and see the relative contribution of each channel.

This differs from a clustered bar chart, which places the series side by side for each category, and from a stacked column chart, which uses vertical bars. Excel also offers a 100% stacked bar chart. It scales every bar to the same length, making it useful for comparing proportions rather than totals. Because the 100% version hides differences in the underlying totals, it is not the best choice when total size matters.

One limitation is that only the first segment in each bar shares a common starting point. It is therefore easy to compare that series across categories, but harder to judge the values of segments in the middle. If comparing every series precisely is the main goal, a clustered chart or a table of values may communicate the data more clearly.

Prepare the worksheet data

Keep the data in a simple rectangular range: one header row, one category per row, and one series per value column. For example, a report of quarterly expenses could use this layout:

  • Column A: Department
  • Column B: Salaries
  • Column C: Supplies
  • Column D: Travel
  • Rows 2 through 6: department names and their amounts

Use clear, distinct headings because Excel uses the column headings for the legend. Avoid merged cells, blank rows inside the data, and totals mixed into the category list. If you include a grand-total row, Excel may plot it as another category and make the chart misleading. Keep totals outside the chart’s source range unless you deliberately want them displayed.

For a chart that is likely to grow, convert the range into an Excel Table. Select a cell in the data and use Insert > Table, confirm the range, and make sure My table has headers is checked. On Windows, Ctrl+T is a shortcut; on Mac, Command+T opens the Create Table dialog in Excel. A Table gives the data named headers and makes it easier to add records. When a chart is based on the Table, adding rows to it generally extends the chart’s source data as well.

Create the stacked bar chart

  1. Select a cell in the prepared data or select the complete range, including its headings.
  2. Choose Insert > Insert Column or Bar Chart on the ribbon.
  3. Under 2-D Bar, select Stacked Bar. Choose 100% Stacked Bar only if the purpose is to compare shares of category totals.
  4. Check that each bar represents a category and each legend entry represents a series. If Excel has interpreted the orientation the other way around, select the chart and choose Chart Design > Switch Row/Column.

Excel creates the chart on the worksheet, where you can move or resize it. The chart’s source range can be reviewed by selecting it and choosing Chart Design > Select Data. This dialog lets you inspect the category labels and series, add or remove a series, or edit the referenced ranges. It is a useful place to diagnose a chart that omitted a column or used the wrong labels.

For a quick start, the chart can be inserted before converting the range into a Table, but a Table is helpful when the dataset changes regularly. A chart created from a fixed cell range will not necessarily include new records typed below that range; check its source when adding data if the bars do not update.

Choose between stacked, 100% stacked, and clustered

Choose the chart type based on the question, not simply on how many columns of data you have. A standard stacked bar is appropriate when both total size and component values matter. For instance, it can show total monthly support requests by team while dividing each bar into request types. Teams with more requests have longer bars, and the segments show the composition.

A 100% stacked bar is better when the mix is more important than the total. It can reveal that one region gets a higher proportion of revenue from subscriptions even if its total revenue is smaller. Every bar is normalized to 100%, so compare percentages, not totals. A clustered bar works better when readers need to compare individual series across categories, because each series starts from the same baseline. The trade-off is that the overall total is less immediately visible.

Consider the number of categories and series, too. A chart with many narrow segments or a long legend can be difficult to read. Combining minor categories into an accurately named group may help, but do not combine values if doing so would conceal a meaningful difference. For long category names, horizontal bars are often easier to label than columns.

Format labels, colors, and axes

Give the chart a descriptive title that states what is measured and, where useful, the period or unit. Select the chart title and type the replacement. To add segment values, use Chart Design > Add Chart Element > Data Labels and select a position such as Inside End. Labels are helpful when there are only a few segments, but can overlap or become unreadable in a dense chart. For a 100% stacked chart, percentage labels can be more informative than raw amounts, depending on the available label options and the data.

Use a restrained, consistent color palette and make series colors distinct enough to tell apart. The legend identifies the colors; if its order is confusing, adjust the series order in Chart Design > Select Data. The series listed first is generally plotted nearest the category axis, though the visual order can also depend on axis settings. Check the finished chart rather than assuming that changing the legend alone changed the segment order.

Axis formatting can improve readability. Select the value axis to set number formatting, bounds, or tick spacing in the Format Axis pane. For a standard stacked chart, the horizontal value axis begins at zero in ordinary use; avoid altering the scale in a way that exaggerates differences. If category order appears upside down, right-click the category axis, choose Format Axis, and look for Categories in reverse order. The exact pane options can vary with chart type.

Show the data table below the chart

People asking for a stacked bar chart table in Excel may mean either a chart based on worksheet data or a table of the source values shown beneath the chart. These are different features. The worksheet range or Excel Table holds and organizes the data; the chart’s Data Table is a visual element attached below the plot. It does not replace the worksheet data and is not the same as converting the chart into a spreadsheet table.

To display values beneath the plot, select the chart and choose Chart Design > Add Chart Element > Data Table. Pick With Legend Keys or No Legend Keys, as appropriate. A data table can make exact values easier to look up while keeping the bars for a quick visual comparison. However, it takes up space and can become cramped when there are many categories or series. In that case, keep the worksheet Table nearby or use labels selectively.

Common problems and fixes

  • The bars show categories as series, or vice versa: Use Chart Design > Switch Row/Column. If that does not fix it, inspect the ranges in Chart Design > Select Data.
  • A heading appears as a bar: Check that the first row and first column were included correctly and that Excel recognized the headers. Remove any total row from the selected range if it should not be plotted.
  • New records do not appear: Confirm that the chart uses the Excel Table or expand its source range in Select Data. Typing below an ordinary fixed range does not guarantee that the chart will include the new row.
  • Some segments disappear or look unexpected: Check for blank cells, text in a value column, or negative values. Stacked charts can display positive and negative values on opposite sides of the axis, which may not read like a simple total. Verify the source values and consider another chart type if positive and negative amounts need direct comparison.
  • Labels overlap: Remove some labels, enlarge the chart, reduce the number of categories, or show the exact values in a data table instead. Avoid shrinking text until it is difficult to read.

When an Excel Table is filtered, charts based on it commonly reflect the visible data, but chart settings can affect whether hidden rows and columns are plotted. If filtering does not produce the expected view, select the chart and check Chart Design > Select Data and the chart’s hidden and empty cell settings. This is especially important when the chart is part of a dashboard.

When to use a different chart

Use a clustered bar chart if the key task is comparing separate series values across categories. Use a line chart when the horizontal dimension is time and the goal is to show a trend; stacking lines can obscure the shape of individual series. Use a 100% stacked chart for composition when category totals vary but proportions are the intended comparison. For a small number of categories and a need for exact values, a formatted worksheet Table may be clearer than any chart.

For a very large number of categories, consider whether the chart should show a subset, a summary, or a filtered view. Sorting categories by total can make a standard stacked chart easier to scan, but preserve a logical order such as chronology when that order matters. Chart selection is not just a visual preference: the scale and layout determine which comparisons readers can make accurately.

Frequently Asked Questions

Can I make a stacked bar chart directly from an Excel Table?
Select a cell in the Table and insert a 2-D Stacked Bar chart from the Insert tab. Excel uses the Table’s headings and values. Adding rows to the Table generally updates the chart, but check the chart’s source if a new record is missing.

What is the difference between a stacked bar and a 100% stacked bar?
A standard stacked bar preserves the segment values and the total length of each category. A 100% stacked bar makes every category the same length and shows each segment as a share of its category total, so it emphasizes proportions rather than total size.

How do I change the order of the stacks?
Select the chart and open Chart Design > Select Data. Select a series and move it up or down in the series list. Then check the chart, since the plotted order can also be affected by the category-axis settings.

Can a stacked bar chart show values in a table?
Yes. Select the chart and choose Chart Design > Add Chart Element > Data Table, then choose whether to include legend keys. This displays the chart’s values below it, but the original worksheet data remains the source.

Why does Excel show the wrong bars after I select my data?
Excel may have interpreted rows and columns in the opposite orientation, or included a heading or total as data. Try Chart Design > Switch Row/Column, then review the category and series ranges in Select Data.

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.