By Stephen Nelson
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.
About the author: Seattle CPA Stephen L. Nelson wrote the bestselling book, MBA’s Guide to Microsoft Excel, from which this short article is adapted. Nelson also writes and edits the S Corporations Explained and LLCs Explained websites.
Want to read more about MS Excel tips and tutorials? Visit Hot Excel.