How to work with data series

In this video, we'll look at how data series work in Excel charts.

A row or column of related numbers plotted in a chart is called a data series. 

Excel can plot data arranged in rows or columns.

When you create a new chart, Excel makes a decision about data series.

When the data contains more rows than columns, Excel uses columns for the data series.

When the data contains more columns than rows, Excel uses rows for the data series.

For example, here we have chocolate sales data for 6 months. There are 5 products and 6 months of data, so when I insert a chart, Excel uses rows as the data series.

However, if I remove the last two months of data, and then insert the chart, Excel uses columns for the data series.

In summary, Excel will use the smaller number for data series.

Notice that the chart data series are linked to the legend.

Once you have a chart, you can confirm the data series at any time by clicking the Select Data button on the Design tab of the ribbon under Chart Tools. Or, easier, right-click a chart and choose Select Data.

The Select Data Source window shows the data series on the left. If you click edit for a series, you can verify settings for the name and data range.

You can also confirm the data series by clicking directly on a data series in the chart. Excel will highlight the series on the worksheet, along with the axis labels.

Sometimes you want to reverse the way that Excel plots data series. 

Just click the "Switch Row/Column" button on the Design tab to reverse.

You can find this same option in the Data Source window. I'll use it here to switch rows and columns in the second chart.

The Data Source window also lets you reorder data series using up and down buttons.

You can also enable or disable series here as well. But, you'll find the filter menu that's attached directly to the chart an easier way to include or exclude data series, as well as categories.