Ann from Wisconsin writes with a really annoying Wrap Text problem in Excel 2003. Episode 999 suggest a few workarounds to solve the problem.
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 amounts of data.
How we're going to analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Back to the MrExcel netcast.
I'm Bill Jelen.
Great questions toady send in by Ann.
Ann was in one of my seminars in Stevens Point.
So, she's from Wisconsin and she says hey, a vendor sent me a file.
It has about 250 words in a single cell.
I can't see all of the words in the cell unless I select the cell.
All right so, Ann is using word wrap obviously.
So, let's go select that cell and we'll go to Format Cells and turn on Wrap Text, Vertical Top that's great, click OK.
Now, I know because I set this up that there's 512 words here and you can see, that we can see down about word 143.
So, I decide alright. I'm going to make this cell wider to allow more of the words to fit and it's weird.
It's, it's ending around word 161 and when I click in there, I can see that there are more words, but when I click away from the cell, it's only show me word 161 and initially my reaction was okay.
Well, you know, we're having a problem with it adjusting the row height, but you can see that it went to the maximum 409.5 and it just is not fitting.
Alright, this is a problem that we've had in previous versions of Excel.
Now, let's see it was going up to about word 130.
So, what we have to do is click in the cell right before the words that we can't see word 131 and press Alt+Enter.
Alt+Enter, will actually allow us to see more of this cell.
So, this is going to be very tedious for me now to go through and keep using Alt+Enter, at various spots, but in fact that is a solution that will allow us to see more of the cells.
So, a second ago, we can only see the word 131.
Let's check it out now, you know, now yes we can see more and if we make it taller, we can continue to see more.
So, very painful solution back in Excel 2003 and earlier is just to keep going through and using that Alt+Enter trick to get Excel to show you more words beyond the limit.
Now, in Excel 2007 looks like they took some great steps to solve this.
Now, this is the amazing expanding formula bar.
Let me close that up here and when we turn on Wrap Text.
We can eventually get to the point if we make the cell wide enough, to see all of the words out to the end.
If it's not wide enough, you run into that limit of the cell gonna be 409 rows high.
you know and then you know, what can you do, I don't know.
Now, one workaround to this and this is so strange in the last two weeks now I've talked about a text box three times.
After hardly ever talking about text boxes at all is to replace that cell with the text box.
So, insert Shapes, we're just going to draw big huge text box in here and inside of that text box we can paste as much text as we want.
So, here from this cell I will choose everything from the formula bar control+C to copy and then go back to my text box and click inside control+V to paste, choose a nice small font size.
There we go and you can get all of that text to appear whether you are in the cell or not in the cell.
Now, again this would be tedious if you had more than a few cells with all of these words, but it is a great way to get everything to show up all the time. Okay So, if you're in Excel 2003, text box is one solution or using Alt+Enter at various spots.
if you're in Excel 2007, you just need to go to nice small font really wide column and maximize the height to see all of those words.
There you have it, great question form Ann.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel Well, thanks for stopping by.
We'll see next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data.
How we're going to analyze this. Well, let's fire up a pivot table.
Let's see if you can solve this problem.
Back to the MrExcel netcast.
I'm Bill Jelen.
Great questions toady send in by Ann.
Ann was in one of my seminars in Stevens Point.
So, she's from Wisconsin and she says hey, a vendor sent me a file.
It has about 250 words in a single cell.
I can't see all of the words in the cell unless I select the cell.
All right so, Ann is using word wrap obviously.
So, let's go select that cell and we'll go to Format Cells and turn on Wrap Text, Vertical Top that's great, click OK.
Now, I know because I set this up that there's 512 words here and you can see, that we can see down about word 143.
So, I decide alright. I'm going to make this cell wider to allow more of the words to fit and it's weird.
It's, it's ending around word 161 and when I click in there, I can see that there are more words, but when I click away from the cell, it's only show me word 161 and initially my reaction was okay.
Well, you know, we're having a problem with it adjusting the row height, but you can see that it went to the maximum 409.5 and it just is not fitting.
Alright, this is a problem that we've had in previous versions of Excel.
Now, let's see it was going up to about word 130.
So, what we have to do is click in the cell right before the words that we can't see word 131 and press Alt+Enter.
Alt+Enter, will actually allow us to see more of this cell.
So, this is going to be very tedious for me now to go through and keep using Alt+Enter, at various spots, but in fact that is a solution that will allow us to see more of the cells.
So, a second ago, we can only see the word 131.
Let's check it out now, you know, now yes we can see more and if we make it taller, we can continue to see more.
So, very painful solution back in Excel 2003 and earlier is just to keep going through and using that Alt+Enter trick to get Excel to show you more words beyond the limit.
Now, in Excel 2007 looks like they took some great steps to solve this.
Now, this is the amazing expanding formula bar.
Let me close that up here and when we turn on Wrap Text.
We can eventually get to the point if we make the cell wide enough, to see all of the words out to the end.
If it's not wide enough, you run into that limit of the cell gonna be 409 rows high.
you know and then you know, what can you do, I don't know.
Now, one workaround to this and this is so strange in the last two weeks now I've talked about a text box three times.
After hardly ever talking about text boxes at all is to replace that cell with the text box.
So, insert Shapes, we're just going to draw big huge text box in here and inside of that text box we can paste as much text as we want.
So, here from this cell I will choose everything from the formula bar control+C to copy and then go back to my text box and click inside control+V to paste, choose a nice small font size.
There we go and you can get all of that text to appear whether you are in the cell or not in the cell.
Now, again this would be tedious if you had more than a few cells with all of these words, but it is a great way to get everything to show up all the time. Okay So, if you're in Excel 2003, text box is one solution or using Alt+Enter at various spots.
if you're in Excel 2007, you just need to go to nice small font really wide column and maximize the height to see all of those words.
There you have it, great question form Ann.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel Well, thanks for stopping by.
We'll see next time for another netcast from MrExcel.
