To edit a clustered column chart in Excel, select the chart and use the Chart Design or Format tab to change its source data, series, labels, axes, colors, and spacing. For Microsoft Excel for Microsoft 365 on Windows, start with a chart already in your workbook, or select the data you want to compare and create one with Insert > Insert Column or Bar Chart > Clustered Column.
What You’ll Need
- An Excel workbook containing the data you want to chart. Arrange categories in one column or row, with a separate column or row for each data series, and include headings.
- An existing clustered column chart, or a data range from which you can create one. If the chart already exists, it helps to know which cells it should use.
- Microsoft Excel for Microsoft 365 on Windows for the ribbon paths below. On Mac, the chart tools are also available when you select a chart, though some controls may be arranged differently. Excel for the web has chart editing too, but some detailed formatting options are more limited.
Step-by-Step
- Select the chart and identify what needs changing. Click the chart’s outer border so the whole chart is selected. Excel displays the Chart Design and Format tabs on the ribbon. If you see selection handles around just one column, click the chart background or press Esc and select the chart again. Decide whether you are changing the displayed data, the layout, or the appearance; these are handled in different places.
- Change the data range if the chart is missing or including extra cells. Select the chart, then choose Chart Design > Select Data. In the Select Data Source dialog, check the Chart data range box. Enter or select the complete range, including the headings and category labels, then click OK. For example, if the labels are in A1:A5 and two series are in B1:C5, the selected range should include A1:C5. Use this option when adding a new category or data series to the chart’s source.
- Correct the category labels and series names. In Chart Design > Select Data, review both the Legend Entries (Series) list and the Horizontal (Category) Axis Labels area. Select a series and choose Edit to change its series name or values; select Edit under the category labels to choose the cells containing labels such as months or product names. Use the worksheet’s heading cells where possible, so the chart stays understandable if the data changes. Click OK to apply your selections.
- Switch between comparing categories and comparing series. If Excel has grouped the data in the wrong direction, select the chart and choose Chart Design > Switch Row/Column. For example, a table with months down the first column and products across the top may show each product as a separate series. Switching the row and column orientation can instead make each month a series. Check that the legend and horizontal axis now match the comparison you want; switch back if the result is less clear.
- Change the chart type only if the data calls for it. Select the chart and choose Chart Design > Change Chart Type. Choose Column, then select Clustered Column if you want the series’ columns displayed side by side for each category. Click OK. This is useful when a chart was accidentally changed to stacked or 100% stacked columns. If you want one series to use a different type, choose Combo in the dialog and set the type for each series; use a secondary axis only when the scales genuinely need to be read separately.
- Add or edit the chart title, legend, and labels. Choose Chart Design > Add Chart Element to add or position a Chart Title, Axis Titles, Data Labels, Legend, or Gridlines. To edit title text, click the title in the chart and type. To edit an axis title, click it and enter a clear description, including units when useful. Select a legend and press Delete to remove it, or choose a different legend position from Add Chart Element. Avoid adding every available element: keep only what helps someone interpret the chart.
- Format columns and series for easier comparison. Click a column once to select its entire series; click it again if you intend to select only that individual data point. Right-click the selection and choose Format Data Series. In the Format Data Series pane, use Fill & Line to change the fill or border. Choose a restrained set of distinct colors, and keep the same color for a series throughout the chart. To adjust the space between category clusters, open Series Options and change Gap Width. A smaller percentage makes columns wider; a larger percentage makes them narrower.
- Adjust the value axis and number display. Right-click the vertical value axis and choose Format Axis. In Axis Options, set bounds or units if the automatic scale makes the comparison difficult to read. Use care with the minimum bound: starting above zero can exaggerate differences between columns. To change the number format, expand Number in the pane and choose an appropriate category, such as Number or Percentage. If the axis should follow the worksheet’s formatting, use the option to link its number format to the source where available.
- Reorder series or categories when the reading order is confusing. To reorder data series, go to Chart Design > Select Data, select a series in Legend Entries (Series), and use the up or down arrow buttons. The order in the list affects the series’ order in the clusters and legend. To reverse the category order, right-click the horizontal category axis, choose Format Axis, and use Categories in reverse order under Axis Options. Confirm the resulting order is appropriate, especially if the categories represent time.
- Check the finished chart in context. Click outside the chart to see it without selection handles. Verify that the title explains the comparison, all categories and series are present, axis units are clear, and labels do not overlap. Resize the chart if text is clipped: drag a corner handle to preserve its proportions. Save the workbook after reviewing the chart at the size at which readers will view it.
Common Mistakes
A frequent problem is selecting an incomplete source range. If a heading or category label is omitted, Excel may show generic names such as Series 1 or categories numbered 1, 2, and 3. Reopen Select Data and check that the range includes the right labels as well as all the values. The opposite mistake is selecting nearby totals or notes, which can create an unwanted series or distort the scale.
Another mistake is choosing Switch Row/Column when the real issue is a missing heading, or changing the chart type when the data orientation is wrong. These actions can make the display look different without fixing its meaning. Check the selected source and the series list before changing the layout. Also avoid using too many bright colors, 3-D effects, or data labels on every column in a crowded chart; these additions often make comparisons harder, not easier.
Finally, be cautious when changing the value axis minimum. A column chart visually emphasizes bar length, so an axis that starts well above zero can make small differences look dramatic. If there is a sound reason to use a nonzero bound, make the scale clear and explain it in the chart or accompanying text.
Tips
Keep the source data in a simple rectangular table with one header row and one category-label column. This makes it easier to add rows, select the correct data, and recognize each series. If you expect the data to grow, consider converting the range to an Excel table with Insert > Table before building the chart; charts based on a table can include added table rows as the source expands.
Use short, descriptive series names in the worksheet headings, since those names usually appear in the legend. Put units in the axis title or heading rather than repeating them in every data label. If exact values matter, add data labels selectively or format the axis to display sensible decimal places. For a quick comparison of many series, a consistent color scheme and moderate gap width usually work better than decorative effects.
To make targeted edits, select the specific chart element before opening its format options. A single click on a series selects the series; a second click on one of its columns selects that point. If you are unsure what is selected, use the Current Selection control on the Format tab or look at the selection handles before changing formatting. On Mac, select the chart to show the chart-specific ribbon tabs; names are similar, though the ribbon layout can vary. In Excel for the web, basic titles, colors, and chart data edits are available, while some advanced axis and series controls may require the desktop app.
Troubleshooting
The chart does not update after I edit the worksheet. Check that the edited cells are inside the chart’s source range. Select the chart and open Chart Design > Select Data to inspect the range. If data was pasted next to, rather than within, the existing range, expand the range in the dialog. If the chart is based on an Excel table, verify that the new records were added inside the table.
The legend shows Series 1 or the wrong names. Open Select Data, select the affected entry under Legend Entries (Series), and click Edit. Set Series name to the correct heading cell or enter a descriptive name. Check that the chosen name cell is not blank and that the series values still point to the intended numeric cells.
Categories appear as numbers or in the wrong order. In Select Data, edit Horizontal (Category) Axis Labels and choose the worksheet cells containing the category names. If the names are right but run in an unexpected direction, use Format Axis and check Categories in reverse order. For dates, verify that the source cells contain actual Excel dates rather than text that only looks like dates.
Columns overlap or are too thin. Select a column series, right-click, and choose Format Data Series. Under Series Options, adjust Gap Width to control the space between category clusters. If multiple series overlap, check Series Overlap as well; values nearer 100% place columns more on top of one another, while lower values separate them. The best setting depends on the number of series and the chart’s size.
Labels or titles are cut off. Enlarge the chart by dragging a corner, or reduce the text size and shorten long labels. You can rotate category labels by right-clicking the horizontal axis, choosing Format Axis, and changing its text options where available. If the chart is crowded, show fewer labels or remove nonessential data labels rather than shrinking everything until it is difficult to read.
Frequently Asked Questions
How do I edit the data in a clustered column chart?
Select the chart and choose Chart Design > Select Data. Update the chart data range, category-label cells, or individual series, then click OK. You can also edit the source worksheet cells directly if they are already included in the chart.
How do I add another series?
First add the series’ heading and values to the worksheet. Select the chart, open Chart Design > Select Data, and choose Add under Legend Entries (Series). Enter or select the series name and values, then confirm the category labels if needed. If the new series is outside the current source range, adding it through the dialog makes the selection explicit.
Can I change just one column’s color?
Yes. Click the series once and then click the individual column again to select that data point. Right-click it, choose Format Data Point, and change its fill. If you want all columns in that series to share a color, select the series with one click and format the series instead.
What is the difference between clustered and stacked columns?
Clustered columns place series side by side for each category, making it easier to compare individual values. Stacked columns place series on top of one another, emphasizing the total and each series’ contribution. Choose the layout that answers the question your chart is meant to show.
Can I edit a clustered column chart in Excel for Mac or the web?
Yes. In Excel for Mac, select the chart to reveal chart-specific tabs and use the corresponding design and formatting controls; their arrangement may differ from Windows. Excel for the web supports common chart edits, but switch to the desktop app if a detailed formatting control is unavailable.
Related
- How to Delete Empty Rows in Excel Without Losing Data
- How to Delete Duplicate Lines in Excel
- How to Delete Duplicate Names in Excel Without Losing Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Divide by Zero Error: Causes and FixesAn Excel PivotTable’s #DIV/0! usually comes from a division formula in its source data or a calculated field…
- Excel Pivot Table: Show Errors as BlankTo show errors as blank in an Excel PivotTable, select the PivotTable, open PivotTable Analyze > Options, go…
- Generate an Org Chart in PowerPoint: Steps and TipsTo generate an org chart in PowerPoint for Microsoft 365 on Windows, select Insert > SmartArt > Hierarchy,…
- How to Make a Log Graph in Google SheetsTo make a log graph in Google Sheets, put your data in columns, select it, choose Insert >…
- Excel Combo Chart Not Available? Causes and FixesYes—Excel for Microsoft 365 on Windows includes combo charts. If the Combo option is missing, try selecting a…
- How to Make a 4-Quadrant Graph in ExcelTo make a 4-quadrant graph in Excel, plot your paired X and Y values in an XY scatter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.