Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Right-click a blank bit of the chart, choose Chart Options, and on the Axes tab, change the X axis type to Category.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  4. #4
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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

    [attachment=87827:FormatAxis.jpg]


    Assuming I understood the Question. (See Attached)

    [attachment=87828:Mixed Chart.xlsx]
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    • 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.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 . . .
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •