+ Reply to Thread
Results 1 to 7 of 7

Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

  1. #1
    Registered User
    Join Date
    03-03-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    Hello All,

    I'm sorry to start my posing in these forums by picking your brains. I've been trying to get this to work for a day and a half and, as of yet, cannot figure out a solution.

    My problem is this: In a Line chart in Excel 2007, I need to center my years between the applicable tick marks on the X axis.

    I followed the example here:

    http://peltiertech.com/WordPress/dat...entered-years/

    and got close but could never seem to get it to work quite right. I think the formatting in the example might be different from mine for some strange reason.

    Does anyone have a straightforward way of doing this that I could (eventually) build into a VBA macro to reproduce these charts every month? Attached is a sample of the data and charts where I would need the labels centered.

    Test.xlsx

    Again, I'm sorry to barge in here with questions but this is making me feel like an idiot. How could Microsoft look over something so obvious? Or am I the one overlooking the simple solution? :D

    Thank you,
    Michael

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

    Re: Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    Looks like you did not follow the instructions completely.

    First the Label series needs to be on the secondary axis. The you need to adjust the data points for the series to 1 for each year.

    You will need to enable the secondary horizontal axis and the apply data labels to the series and position them below.

    finally remove the labels from the primary horizontal axis.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-03-2011
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    Andy,

    Thanks for weighing in. I really appreciate the help. Just two questions:

    - If the goal is to create text boxes and locate them under the series tick marks, Why not just do so directly?

    - How did you get the years centered on the second horizontal axis between the ticks? That's exactly the effect I am going for.

    Thanks again,
    Michael

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

    Re: Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    If you use textboxes, rather than data labels, then they will not be linked to the chart. If you add more periods you will need to add another textbox and reposition all existing ones.
    With a data series all you need do is extend the data range and adjust the scale.

    As both original website and I described. You need to move the data series to the secondary axis. Then format the secondary axis to display data labels in the Low position rather than Next to axis.

  5. #5
    Registered User
    Join Date
    01-29-2014
    Location
    ottawa
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    Hi,

    I am trying to do something similar and this post was really usefull. However I still can't get what I need. What I need to do is:

    - I want the year to be center (actually if I have monthly data I would like year (label) to be aligned with the June value)
    - Also I would like a major tick mark at the beginning and end of each year and a minor (smaller) tick mark at every quarter (jun, sep, dec, mar)

    Any advice?

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,293

    Re: Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    kmartinboc, even though your question is similar, it is still a new question. Please start a new thread with your question, and it would also be helpful to attach your file, if it does not contain sensitive data. Please take the time to review our rules. There aren't many, and they are all important.

    Rule #2 Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  7. #7
    Registered User
    Join Date
    09-21-2015
    Location
    UK
    MS-Off Ver
    2010 and 13
    Posts
    1

    Re: Centering Years, Months, Weeks, or labels between X-Axis Tick Marks.

    Changing the text alignment to plus 1% often does the trick

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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