keepcalculator

06 Numbers and Powers

How to Add and Remove Data Series in Excel Charts

Card No. 06·71124 · Updated · Standard formula, shown below

This guide shows you how to add or remove data series from an Excel chart, so you can control exactly which data appears. You’ll learn the quickest methods for both Windows and Mac, plus how to fix common issues when the chart doesn’t update as expected.

What You’ll Need

  • An Excel workbook with a chart already inserted (column, line, pie, etc.)
  • The data you want to add or remove, ideally in a contiguous range of cells
  • Basic familiarity with selecting cells and ranges in Excel

Step-by-Step

Adding a Data Series

  1. Select the chart by clicking on it. This activates the Chart Tools tabs in the ribbon.
  2. Right-click the chart and choose Select Data from the context menu. (On Mac, you can also go to the Chart Design tab and click Select Data.)
  3. In the Select Data Source dialog box, look for the Legend Entries (Series) section on the left.
  4. Click the Add button. A small Edit Series dialog appears.
  5. In the Series name box, either type the name of the series or click the cell that contains the name (e.g., the column header).
  6. In the Series values box, clear the default ={1} and then select the range of cells that contain the data for this series. Click OK.
  7. Click OK again to close the Select Data Source dialog. The new series appears on the chart.

Alternative quick method: Select the new data range (including the header), then copy it (Ctrl+C or Cmd+C). Click the chart and paste (Ctrl+V or Cmd+V). Excel adds the data as a new series. This works best when the new data is in the same worksheet and aligns with the existing categories.

Removing a Data Series

  1. Click the chart to select it.
  2. Right-click and choose Select Data.
  3. In the Legend Entries (Series) list, click the series you want to remove.
  4. Click the Remove button.
  5. Click OK. The series disappears from the chart.

Faster way: Click directly on the series in the chart (the line, bars, or pie slice). Press the Delete key on your keyboard. The series is removed instantly.

Common Mistakes

  • Selecting the entire column when adding series values, which includes empty cells. Excel may plot blanks as zeros or gaps.
  • Not including the header in the series name, so the series is named “Series1” or similar.
  • Deleting the data cells instead of the series. If you delete the underlying data, the chart series disappears, but you also lose the data.
  • Using the wrong chart type – some charts (like pie) only support one series. To add more, you need to change the chart type.
  • Forgetting to update the chart after changing data – usually automatic, but if you’ve turned off automatic recalculation, the chart may not refresh.

Tips

  • Use named ranges for series values to make updates easier. If you add data to a named range, the chart automatically includes it.
  • Convert your data to an Excel Table (Ctrl+T). Charts based on tables automatically expand when you add new rows, so new data appears without manual steps.
  • Reorder series in the Select Data Source dialog by selecting a series and using the up/down arrows. This changes the drawing order, which matters for overlapping bars or lines.
  • Use the Chart Filters button (the funnel icon next to the chart) to temporarily hide or show series without deleting them.
  • For a pie chart, to add more categories you actually add more data points to the single series, not a new series.

Troubleshooting

The new series doesn’t appear. Check that the series values range is correct and includes numbers, not text. Also ensure the chart type supports multiple series (e.g., a pie chart won’t show a second series). If the chart is a 3D chart, some series may be hidden behind others – try changing the chart type.

The series appears but with wrong values. This usually happens when the series name and values ranges are swapped or when the values range includes the header. Double-check the ranges in the Edit Series dialog.

The chart doesn’t update when I change data. Go to Formulas > Calculation Options and ensure it’s set to Automatic. If it’s Manual, press F9 to recalculate.

I can’t select the series to delete it. If the chart is small or the series is hidden behind another, use the Select Data dialog instead. Alternatively, use the Chart Elements button (plus sign) and then the arrow next to Series to select the specific series.

Removing a series also removes the data from the worksheet? No, removing a series only removes it from the chart. The underlying data remains intact. If you accidentally deleted the data, use Ctrl+Z to undo.

Frequently Asked Questions

Can I add a data series from a different worksheet?

Yes. In the Edit Series dialog, click the range selector and then click on the other worksheet tab to select the data. The series will reference that sheet.

Why is my new series showing as “Series1”?

Because you didn’t specify a series name. In the Edit Series dialog, click the Series name box and select the cell containing the header, or type the name manually.

How do I add a series to a pie chart?

Pie charts only show one series. To add more data, you need to add more categories to the existing series by expanding the data range. If you want multiple series, switch to a bar or line chart.

Can I remove a series without opening the Select Data dialog?

Yes. Click directly on the series in the chart and press Delete. This is the fastest method for most charts.

What if my chart is a combo chart (e.g., bar and line)?

Adding a series works the same way, but the new series will use the default chart type. To change it, right-click the series and choose “Change Series Chart Type” to assign a different type.

Why does my new series have a different axis?

Excel may automatically add a secondary axis if the values are very different in scale. To control this, right-click the series, select Format Data Series, and choose which axis to plot on.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →