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:
If we select the data and create a column chart, it looks like this:
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.
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.
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.