Excel’s JUSTIFY Command
By Bob Umlas, Excel MVP
January 25, 2016
Have you heard of the Justify command? Let’s take a look at it here.
Suppose you have the data below:
You may not know that Excel has this ability already built-in! It’s called the Justify command, found on the right side of the Home tab:
What this feature does is reflow text. To use it, you have to make a specific selection first. In the above example, the selection had to be A1:C3 or longer (A1:C4, A1:C100, etc.):
From this selection, issuing the Justify command would reflow the text.
You could have selected just columns A and B and reissued the same command for this result:
(The resulting selection, A1:A4, happens after the command is completed. The entire columns A:B were selected prior to using Justify.
It aligns with the column widths. Notice the difference with different column widths here (column B is wider, column C is pretty narrow), where A:C was selected:
OK, here’s another trick: if you select A1:D3 and issue the justify command, you get this:
It reflows text from 3 lines into 2 lines! If you had selected A1:G3, you’d see this:
Pretty cool.
Let’s look at a new example. Say you have the data in column A, below, and you want to make it look like cell C1:
If you select A1:J7, for example (remember, there’s nothing yet in C1) and issue the command, you’d see this:
So here’s a trick to overcome that. In cell C1 we’ll enter a formula to both change A1 to text and insert a comma. That formula is =A1&”, “, and we fill down to C7:
We have the makings of a solution, but we don’t want that ending comma in cell C7, so we change that formula to =A7&”” which appends an empty string but it’s now text:
We’re not yet ready to use the Justify command because, if you read the message above, it also can’t contain formulas! So we have to copy C1:C7, then paste only the values back into the same place. Ctrl/C can copy, and we can use the Paste Special command to paste just the values:
So this:
becomes this:
And we’re complete!
Bob Umlas
Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.
