Skip to Main Content

Indiana University Indiana University


Charting Noncontiguous Dates in Excel

Have you ever needed to chart noncontiguous dates in Excel?  Often, Excel will attempt to fill in the missing pieces of the series.

This example shows the total number of items sold on a weekly basis:

Number Sold

If we select the data and create a column chart, it looks like this:

Num Sold Chart

Excel has attempted to fill in some of the missing dates; However, because of the size of the chart, Excel is just showing the even numbered dates.

If the chart were made larger, all of the dates would appear on the horizontal axis. This isn’t what we want the chart to show.  We want to just see the dates that were included in the data.  Removing the extra dates is simple, if not intuitive.

On the Layout command tab, in the Axes group, click the Axes button.  On the drop-down menu, point to Primary Horizontal Axis, and then click More Primary Horizontal Axis Options.  This opens the Format Axis dialog box.

FormatAxisdb

The extra dates are caused by the “Major unit:” option.  In this example, the Major unit is set at 2 days.  Our data is weekly data, so the Major unit should be changed to 7 days. To change this, click the “Fixed” radio button.  Replace the current number (2) with the actual number of days we want represented on our chart, 7.  Click Close to close the dialog box.

When the dialog box is closed, the chart is automatically updated with the corrected axis.

Correct Dates

At this time, we could do some additional formatting to make the chart more attractive and, possibly, easier to read, but it isn’t necessary.


4 Responses to "Charting Noncontiguous Dates in Excel" to “Charting Noncontiguous Dates in Excel”

  1. Jon Peltier Says:

    If you want to avoid the huge gaps between bars, you can change the axis from the default date scale to a text scale, on the same dialog with the axis scale settings. Excel detected the dates in your data and assigned a date scale axis, which has a slot on the axis for every date. The bars are narrow because they allow space for the six days between data points.

  2. Frank Says:

    Thanks Jon – your advice resolved the issue I was having with my chart.

  3. Larry Wolfson Says:

    This doesn’t at all do what you say it does. All you have done is changed the scale which is nice but it is not just showing the points in the data. If you had missed one or more data points by the week you would show those dates even though there were no data points for them. From a university, I would at least expect the description of the issue to be accurate. This tip does not match its description.

  4. Bart Hawkins Says:

    Somewhat….ludicrous, and doesn’t really do what the topic says, anyway.

    Why not just select “Text axis,” under “Format axis,” which will simply add the dates – irrespective of intervals between them – as axis labels.

    ALWAYS select the simplest solution.

Leave a Reply

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.