Issue with Excel 2010 not displaying all X-axis labels

Hi Everyone,

I've been looking for a solution to this problem for a while in a few different places, but i'm out of luck so far... I'm hoping that somebody with a lot more experience than me can help!

Basically the problem is that I need to display a bar chart with 150 catagories, and each individual X-axis label needs to be displayed.

Easy right?! Just Format the axis and change the 'Specify unit interval' option to 1... Damn! For some reason this option only works if you have 100 or less catagories to display, or you set the text size to 3.7 (which is very small...!). If you need to display 101 catagories, Excel won't let you, as far as I have found...

Unfortunately I can't share the data i'm using as it is protected, but the problem is easy to replicate by creating more than 100 cells of mock data and attempting to bar chart it.

It would be great if anyone has come across this before and solved the problem could let me knwo the solution. Or I would be happy to work along side somebody to find a solution.

Thanks,

Rich

Hi RJAlison,

Welcome to the forums.

I agree with JosephP, why would you need to show 150 items? Can you not just show the top 10 or 20? Showing the outlyers might be more beneficial.

Alternately, if your categories are years or something similar, I helped someone with a similar problem, but their categories were years so we were able to just show the Decades.

How-to Show Decades and Highlight a Year in the Horizontal Axis

Good Luck,

Steve=True

Sorry, I think i meant to write column chart isntead of bar...

As to the catagories, they show how things change across the areas of a country, and they can't be aggregated (like years -> decades) because there are large variations between the areas inside the larger regions they would be aggreggated into - sorry I can't be more specific, but the work is protected as I mentioned!

I also need to show all 150, and not just the outliers, because each area is important in its own way - and the people who are seeing that chart need to be able to point at a column and see who will be effected. I've discussed the benefits of not showing all 150 with my 'stakeholders', btu they are adamant that they want all 150 on one chart...

It is quite annoying that this has been left out of the capabilities of the program though...

Thanks for your replies,

Rich

Wow! Thanks Andy!

That is almost exactly what I need! I'm having a little bit of trouble replicating what you've done with my data, and i'm going to be a little bit distracted for today, but i'll get back to you asap and let you know how I get on!

Thanks for all your help, it really is appreciated! :D

Rich

Andy... You are a legend!

I've been playing with your solution, and while it is annoying that we have to do it this way, with a little bit of work it looks great!

My data has positive and negative values, so I wasn't able to make the data labels sit under the axis like you did - How did you do that?! But you've saved me a headache with my stakeholders, and i've picked up some Brownie Points in the process.

Thanks!