In my work as a trainer I’m usually the one serving the Excel goodies to the audience. But every so often I’m the one getting served. Whereas sometimes when I’m done so on an Excel Basic training, it just makes me want to rethink the whole career path, in general, these are the moments I crave for since it means I am about to learn something new.
So just the other day, as the lesson on Fill/Series… took us deep into Weekday territory, I got served with a rather unorthodox use of the command Fill/Justify described here in Fill/Series secondary. But as this is a rather unknown command in Excel, let’s dig into it from the start.
The four icons bellow are well known to all. Well the last one is called Justify and the following picture shows what it does. (This is from Word)
Now whereas this seems great in word, in Excel, the icon is missing. Interestingly enough, it’s only missing from the HOME tab, but you can add it to your QAT (Quick Access Toolbar). Once you use it, you get
Well this is great, and works as one would expect, but there is another Justify in Excel. You can find it in HOME/Editing/Fill/Justify
Now this one can do wonders, and in many different ways. We will sort them into two piles, primary and secondary.
Often you are given the data that looks very similar to the picture above. In our case it’s the lyrics of “A Day in the life” by The Beatles. But as we can see, the lyrics span over unevenly and in some cases (Paul McCartney’s middle section) over the entire length of the Excel window. So the Question would go, how to get all this text to span evenly over the first seven columns. Now doing this manually could turn out to be quite a difficult task. But here’s how you can achieve this by using Fill/Justify.
You select the cells up to the seventh column (G). It would be more prudent to select the entire columns but if we only select the used cells in the first seven columns as shown in the picture above, it still works, but we do get an additional question.
This is very logical actually. If you only allow the text to extend to the seventh column, then the overflowed text will have to go to the extra rows, so it will extend beyond row 5. So, you put OK and you get
Pretty great and straight forward but also very useful. But here is the secondary use. This one will blow your sox off!
We are given the following situation.
Now if someone was to say, “Let’s put all the month names in the first cell (A1)”, one would start to think about CONCATENATE or a formula that looks like =A1&A2&A3&… But there’s a far more elegant way. You select the cells as shown above, and keep in mind that the first column must span the width of the future text in A1. Now we select HOME/Editing/Fill/Justify and we get
Great little trick. Now how about to get them apart. Guess what, you only make the A column wide enough for one, and select HOME/Editing/Fill/Justify and here is eternal happiness
Now all this new knowledge takes us one step closer to eternal happiness.
Welcome back to Artificial Intelligence in Excel! In Part 1, ...
Highlighting Weekends can be hard in Excel. So just for fun, ...
The Excel SEQUENCE function In this article, we’ll ...
Last year, Microsoft announced the introduction of a new group of ...
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Great !! Thanks a lot for your post
This is a great tip!! Thank you so much…
Excellent! It is quite useful, and save time. Thank you.
Most people at work know me as someone to go to with their Word and Excel questions. Like you, I love to learn more and this is one I can immediately see I’ll use because I do fuss with text in Excel in certain types of worksheets. I had no idea there was a shortcut – GREAT tip, even for knowledgeable users :-0
Thank you so much for this excel tip. I will try it.
Regarding the secondary use, I may be missing something, but it seems to me you can go directly from step 1 (secondary use) to the final step (“to get them apart by selecting HOME/Editing/Fill/Justify).
If you look at the screenshot with the months, column A fills the full width of the screen. If you put your cursor on the little vertical line to the right of the A, and double click on it, the width of column A is set automatically to the width of the longest word in that column. And there is no overflow into column B.
So, using the Justify loop seems a unnecessarily long way around. But, like I said, maybe I am missing something.
Hi,
once you get to the last sample of the secondary use, you already have all the month names in one cell (A1). At that point that double click will not give you the correct column width and therefore you must set it by hand. Now the overflow can be easily solved then by the double click.