More things that Bill learned at Trainertage 2013.
Transcript of the video:
Excel tricks that I learned at Trainertage.
This is episode 1640: formula indenting.
All right, here's a-- cool trick.
I was sitting in the back of a Dietmar Gieringer’s class and I was watching him work.
He's speaking in German and first thing I noticed, you know, we separate the arguments in our functions with commas but in the German version of Excel they can use semicolons.
So that's the first thing that caught my eye but that doesn't work in the US version but, you know, you are allowed to separate by comma and space.
So you can add a little bit of space to help make the formula more readable, which in this case, you know, might or might not help.
I’m going to go into the next worksheet where we have a much bigger formula.
Okay, so you know, here's a really complicated formula, I realize you can't see the whole thing, that is doing a VLOOKUP with an OFFSET, with a MATCH, with a COUNTIF, a lot of different arguments going on inside of here.
Of course, you can make the formula bar bigger using the V at the end of the formula bar but here’s the thing that was really interesting to me.
In addition to being able to separate with a space after the comma, you're allowed to use Alt + Enter and you can actually have as many spaces as you want.
All right, so Dietmar showed an example where he was talking about a formula.
I think it was maybe INDEX and MATCH or some complicated formula and he actually went through and used Alt + Enter and space to line up the formula kind of indenting it like we would do in VBA.
If you're a VBA programmer, you know we indent the loops, we indent the If statements and it allowed all of the arguments that were basically at the same level, the arguments for the OFFSET function to get lined up.
All right, now rather than make you sit here and watch the whole thing let me go to the next worksheet where I’ll show you what it looks like.
All right, so, you know, here's a VLOOKUP.
The VLOOKUP has arguments that are at this level, there's four arguments 1, 2, 3, 4.
Here's an OFFSET.
There should be five arguments for offset so 1, 2, 3, 4, 5.
Here's a MATCH with 1, 2, 3 arguments.
I didn't bother to indent that one there.
So, you know, to a certain extent this formula becomes much more readable.
We're doing a VLOOKUP.
As the second argument of the VLOOKUP, we're doing an OFFSET so in other words the LOOKUP table is being defined on the fly.
All right, and so I think this is a little bit easier.
The problem with this-- the problem with this is that when the formula bar is collapsed, you don't get to see the whole formula here.
You're seeing just part of the formula $A$8:$A$37, So if I do Ctrl + Enter here, you see you just see the first part of the formula =VLOOKUP(B2, which might lead you to believe that it's just, you know, some weird VLOOKUP and you don't see the rest of it.
So one of the people in the class, it was a Dominik Petri, said, “Hey wait, there's a solution to this,” and it's my good old friend the N function.
Now N of a number is the number but N of any text is zero so we're going to add the N function and in quotes here just say =N(“Caution - long formula - expand the formula bar”)+VLOOKUP(B2, Now when we see this formula, it'll say, “Hey caution long formula…” I have to expand the formula bar to see the whole formula.
And there you have it.
All right, so I thought that was clever.
I've never thought of actually using Alt + Enter to line up the formulas.
I do this all the time with code but I've never tried with formulas.
Okay, well, hey, I want to thank Dietmar for that cool trick.
Of course, Dietmar has the Excel 2010 idea book in German about dashboards and also a shout-out to Dominik for throwing in the N function.
Next year trainer days January 17th through the 21st 2014 in Lucerne, Switzerland.
Most of the sessions are in German.
You know, I’ll be doing mine in slow English if you're anywhere nearby.
Hope to see you there.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
This is episode 1640: formula indenting.
All right, here's a-- cool trick.
I was sitting in the back of a Dietmar Gieringer’s class and I was watching him work.
He's speaking in German and first thing I noticed, you know, we separate the arguments in our functions with commas but in the German version of Excel they can use semicolons.
So that's the first thing that caught my eye but that doesn't work in the US version but, you know, you are allowed to separate by comma and space.
So you can add a little bit of space to help make the formula more readable, which in this case, you know, might or might not help.
I’m going to go into the next worksheet where we have a much bigger formula.
Okay, so you know, here's a really complicated formula, I realize you can't see the whole thing, that is doing a VLOOKUP with an OFFSET, with a MATCH, with a COUNTIF, a lot of different arguments going on inside of here.
Of course, you can make the formula bar bigger using the V at the end of the formula bar but here’s the thing that was really interesting to me.
In addition to being able to separate with a space after the comma, you're allowed to use Alt + Enter and you can actually have as many spaces as you want.
All right, so Dietmar showed an example where he was talking about a formula.
I think it was maybe INDEX and MATCH or some complicated formula and he actually went through and used Alt + Enter and space to line up the formula kind of indenting it like we would do in VBA.
If you're a VBA programmer, you know we indent the loops, we indent the If statements and it allowed all of the arguments that were basically at the same level, the arguments for the OFFSET function to get lined up.
All right, now rather than make you sit here and watch the whole thing let me go to the next worksheet where I’ll show you what it looks like.
All right, so, you know, here's a VLOOKUP.
The VLOOKUP has arguments that are at this level, there's four arguments 1, 2, 3, 4.
Here's an OFFSET.
There should be five arguments for offset so 1, 2, 3, 4, 5.
Here's a MATCH with 1, 2, 3 arguments.
I didn't bother to indent that one there.
So, you know, to a certain extent this formula becomes much more readable.
We're doing a VLOOKUP.
As the second argument of the VLOOKUP, we're doing an OFFSET so in other words the LOOKUP table is being defined on the fly.
All right, and so I think this is a little bit easier.
The problem with this-- the problem with this is that when the formula bar is collapsed, you don't get to see the whole formula here.
You're seeing just part of the formula $A$8:$A$37, So if I do Ctrl + Enter here, you see you just see the first part of the formula =VLOOKUP(B2, which might lead you to believe that it's just, you know, some weird VLOOKUP and you don't see the rest of it.
So one of the people in the class, it was a Dominik Petri, said, “Hey wait, there's a solution to this,” and it's my good old friend the N function.
Now N of a number is the number but N of any text is zero so we're going to add the N function and in quotes here just say =N(“Caution - long formula - expand the formula bar”)+VLOOKUP(B2, Now when we see this formula, it'll say, “Hey caution long formula…” I have to expand the formula bar to see the whole formula.
And there you have it.
All right, so I thought that was clever.
I've never thought of actually using Alt + Enter to line up the formulas.
I do this all the time with code but I've never tried with formulas.
Okay, well, hey, I want to thank Dietmar for that cool trick.
Of course, Dietmar has the Excel 2010 idea book in German about dashboards and also a shout-out to Dominik for throwing in the N function.
Next year trainer days January 17th through the 21st 2014 in Lucerne, Switzerland.
Most of the sessions are in German.
You know, I’ll be doing mine in slow English if you're anywhere nearby.
Hope to see you there.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
