+ Reply to Thread
Results 1 to 26 of 26

Adding Multiple Level X-Axis labels

  1. #1
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Adding Multiple Level X-Axis labels

    Hi-

    Please see my attached data/chart. Currently the labels in lines 31/32 are just pasted in. This requires a lot of manual work. Is there a way to include in the bar chart, without having to paste in. My goal would be to have the chart automatically update when I update the data points. Chart Example.xlsx

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Adding Multiple Level X-Axis labels

    This is pretty close to what you wanted (I think...)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    That works. Can you provide me a step by step on how get the grid like you have created? Also, is there a way that I can edit so no gridlines in the grid you created? Do i need all four categories,or can i just show wgt, and ctr?

    Thank you

  4. #4
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    if that is not possible using data table, how can I go about doing something similar using embedded? That may have more flexibility?

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Adding Multiple Level X-Axis labels

    I first created two more datasets (WGT & CTR) and plotted them on a secondary Y-axis. Of course that looked a mess. The next step was to set the fill and line colour of both dataset to "none" and the labels on the secondary axis to "none". The two datasets and their odd second Y axis then disappear.

    Finally, I added the table from Layout/data table. You can get rid of the gridlines from Layout/data table/datatable options/border colour and set the transparency of the solid line to 100%. I don't know how to display only the CTR and WGT values. You have two options. You could remove the values from the chart, so they only appear once - or cheat. In the attached file, I have hidden the offending rows with a text box, filled in white...
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Adding Multiple Level X-Axis labels

    There may be a "real" way to do it. But I'm not aware of any way. Not that that means much!!

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

    Re: Adding Multiple Level X-Axis labels

    One way is to use the secondary horizontal axis to display those values.
    Add series to secondary axis and use category labels that concatenate the information you want to display.

    The labeling for the 2 rows is provided by the secondary axis title.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Adding Multiple Level X-Axis labels

    Duhh. Why didn't I think of that...

  9. #9
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    Thank you. That is exactly what I am looking for. Since, I am not a chart expert, can you please help in guiding me on how to do below?

    -Find/use secondary horizontal axis to display those values.
    -Add series to secondary axis and use category labels that concatenate the information you want to display.

    Thank you, and I will not forget to add reputation.

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

    Re: Adding Multiple Level X-Axis labels

    Select chart.
    On Design tab use Select Data to display data dialog.
    Add new series.
    Select new series in chart and CTRL+1 to display format dialog. Select Secondary axis.

    Layout tab > Axis > Secondary Horizontal axis.
    Format axis to set axis labels low.

  11. #11
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    Andy-

    Is that the same directions for excel 2010? I am still not finding where I can select a secondary axis. Also, when I select a new series it wipes out existing horizontal data that has country names.(I'm sure has to do with above)

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

    Re: Adding Multiple Level X-Axis labels

    Those are 2010 instructions.
    Yes if you don't move the series to the secondary axis first then changing the range for category labels will affect all series.

    With the chart selected.
    Chart Tools > Layout > Axes > Secondary Horizontal Axis > Show Left to Right

    1022875.png

  13. #13
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    do I need an add on? Under axes, I only have Primary Horizontal axis, and primary vertical axis. I do not have the secondary ones as in your pic. Sorry for all the questions, and thank you for your patience.

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

    Re: Adding Multiple Level X-Axis labels

    No Add on required. If you can't see the secondary axis options it's because you have not set a series to the secondary axis.

    Select the newly added series and press CTRL+1 to display Format dialog. Set to secondary axis.

    1022875b.png

  15. #15
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    Here are the steps I take, that are not working. I right click in the chart-> Select Dta->in legend entries (series), I add ->series name, blank, OK-> in horizontal (category) axis labels, edit, select data, ok. This wipes out original horizontal. I do not know how to add a series that is separate.

  16. #16
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    also, are you using a custom chart? if I right click in yours change chart type, it comes up as template, where mine is bar chart. Not sure if that is one of reasons I can't replicate.

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Adding Multiple Level X-Axis labels

    Rely on Andy's advice for the remainder of this. He's A1 on all aspects of charting (amongst others). I'm on Excel 2007 (makes it sound like heroin doesn't it...) and 2010 may well be different.

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

    Re: Adding Multiple Level X-Axis labels

    You need to do this in 3 stages.

    Here are the steps I take, that are not working.
    1. I right click in the chart-> Select Dta->in legend entries (series), I add ->series name, blank. Now close Select Data
    2. Select Series and move to secondary axis as per post #14.
    3. Return to Select Data dialog, in horizontal (category) axis labels, edit, select data, ok.

    "if I right click in yours change chart type, it comes up as template,"
    This happens once you have multiple chart types (combination). If you select a series and display chart type it will highlight correct chart type

  19. #19
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    Chart Example.xlsxSee attached. I just get a big green bar, and was unable to replicate. If I were to choose a secondary horizontal axis to show left to right it just moves the bar to the top. Please let me know what I am doing wrong. Thanks

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

    Re: Adding Multiple Level X-Axis labels

    You have not selected any data for the values of that new series.

    Currently you have =SERIES("blank",Example!$G$17:$G$27,{1},3)

    where as it should be =SERIES("blank",Example!$G$17:$G$27,Example!$F$17:$F$27,3)

    Also you had not added secondary horizontal axis as previously detailed.

  21. #21
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    I have series name now, but nothing else. I am just very frustrated, because I am so lost. I am following your instructions. I have asked multiple people, and no one knows what to do following your instructions.
    Attached Files Attached Files

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

    Re: Adding Multiple Level X-Axis labels

    Contains step by step instruction with pictures.
    Attached Files Attached Files

  23. #23
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    Chart Example.xlsxAwesome. Thanks. I got it. Can you take a look one more time. I have some weird line under my title. Not sure what I did.

  24. #24
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    also, for some reason the bottom isn't in inline with the country name. How did you get yours to be like that?

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

    Re: Adding Multiple Level X-Axis labels

    The line at the top is the secondary axis. So you need to format that to have No line and no tickmarks.

    The numbers do no align because the number of categories is different. You need to extend the data range used to match that of the information on the primary axis.
    So Thailand is the data point missing. Either extend formula to calculate value for Thailand or leave empty. Then extend series formula range.

  26. #26
    Forum Contributor
    Join Date
    07-21-2013
    Location
    washington dc
    MS-Off Ver
    365 MSO
    Posts
    473

    Re: Adding Multiple Level X-Axis labels

    Thank you so much for your help!!! This is a Life Saver. I will add rep.

+ 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. Graph X axis (multi-level category labels) not showing all the text
    By mclhone in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-05-2013, 09:33 PM
  2. [SOLVED] X-Axis labels disappear when adding data table to chart
    By phacexcel in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-22-2012, 02:37 PM
  3. Replies: 2
    Last Post: 05-16-2010, 11:23 PM
  4. [SOLVED] Alignment of Multiple-Level Category Labels Not Possible?
    By MCP in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-26-2005, 03:06 AM
  5. [SOLVED] Adding x axis labels
    By Happy in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-21-2005, 10: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