Dan from Akron notes that when he inserts new columns in Excel, Excel is automatically extending the size of any charts that happen to span the new column. With most objects, you can right-click and indicate that you dont want to move or size with cells. However, charts dont seem to have the ability to change the resizing options. Episode 456 will show you the bizarre solution.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question from Dan and akron.
If you have a question plenty of ways to get it in.
Email it, send it In as a voicemail, skype however you want to do it.
Dan' squestion is pretty interesting.
He has a chart.
In excel it's charting 4 people and when someone new gets added to the department.
Dan adds a new column into the middle of the chart and this isn't a matter of how to add the data.
Dan is frustrated than when he adds the column excel automatically extends the size of the chart.
So let me just come here and do insert column and all of a sudden now the width of the chart is increased.
He does'nt want the size of his charts to increase at all so let me remove that column.
Now normally when we have some sort of an object in excel.
you can right-click the object choose format and on the properties tab say that you don't want to move in size.
that you don't want to move in size with cells but when we right click the chart we just get to format chart area which allows us to change the color of the chart area not the properties.
Well it turns out if you think back to the days of Lotus 123, all new charts were always created as their own chart sheets and going back to the early days of excel. That's the way it work, but at some point there is a split and excel said well we're going to let you put a chart embedded right on the current sheet and even though it looks like the chart is on the sheet it turns out that the chart is actually in a chart container, and it's the chart container that we need to get to in order to access the chart.
It's completely unintuative but what you have to do is you have to Ctrl click on the chart.
You"ll notice that instead of having the black handles that I had at the beginning of the podcast, I now have white handles on each side.
Now when I right click. I have a completely different menu including "Format object".
Inside "Format object" I can get to the "properties" tab where I can say "Don't move or size with cells" click OK.
Now when I insert a new column The chart stays the exact same width that it was before.
Thanks to Dan. That was a great question, but we'll see you next time for another netcast from MrExcel.
Today we have a question from Dan and akron.
If you have a question plenty of ways to get it in.
Email it, send it In as a voicemail, skype however you want to do it.
Dan' squestion is pretty interesting.
He has a chart.
In excel it's charting 4 people and when someone new gets added to the department.
Dan adds a new column into the middle of the chart and this isn't a matter of how to add the data.
Dan is frustrated than when he adds the column excel automatically extends the size of the chart.
So let me just come here and do insert column and all of a sudden now the width of the chart is increased.
He does'nt want the size of his charts to increase at all so let me remove that column.
Now normally when we have some sort of an object in excel.
you can right-click the object choose format and on the properties tab say that you don't want to move in size.
that you don't want to move in size with cells but when we right click the chart we just get to format chart area which allows us to change the color of the chart area not the properties.
Well it turns out if you think back to the days of Lotus 123, all new charts were always created as their own chart sheets and going back to the early days of excel. That's the way it work, but at some point there is a split and excel said well we're going to let you put a chart embedded right on the current sheet and even though it looks like the chart is on the sheet it turns out that the chart is actually in a chart container, and it's the chart container that we need to get to in order to access the chart.
It's completely unintuative but what you have to do is you have to Ctrl click on the chart.
You"ll notice that instead of having the black handles that I had at the beginning of the podcast, I now have white handles on each side.
Now when I right click. I have a completely different menu including "Format object".
Inside "Format object" I can get to the "properties" tab where I can say "Don't move or size with cells" click OK.
Now when I insert a new column The chart stays the exact same width that it was before.
Thanks to Dan. That was a great question, but we'll see you next time for another netcast from MrExcel.
