About using dates in charts

When you create a chart from worksheet data that uses dates, and the dates make up the category (x) axis in the chart, Microsoft Excel automatically uses a time-scale category axis. You can change the category axis to a time-scale axis. A time-scale axis displays dates in chronological order at specific intervals, or base units, even if the dates on the worksheet are not in order or in the same base units.

Charts that use a time-scale axis

Charts that use a time-scale axis

Time-scale axes are available on stock charts and on 2-D and 3-D line, column, bar, and area charts, except when these charts have multiple-level category labels (multiple-level category labels: Category labels in a chart that, based on worksheet data, are automatically displayed on more than one line in a hierarchy. For example, the heading Produce might appear above a row with headings Tofu, Apples, and Pears.). You can specify the time-scale option on the Axes tab of the Chart Options dialog box. Time-scale axes are not available on PivotChart reports (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.).

Excel initially sets the time-scale base units (days, months, or years) according to the smallest difference between any two dates in the data. For example, if you have data for stock prices where the smallest difference between dates is seven days, Excel presets the time-scale base unit to days. You can change the base unit to months to see the performance of the stock over a shorter or longer period of time, as shown in the first chart in the example.

What do you want to do?

Change the category axis to a time-scale axis

Change the base unit on a time-scale axis

Change the category axis to a time-scale axis

  1. Click a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.) that displays a chart with dates on the category axis.
  2. On the Chart menu, click Chart Options.
  3. On the Axes tab, under Category (X) axis, click Time-scale.

 Note   You cannot have a time-scale axis if the dates in your chart appear in the legend (legend: A box that identifies the patterns or colors that are assigned to the data series or categories in a chart.). You may want to change the way that data is plotted in the chart so that the dates appear on the category axis instead.

Top of Page Top of Page

Change the base unit on a time-scale axis

  1. On a chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or in an embedded chart (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), click the category (x) axis for which you want to change the time-scale base unit.
  2. On the Format menu, click Selected Axis.
  3. On the Scale tab, click the time-scale options that you want.

 Note   You cannot create time-scale charts from data that is measured at intervals of hours, minutes, or seconds. Only days, months, and years are considered base units in time-scale charts.

Top of Page Top of Page

 
 
Applies to:
Excel 2003