+ Reply to Thread
Results 1 to 6 of 6

Excel line plotted graph changes when horizontal axis is changed to Date

  1. #1
    Registered User
    Join Date
    01-31-2023
    Location
    Malta
    MS-Off Ver
    365
    Posts
    3

    Question Excel line plotted graph changes when horizontal axis is changed to Date

    Hello everyone

    I have a problem with plotting that I have experienced for years... so any help would be greatly appreciated!

    Basically, when plotting a line graph, the plotted lines are smooth as they should be. But the dates on the horizontal axis are not always displayed as they should. So, upon changing the format of the horizontal axis to Date Axis - in order to have the dates correctly displayed - the plotted lines become jagged and un-smooth. The plotted lines become smooth once again upon formatting the horizontal axis to Text... but I need to have the lines smoothened and correct dates at the same time if possible Again, this has frustrated me for years and numerous searches online proved futile... so please help me if you know how to solve this!

    Please find the attached photo of the graph before and after formatting the horizontal axis...zoom in and you'll see how the plotted lines change.plotprob.jpg

    Zoomed image showing the changes of the plotted lines when horizontal axis is changed: plotprob detail.jpg

    Many thanks for your kind attention
    David
    Last edited by dbugeja; 01-31-2023 at 05:47 AM. Reason: adding another photo detail

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Excel line plotted graph changes when horizontal axis is changed to Date

    Hi and welcome
    there is no way to zoom in or do anything with the provided pictures (due to the forum itself I guess). Perhaps post a sample sheet ( see yellow banner for how-to). No pics please

  3. #3
    Registered User
    Join Date
    01-31-2023
    Location
    Malta
    MS-Off Ver
    365
    Posts
    3

    Re: Excel line plotted graph changes when horizontal axis is changed to Date

    hi and thanks for getting back to me. I have managed to edit my question and added a detailed image that shows the problem...
    thank you so much!!

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

    Re: Excel line plotted graph changes when horizontal axis is changed to Date

    I will echo Pepe Le Mokko -- a sample file will be much more useful for debugging this than pictures.

    As a guess, I would guess that your data includes date + time information. A text axis doesn't care anything about the numeric values of the categories, it simply plots each entry as text. A date axis, on the other hand only sees the date information and cannot see the time of day information. A line chart/date axis will show all points on a given date at the same horizontal position along the axis -- resulting in the "vertical" line segments in your pictures.

    If I have guessed right and the problem is that a line chart+date axis cannot correctly show date+time information, then the (not always satisfactory) solution is to use an XY scatter chart instead. A scatter chart sees both date and time information and plots the data accordingly. The main downside is that you lose the date axis formatting options like showing one axis label for each month.

    Did I guess right?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    01-31-2023
    Location
    Malta
    MS-Off Ver
    365
    Posts
    3

    Re: Excel line plotted graph changes when horizontal axis is changed to Date

    You are most kind. I will try to upload the file, but this happens with every plot I make.
    Your comment is interesting... I tried to change the format of the columns that include the raw values to: general, text, number, custom... but still, the plotted lines on the chart remain jagged.

    the file is basically comprised of 3 columns: DATE, temperature values, and RH values. The date (and time) are kept separately in column A. The other values are kept in another two different columns (B, and C). The values are in no way linked to a timestamp, they're simply a list of numbers pasted from a text file. This is why I could not understand the problem. If you still need a file, I will prepare a small one as an example and learn how to upload it here.

    Thank you so much for this, I really appreciate your help!!!
    David

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

    Re: Excel line plotted graph changes when horizontal axis is changed to Date

    I would not expect number formatting to have anything to do with this issue. Number formatting only changes what you see in the cell or on the axis. It doesn't change the underlying cell values at all.

    You claim that column A is date (and time) data. I'm going to assume it is imported correctly as numbers and not being stored as text. Also assuming that there are multiple entries per day (at different times of the day??), then what you show in your second pictures is generally what I would expect of a line chart + date axis. I'm still inclined to recommend changing to an XY scatter chart to correctly show both date and time information.

    If it helps, here's an example file (with random data) I put together to illustrate the difference between XY scatter and line + date axis: https://www.excelforum.com/excel-pro...ml#post4071488
    Last edited by MrShorty; 01-31-2023 at 10:41 AM.

+ 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. Horizontal Bar Graph with Plotted Point
    By djdietz in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-10-2019, 03:22 PM
  2. [SOLVED] Bold Horizontal line need to be plotted
    By Sekars in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-08-2016, 09:02 AM
  3. Line Graph variable horizontal axis
    By Kat82 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-07-2015, 11:20 PM
  4. Scaling on horizontal axis is irregular, leads to improper line graph
    By Al Lagasse in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-16-2013, 04:03 PM
  5. Line graph with dates on horizontal axis
    By fish2012 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-04-2012, 08:52 AM
  6. [SOLVED] How to make a line graph start at 100 not 0 on the Horizontal axis
    By Buster^ in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-07-2012, 02:55 PM
  7. Replies: 1
    Last Post: 03-01-2011, 05:09 PM

Tags for this Thread

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