Stop Proportionatly Scaling Bar Graph « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Stop Proportionatly Scaling Bar Graph

(11 posts) (4 voices)

Started 2 years ago by wsnyder
Latest reply from Hui

Tags:

wsnyder

Member
Joined: Nov '09
Posts: 25

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

Posted 2 years ago #
Chandoo

Key Master
Joined: Jul '09
Posts: 587

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.

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040

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.

Posted 2 years ago #
wsnyder

Member
Joined: Nov '09
Posts: 25

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

Posted 2 years ago #
TessaES

Member
Joined: Nov '09
Posts: 35

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

Posted 2 years ago #
wsnyder

Member
Joined: Nov '09
Posts: 25

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040

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

Posted 2 years ago #
wsnyder

Member
Joined: Nov '09
Posts: 25

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040

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

Posted 2 years ago #
wsnyder

Member
Joined: Nov '09
Posts: 25

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

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,040

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

Posted 2 years ago #