Quick, clean, and to the point

Understanding data series

In this video, we'll take a closer look at data series.

When you create a chart in Excel, you're plotting numeric data organized into one or more "data series".

A data series is just a fancy name for a collection of related numbers in the same row, or the same column.

For example, this data shows yearly sales of shorts, sandals, t-shirts, and hoodies for an online surf shop.

If I create a column chart with the default options, we get a chart with three data series, one for each year.

In this chart, data series come from columns, and each column contains 4 values, one for each product.

Notice that Excel has used the column headers to name each data series, and that these names correspond to items you see listed in the legend.

You can verify and edit data series at any time by right-clicking and choosing  Select Data.

In the Select Data Source window, data series are listed on the left. If I edit one of the entries, you can see that the data series has both a name and range of values.

You're free to edit this information manually.

So what are the values listed on the right side of Select Data Source window?

These are axis labels, in this case, Horizontal axis labels, as you can see on the chart.

In short, this chart pulls data series names from columns, and axis labels from rows.

If I click the Switch Row/Column button, this is reversed.

The data series now come from rows and axis labels come from columns.

Again, notice the legend lists data series names.

Finally, I want to point out that when you select a data series, you'll see formula in the formula bar. This formula is based on the SERIES function, which takes four arguments:

=SERIES([Series Name],[X Values],[Y Values],[Plot Order])

As I select each series, you can see these arguments change to match the data highlighted on the worksheet.

You can edit the SERIES formula if you like. For example, if I change plot order for the shorts data series to 4, Excel automatically plots the series last, and adjusts the order of the other series automatically.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.