The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
November 5, 2015

Greetings from The Excel Addict
Hi fellow Excel Addict,

Francis Hayes (TheExcelAddict.com)My philosophy has always been that there's an easier and/or better way to do almost everything.

I'm always amazed how people can continue to do monotonous tasks for years without even considering that there may be a better way.

I hate doing monotonous tasks. Whenever I'm given a monotonous task to do, I immediately start thinking about how to improve the process. The result is usually hours of work reduced down to minutes or even seconds.

My advice to you is, if you are doing something that you would like to be able to do easier and faster, keep on looking. Almost always, there is a better way...especially in Excel.

Keep on Excelling and keep on sharing these tips with your friends and colleagues.

Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com




If you missed my last newsletter, you can click here to view it online.

You can access even more tips on my website by going to my members' page


The Excel Addict Quote of the Week
"Remembering that you are going to die is the best way I know
to avoid the trap of thinking you have something to lose.
You are already naked.
There is no reason not to follow your heart."
-- Steve Jobs --
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Can You Justify This?

Because Excel is used for such a wide variety of tasks, you may occasionally find yourself typing paragraphs of text into your worksheets. Since Excel allows word wrapping in a cell, you can easily type a paragraph or more into a single cell. However, there may be times when word wrapping is not practical because the increased row height also affects adjacent cells.

Wrapping Text In One Column Adjusts Row Height Of All Columns in Microsoft Excel 2003, 2007, 2010, 2013, 365
One way to avoid this is to type your paragraph into several cells going down the column. If you have ever done this, no doubt, you have also had to make changes to your text or re-adjust the column width and then found yourself cutting and pasting text from one row to the next to make it fit on each line.
 
Another example would be if you copy some text from a web page or elsewhere and paste it into your worksheet. You may find that all of the text is pasted into one cell and extends to the right off the screen but you want to spread the text over several rows and columns.
 

Well, you'll be happy to know that there is a little-known option, practically hidden in Excel, that can be a godsend whenever you find yourself trying to redistribute text over several rows. It's called Justify.

Now let's see what Justify does...
 
For Justify to work, your text must be in one or more cells in a single column. You can spread the text to display across several columns and rows but the actual text will be stored in the leftmost column only. You can also include blank cells (i.e. paragraph breaks) in your text.
 
Believe me, using Excel's Justify command is really useful and really easy! Please follow along with these examples and see for yourself.

Distributing Text Down A Single Column

1) Open a new workbook;

2) Type a couple of sentences in cell A3 (a single cell) or copy some text from a web page and paste (right-click the cell and select Paste Special, Text, OK) it into cell A3. Go ahead. Try it now. When you Paste Special, Text, the text should be in one cell but will overflow into the blank columns to the right;

3) Adjust the width of column A to 50;


4) Now, highlight cells A3:A10;
Distribute (Justify) Text Across Multiple Rows And Columns in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
5) On the Home tab, in the Editing group, click the Fill command (the small button with the downward pointing arrow) and select Justify;. All of the text from the 'active cell' gets evenly distributed down column A;

Text Evenly Distributed  To All Cells In The Selected Range in Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365
To see that work again, change the width of column A (wider or narrower) and repeat step 5. Isn't that cool?

Distributing Text Across Multiple Columns and Rows

As I said before, the text must all be in one column. But rather than adjusting the column width as in the example above and fitting the text into one column, instead you can have the text appear to be distributed across several columns.

Try this.

1) Resize column A to it's original width 8.43;

2) Highlight cells A3:D12;

3) On the Home tab, in the Editing group, click the Fill tool and select Justify. Notice that, although the text is still contained only in the cells in column A, it now appears to be distributed across the columns you originally selected.

Text Distributed Across Multiple Columns And Rows in Microsoft Excel 2003, 2007, 2010, 2013, 365

Now, if you need to edit the text in cell A3 but still want to keep it displayed within columns A to D, it's just a simple matter re-selecting cells A3:D12 and redistributing using the Justify command. This sure beats all that cutting and pasting for each row.

Combining Text from Multiple Rows into One Row

Justify also works in reverse. By selecting more columns in your distribution range, you can combine text from multiple rows into fewer, or just one, row(s).  But keep in mind that the maximum number of characters that can be contained any single cell after the text has been justified is 255 (see note below).

1) Select cells A3:V8;

2) On the Home tab, in the Editing group, click the Fill tool and select Justify. The text from the cells in column A is now redistributed to fit within the new range you have selected.
And since you selected more columns, the text fits on fewer rows. If some of the text gets truncated, click Undo (CTRL+Z) and include fewer columns in your distribution range.

Combine Text From Multiple Rows Into One Row in Microsoft Excel 2003, 2007, 2010, 2013, 365
A few things to keep in mind about using Excel's Justify command
  • The maximum number of characters you can have in one cell for Justify to work is 255. Extra characters will be truncated. If in doubt, use the LEN function to count the number of characters in a cell e.g. =LEN(A3).
  • If you need to use Justify (distribute) text containing more than 255 characters, first split the text into multiple cells (in the same column) so that neither cells contains more than 255 characters, then select a range that includes these cells in the leftmost column and Justify them.
  • If you need to use Justify (combine) more than 255 characters on fewer rows, make sure your distribution range is narrow enough that each row/cell after justifying will accommodate no more than 255 characters.
  • If the range you select isn't large enough to accommodate the text when justified, you will get a warning message allowing you to abort if necessary, otherwise it may overwrite data in the cells below.

Thanks for supporting this newsletter and website


Subscription Information: Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter. If you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com

If you would like to share this newsletter with others...
1) Share a link to this page
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com
3)  Post a link to TheExcelAddict.com in a company newsletter or website.

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630