Excel Pivot Chart Stacked Column: How to Create One

Charts & pivot tables · Updated November 19, 2026

To create a stacked column PivotChart in Microsoft Excel for Microsoft 365 on Windows, click inside a PivotTable, choose PivotTable Analyze > PivotChart, select Column, then Stacked Column, and click OK. The chart shows each category as a column divided into colored series, with the segment heights representing the values summarized in the PivotTable.

What a stacked column PivotChart shows

A PivotChart is a chart connected to a PivotTable. Its categories, series, and values are controlled by the fields in the PivotTable, so filtering or rearranging those fields changes the chart as well. A stacked column chart places the series on top of one another within each category. The full column height is the total across its segments, while each color indicates one series.

For example, imagine a sales table with Date, Region, Product, and Sales columns. A chart with months as categories, regions as series, and summed Sales as values can show monthly sales totals split into regional contributions. It is useful when you want to compare both the overall total and the parts making up that total. The trade-off is that only the bottom segment shares a common baseline; comparing the height of other segments across columns can be harder.

Prepare the source data

Good source data makes the PivotChart easier to build and less likely to produce confusing results. Keep the source in a rectangular list: one header row, one record per row, and one field per column. Avoid blank header cells, merged cells, and separate subtotal or grand-total rows in the middle of the records. Give each column a distinct, descriptive name, such as Month, Region, and Sales.

Each row should represent one observation at a consistent level of detail. For instance, if one row is a product sale in one region on one date, avoid mixing in rows that are already monthly regional totals. Mixing detailed records with pre-summarized totals can cause the PivotTable to count or add values twice.

  • Use consistent labels for categories: North and NORTH may be treated as separate items.
  • Store numeric measures such as sales or units as numbers, not text that merely looks numeric.
  • Include a date field as real Excel dates if you want to group by month, quarter, or year.
  • Consider converting the source range to an Excel Table with Insert > Table. A Table can expand when you add rows, although you still need to refresh the PivotTable to include new records.

Create the stacked column PivotChart

On Windows, first create a PivotTable if you do not already have one. Select a cell in the source data, choose Insert > PivotTable, confirm the table or range and destination, then click OK. Drag the relevant fields into the PivotTable Fields areas. For the sales example, place Month in Rows, Region in Columns, and Sales in Values. Excel will generally summarize a numeric field by Sum; check the value field setting if the result is not what you expect.

With a cell in the PivotTable selected, choose PivotTable Analyze > PivotChart. In the Insert Chart dialog, select Column and then Stacked Column, and confirm with OK. If starting from a selected source range, Insert > PivotChart may also be available; choose the PivotChart option rather than a standard chart when prompted. The exact dialog appearance can vary slightly by Microsoft 365 update.

On Excel for Mac, select a cell in the PivotTable and use the PivotTable Analyze tab to insert a PivotChart; chart selection is made in the chart dialog. Ribbon placement and dialog details can differ between Mac and Windows releases. Excel for the web has a different interface and may not expose every PivotChart editing option described here, so use the desktop app if a needed control is missing.

Arrange fields to tell the right story

The field placement determines what the chart compares. In the PivotChart Fields pane, Axis (Categories) defines the labels along the horizontal axis, Legend (Series) defines the colored stack segments, and Values supplies the measurement. Filters can limit the records included in the chart. In many layouts, moving a field between Axis and Legend changes the reading of the chart substantially even though the underlying numbers are unchanged.

For a monthly regional sales chart, put Month in Axis, Region in Legend, and Sales in Values. If the months appear alphabetically or the axis is too crowded, verify that Month is a real date field and consider grouping it in the PivotTable. Right-click a date in the PivotTable and choose Group to select intervals such as months or quarters when the option is available. If you have text labels like Jan, Feb, and Mar rather than dates, Excel may sort them alphabetically; a proper date field or a custom sort order can help.

Keep the number of series manageable. A stack with two to five meaningful groups is often easier to read than one with dozens of thin segments. If a field has many unique items, filter it, group related items where appropriate, or choose another chart style.

Choose between stacked and 100% stacked

Stacked Column is best when both composition and totals matter. If monthly sales grow from 10,000 to 20,000, the columns can show that growth while their colored segments show each region’s contribution. Because the total height changes, however, a segment’s height alone does not make percentage comparisons easy.

Use 100% Stacked Column when the main question is how the mix changes, not how the total changes. Each column is normalized to 100%, so the segments show relative shares. This makes proportions easier to compare, but hides changes in the overall total: two columns of very different sales can be the same height. A regular Stacked Column preserves totals; a 100% Stacked Column emphasizes distribution. Neither is universally better—the choice follows the question.

A standard clustered column chart is another option when comparing the absolute value of each series across categories matters more than showing the total. The bars sit side by side, making individual series easier to compare, but the overall total is less visually immediate.

Filter, refresh, and interact with the chart

PivotCharts work with PivotTable filters and field controls. Depending on the chart and layout, buttons on the chart let viewers filter a field or change which items appear. You can also use the PivotTable Fields pane to move fields, and add a slicer from PivotTable Analyze > Insert Slicer. A slicer provides clickable buttons for filtering a field such as Region or Product. If a slicer should control more than one PivotTable, use Slicer > Report Connections or PivotTable Connections and select compatible PivotTables that share the same source or PivotCache.

When source records change, refresh the PivotTable so the PivotChart reflects the updated summary. Right-click the PivotTable and choose Refresh, or use Data > Refresh All to update workbook connections and PivotTables. A PivotChart does not necessarily expand its source automatically when data is appended outside a fixed source range. Using an Excel Table as the source helps include new rows after refresh. If the source range itself must be changed, select the PivotTable and choose PivotTable Analyze > Change Data Source.

Format the chart for clear reading

Give the chart a specific title, such as Monthly Sales by Region, and use axis labels or units where the context is not obvious. To change the chart title, select it and type the replacement. Use a restrained color palette and keep the same color assigned to a series throughout the chart. If color carries meaning in a report, include labels or a clear legend so that the information is not conveyed by color alone.

Currency values may need a currency number format, while large values may be easier to scan when shown in thousands or millions. Format the underlying PivotTable value field where suitable: right-click a value, choose Value Field Settings, then Number Format. This is generally more dependable than formatting individual displayed cells, because the PivotTable can rearrange or refresh. Add data labels only if they improve interpretation; labels on every segment of a crowded chart can make it harder to read. For a small chart, prioritize a useful title, readable categories, and a visible legend.

Common problems and how to fix them

  • The chart shows Count instead of Sum: The source values may contain text or blanks, or Excel may have detected the field as nonnumeric. Check the source data, then right-click a value in the PivotTable, choose Summarize Values By, and select Sum if it is available.
  • The chart omits new rows: Refresh the PivotTable and confirm that the new rows are inside its source range. An Excel Table source can reduce this problem when data is appended.
  • There are too many colors or tiny segments: Filter or regroup the series field, or use a different visualization. A long list of categories is rarely improved by stacking all of them.
  • Months appear in the wrong order: Use real Excel dates and group them, or set a suitable sort order for text categories. Alphabetical sorting is not chronological sorting.
  • The total is hard to compare with a segment: Remember that segments above the bottom do not share a baseline. Use data labels, a different chart type, or a separate view if precise comparisons between individual series are essential.

One common misconception is that a PivotChart is simply a normal chart with a PivotTable behind it. Its field controls and filtering behavior are tied to the PivotTable, which is why changing the table layout can also change the chart. If you need a chart with a manually chosen data range, custom series combinations, or a specialized chart type, a regular chart based on a separate summary table may offer more control.

When to use a regular chart instead

Choose a PivotChart when the data needs interactive filtering, quick regrouping, or an exploratory summary that changes as fields are moved. Choose a standard chart when the presentation needs a fixed layout, a custom combination of chart types, or data points arranged independently of a PivotTable. A practical compromise is to create a PivotTable for summarizing, then build a standard chart from a separate, carefully maintained summary range when you need more formatting control. That approach takes extra upkeep and will not behave exactly like a PivotChart when users filter fields.

Frequently Asked Questions

Can a PivotChart be a stacked column chart? Yes. Select a PivotChart and choose Column > Stacked Column in the chart type dialog. The chart will stack the series selected by the PivotTable fields.

How do I change a PivotChart from clustered to stacked columns? Select the chart, open Chart Design > Change Chart Type, choose Column, then select Stacked Column and confirm. The chart remains connected to its PivotTable.

Can I make a PivotChart 100% stacked? Yes. In the chart type choices, select 100% Stacked Column. This displays each category as a normalized 100% total, so it compares proportions rather than absolute totals.

Why does my PivotChart show counts instead of totals? Excel may be treating the value field as text or otherwise not recognizing it as numeric. Check the source column for text values, then set the PivotTable value field to Sum if available.

Does a PivotChart update when I add data? It updates after its PivotTable is refreshed, provided the added records are included in the PivotTable source. An Excel Table is a convenient source for expanding data, but refreshing is still needed.

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.