+ Reply to Thread
Results 1 to 9 of 9

Chart adds date labels to X axis that are not in data

  1. #1
    Registered User
    Join Date
    01-15-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    12

    Chart adds date labels to X axis that are not in data

    Hello all. This is my first post here. I found this forum while trying to Google this problem. It looks to be a very active forum!

    I am collecting a single data point each work day. Therefore, I have dates in the A column that go, for example, cell A1 has Monday, Jan 4, cell A2 has Tuesday, Jan 5......cell A5 has Friday, Jan 8. Then since I only collect this data during week days, the next cell, A6 has Monday, Jan 11, cell A7 has Tuesday, Jan 12...and so on. Just a straight list on weekdays with a corresponding single number in column B.

    The problem occurs when I try to chart this info. Excel adds the "missing" Saturdays and Sundays to the X-axis labels even though they are not in the column A data.

    I have tried several formats but it does not do any good. I have also tried to adjust the axis scale but it seems to automatically assume a 7 day week even when I only have 5 days per week listed. I have also tried searching this forum for this question but did not find an answer within the first few pages of the search.

    I am running Excel 2003.
    Any help would be appreciated.

    Thanks,
    Numcruncher1

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Chart adds date labels to X axis that are not in data

    What chart type? If this is a scatter plot, this is the behavior you will get.
    If this is one of the other available chart types, what kind of horizontal X axis are you using: timescale axis or text? (An "automatic" setting will probably default to a timescale axis).

    It sounds like you want Excel to use a simple text axis for your chart. Make sure your chart is not an XY scatter chart, then make sure that the X axis is formatted as a text axis.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    01-15-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    12

    Re: Chart adds date labels to X axis that are not in data

    Thanks for the quick reply, MrShorty.
    The chart is a line chart.
    The data column is custom formatted to ddd, mmm dd, 'yy, the chart axis, when I set it to TEXT shows the dates as a 5 digit number. Mon, Jan 04, '16 in the data shows up on the chart as 42373. If I then check the LINK TO SOURCE box, that date appears on the axis as a DATE type showing 1/4/2016.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Chart adds date labels to X axis that are not in data

    If you are unfamiliar with how Excel stores and uses date and time information, I would suggest like this: http://www.cpearson.com/Excel/datetime.htm#SerialDates

    If that is not the desired date format, you should be able to format axis -- number -- and specify the desired date format for the chart axis.

  5. #5
    Registered User
    Join Date
    01-15-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    12

    Re: Chart adds date labels to X axis that are not in data

    If I format the data as NUMBER then the X-axis only shows the dates I put in the data column so that sort of works. However, now the dates in the data column are 5 digit numbers which can not be easily read as dates.
    Is there not some sort of format that places whatever is in the data column onto the axis as is without changing anything?

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Chart adds date labels to X axis that are not in data

    The "linked to source" should do that (at least it does for me in both 2002 and 2007), so I'm not sure why it did not for you. You might try again to see if it behaves correctly or not.

    If that does not, you may need to upload a sample spreadsheet for us to look at so we can see exactly what you have and what you are trying to do that is not working.

  7. #7
    Registered User
    Join Date
    01-15-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    12

    Re: Chart adds date labels to X axis that are not in data

    Using the LINK TO SOURCE check box does not work.
    See the attached spreadsheet. As shown, the previous 2 weekends are not in the data column but are on the chart's X-axis.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Chart adds date labels to X axis that are not in data

    It looks like you still have the horizontal axis set to choose type automatically, which means Excel will default to a date axis. As originally suggested, make sure the axis type is "category" not "timescale" or "automatic". If you are not sure where to find this option, it is in Chart Menu --> Chart Options --> Axes tab --> three radio buttons under "category X axis". Make sure "category" is selected and not "automatic" or "timescale".

    If I understand your question, that one change should get what you want.

  9. #9
    Registered User
    Join Date
    01-15-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    12

    Re: Chart adds date labels to X axis that are not in data

    Thanks!
    That did it. Problem solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need graph to show alphanumeric values as Y axis labels with Date as Z axis labels
    By Ochimus in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-24-2015, 08:40 PM
  2. Replies: 3
    Last Post: 04-30-2014, 05:57 AM
  3. Chart with 2 x-axis labels
    By Bob77 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-09-2013, 03:36 PM
  4. [SOLVED] X-Axis labels disappear when adding data table to chart
    By phacexcel in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-22-2012, 02:37 PM
  5. Chart data and axis labels
    By Larph in forum Excel General
    Replies: 3
    Last Post: 08-21-2010, 09:09 AM
  6. moving the X - axis data labels on a bar chart
    By robert111 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-29-2008, 12:43 PM
  7. how to put x axis values in the chart instead of x axis labels
    By Sinclair in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-21-2005, 06:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1