In todays episode, I take a look at three non-macro solutions to solve Ethans long text problem. Episode 617 shows you Shrink to Fit, Wrap Text, and AutoFit.
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 am Bill Jelen.
Now, yesterday we had a question from Ethan.
Where he was trying to make some long data in column B, show by optionally erasing some data in column C.
And I just want to go through some simple alternatives to that.
I'm sure there's some reason why Ethan couldn't use these.
You know the first one is just to make column to be wider.
Alright! Double-click here between column B and C.
And it'll make the column wide enough to show the longest value.
I'm suspecting Ethan couldn't do that because there's some values that are just way too long.
But what we could do is select all of column B.
And we'll go to format cells [ CTRL+1 ] is the fast way to get to format cells and on the alignment tab, we can have a setting there called shrink to fit.
Shrink to fit...
What will happen there is when the data is really really long, it will shrink it down to a smaller font size.
Maybe six or eight point.
And order to get it to fit.
So, we can see all of that data.
That way you still have the data out in column C but you can still see your values.
Now, the other way to go and here I have an identical copy that worksheet is to turn on wrap text.
Will turn on wrap text in column B So I'll select all of column B.
Go back to format cells on the alignment tab.
Will have to turn on wrap text and what will happen there is it will create rows that are basically double high in order to make all of our data fit.
The one thing that I hate about wrap text though is then the other columns are aligned with the bottom of the cell.
What I really feel they should be aligned with the top of the cell.
So, we'll choose column A and column C.
Use format cells and say that we want to use the vertical alignment to the top.
Click OK and things get a line at the top.
Now, this is one example where it's fairly difficult to go through and wrap text.
And then change vertical alignment to the top.
And for those who are using Excel 2003, you probably don't remember this day.
But about two or three days after you started using the product, a little question popped up and said hey, would you like to make Excel better?
and if you click yes to that then you've been participating in a large research study that basically tells which commands you use and how often.
And so in Excel 2007, they were able to look through that data and realize that a lot of people are using wrap text and then after they use wrap text They immediately go through and use vertical alignment top on the other values.
And so here in Excel 2007, I really appreciate that Microsoft took the time to look at that data and see the wrap text is something we use all the time.
So, if I choose column B, now we actually have an icon.
Right on the home ribbon called Wrap Text.
We've never had an icon before it wasn't even something you could add to the standard or formatting toolbars.
So, I can very easily Wrap Text and then I will select all four columns and use the vertical alignment top.
This is another button that we never had before.
We always have to go in to format cells in order to do the top align.
So there you have it.
A couple of options one, makes the column wider.
Two, you shrink to fit.
In order to make those long cells fit in column B or three, turn on wrap text in column B.
And then use a vertical align top in the other columns to make things line up.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Now, yesterday we had a question from Ethan.
Where he was trying to make some long data in column B, show by optionally erasing some data in column C.
And I just want to go through some simple alternatives to that.
I'm sure there's some reason why Ethan couldn't use these.
You know the first one is just to make column to be wider.
Alright! Double-click here between column B and C.
And it'll make the column wide enough to show the longest value.
I'm suspecting Ethan couldn't do that because there's some values that are just way too long.
But what we could do is select all of column B.
And we'll go to format cells [ CTRL+1 ] is the fast way to get to format cells and on the alignment tab, we can have a setting there called shrink to fit.
Shrink to fit...
What will happen there is when the data is really really long, it will shrink it down to a smaller font size.
Maybe six or eight point.
And order to get it to fit.
So, we can see all of that data.
That way you still have the data out in column C but you can still see your values.
Now, the other way to go and here I have an identical copy that worksheet is to turn on wrap text.
Will turn on wrap text in column B So I'll select all of column B.
Go back to format cells on the alignment tab.
Will have to turn on wrap text and what will happen there is it will create rows that are basically double high in order to make all of our data fit.
The one thing that I hate about wrap text though is then the other columns are aligned with the bottom of the cell.
What I really feel they should be aligned with the top of the cell.
So, we'll choose column A and column C.
Use format cells and say that we want to use the vertical alignment to the top.
Click OK and things get a line at the top.
Now, this is one example where it's fairly difficult to go through and wrap text.
And then change vertical alignment to the top.
And for those who are using Excel 2003, you probably don't remember this day.
But about two or three days after you started using the product, a little question popped up and said hey, would you like to make Excel better?
and if you click yes to that then you've been participating in a large research study that basically tells which commands you use and how often.
And so in Excel 2007, they were able to look through that data and realize that a lot of people are using wrap text and then after they use wrap text They immediately go through and use vertical alignment top on the other values.
And so here in Excel 2007, I really appreciate that Microsoft took the time to look at that data and see the wrap text is something we use all the time.
So, if I choose column B, now we actually have an icon.
Right on the home ribbon called Wrap Text.
We've never had an icon before it wasn't even something you could add to the standard or formatting toolbars.
So, I can very easily Wrap Text and then I will select all four columns and use the vertical alignment top.
This is another button that we never had before.
We always have to go in to format cells in order to do the top align.
So there you have it.
A couple of options one, makes the column wider.
Two, you shrink to fit.
In order to make those long cells fit in column B or three, turn on wrap text in column B.
And then use a vertical align top in the other columns to make things line up.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
