Stephen L. Nelson, CPA asked:


How Excel Sees Chart Data

To easily use Excel for charting, you need to learn three key terms: data points, data series,

and data categories.

Data Points are the Basic Building Blocks

The individual values you plot in a chart are called data points. Because a chart visually represents one or more numeric values, data points are always values. Note, however, that most Excel formula results are also numeric, which means that you can also plot formulas. (Actually, in this case, you’re really plotting the values that the formula calculates.)

Data Series Organize Data Points

The term data series refers to a collection of values that are all related—that are all part of the same set. That might sound complicated, but it’s really not. If you want to chart monthly interest rates over the last 10 years, that collection of interest-rate percentages is a data series.
If you want to plot advertising expenditures over the last 12 months, that collection of expense

values is a data series.

Most charts you create will use more than one data series. For example, if you wanted to compare sales revenues of three competitors, each competitor’s sales revenues would probably constitute its own data series.

Suppose, for example, that you had an Excel worksheet with annual sales revenues for three fictitious companies: Anderson Company, Baker Incorporated, and Carson Corporation. The data points that show Andersen’s revenue would represent a data series. The data points that show Baker’s revenue would represent another data. And the data points that represent Carson’s would revenue represent still a third and final data series.

Data Categories Represent the Secondary View

The term data categories refers to the secondary view, or perspective, on to-be-charted data.

In a chart that plots annual sales revenues for three companies over a five-year time frame, each year’s collection of data points represents a data category. The collection of Year 1 data points represents the Year 1 data category. Similarly, the Year 2 data points represent the Year 2 data category. The same thing is true of the Year 3, Year 4, and Year 5 data points.

Rules of Thumb for Distinguishing Between Data Series and Data Categories

People commonly get confused about the differences between data series and data categories (in part because Microsoft’s product documentation typically hasn’t done a very good

job of defining and distinguishing these two terms). You can use the following two rules of thumb to help distinguish between data series and data categories in your own Excel charts:

1. In general, if you look at a chart and ask, “What does this chart show,” every concise answer identifies a data series. In the case of a simple line chart that plots the fictitious sales revenue for three companies named Anderson, Baker and Carson, you might say something like, “Well, the chart shows Anderson’s revenue, Baker’s revenue, and Carson’s revenue.” Which is not a coincidence; charts show data series. Andersen’s revenue is a data series. And so is Baker’s revenue, and so is Carson’s revenue.

2. Any chart that shows how some value changes over time is a time-series chart. In any

time-series chart, the data categories will be some time interval, such as months or quarters

or years. For example, if a chart plots sales revenue over a five-year period

of time, the data categories are years.