To edit a stacked bar chart in Excel, select the chart and use the Chart Design and Format tabs to change its data, series order, labels, colors, title, or layout. For a reliable result, check the source data first, then make one change at a time and confirm that the chart still communicates the comparison you intend.
What You’ll Need
- A workbook containing the data you want to chart. Usually, categories go in the first column and each stack component has its own column heading.
- An existing stacked bar chart, or permission to create one from that data.
- Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac has similar chart controls, though some buttons may be arranged differently. Excel for the web supports common edits, but desktop Excel offers more formatting options.
Step-by-Step
- Select the chart and identify what needs changing. Click once on the chart’s outer edge so the chart is selected, rather than a worksheet cell. The Chart Design and Format tabs should appear on the ribbon. If you see handles around a single bar or label, click the blank chart area and try again. Decide whether you are changing the data, the order of the stacks, the chart’s appearance, or its labels; doing one type of edit at a time makes unexpected changes easier to spot.
- Check the source data before editing the chart. Look at the worksheet range behind the chart. A common layout has category names down the first column and series headings across the top row, with one numeric value in each category-and-series intersection. For example, a chart of quarterly sales by product could have quarters in column A and product names in row 1. Make sure headings are present, the values are numeric, and there are no accidental blank rows or columns inside the range. Correcting the table may fix a chart that appears incomplete or has the wrong labels.
- Change which cells the chart uses. With the chart selected, choose Chart Design > Select Data. In the Select Data Source dialog, inspect the Chart data range. You can replace the range by selecting the range box and then highlighting the correct cells on the worksheet. Check that the selection includes the category labels and all series names and values, but does not include unrelated totals or notes. In the Legend Entries (Series) area, use Add, Edit, or Remove to adjust series. In the Horizontal (Category) Axis Labels area, choose Edit to select the correct category-label cells. Select OK and check that the bars represent the intended data.
- Switch how Excel interprets rows and columns if the stacks are wrong. In the same Select Data Source dialog, choose Switch Row/Column. This changes which data groups Excel treats as series and which it treats as categories; it does not change the worksheet itself. For example, if the chart shows one bar per product when you expected one bar per quarter, switching may correct the grouping. Use it only when the current interpretation is reversed, then verify both the category labels and legend entries.
- Reorder the components within each stack. Select Chart Design > Select Data. Under Legend Entries (Series), select a series and use the up or down arrow buttons to move it. The order determines how series are arranged in the stacks and in the legend. After changing the order, check whether the most important component is easy to compare across categories. Do not assume that changing the order of the source columns will always be the clearest choice; you can use the dialog to set the chart order independently.
- Choose the appropriate stacked bar chart type. Select the chart, then choose Chart Design > Change Chart Type. Pick Bar and choose Stacked Bar, 100% Stacked Bar, or another suitable option. Stacked Bar displays the component values as lengths, so the total bar length can vary by category. 100% Stacked Bar scales each category to 100%, making proportions easier to compare but hiding differences in total size. Avoid switching to 100% stacked when readers need to compare actual totals. If you need vertical bars, choose the corresponding Column chart type instead.
- Edit the chart title and add labels or a legend. To change the title, click the title on the chart and type a descriptive replacement. If a title is missing, use Chart Design > Add Chart Element and choose Chart Title or Data Labels. In that menu, select a label position that leaves the values readable; options depend on the chart type. To change the legend, use Chart Design > Add Chart Element > Legend and select a position, or click the existing legend and press Delete if it is unnecessary. Keep labels concise, and avoid showing every value if the chart becomes crowded.
- Format an individual series or the whole chart. Click a segment once to select its series; click again only if you need to select a single data point. Right-click the selection and choose Format Data Series or Format Data Point. The Format pane lets you change fill color, borders, and other available options. For a chart-wide preset, use Chart Design > Chart Styles. Choose colors with enough contrast to distinguish adjacent segments, and keep the same series color consistent wherever it appears. If you change one point by mistake, undo immediately or select the entire series again before formatting.
- Adjust bar spacing and category order. Right-click a bar and choose Format Data Series. Under Series Options, adjust Gap Width to make bars appear closer together or farther apart; a smaller gap makes bars thicker. Change it gradually, because bars that are too thick can make categories difficult to distinguish. To reverse the category order, right-click the category axis, choose Format Axis, and look under Axis Options for Categories in reverse order. Check the value-axis position afterward, since reversing categories can also change where the value axis crosses.
- Review, save, and test the chart at its final size. Check the chart against the source table: the number of categories and series should match, the legend should identify the colors correctly, and labels should not overlap. Resize the chart or the worksheet columns only after the main edits are complete. If the chart will be printed, included in a report, or viewed on a small screen, check it at that size rather than relying on the zoomed-in worksheet view. Save the workbook when the chart is correct.
On Mac, select the chart to reveal the Chart Design and Format tabs, then use the equivalent Select Data, Change Chart Type, and Add Chart Element commands. In Excel for the web, select the chart to access its chart tools; if a detailed formatting option is unavailable, open the workbook in the desktop app. Ribbon labels and dialog layout can vary slightly between updates.
Common Mistakes
- Choosing 100% Stacked Bar when totals matter. This chart makes every category the same overall length. It shows each component’s share, not the category’s total amount. Choose regular Stacked Bar when both the components and total size are important.
- Including totals as another series. If a row or column of row totals is included in the chart range, Excel may stack those totals on top of the component values and exaggerate the bars. Exclude calculated totals unless they are deliberately part of the visualization.
- Confusing the legend with the category labels. The legend names the series that make up each stack; the category axis identifies each complete bar. If either is wrong, use Select Data to edit the relevant labels instead of typing over worksheet data without checking the chart range.
- Using too many colors, labels, or categories. A different color for every category is not helpful when colors represent series. Keep series colors consistent, and remove labels that overlap or repeat information already obvious from the axis or legend.
- Assuming a chart is fully editable after copying it as a picture. A pasted image has no live link to the data and does not expose chart series for editing. Return to the workbook’s original chart, or recreate the chart from the source table if only an image remains.
Tips
- Give the chart a specific title. A title such as “Units Sold by Quarter and Product” explains both the measure and the grouping better than “Sales.” Add units to the axis title or chart title where that prevents confusion.
- Use a meaningful stack order. Put components in an order readers can learn quickly, such as a consistent process sequence or a progression from smaller to larger values. The bottom series is often easiest to compare across bars because it shares a common baseline.
- Keep category names short and consistent. Long names can make a horizontal bar chart tall or cramped. If you shorten labels, use terms that readers can understand without guessing and retain a clear explanation elsewhere in the workbook.
- Use direct labels selectively. Data labels can be useful when readers need exact values, but labeling every segment in a chart with many categories can create clutter. Consider adding labels only to important segments or keeping the exact figures in the table beside the chart.
- Keep the source range easy to maintain. If new categories will be added regularly, format the source data as an Excel table before creating or updating the chart. Charts based on a table generally expand as rows are added, but check the chart after changing the table structure.
- Preserve an original before a substantial redesign. Duplicate the worksheet or save a copy of the workbook before trying a different chart type or rebuilding the data range. That gives you a way to compare versions and restore the original presentation.
Troubleshooting
The chart does not update after changing values. Confirm that the edited cells fall inside the chart’s source range by opening Chart Design > Select Data. Check that the workbook calculation and data are current, and that the chart has not been pasted as a picture. If the chart uses an Excel table, confirm that new rows were added within the table rather than below it.
The legend or category names are showing numbers or the wrong text. Open Select Data and check the ranges for both series names and Horizontal (Category) Axis Labels. Select the heading cells for series names and the intended label cells for categories. Also check whether Switch Row/Column would better match the way your data is arranged.
Some segments are missing, empty, or unusually large. Inspect the corresponding worksheet cells for blanks, text stored where numbers should be, errors, or a misplaced total. Confirm that the chart range includes the full data table and not extra rows. A blank cell is not the same as a numeric zero, so choose an appropriate value in the source data if you need the chart to show a genuine zero.
The bars are hard to read or labels overlap. Reduce Gap Width a little, enlarge the chart, shorten category names, or remove some data labels. If the categories have very different totals, remember that regular stacked bars show both component size and total size; 100% stacked bars may help with comparing proportions, but they will remove visual differences in totals.
The colors or formatting change unexpectedly. Make sure you selected the intended series rather than one data point or the whole chart area. If a chart style overrides a manual choice, reapply the series fill through Format Data Series after selecting the series. For precise color matching, note the color settings before applying another style.
Frequently Asked Questions
How do I add a new series to a stacked bar chart?
Add a heading and values for the new series in the source table, then select the chart and open Chart Design > Select Data. Use Add under Legend Entries (Series) and specify the series name and its values. Check that the new series uses the same category rows as the existing series.
Can I change a stacked bar chart to a stacked column chart?
Yes. Select the chart and choose Chart Design > Change Chart Type, then choose Column and select Stacked Column or 100% Stacked Column. A bar chart places categories along the vertical axis; a column chart places them along the horizontal axis, so check that labels remain readable after the change.
How do I show the total at the end of each stacked bar?
Excel does not provide a simple built-in total label for every stacked bar in all chart setups. A common workaround is to add a helper series containing the category totals, format that series so its bars are not visible, and display data labels for it. Because this requires careful series positioning and formatting, work on a copy and verify that the hidden series does not alter the visible stacks.
Can I edit the chart without changing the worksheet data?
Yes. You can change the chart type, series order, axis order, title, colors, and labels without altering the source values. However, changing the chart’s data range or editing its series references changes what the chart displays, even though it does not rewrite the cells themselves.
Why do all the bars look the same length?
You may have selected 100% Stacked Bar, which scales every category to a total of 100%. Switch to regular Stacked Bar if the total values should have different bar lengths. If you need to compare proportions rather than totals, the equal lengths are expected.
Related
- How to Delete Duplicate Entries in Excel
- How to Delete Certain Data in Excel Without Losing More
- How to Delete a Border Line in Excel: Quick Steps and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table: Use a Field as Row and FilterYes. In an Excel PivotTable, you can place the same field in both the Rows and Filters areas.…
- Excel Pivot Table: How to Exclude Blank Rows and ItemsTo exclude blanks from an Excel PivotTable in Microsoft 365 for Windows, open the dropdown for the row,…
- Excel Add Shaded Area to a Chart: Steps and OptionsTo add a shaded area to an Excel chart, use an area series for the region you want…
- Bar Chart Pivot Table: How to Create and Format in ExcelA bar chart pivot table is a PivotChart that displays summarized PivotTable data as horizontal bars. In Excel…
- Data Not Showing in Excel Chart? Fix the Source and AxisIf data is not showing in an Excel chart, first select the chart and check its source range…
- Add a Pareto Line to an Excel Bar ChartYes. In Excel for Microsoft 365 on Windows, add a cumulative-percentage column beside your category counts, then use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.