Stop Proportionatly Scaling Bar Graph

[wsnyder]

Hi all


Using Excel 2007

I have a Bar Graph

As I try to shrink the chart from right to left the end of the data bar moves proportionately away from the right edge of the plot area and the chart area


I would simply like to decrease both the chart area and plot area so they are snug to the right end of the longest bar. This way, no wasted space.


I will then copy the chart, paste and update the datsource to the new set

I am a bit concerned with this strategy as it may lead users to draw incorrect conclusions of the dash as the values may have large swings


I thought about solving this with data labels at right end of the bars, but some values may become so small that the value label dangles in thin air


How do you suggest to handle these issues?

Can you point me to some good samples?


Thanks

Winston

 

[keymaster]

Why not resize plot area after you finish adjusting the chart size. (if you cannot select plot area, go to format ribbon and select "plot area" and then use mouse to resize.


I often face this problem and would love to know if there is a more elegant solution to this.

 

[Hui]

I agree and believe that the primary focus of any chart should be the Plot Area not the chart area as it is now.


By making this change it would allow people to easily align chart axis in either direction and if a user decides to drag the Chart Area in and hence obscure/cover the axis titles or labels that should be there choice.

 

[wsnyder]

Thanks guys,


Maybe I'm missing something?

As I I select the plot area handle in the middle right and move to the left the plot area and corresponding data bar graph are "downsized" proportionately right to left.


I am definitely selected on the plot area and it is confirmed as I hover with the mouse


I appreciate your help.

Kind regards,

Winston

 

[TessaES]

It could be that when you make the plot area smaller, Excel is changing the scaling on the axis, thereby changing the bar lengths.

 

[wsnyder]

Thanks TessaES,


I created a new bar chart

I changed all X Axis options to "fixed"

I also tried changing Max value to 140 as my highes value is 140 but Excel seet the Max Value as 160. But what happenjs next week is my max value is greater than 140?


I also still have a problem with the chart area as I reduce size right to left the plot are and data bars reduce proportionately right to left instead of using the dead white space at the end of the data bars


Thanks

Winston

 

[Hui]

Winston

I can't reproduce your problem.

so a few suggestions anyway:

+ Make sure your Major Unit evenly divides into the Maximum

+ Make sure your X-Axis isn't using a logarithmic scale

+ Make sure the X-Axis font isn't too large or the words to long

eg: use 10 instead of 10,000,000

+ make sure any data Labels are not located Outside End


If the above doesn't help can you post an example somewhere

 

[wsnyder]

Thanks Hui


The 3 items you point out all check out fine


X Axis is deleted


I also played around with "How To Visualize Survey Results Using Incell Panel Charts"

But I'm not sure how to make that work doe to extreme range of values ($M's to Neg $K's)


I posted the workbook on skydrive

Here is the link

http://cid-fb86d3d43fbd123b.office.live.com/browse.aspx/.Public


Choose "Data Bar Example.xls"


The data bars are on tab Rpt2 at row 35


Thanks

Winston

 

[Hui]

Winston

Select the Horizontal Axis or goto Axes, Horizontal Axis, More Horizontal Axis Options and

Set the Horiziontal Axis Axis to Fixed Mininum = 0 , Fixed Maximum = 150,000

The chart will expand to fill the Plot Area

Select and Delete the Axis

Move to where it should be using Alt to snap to cell edges

 

[wsnyder]

Thanks Hui,


I did that, my only concern that the data will change every week.

150K Max will work for the current week but the possible range of values is +/- $M's


Is there no other workaround?

Or do I need some VBA to get Large/Small value each week to reset


Any thoughts on how I could use incell panel to display the data given the extremes in values?


THanks

Winston

 

[Hui]

The only way to automate the maximum is to set it to Automatic

and add a dummy set if data to your chart which will have the maximum value you need

Then change the dummy bar to a line chart and set it to no color

This way you can have several charts using the same dummy set of data and hence have the same scale


or use VBA