Yes. To compare years in Excel, put each year in its own row and each component you want to compare in a separate column, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. The result shows one bar per year, with colored segments representing the components; it lets you compare both each year’s total and how that total is divided.
What a stacked bar chart shows
A stacked bar chart combines several related values into one bar for each category. In a year-over-year comparison, the years are the categories, and the series are the parts that make up each year’s value. For example, a retailer could show each year as a bar and use segments for clothing, home goods, and electronics. The full bar represents that year’s combined sales, while each colored section represents one department.
In a standard stacked bar chart, bar length encodes the total. This makes it useful when the total matters alongside the composition. If you need to compare the same component across years, however, remember that only the first segment starts at the same zero point. Other segments begin where the preceding segment ends, so their lengths can be harder to compare precisely.
Excel’s stacked bar chart is horizontal: the years appear on the vertical category axis, and values extend along the horizontal axis. A stacked column chart uses the same stacking idea but displays vertical columns. Either can compare years; choose based on which layout suits your labels and page space.
Arrange the data before inserting the chart
Use a simple rectangular table, with one header row and one row per year. Put the year labels in the first column and the series names in the remaining column headings. For example:
| Year | Clothing | Home goods | Electronics |
|---|---|---|---|
| 2022 | 40 | 35 | 25 |
| 2023 | 45 | 40 | 30 |
| 2024 | 50 | 38 | 32 |
Here, the totals are 100, 115, and 120 respectively. Excel uses the first column for category labels and each other column as a series. Clear, distinct headers help Excel assign useful legend names automatically.
- Keep the data contiguous, without blank rows or columns splitting the table.
- Use numeric values for the charted measurements, not numbers stored as text.
- Use the same unit and measurement basis for every year, such as thousands of dollars throughout.
- Include all the years and series you want to compare in the selected range, including the headings.
If your source data has one row for every transaction rather than an already summarized yearly table, first summarize it by year and component. A PivotTable is one practical option: select the source data, choose Insert > PivotTable, put the year field in Rows, the component field in Columns, and the numeric measure in Values. Then create the chart from the summarized results. Check that the value field is being summed, rather than counted, when a sum is intended.
Create the chart in Excel for Microsoft 365
- Select the complete table, including its header row and year labels.
- On the Windows ribbon, select Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. Choose Stacked Bar, not 100% Stacked Bar, unless you specifically want to compare proportions.
- Review the chart. The legend should list the component headings, and the category axis should list the years.
- Select the chart title and type a descriptive title, such as Annual Sales by Department.
- If Excel has assigned the years and series in the wrong direction, select the chart and choose Chart Design > Switch Row/Column.
On Excel for Mac, select the data and use the Insert tab to choose the bar-chart options and Stacked Bar; ribbon placement and menu wording can vary slightly by release. The chart’s Switch Row/Column control is also available when the chart is selected, although its location may differ. In Excel for the web, select the data and use Insert to choose a bar chart, then check the chart type and series orientation.
To change the chart after inserting it, select it and open Chart Design > Change Chart Type. This is useful if you accidentally inserted a clustered chart or want to test a 100% stacked version without rebuilding the data range.
Choose between stacked, 100% stacked, and clustered
The right chart depends on the question you want the reader to answer. A standard stacked chart preserves the actual totals and their components. In the example, viewers can see that the combined figure rises from 100 in 2022 to 120 in 2024. The growing bar length is meaningful because the underlying totals are retained.
A 100% stacked bar chart scales every bar to the same length. Its segments show each component’s share of that year’s total, not the component’s raw amount. This is a better choice when the question is whether the mix changed—for instance, whether electronics account for a larger share—even if yearly totals differ. It hides the total size, so pair it with a separate total label or another view if totals matter too.
A clustered bar chart places series side by side for every year rather than stacking them. It is often easier to compare a particular department across years because each bar starts from zero. The trade-off is that the combined yearly total is less visually immediate. Use clustered bars when comparing individual series is the main task; use stacked bars when both the total and its composition are important.
Make the chart easier to read
Keep the legend order and segment order consistent with the way readers are likely to interpret the data. Excel generally follows the series order in the source table. If the order is distracting, rearrange the series in the table or use Chart Design > Select Data to adjust the series. Choose colors with clear contrast, but avoid assigning similar shades to adjacent segments.
Useful finishing steps include:
- Add axis titles when units are not obvious, such as Revenue (thousands of dollars).
- Use Chart Design > Add Chart Element > Data Labels when the exact component values are important and the segments are large enough to hold labels.
- Keep the legend visible and use plain-language series names.
- Format the value axis with an appropriate number format, such as currency or whole numbers, so values are not misleadingly displayed.
- Use a concise title that identifies the measure and period, rather than a generic title such as Chart Title.
Data labels on a stacked chart usually identify the value of each segment, not the total at the end of the whole bar. If readers need both, explain that distinction or provide totals in a nearby table. Very small segments can make labels overlap or become unreadable; removing those labels is often clearer than shrinking the type excessively.
Common problems and how to fix them
- The years appear in the legend. Excel may have read rows as series and columns as categories. Select the chart and choose Chart Design > Switch Row/Column. For the example table, years should be categories and the three department headings should be series.
- The chart shows one bar per department instead of one per year. Check the selected range and the row/column orientation. Switch Row/Column or revise the source layout so years are in the first column and series names are across the top.
- Bars are missing or values look too small. Check for blank cells, text-formatted numbers, filtered-out rows, or values recorded in inconsistent units. For instance, mixing dollars and thousands of dollars can make a series appear disproportionately large or small.
- The year order looks backward. A horizontal bar chart’s category axis can display the first category at the bottom. Select the category axis, open Format Axis, and look for Categories in reverse order. Changing this setting changes the display order, not the underlying data.
- The chart does not show the total as a label. Stacked segments display their own values by default; the total is the combined bar length. Add the totals to a nearby table or use a deliberate total-label method if exact totals must appear on the bars.
When the chart seems to include unexpected blank or extra categories, select Chart Design > Select Data and review the chart data range. Excel may have included nearby cells, or the table may contain a heading or note that was mistaken for data. Correcting the range is usually better than trying to hide unwanted categories with formatting.
When a stacked bar chart is not the best choice
Stacked bars work best with a modest number of series and a meaningful whole. With many components, the legend becomes hard to scan and small segments are difficult to compare. If the parts do not add up to a useful total—for example, if they are overlapping measures rather than distinct portions—stacking can imply a relationship that the data does not have.
For precise year-to-year comparison of one component, a line chart or clustered bar chart may communicate more clearly. For many years and a small number of categories, a line chart can make trends easier to follow. If the point is to compare proportions, use a 100% stacked chart, but show totals elsewhere when their scale is relevant. A well-chosen chart answers the intended question rather than simply displaying every column in the source table.
Frequently Asked Questions
Can I compare years with a stacked bar chart in Excel?
Yes. Put each year in a separate row and each component in a separate series column, then insert a Stacked Bar chart. Each bar represents a year, and its segments represent that year’s component values.
Should years go in rows or columns for a stacked bar chart?
Either orientation can work, but years in rows with component names in columns is straightforward. If Excel interprets the series backwards, select the chart and use Chart Design > Switch Row/Column.
What is the difference between stacked and 100% stacked bars?
A standard stacked chart preserves actual values and totals, so bars can have different lengths. A 100% stacked chart makes every bar the same length and shows each component as a proportion of its year’s total.
Can Excel show total labels on stacked bars?
Excel’s usual data labels show the individual segment values, not automatically the total at the end of each bar. You can display totals in a nearby table or use an additional labeling approach, taking care not to confuse totals with segment values.
Is a stacked bar chart better than a clustered bar chart for comparing years?
It depends on the comparison. Stacked bars show the yearly total and its composition together; clustered bars make it easier to compare individual components across years because each series has its own baseline.
Related
- Excel Ribbon Keyboard Shortcuts: A Practical Guide
- Excel Reverse Undo Shortcut: What Works and What Doesn’t
- Excel Reverse Worksheet Order: How to Do It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Scatter Plot With Dates on the X-Axis: Setup TipsTo make an Excel scatter plot with dates on the x-axis, put genuine Excel dates in one column…
- Excel Mac Chart Creation: Make and Format ChartsTo create a chart in Excel for Mac, select your data, choose Insert > Recommended Charts, select a…
- How to Change Chart Area in Excel: Color, Border, and SizeTo change a chart area in Excel, select the chart, right-click its outer background, choose Format Chart Area,…
- Bubble Chart in Excel: Plot Three Variables ClearlyYes. An Excel bubble chart displays three numeric variables at once: one on the horizontal axis, one on…
- Federal Reserve Balance Sheet Graph: How to Read It TodayThe Federal Reserve balance sheet graph is available on FRED as the weekly series WALCL: Total Assets. It…
- Excel Column Chart Side Wall: What It Is and How to FormatIn Excel, a side wall is a background plane on a three-dimensional (3-D) chart; an ordinary two-dimensional (2-D)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.