Column Charts Axis Labels - Not showing all of them

[exkris]

I had a column chart with 90 columns on it and every value for the X axis was present. I had to add another ~20 and now only every second X axis value is displayed. I have:

1) Reduced the size of the text to see if that would show the missing values, nope.
2) Under axis options, the value "Specify interval unit" is equal to 1.


Is there any way I can show each X axis value or is this a limitation of excel column charts?

 

[exkris]

Haha awesome. Thank you Hermanito.

Sometimes its nice to go tell the boss that we are utilizing Excel to its maximum capability.

 

[Hermanito]

More testing learned me the following:
- up to 100 labels, the Interval setting works as expected
- anything above 100 and it starts skipping every other one
- anything above 200 and it starts skipping 2 out of three

So it seems the chart is limited to max 100 labels shown at any time, no matter the interval setting.

I do need to confirm if this also occurs on Excel 2003 and Excel 2010, because if I recall correctly, I tested with 90 series there.
So, my memory of having charts with well over 100 might have been wrong... I will try to find out later, but now I'm off to bed

 

[rjallison]

Hello,

I've having exactly the same problem asexkris, only with a bar chart.

I've been attempting to plot 150 labels, but as we're aware we can't get excel to play ball...

Has a solution to this issue been found? It would be massively appreciated!

 

[exkris]

Hi rj,

I never found a solution to this other than putting text boxes under each column. Which is not ideal by any stretch of the imagination. I haven't tried this in the Excel 2013 preview yet.

 

[rjallison]

This is such an annoying problem! Ha!

Yeah, i've been searching for a solution for a little while, but i haven't found anything closer than this thread, which seemed to stall.

I had a glimmer of hope with a website that used VBA, but the code they used didn't work, and i haven't been able to track down the correct code to use yet.

 

[rjallison]

exkris, I've found a work-around!

I posted on another forum, and somebody showed how you can 'fake' the axis : http://www.excelforum.com/excel-cha...t-displaying-all-x-axis-labels.html?p=3045177

It isn't perfect, and takes a little bit of work to fix it, but it is a solution

 

[ExcelDave22]

Found it! I've been fighting this for years - it worked perfectly in Excel 2003, but 2007 destroyed that (along with destroying custom formatting in Pivot Tables).

Here's how to get the label to show on EVERY row in your Pivot Table:

• Right-click on a row label, click Format Axis at the bottom of the pop-up window.
  • If you like the practically useless space-hogging ribbon, you could click on Layout, Axis, Primary Vertical, click, click, click, click (I don't like to aggravate my carpal tunnel, but every Office version since 2003 does that).
• I had been setting "Specify interval unit" to 1 - this DOES NOT WORK! Seems obvious that this should work, but it doesn't!
• Change the "Interval between labels" to Automatic.
• Stretch your Pivot Chart vertically to be long enough to show every label.

It now shows EVERY ONE!

Best regards,
Dave

 

[cmnapper]

I have a file with well over 100 data points and had this same issue. I drug my chart out an made it very large and now it all shows. It is quick and easy.

Found it! I've been fighting this for years - it worked perfectly in Excel 2003, but 2007 destroyed that (along with destroying custom formatting in Pivot Tables).

Here's how to get the label to show on EVERY row in your Pivot Table:

• Right-click on a row label, click Format Axis at the bottom of the pop-up window.
  • If you like the practically useless space-hogging ribbon, you could click on Layout, Axis, Primary Vertical, click, click, click, click (I don't like to aggravate my carpal tunnel, but every Office version since 2003 does that).
• I had been setting "Specify interval unit" to 1 - this DOES NOT WORK! Seems obvious that this should work, but it doesn't!
• Change the "Interval between labels" to Automatic.
• Stretch your Pivot Chart vertically to be long enough to show every label.

It now shows EVERY ONE!

Best regards,
Dave