Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Charts for Excel 3.0

 

X Axis: Category vs. Value

In Microsoft Excel charts, there are different types of X axes. While the Y axis is a Value type axis, the X axis can be a Category type axis or a Value type axis. Using a Value axis, the data is treated as continuously varying numerical data, and the marker is placed at a point along the axis which varies according to its numerical value. Using a Category axis, the data is treated as a sequence of non-numerical text labels, and the marker is placed at a point along the axis according to its position in the sequence. The sample below illustrates the difference between Value and Category Axes.

Our sample data is shown in the simple table below. The first column contains our X axis data, which can be treated as Categories or as Values. Note that the numbers are not equally spaced, nor do they even appear in numerical order.

  A B C D
1   Cats Dogs Fish
2 1 7 7 8
3 3 6 5 7
4 2.5 5 4 3
5 3.5 4 3 2

We will display this data on two types of chart: a Line Chart, and an XY Scatter Chart; these charts look the same but behave differently.

Line Chart: X as Category Axis


Line Chart with Category X Axis

Notice the X axis in this Line Chart. The labels seem to defy numerical order. But in a Category Axis, Excel merely deals with the labels in the order that they appear in the list, without regard to any possible numerical values these labels may have. The tick marks are evenly spaced, again regardless of differences between the numerical values of the labels. For all intents, these labels could be non-numeric attributes, like "Red", "Orange", "Blue", and "Green". The charted lines run from left to right continuously from one category to the next, with each adjacent pair of points horizontally equidistant.

XY (Scatter) Chart: X as Value Axis


XY Chart with Value X Axis

Notice the X axis in this XY Chart. The tick marks are uniformly spaced, as our eyes are trained to expect. The labels appear in ascending order, as expected (they could also be formatted to appear in descending order, but in any rate, they change monotonically). But the data points do not line up like mindless robots above the next available tick label. The data appear at a point above the appropriate value of the X axis, with the lines zig-zagging as required to put the points where they belong.

Combo Chart: X as Category AND Value Axis

You can put both a Line Series and an XY Series onto the same chart. This mixing of chart types on the same chart results in a Combination Chart. Excel provides a short selection of Combination Charts in the Chart Wizard, but you can create them in almost any combination you want. (For a demonstration of Combination Chart creation, see Roll Your Own Combination Charts elsewhere on this site.) In this case, I built the chart with two line series, then selected the Cats series and used Chart Type on the Chart menu to change the series to an XY type. Excel notices the chart types are different, and adds secondary axes to the chart to accommodate the XY series (below left).

The two series can be plotted on the same set of axes. Double click the Cats series, and on the Axis tab, change the option from Secondary to Primary. The result is shown below right. Excel considers the X axis as a Category axis, but it plots the XY series as if Category 1 has a value of 1, Category 2 has a value of 2, etc.


Line Series on Primary Category X Axis,
XY Series on Secondary Value X Axis


Line and XY Series on Primary Category X Axis

In the right-hand chart, the first point of the XY series "Cats" is plotted above the first category listed (i.e., "1"), because its X value is 1. It is merely coincidental that the category label and the X value are both 1. The second point is plotted above the third category ("2.5"), because its X value is 3. The third point is plotted between the second and third categories, because its X value is 2.5, between 2 and 3. The fourth point is plotted between the third and fourth categories, because its X value is 3.5. The four points of the Line series "Dogs" are plotted in order above the first through fourth categories, because they are listed in that order.

The different treatment of category data is important if you need to add an additional series for any reason, such as adding a reference line to the chart. This also is how you would work around a line chart using the categories from the first series for all series; an example later in this article shows how to plot time series with different date values for each series.

Line Chart: X as Time-Scale Axis

There is one exception to a Line Chart having evenly spaced categories. This is the case of dates as X axis values. Excel calls this a Time-Scale axis, but it is more accurately called a Date-Scale axis. Consider a Line Chart constructed from the sample data below.

  A B
1 Date Value
2 3/15/01 1
3 4/15/01 2
4 7/1/01 3
5 8/15/01 4
6 11/15/01 5
7 12/15/01 6

According to the scenario described earlier, Excel should stick the dates in the first column into equispaced category labels, as in the left hand chart below. If Excel recognizes the labels as dates, however, it will actually provide a Time-Scale (Date-Scale) axis, and space the points according to the date, as shown below right. And it is a smart axis scale; if the Base Unit of the axis scale is set to days, you would see slight differences in the spacing between months. While thirty days hath September, October hath 31.


Line Chart with Category X Axis


Line Chart with Date-Scale X Axis

This type of axis is not super smart, however. If you add a series that had different values for these dates, Excel would still use the dates from the first series' X values (see workaround below). In addition, even though it is called a 'Time' Scale axis, it is really a 'Date' Scale axis: all times from a given date are treated as integers, that is, they are plotted at midnight at the start of that date. No partial days (i.e., times) are considered.

While this time scale capability might seem redundant, given the greater flexibility provided by the pure Value axis of the XY Scatter Charts, it has some uses. One advantage is that a Date-Scale axis allows you to have a tick mark at exactly the first of each month, which the XY Chart's Value axis does not. The XY chart at right has a Value axis. It is not possible to get the same date spacing as in the line chart with a Date-Scale axis (above right). The axis minimum and maximum dates are the same, but the XY chart forces single major and minor spacing settings (this chart uses 60 days for major and 30 for minor). Because months have variable numbers of days, the axis becomes disconnected from the first of each month.


XY Chart with Value X Axis

Date-Scale Chart for Series with Different Dates

Excel's line charts do not acknowledge different category data for different series. Instead, the chart uses the category values from the first series for all series. Excel's XY charts allow each series to have distinct category values, but do not provide the nice Date-Scale axis scaling that line charts provide. This example illustrates the problem with multi-series line charts, and it uses the combination chart approach described above to allow multiple sets of dates for multiple series.

  A B C D
1 Date 1 Value 1 Date 2 Value 2
2 3/15/01 1 3/20/01 2
3 4/15/01 2 4/1/01 3
4 7/1/01 3 5/15/01 4
5 8/15/01 4 9/15/01 5
6 11/15/01 5 11/30/01 6
7 12/15/01 6    

Select the data for the first series in columns A and B, and create a line chart with a Date-Scale axis (it will look like the example above). Add the data in columns C and D to the chart as a new series: Copy the range, select the chart, and use Paste Special from the Edit menu to add the data as a New Series, with Series Names in the First Row and Categories in the First Column. The result is showm below left. Although the second series has completely different dates in its source data, and you explicitly indicated these when you copied the range, the line chart ignores these and uses the dates from the first series for both.

To enable multiple date ranges, convert the chart to a combination chart as described above. Keep the first series as a Line series, and change each additional series to an XY series: Select the series, choose Chart Type from the Chart menu, and select an appropriate XY chart type. You should be able to use the F4 function key shortcut to repeat this last action on more series. As above, Excel adds secondary axes for the XY series, so double click on the first XY series, and on the Axis tab of the dialog, select Primary; use F4 to repeat for any additional XY series. As shown in the chart below at right, the series now reflect independent date scales. After changing an added series to an XY series, subsequent series added to the chart will be added as XY series.


Line Chart with Two Line Series


Combo Chart with Line and XY Series

Order of Points for Axis Types

An interesting behavior of Date-Scale charts is illustrated with the data below. The points are the same as in the previous section, but rearranged out of sequence. Charts with the three axis types shown above are repeated here with the rearranged data.

Time Data, Listed Out of Order

  A B
1 Date Value
2 4/15/01 2
3 3/15/01 1
4 7/1/01 3
5 8/15/01 4
6 12/15/01 6
7 11/15/01 5


Line Chart with Category X Axis


Line Chart with Date-Scale X Axis


XY Chart with Value X Axis

The Category Axis chart simply plots the out of sequence dates across the X axis in the order they appear in the worksheet, regardless of the order of the dates. Both the Date-Scale and Value Axis charts display the points from left to right in date order, spaced proportionally to the duration between dates. The two charts show different behavior of the connecting lines: the XY chart with the Value axis connects the points in the order that the points appear in the worksheet, while the Line chart with the Date-Scale axis connect the points from left to right, ignoring the order of the points.

Which Type of X Axis Should You Use?

If you require the precision of a Time Scale axis, or if your data used non-numeric categories for the X values, you should use a Line Chart. For almost any other application, you should use an XY Scatter Chart, with its Value axis. You can format the lines and markers of either type of chart identically, but you have greater flexibility using the Value axis. Data points are located along the X axis according to their X values, not the order they are listed in the worksheet. In addition, if you need a logarithmic scale on your X axis, you can only get it with a Value axis.

For further discussion about axis types and chart types, see my article, Scatter Chart or Line Chart?, in the TechTrax web magazine.
 

 

Peltier Tech Charts for Excel 3.0


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile