vertical grid lines for multi-level category axis labels

[joelrdrgz]

I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.

 

[pgc01]

Hi Joel
Welcome to the board

I may be wrong, but I think you cannot do it directly the way you want.

If you want the gridlines with a different spacing, I would not use drawing tools or textboxes, that may require some work each time you change something in the chart. I'd add vertical lines wherever I want using Scatter series.

 

[joelrdrgz]

I found an workaround to space vertical gridlines on charts with multilevel categories. My multilevel axis consisted of years and months (two rows). I wanted vertical gridlines for every year.

1. First select the gridlines and delete them.
2. Then Create dummy category and plot it on the secondary axis.
3. For the secondary axis label, select only the years (one row) instead of multilevel with year and month (two rows).
4. Go to Layout/Axes and plot the secondary axis on top.
5. Select the secondory axis on top.
6. Then go to the Layout/Gridlines and add a secondary vertical gridline.
7. Then just select the secondary axis on top and delete it.
8. That is it.

Note: The "Layout/Axes" and "Layout/Gridlines" are located in the "Chart Tools" that shows up on the top right of Excel toolbars when you select a chart.

I tried to report this to Microsoft but could not find a way to do so. In the Excel Help there is a link to contact Microsoft and let them know how they can make Office better but it takes you to a website with no options to let them know. Apparently Microsoft do not want feedback from its customers.

 

[joelrdrgz]

I forgot to mention that on the secondary axis, you need to go to the "Format Axis" and in the "Axis Options" set the "Interval between tick marks" to 12. That will make the secondary vertical gridlines to show between each year.