They say the true Excel fan has uninstalled Word from their PC, and removed the F1 key from their keyboard (accidentally pressing F1 opens ‘Help’ which slows you down).
After all, who needs Word when you have everything you need to write a letter right there in Excel?
A must know tool for us Excel Die Hards is the Fill Justify tool which enables us to rearrange text across columns and rows automatically.
You can see in the animated image below that it’s quick to change the number of rows or columns the text occupies by simply selecting the text and the number of columns you want it to span by applying the Fill Justify tool:
If you manage to read the text in the animated image above you should know that I am joking 🙂 Excel can't.... um,... there must be something it can't do that Word can....um...oh, there is one thing; it can't do Mail Merge!
You’ll find Fill Justify on the Home tab of the ribbon in the Editing group:
And if you use it all the time you can add it to your Quick Access Toolbar so it's always just a click away.
Tips:
- Excel will automatically select the number of rows required. If you select too many rows Excel will just fill what it needs, and if you select too few it will give you a warning, which is handy if you have data underneath your text.
- Excel only includes the rows of text that are selected.
- The text can be in one row or multiple rows.
- You can repeat the process again and again until you have it just the way you want.
- The text is in the first selected column, it is not merged across cells.
Limitations
Ok, maybe uninstalling Word is a bit hasty 🙂 …. If you have more than 255 characters in a single cell, Excel will truncate the characters down to the first 255, or the last complete word under 255.
You can check how many characters in a cell using the LEN function. For example if you have a sentence in cell A1 you can use this formula: =LEN(A1) to return the number of characters.
If you've got more than 255 in one cell then you'll need to split it up into multiple cells before using Fill Justify.
Whooooooah. I don’t tend to have long text passages in my spreadsheets, but there have definitely been times when this would have been useful to know about!
Also, you said “oh, there is one thing; it can’t do Mail Merge!”. Technically you are correct, but it’d be a whole lot easier to add a little VBA to a form linked to a table through lookups than to do all the manipulation in Word!
🙂 I agree VBA would probably be better than the built in Mail Merge in Word.
Mynda.
A very good tip Mynda. I have put it on my blogg http://www.datamentor.se
Feel free to use my tips, although it’s in Swedish.
/Bengt
Thanks, Bengt 🙂
Awesome tip! I don’t think I have ever used that one either.
I use OneNote for all my note taking, and have pretty much ditched Word as well. OneNote integrates nicely with Outlook and is great for keeping your projects organized.
The other Word feature missing from Excel is bullet lists. That reminds me that I have a technique for this and should write an article on it.
Thanks again!
Cheers, Jon.
I never got into OneNote. Probably should because it sounds useful with all the integration. Old habits die hard though.
I’m looking forward to your bullet lists technique 🙂
Mynda.
Hi Jon,
It would be nice if there is Bullet list in Excel. Looking forward to your article.
Cheers,
Hi Mynda,
Excellent tip, after so many years of using Excel I never knew it has such a handy function. Too bad is limited to 255 characters, but the extra work is nothing compared to what I used to do manually before knowing the Fill Justify.
Keep up the good work, I always wait excited for your next newsletter.
Thanks,
Pablo
Hi Pablo,
I agree, the 255 limit is a small price to pay for such a versatile tool.
Glad you liked it.
Mynda.
Hi Mynda,
I knew that trick, just that I barely used it in the past and almost forget it. Thanks for reminding. 🙂
Nevertheless, I think it cannot Justify to uninstall Word…
Frankly, I strongly encourage people not to use Excel as Word, as Excel is not intending for writing. Don’t you think so?
Cheers,
Hi MF,
Glad I reminded you of this technique. There are so many cool tricks like this in Excel that it’s difficult to remember them all.
Of course I don’t recommend people uninstall Word…which is why I said ‘I was joking’. I use Word nearly every day too, in fact I write my blog posts/tuturorials and many other training materials in it.
But I’ll always like Excel the best 🙂 ….won’t you?
Cheers,
Mynda.
Hi Mynda,
Of course I know you were joking. ;p
I agree with you that I like Excel the best!
Cheers,
Phew! You had me worried there for a minute 😉
Awesome trick!
Thanks!
Cheers, pmsocho 🙂
This is TOO amazing! I wish I would have know this months ago! This would have saved me hours of time redoing sentences that are too long and get cut off when the page is printed. You can spot a die-hard Excel fan when something THIS simple excites us. LOL
I “so” look forward to your weekly newsletter – you just never know what nugget of information will be shared.
🙂 Thanks, Wanda. You definitely qualify as an Excel Die Hard!
Mynda.