.JPG){kind=small}
Master the intricacies of Excel's in-built Justify command to reflow and transform your text data. Understand how to use formulas and selections to achieve differently formatted results based on your specific needs.
Key Insights
- The Justify command in Excel, located on the right side of the Home tab, allows you to reflow and transform your text data.
- To use the Justify command, a specific selection of cells needs to be made first such as A1:C3 or longer (A1:C4, A1:C100, etc.).
- The Justify command aligns with the column widths, leading to different results with different column widths.
- Formulas can be used in combination with the Justify command to change the text and insert elements like a comma.
- Prior to using the Justify command, the selected cells cannot contain formulas. The values need to be copied and pasted back into the same place using the Paste Special command.
- Using the Justify command in creative ways can lead to a complete transformation of your data, allowing for more efficient and organized presentations of your information.
Excel Workbook Checklist
-
Auto-banding, structured references, automatic filter.
-
Formulas → Define Name. =SUM(Sales) beats =SUM(B2:B100).
-
Note assumptions, sources, and gotchas in the cells.
-
Review → Protect Sheet to prevent accidental edits.
Suppose you have this data:
And you’d like to make it look like this:
Many people are unaware that Excel has this ability already built-in! It’s 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 as we saw.
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 would get this:
That is, 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! We have to copy C1:C7, then paste just 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 when you use the justify command:
And we’re complete!
.JPG)
