+ Reply to Thread
Results 1 to 6 of 6

Scatter Chart Help - Excel 2010 - Date as X axis

  1. #1
    Registered User
    Join Date
    03-24-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    2010
    Posts
    3

    Scatter Chart Help - Excel 2010 - Date as X axis

    Hi,
    I am trying to create a scatter chart with dates as the x values. I need to have the x axis values displayed on the x axis be simply year, but the data points themselves need to reflect the specific date, i.e. if a data point relates to June 5th 2010 I need the point to appear between the ticks on the x axis for 2010 and 2011. Currently I'm only able to get the data points to line up on the year ticks and I'm not able to get the x axis to show the right year. I have attached two files - one very basic Excel file and one drawing of what I am trying to do. I know this must be so simple but I can't figure it out! I can't find any place to change the axis type to Date. Any help is greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Scatter Chart Help - Excel 2010 - Date as X axis

    Note that this is actually a line chart with no line. The first issue is that the dates were text and not Excel recognized dates. I retyped them as dates (5/1/2006) and then used custom formatting m-d-yyyy so that they display as before. Format the horizontal axis to Date Axis with the minimum of 1/1/2006 and the maximum of 1/1/2014 and the Major Axis fixed at 1 year (drop down). The Number format for the axis will be custom yyyy.
    Here is a copy of your file with the formatting applied: Copy of Chart in Microsoft PowerPoint.xlsx
    Double clicking the axis will bring up the Axis options box which will show you, better than I can explain, the settings.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    03-24-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    2010
    Posts
    3

    Re: Scatter Chart Help - Excel 2010 - Date as X axis

    Many many thanks JeteMc! Saved by the kindness of strangers!

  4. #4
    Registered User
    Join Date
    03-24-2016
    Location
    Stockholm, Sweden
    MS-Off Ver
    2010
    Posts
    3

    Re: Scatter Chart Help - Excel 2010 - Date as X axis

    Jaha, here I am again with another question. I really do want a bubble chart with the ability to have sized data points based on a third set of data. I have it behaving almost as I want it too, but the dots are appearing 1 year off - i.e. x value 2006-05-01 is showing up between year 2005 - 2006. Any ideas?? Please see the attached sheet.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Scatter Chart Help - Excel 2010 - Date as X axis

    Quote Originally Posted by GLilli View Post
    Many many thanks JeteMc! Saved by the kindness of strangers!
    You're Welcome and thank you for the feedback. As to the X axis of the bubble chart I got better, although not great, results by making the following changes in the Format Axis box, minimum fixed: 38790.0; maximum fixed: 41347.0 major unit fixed: 365.25
    Let me know if you have any questions.

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

    Re: Scatter Chart Help - Excel 2010 - Date as X axis

    Scatter and Bubble charts cannot cleanly give you "1st of the year" tick marks the way a line chart with a date axis could (this is a decent discussion of the axis types available, and how they behave: http://peltiertech.com/Excel/ChartsH...ValueAxis.html ). If you expand the date format on your axis, you will see that each tick mark represents a different day during the year. You have manually forced your chart to start at serial number 38500, which corresponds to 28 May, 2005, which correctly displays as 2005 with the chosen "yyyy" date format, but it still means late May 2005 and not 1 Jan 2005. You have left your major unit to 500, which means that your second tick mark is 500 days later, or serial number 39000, which corresponds to 10 Oct 2006. Again this correctly displays as 2006 when formatted "yyyy", but it still means mid Oct. 2006. Your first point at May 1st is correctly positioned between those two dates, so it appears that the bubble chart is working correctly.

    As I indicated, it is difficult to get a good date axis on a bubble chart. The easiest approach might be to test serial numbers until you figure out what serial number corresponds to 1 Jan 2005 -- Enter 1 Jan 2005 into a cell and then format as General. Use that value as your fixed "axis min value", or maybe something a little larger so that the yyyy display will be correct. Then manually set your major tick mark setting to something like 365. Your tick marks will not perfectly be at the first of each year, but they will be close.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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 (XY) chart with X-axis as a date?
    By I-Like-Excel in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-30-2015, 07:59 AM
  2. Replies: 0
    Last Post: 10-09-2014, 05:43 PM
  3. Replies: 10
    Last Post: 07-21-2014, 11:11 AM
  4. [SOLVED] How to use scatter chart (XY) in Excel 2010?
    By GIS2013 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-23-2013, 07:20 AM
  5. Can't input date range as x-axis in scatter with straight lines chart using VBA
    By alapatik in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2012, 11:32 AM
  6. Axis titles in excel scatter chart
    By gdavisjm@googlemail. in forum Excel General
    Replies: 0
    Last Post: 01-01-2011, 07:52 AM
  7. x axis cut off on an Excel 2002 scatter chart
    By Geenie in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-09-2005, 08: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