Excel Graph Labels And Negative Values

[AGlazer]

Hi, everyone.

This is probably more of a philosophical point, but I'm curious how you all have dealt with it. I have a series of graphs with a combination of negative & postive values. I want to report both the value of the bar (-4.5%, for example) and have the label show. The problem is that, unless the bar is exceptionally long, the label and the value overlap.

Does anyone know of a simple solution to this? I'd be happy with either having all labels above the axis or having the labels appear below the level of the bar.

Thanks,

Aaron

 

[SkipVought]

Hi, Aaron,

Select your data labels and open the format window. There you can change the placement of your data labels.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Skip,

Heh. Sorry, should have made it clearer.

The problem is that Excel doesn't have the ability to do what I want. (I know how to change the format of the data labels).

If I have a chart that has negative values, and I put the X-axis labels on the chart, the labels overlay the bar, right? That's fine, because you can always read the labels. The problem arrises when you add data values as well. Unless the bar is longer than the length of the label, no matter where you put the data value, it overwrites the x-axis label, meaning you can't read the value.

That make more sense?

Thanks,

-A

 

[SkipVought]

Gocha - but, sorry, no solution. :-(

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Skip,

Thanks, anyway!

-A

 

[DaleWatson123321]

Aaron,

I see no one has yet come up with a solution, and that's perhaps because, as both you and Skip have realized, there appears not to be one - at least not an easy one.

(As this problem has existed "from day one", wouldn't it be nice if Microsoft were to correct this problem ...finally.)

In the meantime, here's an option. It's not great, in that it involves "manual" work, but at least it's an option that will give you the results you require.

Steps:

1) Instead of including the X-axis labels, delete the chart's reference to the cells containing the labels.

2) Insert text-boxes to replace the X-axis labels.

After inserting the first text box, format it - i.e. change: a) Alignment - Orientation, b) Fill Color - No Fill, c) Line Color - No Line.

3) Copy the first text box to the other X-Axis locations - probably below the data labels.

4) Depending on the numbers, you might have to change the size of the "Plot Area" within the "Chart Area", to allow sufficient room for your text boxes.

Hope this helps. Please advise as to whether this is a workable alternative (until Bill comes through for us)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Dale,

Good work-around!

I've done something like this with Bar Charts (horizontal bars) where the text I want is very long -- several columns wide.

I'll format data in in rows on the sheet and configure my plot area (No fill) to align with those rows. The sheet-row grid becomes my Major axis grid.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DaleWatson123321]

Thanks, Skip !!!

Aaron,

Here's an additional bit of help...

If your numbers have a maximum negative number, then you could place the text boxes in a "set" position - at the bottom of the "Plot Area".

In the "Format Axis" window, under the "Scale" tab, un-check "Auto Minimum", and enter your maximum negative number (increase this number slightly to allow for placement of the Data Value below the number).

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca