Microsoft Excel

Date or Time Scaling in a Chart

As the previous example demonstrates, using time or date values for the category axis makes a lot of sense for charting progress or spotting long-term trends. However, the example does cheat a little. Even though any sentient human knows that the labels Sales-03, Sales-04, and Sales-05 represent consecutive years, Excel is oblivious to what these labels actually mean. You could chart a bunch of years that are far from sequential (like Sales02, Sales04, and Sales08) and Excel would obediently (and misleadingly) place each value on the category axis, spaced out evenly.

This snafu doesn't present a problem in the previous example, but it's an issue if you need to chart years that aren't spread out evenly. Fortunately, Excel offers an easy solution. Instead of entering text labels, you can enter actual dates or times. Because Excel stores dates and times as numbers, it can scale the chart accordingly (this process is sometimes called category axis scaling). Best of all, Excel automatically notices when you're using real dates, and kicks into action, making the appropriate adjustments, as shown in figure below.

Axis Type Based on Data and Custom Defined
The top chart uses category axis scaling to properly space out dates, even when there are missing values. The bottom chart doesn't.


What's happening in fFigure above is worth examining in a bit of detail. The pictured worksheet shows two charts that illustrate the exact same data: a series of monthly sales figures from two regions (covering the time period between January 2007 and December 2008). The diamonds and triangles on the line charts indicate the data points for which sales data is available. The twist is that a big chunk of data (the months between August 2007 and June 2008) is missing. To make sure Excel handles this omission correctly, you must enter real date values (rather than text labels) for the category axis. If you take that step, the chart Excel creates automatically uses a continuous timescale, as shown in the top chart. (As you can see by looking at the data points, no values fall in the middle of the series.)

On the other hand, if you enter the labels as text (as was done when creating the bottom chart), you'll see an incorrect result: The data from August 2007 and June 2008 are placed close togethereven though they record months that are almost a year apart.

Optionally, you have the ability to tell Excel to disregard any values you've used in your column or row labels, thereby spacing the dates out evenly, as though they're ordinary text labels. That's how the incorrect chart in figure above was created. (Why you'd want to do it is another question, but someone, somewhere, is probably in desperate need of this feature.) To change how Excel scales the category axis, select the chart, and then choose Chart Tools : Layout > Axes > Axes > Primary Horizontal Axis More Primary Horizontal Axis Options to show the Format Axis dialog box. Next, change the Axis Type setting, as shown in figure below.

Axis Options
Under the Axis Type heading, pick one of the following: "Text axis" (treat the category values as labels), "Date axis" (treat the category values as date vales), or "Automatically select based on the data" (let Excel decide based on what it thinks is best).


Category axis scaling works with more than just dates. You can scale any category axis values, as long as they're numeric, which is particularly useful if you're trying to determine the relationship between two different values. If you wanted to determine the relationship between students' IQs and their test scores, you could use the numeric IQ for the category axis, and use the test scores for the value axis. If you want to create a chart like this that compares two sets of numbers, you must use a scatter chart instead of a line chart