remove blank dates from chart (2007)

Topic

New Reply

Hi All:

In Col A I have a series of dates, in Col B the score for each date and in Col C the average score so far. The dates are not consecutive!

So far, so good. However, when I come to Chart the figures (dates in X-axis, Results in Y-axis as Bars, Average as a line, the chart leaves gaps for missing dates, even though the dates are in consecutive rows on the sheet. Does anyone know how I can get the chart to not show gaps where the dates are non-consecutive, please?

I can post the W/Book if the above explanation is not clear enough.

(Edited to remove duplicate signature)

Reply | Quote

Replies

Right-click a blank bit of the chart, choose Chart Options, and on the Axes tab, change the X axis type to Category.

Reply | Quote

Without your example, I will make a guess:

I think that you have created either an XY chart (formatted as dates) or a line chart with the X-Axis set as a time series. Either option will presume and fill in “missing dates” in the entire series.

You need to create a line chart (chart type) and ensure that the X-Axis are categories (in chart -options – Axes(tab)) and not a time-series.

Steve

Reply | Quote

Thanks for your prompt response, Rory. However (from memory) your instructions apply to 2003, which unfortunately I no longer have, it having been mysteriously deleted from my laptop when I installed Office 2007 (arrrggghhh).

As per the title of my post, this chart is in 2007 (although saved as 97/2003 compatible) so any further suggestions would be welcome.

Reply | Quote

Thanks Steve. I am obviously being totally thick, so I have attached the file – 2007 version!

. . . and I used to get on so well with the earlier versions of Excel . . .

and now I can’t get how to remove the xls version from this post, so I guess they’ll both be attached.

Reply | Quote

I don’t know how to do this in XL2007, I only have XL2002….

In versions previous to XL2007, you right-click the chart
Choose “chart option”
Select “Axes” (tab)
Select “Category” under “Category (X) Axis” (you currently have “time-series” selected)
[OK]

If that does not work in XL2007 you will have to wait for a response from an XL2007 user.

Steve

Reply | Quote

I think this will work.
Right Click on the X Axis
Choose Format Axis
In the Dialog set the Major Unit to be fixed 1 Day

Assuming I understood the Question. (See Attached)

Reply | Quote

[*]In 2007 with the chart selected[*]Choose the Chart Tools, Layout Ribbon.[*]Click the Axes button and choose Primary Horizontal Axis, More Primary Horizontal Axis Options.[*]The Format Axis dialog will appear. It should have the Axis Options category selected. In the Axis Options portion of the dialog you will find the radio button to switch from Date Axis to Text Axis.

Reply | Quote

Catherine:

Many thanks!

It was changing the X-Axis from a Date Axis to a Text Axis that has done the trick – the gaps in the chart have now disappeared.

I thought I knew Excel pretty well, and then they bring out a new version . . .

Reply | Quote