+ Reply to Thread
Results 1 to 3 of 3

Odd behaviour in Excel 2016 charts

  1. #1
    Registered User
    Join Date
    London, England
    MS-Off Ver

    Odd behaviour in Excel 2016 charts

    I have a long running experiment with approx. 150 data points per day over approx. 3 years

    Up until now I have been able to chart this in excel 2016 easily by selecting the entire data range and selecting a line type chart job done.
    Moreover - if I wanted to look more closely at just sections of the timeline I have been able to simply click on the horizontal (time) axis and in the format axis panel that opens on the right-hand side of the screen manually edit the axis start date and end date.
    See screenshot 1 ( you may need to open the image in a new window to see the format axis panel on the right)
    This worked perfectly for my needs.

    However, I have recently needed to reorder rows in the data set to clean them up and then redraw the chart, again by selecting the whole data set and selecting line chart.
    However now I no longer have the option to manually edit the horizontal axis date range - and instead the format axis options look like this
    See this screenshot ( I would post the spreadsheet but its huge and I have not been able to demonstrate the issue on other charts)
    see screenshot 2
    i have tried various combinations of options but don't seem to be able to get the option to alter the time axis start / finish dates manually
    What am I missing?
    Attached Images Attached Images

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions

    Re: Odd behaviour in Excel 2016 charts

    Most probably one (or more) of your dates are now no longer dates (at least in excel sense).

    See attached file where in cell A24 is something which looks as date.

    In Poland all other dates in this column are by default formatted dd.mm.yyyy, so this cell looks exactly as other cells. In your case date format can be different so you easily can spot the difference.

    And check axis formatting. It's like your case 2.

    Now write there real date (or just copy a cell from above) and check axis formatting again.

    So inspect your data fir non-dates. You may use ISNUMBER if it's not easy to spot in a long dataset (see column F of the attached file)

    It also might be, that all the dates are dates, but as a range for axis labels you selected not only dates, but also a dates column header (which is most probably text!).
    Attached Files Attached Files
    Best Regards,


  3. #3
    Registered User
    Join Date
    London, England
    MS-Off Ver

    Re: Odd behaviour in Excel 2016 charts

    that seems to be it -

    for anyone else coming across similar issues

    all dates looked like dates in the new sheet - ie 18/12/18 format
    and i had previously wondered if something like this was an issue - because excel is clearly trying to automatically detect what kind of number the axis is in - so to try and ensure it was seeing only dates in that row - i had already tried highlighting the entire date row - and formatting it as a date - and thought that would have solved it

    however - by using the ISNUMBER function - (inserting a fresh row above the date row - and displaying the result of the ISNUMBER query for each supposed date formatted cell there) - as Kaper indicated - it revealed a FALSE outcome for most of the dates in that row - a little surprising.

    i was curious to see how this had happened - and went back to check my old chart ( the one that worked fine ) and indeed that returned TRUE results for the dates in the same row with the the ISNUMBER query

    i can only conclude that something happened in the copy and paste operation used to create the new sheet of data that this chart is based on, and this somehow caused excel to drop or lose the number properties of these cells.

    a copy, paste special, values from a true set of dates corresponding to the same range has fixed the issue without losing any data or comments in cells

    many thanks to Kaper for pointing me in this direction - it is very much appreciated

+ 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] EXCEL 2016 - Hyperlink Unusual behaviour
    By Devvy in forum Excel General
    Replies: 19
    Last Post: 06-03-2021, 05:36 AM
  2. Replies: 0
    Last Post: 11-20-2019, 04:05 AM
  3. Appending Charts using Power Query Excel 2016
    By STokhi1 in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 07-02-2018, 07:30 AM
  4. Charts not reacting although macro working following upgrade to Excel 2016
    By NannyNetty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2018, 04:40 AM
  5. Replies: 1
    Last Post: 09-18-2017, 08:27 AM
  6. Help! Automated Email coding for Excel 2016 and Outlook 2016 problems
    By lisa6421 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2017, 08:07 AM
  7. Histogram Charts in Excel 2016
    By gballard in forum Excel General
    Replies: 1
    Last Post: 08-25-2016, 06:46 AM

Tags for this Thread


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