+ Reply to Thread
Results 1 to 6 of 6

Remove unneeded dates on Scatter x-axis chart

  1. #1
    Registered User
    Join Date
    03-24-2017
    Location
    WI USA
    MS-Off Ver
    O365
    Posts
    5

    Remove unneeded dates on Scatter x-axis chart

    I have a data set with dates used as the x-axis on a scatter chart. When Excel charts the data it includes dates on the x-axis that I haven't used in my data set. I know the trick to change the Axis Type to represent the data as a Text axis but this distorts the data. Can someone help me solve this frustrating challenge and remove the unneeded dates on my chart?
    Attached Files Attached Files

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

    Re: Remove unneeded dates on Scatter x-axis chart

    It's not clear to me exactly what you want, but I'll take a stab at what I think you want.

    1) (optional) your chart is actually a line+XY scatter combination chart. I would change the chart type of one of the data series so that both series are the same chart type. (I would probably change the data1 series to a line chart type, then format the series so it has the desired marker but no line).
    2) If I understand what you want, you only want the axis to display the dates for which you have data. The axis cannot do this, so you need to make your own "axis". This is done by adding a "dummy series" as the axis and labeling it.
    2a) Enter a column of 0's (I selected E3:E11, typed 0, then confirmed with ctrl-shift-enter to fill the entire range with 0.)
    2b) copy (ctrl-c) select the chart, paste. This should add the dummy series to the chart. Format the dummy series with a marker (+) and no line.
    2c) Add data labels to the dummy series, then format the data labels so they display the "category name", and the data labels are below. https://support.office.com/en-us/art...2-f467c9f4eb2d
    2d) format the real axis so that it has no axis labels.
    Is that close to what you want?
    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
    03-24-2017
    Location
    WI USA
    MS-Off Ver
    O365
    Posts
    5

    Re: Remove unneeded dates on Scatter x-axis chart

    Thanks for the help! This is getting very close. I've made the changes you suggested and the chart appears very close to what I had hoped for. Is it possible to remove the "empty" date ranges on the chart? The image below shows the red areas that I would like to remove.

    chart1.png
    Attached Files Attached Files

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

    Re: Remove unneeded dates on Scatter x-axis chart

    I originally anticipated that you merely wanted to not show the extraneous dates, but you still wanted to have the dates properly spaced. Wanting to completely remove those dates from the chart is a different task.

    You still need a date/value axis for the horizontal axis to allow for multiple entries for the same date, but we need some way for Excel to "ignore" that natural spacing between dates.

    At this point, I would retract my first recommendation to make both series a line chart, and probably build this on a scatter chart. Starting from the file in the first post:

    1) (optional) your chart is actually a line+XY scatter combination chart. I would change the chart type of one of the data series so that both series are the same chart type. (I would probably change the avg series to a scatter chart type, then format the series so it has the desired line but no marker). If Excel add in the secondary axis system, delete the secondary axes.
    2) We need different numbers for the X axis data, so add a column to the table (I would probably use column A) and add a suitable number for each date. 1 for 2 Jan, 2 for each 4 Jan, 3 for 5 Jan, and so on. For a small data set like this, it is probably easiest to simply enter the numbers. For a larger data set, you may want to work on a way to automate this.
    3) Change the X axis data for each data series to point to this helper column.
    4) In order to get the dates on the horizontal axis, you still need to make your own "axis". This is done by adding a "dummy series" as the axis and labeling it.
    4a) Enter a column of 0's (I selected E3:E11, typed 0, then confirmed with ctrl-shift-enter to fill the entire range with 0.)
    4b) copy (ctrl-c) select the chart, paste. This should add the dummy series to the chart. Format the dummy series with a marker (+) and no line.
    4c) Add data labels to the dummy series, then format the data labels so they display the "value from cells", and the data labels are below. https://support.office.com/en-us/art...2-f467c9f4eb2d
    4d) format the real axis so that it has no axis labels.
    Is that closer to what you want?

  5. #5
    Registered User
    Join Date
    03-24-2017
    Location
    WI USA
    MS-Off Ver
    O365
    Posts
    5

    Thumbs up Re: Remove unneeded dates on Scatter x-axis chart

    BOOM!!!

    MrShorty, you are a true Excel God amongst us mere mortals! Thank you for your valuable time and effort. This is exactly what my expected outcome was. I will now take this knowledge and share it with the world.

    Capture2.JPG

    Excel Forum FTW!

  6. #6
    Registered User
    Join Date
    01-14-2011
    Location
    tianjin
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Remove unneeded dates on Scatter x-axis chart

    plz upload your file

+ 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. Help with Dates on X axis (scatter plot)
    By apc2012 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-13-2014, 11:41 PM
  3. Replies: 2
    Last Post: 02-13-2014, 06:18 AM
  4. 3D XY scatter chart (x,y,z axis)?
    By Lv27 in forum Excel General
    Replies: 3
    Last Post: 12-09-2012, 07:22 AM
  5. Replies: 3
    Last Post: 06-19-2012, 06:51 PM
  6. scatter chart x axis problem
    By Suzan in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-03-2005, 08:05 AM
  7. Scatter Chart Axis as text C#
    By Pablo Villalobos via OfficeKB.com in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-18-2005, 07:06 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