Excel 2010 Bar/Column Chart Formatting

[walke154]

I am having a particularly rough time with 2 formatting concepts in Excel 2010's Bar and Column Charts:

1) When I have a bar chart, the alignment of the category labels on the vertical axis is positioned next to the tick mark. Instead, I would like the category labels to be centered between tick marks. Unfortunately, I cannot share the files I'm working with. But note that I am using a bar chart (i.e., horizontal bars) in which there are 2 series per category. Ideally, I would like the category label to be centered between the 2 series, for each category. Note that the number of categories will change from 1 to 4, depending on the data I input (i.e., this is a program that will be run multiple times with new data each time).

I have tried tirelessly to no avail to mess with the 'alignment' settings in the axis formatting options, but despite whatever alignment choice I use, I cannot get the labels to center between tick marks.

2) Unfortunately, I do not have an example of this that I can share, but when working with Column charts (i.e., vertical bars), when I include data labels above the columns, I cannot get them to center horizontally above the bars. Note that when I mess with the 3D rotation, I can get the labels to look slightly more centered (I ended up using a 12-degree X-rotation and 7-degree Y rotation), but they are still not perfectly centered. Again, the alignment options in the data label formatting menu are making no difference.

It is extremely important to note that I cannot manually alter any of these formatting issues, since this is a chart I will be using over and over again, with new data and new chart ranges. Therefore, I need an automated fix.

Thanks in advance for your help!

 

[xlhelp]

For your two series, have one merged cell above (or beside) them for the category. This will most likely center the categories for you.

I can't try it out right now, since I came down to shut down the computer owing to an extreme storm warning.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
Goodbye, Tension!.... Hello, Pension!

 

[SkipVought]

In 2007, easy!!

1. Format the vertical category axis, there is an option button group - position axis a) on tic marks b) between tic marks

2. Format data labels outside does what you describe

So I do not understand you quandary!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[walke154]

Thanks for your help, guys! I'm actually using Excel 2010, which is similar to 2007, but has a few small differences.

In regard to the horizontal bar charts, what I noticed is that if I just use the "center" text alignment in the 'Home' ribbon up top, it does the job. :-/

As for the more subtle centered-formatting of the data labels in the Column Charts, I still can't figure that out. Any tips? Like I said, I'm using a 3D Column Chart that has 1 series with 3 different data points. I have data labels above the each of the three columns (in 'Currency' format), but they won't seem to position themselves exactly centered above the columns. When I mess with the 3D rotation (i.e., I reduced it from the automatic 20-degree X-axis, 15-degree Y-axis to a 12-degree X-axis and 7-degree Y-axis), it seems to help, but it's still not quite centered. In the 'Data Labels' formatting options, I am selecting 'Vertical Alignment: Top Centered' and 'Text Direction: Horizontal'; but even if I change the vertical alignment options, it doesn't seem to make a difference...