+ Reply to Thread
Results 1 to 7 of 7

Date axis not in order (using table to dynamically update chart)

  1. #1
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Date axis not in order (using table to dynamically update chart)

    Hello All,
    I have a chart with the months on the x axis. The chart gets its data from a table in the same workbook that I update monthly. In the table the months are not in the real order, I have `Mar, Apr, June, May`, so that when I add July next time in between June and May, the chart updates with the new month automatically....although sometimes I have to extend each of the references of the series values and the axis label range by one column...first of all, is it possible to do this in a better way?
    My other problem is that Excel does not organize the months in the correct order `Mar, Apr, May, Jun` in the chart (earlier it used to!!)...so it leaves them as they are in the source table `Mar, Apr, June, May`. I was trying to sort this out and was looking for the `Bounds` at Axis Options, but they don`t appear. Not sure though if setting the maximum at the Bounds would solve my problem. Also, when I try to flatten this file, all the values disappear from the chart and instead of the month the serial numbers appear. What could be the issue?
    Thank you.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,079

    Re: Date axis not in order (using table to dynamically update chart)

    We really need to see a workbook with examples of data and chart

    The chart will only order dates if they are true date values, not just textual month names, and the axis is set to Date (automatic should also work if real dates are present).
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Date axis not in order (using table to dynamically update chart)

    Thanks Andy. I have attached a part of the file for you.
    Attached Files Attached Files
    Last edited by atoga; 07-21-2015 at 07:15 PM.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,079

    Re: Date axis not in order (using table to dynamically update chart)

    Your dates are not true dates but simply text.

    If you transpose and consolidate your data into a single table then you can use real dates and the chart will automatically update and extend.

    if you can not convert your data then you could use named ranges to handle the series size issue.
    For the dates you would need to use a new range for those rather than the table column headers.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Date axis not in order (using table to dynamically update chart)

    Thank you Andy. So how do I this with the named ranges? I am sorry I have never used named ranges.
    I have named the table headers as `Dates`....what should I do next?

  6. #6
    Registered User
    Join Date
    02-27-2015
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    18

    Re: Date axis not in order (using table to dynamically update chart)

    Thanks, finally I converted the table ranges into a simple range and managed to format the dates as dates with the Datevalue function. So now I can update the charts by inserting columns into the ranges.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,079

    Re: Date axis not in order (using table to dynamically update chart)

    You will find information on dynamic charts here
    http://peltiertech.com/Excel/Charts/Dynamics.html

+ 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. Change the range in x axis dynamically for line chart
    By dimwit in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-27-2013, 05:23 AM
  2. Replies: 0
    Last Post: 02-16-2013, 01:39 PM
  3. Replies: 1
    Last Post: 07-12-2012, 02:33 PM
  4. Help to dynamically update Excel chart
    By zad1999 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2010, 08:35 AM
  5. Update chart dynamically
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2010, 05:41 AM
  6. OWC.CHART - Why does my X-Axis come out in the wrong order - somet
    By DuffyD in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-23-2006, 12:30 PM
  7. [SOLVED] Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis
    By mw210461@yahoo.com in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-13-2005, 05:05 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