Rich wonders why you can not center across selection vertically. Episode 996 will show you two not-very-satisfying workarounds and asks if you have anything better.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today is Monday, April 20th.
That's when this podcast going out.
I have an unusual request for the right person, this Thursday April 23rd, 2009.
I'm going to be driving from I'll probably start around, Kansas City, Missouri up to Rapid City South Dakota and I am looking to do a podcast live with someone, who works somewhere here along I-29.
So, if you're in Omaha, or within five or ten minutes of I-29.
You'd like me to stop by the office.
We'd like to ask your Excel question live on the podcast.
Shoot me a note Bill@MrExcel.com.
Probably going to be sometime during the morning of April 23rd, heading from that seminar the day before in Springfield, Missouri.
Going up to the Friday seminar in Rapid City.
Looks like a nice drive, great place to do a little Excel podcasting along the road.
Now, today I have a problem, and I'm not sure that I have a good solution at least neither solution.
I'm very happy with and I'm putting this out there to see if you have a better solution.
Rich sent in this question.
He says you know, "Hey! You know, there's a great setting here called center across selection." So, if I type something in cell E3 and then I go into the alignment tab of format cells, under horizontal.
We have center across selection and it doesn't merge the cell or anything like that just centers across the selection is a great feature and he says, "Why don't they have that for vertical cells?" So, for example if I put storeID here.
Why can't I get it to center cross selection, vertically and sure enough if we go in there to the alignment tab.
Under vertical there is no option for center across selection and what Rich has to do.
He has two options one of them is merge cells and you know, I hate merging cells.
Where we actually turn on merge cells and say vertical center.
Click [ ok ] and you see that now, creates one big cell.
The problem with merge cells is let's say that we're over here.
We try and select the whole row, that's [ shift + sapce bar ] to select the whole row.
Well, because it hits that merged cell.
It selects five rows, very very frustrating.
The other solution is to use the text box or some sort of a shape over here.
So, if we come to insert and then shapes, and then text box.
I'm going to hold down the [ alt ] key to make sure that it stays perfectly in this range and then in that text box, I can type store Id and we will select the text box and now format cells, [ ctrl + 1 ] for format the object and on the text box, vertical alignment, center.
Middle Center, there we go, text direction horizontal.
Also, we can change the font to something smaller, there we go, and we get something that is similar to center across selection.
But certainly a lot harder to set up.
So, wonder if you have ever run into this.
If you've ever tried the center across selection vertically and what your solution is, again.
Drop me a note Bill@MrExcel.com and may catch on, a future podcast and then this Thursday April 23rd.
If you happen to be along that I-29 counter there.
Going from Kansas City on up to Rapid City, South Dakota.
Love to stop by your office and do a podcast live.
Shoot me a note Bill@MrExcel.com Well, there you have it.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze as well plus fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today is Monday, April 20th.
That's when this podcast going out.
I have an unusual request for the right person, this Thursday April 23rd, 2009.
I'm going to be driving from I'll probably start around, Kansas City, Missouri up to Rapid City South Dakota and I am looking to do a podcast live with someone, who works somewhere here along I-29.
So, if you're in Omaha, or within five or ten minutes of I-29.
You'd like me to stop by the office.
We'd like to ask your Excel question live on the podcast.
Shoot me a note Bill@MrExcel.com.
Probably going to be sometime during the morning of April 23rd, heading from that seminar the day before in Springfield, Missouri.
Going up to the Friday seminar in Rapid City.
Looks like a nice drive, great place to do a little Excel podcasting along the road.
Now, today I have a problem, and I'm not sure that I have a good solution at least neither solution.
I'm very happy with and I'm putting this out there to see if you have a better solution.
Rich sent in this question.
He says you know, "Hey! You know, there's a great setting here called center across selection." So, if I type something in cell E3 and then I go into the alignment tab of format cells, under horizontal.
We have center across selection and it doesn't merge the cell or anything like that just centers across the selection is a great feature and he says, "Why don't they have that for vertical cells?" So, for example if I put storeID here.
Why can't I get it to center cross selection, vertically and sure enough if we go in there to the alignment tab.
Under vertical there is no option for center across selection and what Rich has to do.
He has two options one of them is merge cells and you know, I hate merging cells.
Where we actually turn on merge cells and say vertical center.
Click [ ok ] and you see that now, creates one big cell.
The problem with merge cells is let's say that we're over here.
We try and select the whole row, that's [ shift + sapce bar ] to select the whole row.
Well, because it hits that merged cell.
It selects five rows, very very frustrating.
The other solution is to use the text box or some sort of a shape over here.
So, if we come to insert and then shapes, and then text box.
I'm going to hold down the [ alt ] key to make sure that it stays perfectly in this range and then in that text box, I can type store Id and we will select the text box and now format cells, [ ctrl + 1 ] for format the object and on the text box, vertical alignment, center.
Middle Center, there we go, text direction horizontal.
Also, we can change the font to something smaller, there we go, and we get something that is similar to center across selection.
But certainly a lot harder to set up.
So, wonder if you have ever run into this.
If you've ever tried the center across selection vertically and what your solution is, again.
Drop me a note Bill@MrExcel.com and may catch on, a future podcast and then this Thursday April 23rd.
If you happen to be along that I-29 counter there.
Going from Kansas City on up to Rapid City, South Dakota.
Love to stop by your office and do a podcast live.
Shoot me a note Bill@MrExcel.com Well, there you have it.
Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
