+ Reply to Thread
Results 1 to 9 of 9

Categories disappearing from bar chart

  1. #1
    Registered User
    Join Date
    08-25-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Categories disappearing from bar chart

    Hi,

    I have stacked bar chart with 32 major categories and 3-4 sub-categories for each of them. Now as I add one more line in the data table, some of the categories disappear from the chart – just the category name and not he corresponding bar.

    It doesn’t depend on where I add the line since the problem occurs whatever and wherever I write. I’m thinking there couldn’t a limit of 100 data entries because this happens as soon as I add the 101th, but I can’t figure out what settings it could be. Any ideas?

    Thanks!

  2. #2
    Registered User
    Join Date
    08-25-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Re: Categories disappearing from bar chart

    Does anybody have any ideas about how to solve this?

    I have also tried to start adding data to the table from scratch in a new file and I ended up with the same problem, so it's not something off with the spreadsheet but it's with the table/chart itself.

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

    Re: Categories disappearing from bar chart

    Can you upload a sample that illustrates the problem?

    When I mock up something random in my copy of Excel. Whenever there are more categories than can be clearly displayed along the axis, Excel automatically chooses to display every other category or every 3rd category, or whatever it deems appropriate to make a readable axis. The bars/columns are still there, as you note, but the axis label disappears.

    For fewer than 100 categories, I can force the chart to display every category label by changing the appropriate setting in the format axis dialog. However, it seems that Excel cannot display every category label for more than 100 categories. I cannot see any way to change this, so I suspect that this might be a limitation in my version of Excel.

    Other than being able to replicate your description of the problem, I can offer no solution.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    08-25-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Re: Categories disappearing from bar chart

    It looks like you have already managed to replicate the problem, anyway here is the mock up if it can be helpful. There are currently 100 tasks and as you add one more, for example in line 138, Excel removes some of the categories from the chart.
    Attached Files Attached Files

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

    Re: Categories disappearing from bar chart

    Excel removes some of the categories from the chart.
    I think you are understanding correctly, however, to be precise in language, what I see is not that Excel "removes" the categories from the chart. Rather, Excel is "removing" some of the category axis labels from the category axis. The "removed" categories are still part of the chart, but Excel is no longer displaying the axis label. I think you understand this, but I felt it might be important to pick this little nit to be clear.

    As noted above, I cannot see any way to force Excel to display more than 100 axis labels on a category axis. I note that, between 100 and 200 categories, Excel displays every other label. If I extend your data beyond 200 rows, then it starts to display only every 3rd category label, so it seems to me that there is a hard coded limit of 100 axis labels on a category axis. I cannot find, nor am I aware of, any setting that will change this behavior.

    Perhaps someone else knows more about this behavior.

    ETA: This thread claims that it is a physical size issue: https://www.mrexcel.com/forum/excel-...ll-them-2.html though I could not replicate. It seemed that, no matter how large I made the chart, it still would only show 100 axis labels. Perhaps it needs to be very, very large.
    Last edited by MrShorty; 08-30-2017 at 03:26 PM.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Categories disappearing from bar chart

    Using 2010, I added a few categories to the table and, sure enough, the chart started showing only every second one.
    But I then changed the 'Interval between labels' from 1 to Automatic (right-click on axis → Format Axis → Axis Options → second from top on right-hand side - see attached pic) and voila the labels came back. I know this doesn't make any sense , but it seems to work.

    It also depends on the size of the font used - if I then increased the font size for the axis to 14, the labels went back to being every second one.

    Attached is the file with it working (for me at least).

    Another option is to 'fake it', apparently - see this thread: https://www.excelforum.com/excel-cha...is-labels.html

    Hope that helps.
    (sorry the pic's so big!)
    Attached Images Attached Images
    Attached Files Attached Files
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

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

    Re: Categories disappearing from bar chart

    I can confirm Aardigspook's observation. With the interval between axis labels set to automatic, this behavior seems to be dependent on chart size and font size -- basically it seems to be up to Excel to decide what will fit legibly within the available space.

  8. #8
    Registered User
    Join Date
    08-25-2017
    Location
    Los Angeles
    MS-Off Ver
    2016
    Posts
    4

    Re: Categories disappearing from bar chart

    That's great, thanks guys. I'm pretty sure I've tried to change all the options several times but I must have missed this one

  9. #9
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Categories disappearing from bar chart

    You're welcome - glad we could help.

    As you've only recently joined, you may not know that you can mark the thread as Solved if your original question has been taken care of so others know there's an answer here. Instructions to do it are in my sig - if you could take a moment to do it, that would be appreciated. Thanks.

+ 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. Chart from Categories with Sub-Categories
    By Chris Nolen in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-06-2017, 09:49 AM
  2. Bubble Chart labels disappearing
    By DianaJC in forum Excel General
    Replies: 1
    Last Post: 02-24-2015, 07:55 PM
  3. Disappearing Chart Line
    By K m in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-17-2014, 05:33 PM
  4. [SOLVED] Add line chart to bar chart with multiple categories
    By EthanM in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-02-2014, 04:12 PM
  5. Chart data outlines disappearing?
    By qwopzxnm in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-27-2007, 12:04 PM
  6. GURUS - Chart disappearing
    By plarb in forum Excel General
    Replies: 1
    Last Post: 10-11-2005, 09:05 PM
  7. [SOLVED] Disappearing Chart Axis
    By Richard in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-15-2005, 11: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