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.
Whatever side of the Cloud vs. On Premise debate you take, ...
We start with a range of values in Excel (A1:G20). Now we are ...
This post is a tribute to the event, that is one of a kind in ...
About two months ago, a team assembled to create an Excel AddIn ...