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.

Justify in general

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.

Fill/Justify primary

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!

Fill/Justify Secondary

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

What to watch out for

1. The width of the columns is very important here. As shown in the primary use, as wide as the selected area is, that’s where the Justify command will stop. Keep in mind that the text is still only in the first cells of each row! But it does span the width of the selected cells.
2. In the secondary use, where we used the Justify command for concatenating, it did add spaces to our text! So you will get additional spaces, but you can get rid of those quite easily by using the =SUBSTITUTE(A1,” “,””).
3. In both primary and secondary use, we noticed, that text can overflow into extra rows. If you had data there it will simply overwrite it!
4. The use of this command does not give you the Justify effect from Word. It only gives you an ability to say, where the outer most edge of the text should be, but the ends of rows can still be a bit jagged.
5. One of the big ones is, when you use it to put the text together, and if the text length would exceed 255 characters, you will only get 255 and the rest will be deleted by Excel!!!

Now all this new knowledge takes us one step closer to eternal happiness.