Understanding a Date-Based Axis Versus a Category-Based Axis in Excel 2007:-


Excel offers two types of horizontal axes in a trend chart. Having the proper setting can ensure that your message is accurate.

If the spacing of events along the time axis is uniform, it does not matter whether you choose a date-based axis or a text-based axis. The results will be the same. In this case, it is fine to allow Excel to automatically choose the type of axis. However, if the spacing of events along the time axis is haphazard, you definitely want to make sure that Excel is using a date-based axis.

Usually, if your data contains dates, Excel defaults to a date-based axis. However, you should explicitly check to make sure that Excel is using the correct type of axis. A number of potential problems force Excel to choose a text-based axis instead of a date-based axis, such as dates that are stored as text in a spreadsheet and dates represented by numeric years.
To explicitly choose an axis type, you follow these steps:

1. Right-click the horizontal axis and choose Format Axis.

2. In the Format Axis dialog box that appears, choose the Axis Options category.

3. Choose either Text Axis or Date Axis, as appropriate, from the Axis Type section. Look into the figure (given below).



(You can explicitly choose an axis type rather than letting Excel choose the default.)

A number of complications that require special handling can occur with your date fields. The following are some of the problems you might encounter:

1. Dates stored as text—if your dates are stored as text dates instead of real dates, a date-based axis will never work. You have to use date functions to convert the text dates to real dates.

2. Dates represented by numeric years—all your trend charts may have category values of 2005, 2006, 2007, and so on. Excel doesn’t naturally recognize these as dates, but you can trick it into doing so.

3. Dates before 1900—if your company has been around long enough that you are charting historical trends before January 1, 1900, you are sunk. In Excel’s world, there are no dates before this time period.

4. Dates that are really time—it is not difficult to imagine charts in which the horizontal axis contains periodic times throughout a day. You might want to use such a chart to show the number of people entering a bank. For such a chart, you need a time-based axis, but Excel will group all of the times from a single day into a single point. See “Using a Workaround to Display a Time-Scale Axis” for the rather complex steps needed to plot data by periods smaller than a day.

5. Dates that you need to appear as text in order to draw in a decorative element—the case study, “Using a Decorative Element in a Chart,” later in this chapter, shows a chart by designer Kyle Fletcher in which the dates are forced to be text.