Stacked Bar Chart in Excel With Two Variables

Charts & pivot tables · Updated November 20, 2026

To make a stacked bar chart in Excel, arrange each bar category in one column, put the values for each stack segment in separate columns, select the table, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. One important distinction: a stacked chart needs a category, numeric values, and at least two series to stack, even when people casually describe the data as having two variables.

What a stacked bar chart shows

A stacked bar chart uses horizontal bars to show totals split into components. Each bar represents a category, such as a region, month, or department. The colored sections within it represent series, such as product types or expense categories. The length of each section corresponds to its value, and the full bar represents the sum of those values for that category.

That structure explains a common source of confusion about two variables. If you have one categorical field and one numeric field—for example, a list of regions and one sales figure for each—you have enough information for a regular bar chart, but not a meaningful stack. To make stacked bars, you generally need a second grouping field that divides the numeric measure into components. For example, region and product type are two categorical fields, while sales is the numeric measure.

In everyday spreadsheet language, someone may call those two grouping fields the two variables. Technically, though, the chart displays two categorical dimensions plus a numerical measure. Alternatively, if you already have a category column and two numeric columns, those two numeric columns can be the stacked series. The right layout depends on what the data means, not just how many columns it has.

Arrange the data before inserting the chart

For a small, tidy table, place the main bar categories in the first column and each series in its own column. Use a single header row, avoid blank rows or columns inside the range, and keep each value numeric. For example, a table of sales by region and product might look like this:

  • Column A: Region
  • Column B: Basic
  • Column C: Plus
  • Column D: Premium

Put North, South, and West in the rows below Region, with the corresponding sales values under Basic, Plus, and Premium. Excel will make one bar for each region and use the three product columns as the segments. The bar total is the sum of the three product values for that region.

Make sure the numbers use consistent units and cover the same period. Mixing monthly sales in one series with annual sales in another makes the result misleading, even if Excel draws a chart without warning. If the cells contain text such as a currency symbol typed into the value, Excel may treat them as text rather than numbers. Apply number formatting to numeric values instead of entering extra characters into the cells.

Create a stacked bar chart in Excel

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

  1. Choose Insert > Insert Column or Bar Chart.
  2. Under 2-D Bar, select Stacked Bar. Choose 100% Stacked Bar only if you want to compare proportions rather than amounts.
  3. Check that the category names appear along the vertical axis and the series appear in the legend.
  4. Select the chart and use Chart Design > Add Chart Element to add a title, axis titles, or data labels if they help readers interpret it.

The standard Stacked Bar chart keeps the values on a numeric horizontal axis and draws categories vertically. Stacked Column is the vertical version; it can show the same data, but the bars rise upward instead of extending sideways. Bar charts often suit long category names because the labels have more room.

If Excel treats the series and categories the wrong way around, select the chart and choose Chart Design > Switch Row/Column. This is useful when a table is transposed, but it cannot create a valid second grouping variable where none exists. If the chart still looks confusing, check that the selected range includes the headers and only the intended data.

On Excel for Mac, the chart commands are also on the Insert tab, although button placement and labels can vary by release. Look for the bar-chart options, select Stacked Bar, and use the chart design controls that appear when the chart is selected. Excel for the web also supports common chart creation and editing, but some formatting controls may be more limited than in the desktop app.

Choose between stacked and 100% stacked

Use a regular Stacked Bar when the total amount matters as well as the composition. If North sold 100 units and South sold 250, the full bar lengths preserve that difference, while the segments show how each product contributed to each total. This is a good fit for counts, quantities, or amounts that share a consistent unit.

Use 100% Stacked Bar when the main question is how each category is divided proportionally. Excel scales every bar to the same full length, so a bar with 40% Basic and 60% Plus is directly comparable with another bar showing 70% Basic and 30% Plus. The chart no longer communicates the difference between a small total and a large total: both bars reach 100%.

Neither version is automatically better. If readers need to see both totals and percentages, consider using a regular stacked chart with totals shown in a nearby table, or make a separate percentage chart. Avoid interpreting segment lengths as percentages on a standard stacked chart unless you calculate the proportions yourself; the chart is plotting the original values.

Make the chart easier to read

A stacked chart can become hard to interpret when it has too many series, too many categories, or colors that look alike. A few practical choices make a bigger difference than decorative effects:

  • Use a short, descriptive chart title that states what is being compared and, where useful, the time period.
  • Keep the same series in the same color across related charts, and choose colors with enough contrast.
  • Order the series intentionally. The first series is placed at the axis end of each bar, and later series build outward from it.
  • Sort categories by a meaningful value or a natural sequence, such as month order, rather than leaving an accidental order.
  • Add data labels only when they remain legible. Labels on many small segments can overlap or clutter the chart.

To add a legend or labels, select the chart and choose Chart Design > Add Chart Element, then choose the relevant element and position. If category names are difficult to scan, you can adjust their order through the source data or chart-axis formatting. If the axis appears in an unexpected order, right-click it, choose Format Axis, and review the axis options; Excel may place the first category at the bottom or top depending on the chart settings.

Keep in mind that only the first segment in each bar shares a common starting point. The next segment starts where the previous one ends, so it is harder to compare that middle segment across categories by eye. Data labels can help when exact values matter, but they do not remove this visual limitation.

Build the chart from a long data list

Real-world data often arrives as a list with one row per observation rather than as a cross-tabulated table. For instance, columns might be Region, Product, and Sales, with repeated region and product names on separate rows. That format is useful for recording and filtering data, but Excel’s basic chart insertion may not automatically summarize repeated combinations into one bar segment.

A PivotTable can summarize that list. Select a cell in the data and choose Insert > PivotTable. In the PivotTable Fields pane, place Region in Rows, Product in Columns, and Sales in Values. Check that the Values field is summarized as Sum if sales amounts should be added; Excel may choose Count when values are stored as text or otherwise fail to recognize them as numeric.

Once the PivotTable has the intended layout, select a cell in it and choose PivotTable Analyze > PivotChart. Select Bar and then Stacked Bar. A PivotChart is connected to its PivotTable, so filtering the fields can update the chart. This method is helpful when the source has many observations or needs filtering by additional fields such as year or channel.

For a simple chart, a normal summary table may be easier to maintain than a PivotTable. The key is to aggregate the source first so each category-series combination has one value. If the source data changes, refresh the PivotTable using PivotTable Analyze > Refresh, or update the summary table before relying on the chart for a report.

Common problems and what they mean

  • The chart has one color per category instead of several segments. Excel may have interpreted rows and columns in the opposite direction, or the selected range may not include multiple series columns. Try Switch Row/Column and inspect the source layout.
  • The chart shows a single segment per bar. There may be only one numeric series. A category column plus a single value column supports a bar chart, but there is nothing to stack unless the values are split into meaningful components.
  • Values are missing or unexpectedly counted. Check for numbers stored as text, blank cells, or inconsistent units. In a PivotTable, confirm the Values field uses Sum rather than Count when addition is intended.
  • The bars appear to total more than expected. A stacked chart adds all series in each category. Check whether the source includes subtotals alongside their underlying components, which would double-count the total.
  • Some segments are nearly invisible. A very small value may be legitimate, or a large series may dominate the scale. Check the values and units before changing the chart, then consider a different chart type if the small components are important.

Also distinguish missing values from zero. A zero means there was no amount for that series-category combination; a blank may mean the value is unknown or was not recorded. Those meanings are not interchangeable in analysis. Decide how the data should be represented before filling blanks, because replacing unknown values with zero can change totals and the story the chart tells.

When another chart may work better

Stacked bars are useful for showing part-to-whole composition across categories, especially when the overall totals also matter. They are less effective when the main task is comparing every series precisely across many categories. Since most segments do not begin at the same baseline, their lengths are harder to compare than bars in a clustered chart.

Consider a clustered bar chart when comparing product sales side by side within each region is more important than showing each region’s combined total. Consider a line chart for trends over time when continuity and direction are the focus. A table may be clearer when the dataset is small and readers need exact values for every category and series. With many series, splitting the chart into smaller, consistent charts can also improve readability.

Do not use a stacked chart just because the source has multiple columns. The components should be additive and conceptually belong to the same whole. For example, categories of spending that add to a department’s total can be stacked; unrelated measures such as revenue and customer satisfaction should not be, because their values do not form a meaningful combined total.

Frequently Asked Questions

Can I make a stacked bar chart with only two columns?
Usually not as a meaningful stacked chart if the columns are one category field and one numeric measure. You need at least two numeric series for the segments, or a second grouping field that lets Excel summarize the measure into multiple series.

What is the difference between stacked bar and 100% stacked bar in Excel?
A standard stacked bar preserves the actual totals and shows their component values. A 100% stacked bar makes every category the same length and shows each component’s share of that category’s total, so it does not show which category has the larger total.

How do I change the order of the stacks?
Change the order of the series in the source table, or select the chart and use Chart Design > Select Data to manage the series order. The first series starts at the axis side of each bar, so changing the order changes the visual stacking.

Why is Excel showing the wrong labels or legend?
Excel may have read rows as series and columns as categories, or vice versa. Select the chart and try Chart Design > Switch Row/Column, then confirm the category labels and series names in Chart Design > Select Data.

Can a stacked bar chart show percentages and totals at the same time?
A standard stacked chart shows totals and component values, not normalized percentages. A 100% stacked chart shows percentages but makes all totals equal in length. If both are essential, use a separate summary table or a second chart rather than implying that one version communicates both clearly.

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.