From the course: Excel: Advanced Formulas and Functions

Use Alt+Enter to make formulas more readable

From the course: Excel: Advanced Formulas and Functions

Start my 1-month free trial

Use Alt+Enter to make formulas more readable

“

- [Instructor] Here's something that you can do to bring some ease and calm around complex formulas. Now let's look at this data. There's the compensation agreement, pay hours at a given rate up to 40 hours. The rate is in I1, that's $45.70. Time over 40 hours is paid at 1.5 times the rate. Got it. And if a person worked at least 35 hours at central or west locations, then pay the add rate. Let's look at Friendly. Friendly worked 51.5 hours at Central, so that means Friendly should get the add rate of $115. Friendly should be paid 40 hours times $45.70 cents and an additional 11.5 hours at time and a half. Okay, and we have the pay column with everything calculated. Let's go here. We've got a lot of at symbols, if statement, X look up, dollar signs, plus signs. A lot of stuff is going on in this formula. I'm going to peel it down. Okay, the first part up to this plus sign. Let's see, if hours greater than 40, and then 40 times the rate. Okay, so it's checking to see if the hours are over 40. All right, so here is the juicy part. I'm going to hit Alt + Enter. Oh, what happened? Look. I am going to open the formula bar a little bit more, and that is what is so exciting about this. Now, I have that one section by itself on its own row. But the formula still works. I have not put a space in here or messed it up in any kind of way. So let's continue to look. Hours minus 40 times 1.5, okay, so that's where it's calculating the overtime piece. So I'm going to go over here to this plus sign, hit Alt + Enter. Now, what's next? Should I just leave it like this, or is there another way that I can split this? Let's look. If the hours are less than 35, then zero. Otherwise look up the location. So that's what this last piece is doing It's looking to see that third part of the compensation agreement. If a person worked at least 35 hours at Central or West, then pay the add rate. So this X lookup is doing that work for us, and we could split it down a little further. Go back here and then Alt + Enter, and then I can hit enter. Good. All right. Now you see that all of the formulas have this same format. You can click on any sale in that column and see the formula all split out. Let's do one other thing. Let's do this. Let's say that Simon worked two hours at West. Okay. $91.40 cents is what Simon is due for those two hours of work. No overtime, and did not work at least 35 hours at Central or West. And let's look at the formula. The formula is all split out using Alt + Enter, making it easier for me to read. Say if I wrote this today, and I want to be able to look at it in the future and make sense of it or if I pass it off to somebody, they won't freak out by some long, long function, full of parentheses, at symbols, multiplication brackets, et cetera. Remember Alt + Enter.

Contents