+ Reply to Thread
Results 1 to 7 of 7

X Axis Dates in Scatter Type Graph

  1. #1
    Registered User
    Join Date
    04-17-2019
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    X Axis Dates in Scatter Type Graph

    Good Morning!

    I have a very large data set of dates vs temperature. I have recorded the temperature for each hour for a year and would like to plot it in a scatter graph (when I plot in a line graph it displays screwed results because of the many data points). I can not have my X-Axis display the first of every month; instead it displays random dates throughout the year.

    How can I format the X-axis to display the first of every month?

    Thanks for your help.

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

    Re: X Axis Dates in Scatter Type Graph

    This looks like an extension of yesterday's thread: https://www.excelforum.com/excel-gen...tom-dates.html I proposed two possible approaches yesterday -- do you have specific questions about either approach? Are you familiar with how to create combination charts? Did you follow Peltier's tutorial for creating an arbitrary dummy axis?

    A quick overview of each approach:

    Combination chart:
    1) Select your two columns of data and insert a line chart (yes, it will be ugly at this point).
    2) Select your two columns of data again -> copy (ctrl-c) -> select the chart -> paste special -> as new series, values in columns, category values in first column, but don't replace existing categories.
    3) Select either data series -> change chart type -> xy scatter. My copy of Excel automatically adds the secondary vertical and horizontal axes.
    4) Format the two horizontal axes so that they start and end on the same date. The scatter chart axis will only accept direct serial number entry (you can't enter the max and min as dates), so you may need a cell or two in the spreadsheet where you can enter the desired date and convert the date to its serial number.
    5) "hide" (format as no line and no marker) the line chart series and any of the axes that you don't want to see.

    Step 4 is the most difficult. As data are changed/added/removed, you will need to repeat step 4 to make sure that both horizontal axes have the same start and end date.

    Arbitrary dummy axis:
    1) Select data and insert scatter chart.
    2) In a convenient place in the spreadsheet, add two columns where the left column contains the dates where you want the axis "markers" to appear. The right column will have the value corresponding to the desired vertical position of the dummy axis (minimum y axis value, if you want the axis at the bottom of the chart, for example).
    3) Add these two columns to the chart as a new data series with the x values in the first/left column and the y values in the second/right column.
    4) Add the data labels to this series and format the data labels to show the x value and be positioned below. If necessary, apply the desired number/date format.
    5) Format the horizontal axis to have no tick marks or labels. Format the dummy axis series so that it looks like an axis (+ symbol for marker and line as desired).

    As you add/change/remove data, you will need to adjust the values and entries for the arbitrary dummy axis series in the spreadsheet. I would expect the arbitrary dummy axis approach will be easier to use and maintain in the long run, because you won't need to be formatting the two horizontal axes and figuring out what serial number goes with which date. Either approach should work -- it is just a matter of you deciding which approach you prefer.
    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
    04-17-2019
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: X Axis Dates in Scatter Type Graph

    I am stuck on how to do 4) for the arbitrary dummy axis method. Could you explain in more detail?

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

    Re: X Axis Dates in Scatter Type Graph

    This from MSFT Excel's help files should describe how to add data labels: https://support.office.com/en-us/art...2-f467c9f4eb2d

  5. #5
    Registered User
    Join Date
    04-17-2019
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: X Axis Dates in Scatter Type Graph

    I am unable to do this successfully. Are you able to provide a file example?

    Thanks

  6. #6
    Registered User
    Join Date
    04-17-2019
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: X Axis Dates in Scatter Type Graph

    I am formatting the data labels below the points but the labels do not reach low enough to the x-axis because the graph is a positive parabolic shape

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

    Re: X Axis Dates in Scatter Type Graph

    I can maybe provide a sample later, but my older version of Excel does not natively support adding data labels from cells like your newer version does. I worry about the forward compatibility. What are you trying when you add the data labels?

+ 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. scatter graph (text y-axis)
    By Adam Beauregard in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 10-25-2017, 09:28 PM
  2. Scatter graph, x axis
    By cardyem in forum Excel General
    Replies: 7
    Last Post: 10-28-2015, 12:50 PM
  3. Scatter graph - Time on Axis is wrong?
    By domgilberto in forum Excel General
    Replies: 3
    Last Post: 09-11-2015, 10:58 AM
  4. Scatter Graph X-Axis Problems
    By ih8xc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-13-2011, 03:23 PM
  5. How to plot scatter graph on horizontal axis
    By alsupport in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2010, 04:53 AM
  6. Replies: 2
    Last Post: 03-25-2010, 06:22 AM
  7. Time axis column and scatter/line graph
    By kraymond in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-08-2005, 08:06 PM

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