Excel Hacking II - Formula Blueprint (Beginner & Advanced) | Dragos Stefanescu | Skillshare

# Excel Hacking II - Formula Blueprint (Beginner & Advanced)

#### Dragos Stefanescu, TeacHack Founder - Teaching 1000s Online

Play Speed
• 0.5x
• 1x (Normal)
• 1.25x
• 1.5x
• 2x
23 Lessons (1h 30m)

1:44

2:52

5:29

4:36

2:29

1:52

4:06

4:08

5:41

4:16

2:02

2:50

5:29

6:16

5:15

2:22

3:32

6:29

4:00

7:04

3:07

1:32
• ### 23. Quick Hack #13: Excel Goal-Seeker

2:26
• --
• Beginner level
• Intermediate level
• Advanced level
• All levels
• Beg/Int level
• Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

173

Students

--

Projects

## About This Class

Are You Ready To Take Your Excel To The Next Level?

I was an Excel Rookie too at some point. I put in a lot of hours to hone my skill and I managed to be recognised as an Excel expert in the companies that I worked for. Since then, I've gone on to teach thousands of students how to master Excel in the fastest possible time.

How Did I Do This?

By taking a different approach. I don't want this to be the regular Excel course, where I take you through every single different formula and functionality. I want you to:

• Focus on the benefit of each feature, rather than the means of getting there
• Understand the key elements that Excel experts use 80% of the time (my so-called Excel Hacks)
• Do this in a practical & applicable manner by following along the spreadsheets and completing them yourself

The End Result

I've differentiated the lectures between normal ones, Quick Hacks and Excel Hacks. These last 2 are designed to give you a great understanding of the must-know Excel features.

Here's what you will be able to do by the end of this class:

• Master 99% of the Excel Formulas that you will be using day-by-day
• Start from the basics with Sum, Average, Maximum, Count or IF Formulas
• Learn condition-based formulas (and the logical operators), as well as tricks to dealing with Nested formulas
• Become an expert in Vlookups, HLookups & Nested Lookups
• Master the most advanced formulas & combos which will make your spreadsheets superfast & accurate - Match, Index, Indirect, Address - Extract values based on Row & Column indicators OR Looking up values with 2+ unique criteria

Enroll now and let's start building your Excel journey together!

## Meet Your Teacher

#### Dragos Stefanescu

TeacHack Founder - Teaching 1000s Online

Teacher

About Me

I'm an Internet Entrepreneur with a background in both the Management Consulting & Telecommunications industries. The skills that I have acquired during those stints and in previous years in general have unleashed my passion to teach.

I have a wide array of skills that I share with you via my courses

(1) Social Media

One of my true passions, I have invested a lot of time building a presence online. Highlights include:

Creating a connection base of 100+ people in a large Management Consulting firm within weeks of starting the job. Met with a handful of Managing Directors & Senior Managers after approaching them on Social Media Trained Partners on how to effectively make use of LinkedIn & Twitter to network and generate leads Conducted Workshops wi... See full profile

## Class Ratings

Expectations Met?
• Exceeded!
0%
• Yes
0%
• Somewhat
0%
• Not really
0%
##### Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

# Your creative journey starts here.

• Unlimited access to every class
• Supportive online creative community
• Learn offline with Skillshare’s app

## Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

## Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best. ## Transcripts

1. Excel Hacking Series Trailer: V lookups pivot tables, conditional formatting, sorting and filtering. These are just a few of the aspects. Let's face it. Every Excel course out there teaches boring theoretical concepts without any practical applications. Let's think different. Let's think. What are the top 20 Excel hacks so I can produce mind blowing investment banking quality spreadsheets in record time without spending eight boring long hours Learning Excel theory . That just leaves me confused, perplexed and over. Well, what if you could learn everything twice as fast, skyrocket your productivity and even advance your career earlier than you imagine possible ? So how much are a few 100 hours worth to you? Here's how this course is different. You learn hacks such as one Excel Essentials will cover the quickest waste navigate. And when Nipple E data in Excel is that you can master the basics straightaway to 20 plus quick and killer hacks. Individual lectures focused on particular Excel hacks, which 90% of people don't know about three Bullseye formula blueprint Master Excel formulas by shifting the focus away from how they work to what their usability is. Impractical scenarios. If we truly want to accelerate your progress and hack excel in the shortest amount of time . Then this is for you. Take this course now and meet me in the virtual classroom in a few seconds. 2. Formula Syntax Explained: formula, Syntax explained. Let's just start easy and see how we actually write formulas in excel. So I just have a blank page here and in this blank page, I want to show you a very basic example of a formula. So we're gonna do is we're gonna go into cell where you want to write the formula, and we can either go on the top here as I showed you on the formula bar or weaken, type it straight in the cell and we're going to start with the equal sign. Equal sign shows excel that we're going to introduce a formula. Now, after the equal sign, we have to enter the actual formula. Now, I'm gonna use one of the easiest formulas out there, which is the sum function. You can see that as soon as you start typing letters here, Excel will show you all the formulas that start with those particular letters. So if I right some, the 1st 1 is gonna pop up and Excel is also going to tell you what it does in particular. Now we're gonna open the brackets. This is the third part of the formula syntax. We need to have A brackets because we need to have an argument and excel again shows you exactly how these arguments should be re written down. So for the some, for example, we need number one number two and so on. So here we're gonna choose, for example, number one and I can either drag these and it's gonna take all of them together. Or I can hold control on the keyboard and select them individually. You're going to see this affect the way the formula is perceived. And then we simply close the bracket and hit, enter and then the formula. As you can see it with F two on the keyboard, you can see exactly what the formula looks like. That's a shortcut. Now you have to understand in the actual argument, there's a bit of a difference between having comas and having two dots. Okay, so having comas will select cells individually. So, for example, if I move these cells here, then the formula is going to stay the same, and it's still gonna point to those four values. However, if I take all of these 41 after the other, and I'm gonna modify the formula just to show you what I mean by that. So if I take be for all the way to e four and I closed the brackets I hit enter. It's the same exact formula. However, when I want to insert cells here, it's gonna completely change because I have a range here. And if I have a range, then it's gonna take all of these cells into consideration as well. And if I actually had values here than the sum is going to continuously change, I hope that was a clear explanation of the formula. Syntax. This is a very basic approach to formula writing, and we're going to start going into the basic formulas in the next lessons. 3. A Complete Guide To Absolute & Relative Referencing: absolute and relative referencing in Microsoft Excel. This is an absolutely crucial lesson. If you want to get good with formulas in Excel because you're gonna encounter this type of referencing everywhere, whenever you write formulas, you're gonna need it. So what is absolute and relative referencing? Sometimes when you drag formulas around, you will need to fix them in place, whether to block the row, the column or both. So these are the famed dollar signs that we saw earlier in this course. Now I just have a simple application here where I want to multiply in each of these squares . All of these numbers here's to basically have one times one all the way to nine times nine in this square. Okay, very, very basic. And I'm gonna show you exactly how use this referencing style right now. Let's start with the easiest example. Let's say I want to multiply one by one. And if I do that and try to copy the formulas across, let's see what happens. It's gonna be essential for you to understand how this works. Notice how this isn't really what I want to do. Okay, 576 has nothing to do that. The highest number should be 81 it should be in this corner right here. So why is this happening? Have a look at how these formulas advance. So from one times one, everything moves across one cell at a time with the formula. As you can see, everything moves across one cell at a time. So this shows us that the cells are not blocked. So how can we do this? We're gonna use the absolute and relative references to block the cells in their space. And the way we do this is we will press F four. So that's the shortcut. You can also put the dollar signs, but F four is the easiest way to do it. So again, one times one, I'm going to go on the B three right here and now I'm gonna press F four. And the first thing that it does is it blocks both the column and the road. So the dollar sign before the B means that it blocks the column. The dollar sign before the three means that it blocks the road. So those variables in the formula are not gonna change. Let's see what this is gonna do just as an example if I do this. And if I copy this across, if I paste all the formulas, here's what's gonna happen. This is going to stay fixed at one. As you can see, this cell is fixed everywhere, and the red one moves across with it. Okay, so this is because both valuables are locked. But what I want to have is the column locked, but not the road. I want the road to go down for the first factor. So if I want that, then I can go on it and press F four again. And as you can see now, the road is blocked. Press it again, and the columnist block the dollar sign is before to be. And yet again, let me show you what this does. If I copy the formulas across, this is going to achieve our target 50% of it anyway, because if you look at it now, wherever I go, the Blue Valuable, which is the 1st 1 always stays on this column. However, it's the row now that pivots with the formula. So what I'm gonna do is I'm gonna go back here and do the same thing for the second argument, but I'm going to keep it with the role being blocked. So I pressed F or twice. I have the dollar sign before the number two, which is the road, and I'm gonna press, enter and look what happens now. Do you think this is going to solve our problems? Yes. No. Yes. No. There we go. That kind of looks like it, right. I mean, that's correct. From what I know from my basic maths, I think that's correct. And let's see exactly how this happens. If I go here and I start moving around, notice how the column stays blocked. The row moves, the rose stays blocked. The color moves. So everything is exactly as it should be. Now, this is simple example. You might my ask yourself, Why would I need to create this table? You won't need to actually create this whole table. However, We're going to go into more complex formulas. You're going to see that you're always going to need to either block the row, the column or an entire self. For example, if I wanted to multiply off these by a factor three. Let's say then I would add eight times this column right here, and I would block it completely. And then when I copy the formula across, here's how it looks like everything is multiplied by free. And if I go on every formula, you'll see the three stays in place regardless of where I go. So you're gonna need all of this and this isn't extremely. I cannot emphasize how important this concept is for you understanding how to write Excel formulas. So practice it. Do the same, um, applications that I did on this table in your own spreadsheet. And don't go to the next lesson until you're fully understood how absolute and relative referencing works and you're able to do it moving forward and apply it in the next few lessons. 4. 3 Essential Formulas To Master: this some average and count formulas in excel. So I want to show you really quickly how to apply these three formulas. We're back at our student database, and we have all of their great. We have three new fields here. Total out of an average. Okay, we're on the some average and count sheet in the section for Excel. So let me start with the some function, which is extremely simple. So I'm gonna go on the total call him here. Press equals right some. And as you can see, it pops up. You can press tap to open the bracket. You don't need to press the bracket on your keyboard. So I press tab and now I hold control down. So I hold control and I select all of these fields here. The alternative can be to use control shift like I taught you. So you go on the first cell control shift and press the right arrow and they will still select everything. Now, we don't need absolute and relative referencing for this because we're going to drag the some formula down and one off these instances to go down with it. Now I'm gonna close the bracket and simply press enter. And there we go. We have the some for all of these instances. Now I'm going to go on to the right and complete these 34 minutes, and then I'm gonna drag them down to show you exactly how you can complete the whole table . So let's move on to the average one. The average one is basically the same. It's very easy, and Excel does everything on its own. You basically don't really need to tell it anything more so average. As you can see, I pressed a V e r. It pops up. I can press tab and it automatically completes everything and opens the bracket again. I'm going to select all of these instances here with control shift in the right arrow and then I'm gonna close the bracket and press enter. And, as you can see, it compiles an average for me, which I can then modify the formatting for keep two decimals. That looks a bit better now for the outs of um, column. What I wanted to do is show the total score out of whatever modules these guys took. However, as you can see, most of them all of them are completed all the field. So everybody took everything. So I'm just gonna select all of this table, do a find and replace and replace the eight with nothing. So I'm going to get rid of all of the eights, and I'm going to show you exactly why I did that in a second. You're gonna understand. So now I want to see this guy got 45 points. Bois was the maximum. What was the maximum number of points that he could have gotten? So right now I'm gonna use the count formula. And basically, the count formula has a number of variations which you can see right here. But the basic one count only counts the number of cells in a range that contain numbers. So if I use count on the same range that we used before, be careful. Select the whole thing for when you also fill it on the way down and I closed the brackets , it's gonna return six. However, I don't want six because these grades are on a scale of 1 to 10. So I'm gonna do is I'm gonna go into the formula and multiply it by 10 because that's the maximum great that a student can get. And now, as you can see, I got 45 points out of 60. And coincidentally, if I divide 45 by 60 I'm going to get the average. So see how nicely everything ties up. So now that I have to some the count and the average I have everything that I want in one of the cells. I can simply select them press control, see, to copy, select the whole range. I compress control shift because Excel will go all the way to the bottom of the spreadsheet if idea because there are no values in here, right? Click based, special with formulas. And there we go. Everything is completed, everything looks exactly the same. If we check it out now, just look how everything is referenced to the same cells on every single row. The only thing that is actually messed up are the formulas, which I can just select all of them and make them two decimal spots. And that is it. That is the introduction to the first re formulas that you've learned some count and average 5. Quick Hack #8: Verify Formulas Fast: quick hack verifying formulas fast in Microsoft Excel. So when you work with complicated spreadsheets, you're always gonna put these formulas in. We have to some the count and the average here, and you're always gonna want to check them. Now, I want to show you the quickest way to actually check simple formulas. Let's take this example here. The sum is 45 now, these numbers could be much more complicated, much more complex. And you wouldn't really be able to tell that that some the 45 looks good or not. Maybe you want to do another some here just to be sure. Okay, I just added everything correctly. Everything is good and you press enter and you see that's 45. But what's a shorter way to actually do that? And the funny thing is, I didn't know this trick in the first few months of me working in Excel, and I saw some people do it, and my mind was blown that I I didn't notice this faster. Here's what happens when you select D cells and look at the bottom of the screen. It will show you the count and the sum this by the way can be customized. I'm gonna show you how to customize it right now so you might see different things depending on how your Excel is set by the fault. But it shows me that some of these figures is 45 the Countess six, which have already established. And the average is 7.5, which is exactly what I have here. And you can do this for a number of cells, depending on what you want to see. So that's a quick way to check formulas, and I strongly encourage you to embrace it. Now, the way you actually modify this is when you select the cells, right click and you see all of these things pop up here. And if you look at this section, you'll see you can actually add the minimum and the maximum and a numerical count. And now, if I go to the minimum and maximum sheet, I can select everything here and it's gonna tell me the minimum. It's four, which is here. The maximum is 10 which is here, and we're gonna see this in the next lecture, where we look into the minimum and the maximum formulas and your sheet is gonna look exactly like this one. So that's how you customize this feature. That's how you use it. I strongly encourage you to always select cells and pay attention to the bottom of the screen, because the reality is going to make him or accurate for all these simple formulas, which you can always make mistakes on, especially if you're in a rush and it's gonna make you much more productive going forward. 6. 2 Ways To Identify Maximum & Minimum Values: the minimum and maximum formulas in excel. Now, I'm gonna go very quickly through this lecture because everything is exactly the same as with some count and average formulas, the result is just what the first. So I have the same table. But this time I want to find out what the students worst great is and what his or her best grade is. So I'm gonna use minimum and maximum. So let's press equal in certain men, which is the minimum function press tap to open the brackets and simply select all of the numbers here. As you can see, it shows you the coma so you can select them by holding control individually. And I can show you that as well as I showed you in the syntax lesson. This only makes a difference if you insert Rose, um, or play with the actual formula in the actual fields in there. So I'm just gonna do it the right way. That I think is right in this particular scenario, and I'm going to do a minimum of these for this d seven cells and press enter. And as you can see, the worst grade is for now. I'm gonna just say with the maximum formula, select everything here and press enter. And I think you see the worst grade is for the best Grade is 10. And if I said like everything and look at the bottom of the screen, you'll see minimum it's for and maximum is 10 Now I proceed the same way I copy. I select all the cells all the way down till the table ends and I press space special formulas. And there we go. We have everything set. We only need to modify the formatting just a little bit. And now everything is done. We have the minimum and the maximum grades for all thes students. 7. How To Use The IF Function The Right Way: the if function, let me show you how we can use the function by itself and excel. So the syntax is very simple. We have the student database again. We're on the if function sheet. Now let's say I want to see I have a criteria that everybody that has an average over 7.5 has passed and everybody else has felt What am I gonna write? I'm going to go with if press tab to open the brackets and then I need to put a logical test again. You have all of this syntax on the pdf cheat, cheat for formulas and a strong encouraging to have that in front of you. So you understand the syntax of the formula. Better now, what is my logical test here? I have to test something in particular. And what I need to add to this is all the operators and how use these functions are going to be present in the same cheat cheat. So look at that. Before you go. This lectures, they actually understand what I'm talking about. So my a logical test is going to relate to this value right here in the average field, so I said, I want everything that is over 7.5. So I'm gonna put bigger than 7.5 as a logical test. And then I need to tell Excel what value to put, if that is true and what value to put if that is false. Now, if I want insert text, I'm gonna put these apostrophes here and the value if true. So if there over 7.5, I'm going to say past and then I'm gonna put a coma, the value if false, is gonna be failed, and then I close the whole formula. And as you can see, this guy passed Now, if I paste the formulas down, you're going to see I have a combination of pass fail past failed, depending on what I have here on the value that I chose. Now, let me show you how you can actually, um, it, uh, putting something. So as you can see, there's some squared brackets in here, which means that you don't actually have to put this value in there, so I can just keep it with the past, and then it's not going to complete the failed anymore. It's just gonna put past for the ones that have actually passed and it's gonna say false for the others because I haven't indicated anything for the second branch of that. If what if the condition is no met? So then what I can do is simply put the apostrophes twice. So I'm leaving it blank. And if I do that and copy it downwards now, you're going to see everything changes because I'm gonna have blanks instead of having the false argument in there. Let me show you one more instance of how we can combine the if with an actual Formosa how we can say if this condition is met, Let's actually put a formula in here. So what I'm gonna do is I'm gonna calculate the average, and if the average is over 7.5, I'm gonna express that average in here. If not, I'm going to say failed. So I'm gonna go if and then my condition is gonna be different because I don't have the average calculated, so I'm gonna say average of all of these factors here. If this is larger than 7.5, which was my initial condition, then the value, if true, is the same exact thing. So the average of all these factors here and if not, if it's less than 7.5, I'm going to say failed. And if I say that, then what's gonna happen is it's gonna put the average, um, in that actual box. And if I copy it across and someone has failed, if it's under 7.5, it's purely going to say failed. Rather than calculating the average, that's the way you can actually play with the F formulas, and we're going to see different variations of these formulas as we move forward. 8. 3 Operators To Expand Your Condition-Based Formulas: the and or and not operators that we can use of formulas. I just want to show you how you can customize the arguments in your formulas by using the's three operators. And as you can notice, right now, we're on the end or not sheet in the section for Excel. So I've already written these formulas and I want explain them to you. So with end every condition that you put needs to be true for its return. True, otherwise they will return. False. So what am I following with this particular formula right here? So I'm saying and and this is the condition. Okay, this is the logical test right here. The logical test is if they're great and economics is higher than seven and they're great and businesses hired in seven, then they're part of the business economics top students, okay. And because it's and both conditions need to be true at the same time, some only gonna have. Because the strict sign I'm only gonna have the 89 and tens apply to this condition. Now, if it's true, then it's going to show the average of these two. Okay, so this is the true value and if it's not true, is simply going to say not qualified. So that's how used the and, um, argument. And if I copy this formula down, you're gonna notice how all of these guys that are over eight will have high averages. So you can see 98.59 10. And all of these particular examples right here are over seven in economics and in business , and everybody else is simply not qualified Now. I decided to include a specialist column, and I'm gonna show you the or function here now with Or it's a bit different because any condition can be true and it will return true if all conditions are false, that it will return false, so you only need one to be true for its return. True. So what I decided to show here is is any of these students a specialist and by specialists , I mean in accounting, marketing, operations or finance. And when I defined by specialist is anybody who has a nine or 10 in one of those fields and as you see most of them do so it won't test this function right here, and they will return specialist if true. If it's not true, then it will return a blank. And as you can see, the 1st 1 is a specialist, and if I copy the formula down, you see that most all of them are. But there are a few exceptions. And if you look at these exceptions, you see none of these great touch the nine level 638 He's not a specialist and so on. So that's how used the or function. It's very simple, very easy. Now, finally, let's go to not function. How can I use the North function? I'm just going to reverse this function right here with the end and show you how all the resorts are gonna flip just like that by using the not argument in there. So what are we going to do? We're not gonna put the not at the very beginning, but what we wanted. The gate is the logical condition. Okay, ideological test. So if this returns true, I wanted to return false. So I'm going to write not before the end, an open of racket and close it after the end. So right now it's gonna reverse everything. And as you're going to see When I copy everything down, all the figures here are gonna turn into, not qualified and older, not qualifies are gonna turn into averages. There we go. That's how used these three operators. They're extremely handy, and they're mostly used with the F formulas, So get to grips with them. Look at the cheat sheet, the formula. Cici, everything is explained in there. And I hope everything is crystal clear and ready for you to use in the future. 9. No-Nonsense Guide To Solving Nested IFs: nested ifs. What are nested ifs? It's basically when you have an IT function as part of an F function as part of an IT function and so on. You get the point and you're gonna have to use this in certain situations because there's, you know, it's like a branch. You're going to go with an A function you can have a true and the false is gonna go one way or the other. But sometimes you need to put more conditions than that, and this is the easiest way to do it now. I've tried to do this in a practical way here and this and the next lecture, I hope, will really make a mess that it's clear. And the other thing that I want to mention, even though this might be counterintuitive for this lecture is gonna be a bit long. And I made it a bit more complicated in the sense that I put five bonus rates here because I want to confuse you intentionally to show you how useful the quick the killer hacked that I have in the next lecture is to you. So a very practical situation here is we have a list of almost 100 employees and the annual salaries. We want to calculate their bonus rate, and we have the bonus rates right here, depending on what their admiral salary is now. The way we're going to think about this is we're gonna compare the salary with 20,000 if it's lower than 20,000. We know that's the bonus, right? If not, we have to see if it's lower than 40,000. Then we're going to know what the bonus rate is. If it's no, we're going to go to the next one to the next one. And if not, we're going to know what it is. So let's right that if statement and the whole formula, which is gonna be quite long in itself, so we're going to start with it. If what are logical tests are logical. Test is the annual salary. So I'm wondering whether the annual salary is smaller or equal to 20,000 which is my first branches, my first value that I want to compare. And I'm gonna fix this with the absolute on relative referencing, because as I'm gonna copy the formula down, I want this to always be 20,000 so I'm gonna compare that. And if the value is true, then the bonus rate is gonna be 10%. And again, I'm gonna lock that in on that particular cell. Now, if it's not under \$20,000 that I need to compare it with the next one, So I'm going to write another. If so, see how it works. If it's false, right? If it's true, it gives me the 10%. But if it's fools, I open another. If and another branch this if will test the condition again. So if the same c 12 this time it's smaller or equal to 40,000 which is going to be locked now. If this is true, then the bonus rate is going to be 15% which is locked. If it's false, you guessed that we go into yet another if and it's gonna go the same way four times. If the annual salary is lower or equal to \$80,000 which is locked, then the values of the value of the bonus is going to be 20% which is also long. If not, then that means I need to open another if and test the salary again. So if the salary is less or equal to 150,000 which is locked, then the bonus rate is gonna be 22.5%. And here's where we don't even if because we only have one other value. I'm just gonna put a coma. And if it's false, so if it's over \$150,000 obviously it can't be anything else because I've started from zero all the way to 150,000 then it's gonna be 30%. And I'm gonna lock that in as well. Now notice. You have to pay particular attention when you close the brackets. Excel is gonna bold the colors for bits that you don't lose track of them. 123 and four brackets. And now I'm gonna press, enter and I'm gonna format this whole column right here to show percentages. And once I do that, you're going to see exactly how the bonuses start to be completed. So I'm gonna copy this all the way down. Let's see what we actually get. As you can see, over 150,000 30% 30% 30% 20% which is between 40 and 80,000 and so on. So everything is completed. The if function works just fine, and then I can simply calculate the angle. Sorry times the bonus rate. And I get what every single employee gets as a bonus from the company per year. So that's the way we use nested ifs. It's not something complicated. We just have to get to grips with the thought of Okay. Every function has two branches. It can go on the true branch, and it can go on the false branch. If it goes on the true branch, then it's the end of my if if, and I put exactly what I want to show in this call him. So I always put the bonus rate. If it's true, if it's false, however, I still have a number of options. So I'm gonna go with another test. If it's true, I put in the bonus rate. If it's false, I go into another. If until I get to the variant, I hope there wasn't too confusing off. See on the next lecturer, I'm going to show you exactly how to not get confused when writing this formula 10. Excel Hack #3: Organised Formula Writing For Enhanced Visibility & Productivity: Excel hack how to write your formulas in a very tidy way. Now, this was me in the nested ifs lecture, writing my formulas like a rookie. And I wrote by formulas like this for a long time until I caught this trick. And when I learned this trick, I never let go of it. So what I want you to do right now is go on one of these cells where you have these nested ifs, all of these confusing ifs. And this is not even something to complex because we're only comparing cells to each other . Sometimes you might have an average of something else, and this could take that much space for one of these arguments here, and it just becomes impossible for you to realize what if does what and what's happening. So what I want you to do right now is to click this drop down arrow on the formula bar at the very end so that we expand the formula bar itself. And now I want you to use two combinations of keys. The 1st 1 is all to enter. The 1st 1 is all to enter. And what all enter will do is without affecting your formula, it will put the rest of the text on another road. And I want you to use space because if you didn't know, space is very useful and it does not affect yourself formulas. Atal, unless you put it here, the bracket needs to follow the if right after it. Otherwise, you can use space as much as you want. So let's see how we're gonna do this. Check this out. So we have the initial logical test. Okay, that's our first logical test. I'm going to use space and push this forward to see what it's gonna, um, show me if the value is true. So if I go on the true branch, that's what it's gonna show me. That I'm gonna go here before this if and press fault enter. And I prefer to this twice because I like I like to leave a row in between now. I put this if on the next row, and I'm gonna do exactly the same with everything else in the breast space until I distance this a lot, I'm going to go before the if and press Ault enter twice and then I'm gonna go here again press space until it's aligned. And then I'm going to go before the if and press old enter twice, and then I'm gonna be the exact same thing. Push this forward now, I'm not gonna press old enter because this is the full sprang church, pushed us all the way there and then pushed these brackets to show how many ifs I actually have open. And when I do this, you might notice that if you go on the edge here with the cursor, you can actually expand the formula. Check this out. Look how different this looks than what we had before. I can just press enter and it's going to stay to say, Look at the two formulas. Look at the way it looks normally where you don't really understand anything from it. Okay, you just wrote it, so maybe you can understand it, But look how tidy and neat this is. If all the conditions here c 12 b five, b six, b seven, b eight. So we costly compared B five, b six b seven b eight. We have the true branch here, So if it's be five, then we put C five b six we put C six. If it's be seven, we put C seven. If it's being, we put C eight and that's at the very end. When we contest anything anymore, we go on the false branch, and that's when we put the value from the false branch. How does that feel? How does that feel knowing that you can organize your formulas like this? And as I mentioned, this is a very simple example because we're on Lee using particular cells and compared them to one another. If you have more complex formulas like V lookups or average formulas or whatever the case may be, if they take much more space than this, let's eat that much for a simple argument here than this is gonna be extremely, extremely useful. So by all means, remember the two shortcuts all enter and space and try to implement this in your formulas and write him in the tightest possible way. It's gonna go a long way to you being organized and efficient in Microsoft Excel 11. Section Introduction: welcome to the Advanced Excel Formulas chapter. And I'm really pumped up for this chapter because I know ah, lot of you were waiting for it. And I know this might bring the most value for the most of you. This is what you bought this course, why you enrolled in this course. This is what you wanted to see. So here's what We're going to cover the most use formula. So obviously the obvious candidates vy look up index match and all those. I'm gonna go through each of these formulas and show you how they work. Now, I'm also gonna add some nifty tricks for increased accuracy. So what kind of things can we add to ensure that our formulas are accurate and that we have safeguards in place In case we modify parts of these spreadsheets? I'm gonna go into formula combos, and this is one of my favorite things to do because it's not many of them. Don't get me wrong. I'm not trying to show you all the formulas that Excel has. I don't want to show you all the functionalities next cell because you're gonna get confused. But these are the formulas that I have used mostly, let's say, 90% of the times when I was using Excel for large spreadsheets. These are the things that you need to master, and I'm gonna show each and every one of them to you. And I'm also gonna have a few killer Excel hacks sprinkled here and there throughout the section. Keep your eyes out for them and practice them and try to learn them. As always, have the spreadsheet. If it's true for any section, it's true for this one. Probably most out of all of the sections have the Excel in front of you. As I said, if you had, if you have dual screens, good. If not, just try to do the same modifications that I do. Try to master them. Lecture by Elektra. Don't skip the next one before you've mastered the one that you're at. And if you do that, I guarantee you're gonna learn these formulas in the least amount of time. So that being said, let's just get going with it because we have a long way. We'll sort of long way in the section, and I want to get going as fast as possible. Seeing the next one 12. Time Saver: How To Define Names In Excel: main manager how to add and delete names in excel. So before actually start with V. Look up, which is the first from formula that I'm gonna explore in this section. I want to show you this first tap. This is the database that we're going to use for this entire section. And what I have here is something that might look familiar to you. It might not, but I basically tried to go for the model of a spreadsheet of a input sheet that will be the most useful for what we're going to learn in this chapter. And what I have here is a bunch of models of the Volkswagen golf so different engines and all of that. And here I have the actuals from January running all the way to December. So these are the sales figures for every model for every, um, of that of those months, obviously their fictitious numbers. So don't pay attention to the numbers themselves. And here I have the budgeted numbers for that exact period of time. So I have 24 columns of data in total. Plus the name here, Let me go now into defining names in Excel and how you can play with that and how that can be beneficial for you. So what you can do with names is basically a sign a name like you do a formula that is obviously something different than what a formula would be. The name itself, the characters. And you can refer to it in formats rather than me selecting this whole table in every formula. If I use it throughout this file, I'm just going to select it all. Go on. Defined name. So I'm on the formulas tab. Okay. At the top on the defined name section here, I'm gonna press the fine name, and then it's gonna ask me, what do you want it to be? And I'm just going to write database, okay? And I'm gonna make it available in this workbook. So this name is for the whole workbook for this whole Excel file. And here I have the expression. And as you can see, the referencing is absolutely fixed. So it's not going to move. It's not gonna pivot around, so it's gonna press. Okay. And now, for example, if I go here and I tried to write a V Look up, um, look, up value. Let's say just we're gonna cover this next section. But when I go into table array, I could just write database. And as you can see, it's the first thing that pops up and it's gonna refer to this whole thing. So that's how the name manager works. And if you have Excel files where you have these fixed tables, these fixed ranges, they refer to all the time and your formulas looked like that where, you know, you just drag it. You have a one all the way to whatever is in the other corner of this table. You can just define the name and then you have it in the main manager. However, try to be tidy with this, trying not to define too many. And if you do and you want to delete some of them, you simply click on name Manager. You're gonna have all your names here, select the one that you want to delete and just press the button. And that's how easy this 13. Beginners Guide: Using VLookup: assigning values to rose by using fee. Look up. So in this lecture, this is the first formula based lecture in this section. I want to show you how you can match certain values to your different column or row Heather's. And for the row headers you can use V. Look up for the column Headers you can use hates look up, which is the same exact thing Onley It searches horizontally rather than vertically. So what I want you to focus on is the practicality of this function. I don't want you to just think. Okay, This is how V look up can be used But to imagine how you can implement it in your own spreadsheets. So basically, what I'll do if we look up is I'm gonna want to find out what this value is right here in this column. What do I have for this particular car model for January actuals? And for February, actuals, March, actuals and sons will have the first quarter of actuals and the first quarter of budget. Now I could just equal this and then go manually on this other sheet and find where this model is and just equal. It that. But you can tell by now That's extremely inefficient. So what I want to do is I want a match. This name right here. So this rope I wanna ask Excel. OK, tell me whenever you find this instance in the database, what the corresponding value is and the most important The most important rule for V. Look up and icon Stress this enough. When I was learning view, look up a few years ago, I struggle with this until I got it through my head. So please, please remember that when you use, we look up the value that you're looking for. So in our case, the name of the car has to be in the first column of the table that you're looking through . So as you can see when I'm going to select in the formula, what table I'm looking at, this has to be the first column. Obviously, it's called him a so it's gonna be the first column regardless. But when you have certain tables that are more complex, you have to make sure the value looking for isn't the first column. Now let me show you how the formula works. It's It's relatively easy. And once you get used to it, you're gonna handle it in no time. So we're gonna place equal. And then V look up. I'm gonna press taps that the formula fills itself up. And I have all of these arguments for the for the formula. Now, look of value is what I actually want to look at. So I'm gonna select this. This is the name that I want to look at in the other database. So this usually is gonna be right next to the cell that you want to fell in, which it is right here. Then I'm gonna hit the coma, and I'm going to say which is the table right now, you can either use the name manager, which we've defined earlier, or you can just select the whole table just as I'm doing that. So right now I've selected the whole table, but notice how I'm blocking it with the referencing so it doesn't move. When I dragged, if we look up around and also as I mentioned, the model name is the first column in the table. That's the table. Right? Then this is the column index number. What is this? It's basically a very easy variable or I tell it which column I wanted to show. So if it's looking for golf trend line 1.2, it finds it here on the second row. Then all have touch is okay on this second row, give me column X a number so I can tell it column to column three column for whatever the case may be. In my case, I want January. Actually, which I see are in column two here. So I'm just going to write the to. And in the final argument, this is an optional one. The range of the look up, false or true, True is an approximate match. We're not gonna play that. We're only gonna go for a false match, which is an exact match. So it's gonna look for the exact name of the model car. Otherwise it's gonna return air. And when I pressed enter There we go. The values there, you can see it's the same 764,000 and then what I can do is copy this formula down, and it's just gonna fill it up for everything else. Now if I copy the formatting if I play with the formatting as well that it looks nice. Then I have all the sales for January and then for February. The only thing that I have to change in this particular instance is to change the number of the column and also lock the argument in the formula. As you can see here, I haven't locked it. And right now the V look up is looking at this column right here. So that actually tells me that I have to modify this, lock the column right there, and then copy it across for all of them. And then it's just a matter, as I said of modifying the column here. So instead of two out of three for February here instead of too, I have four for March. So moving one. Call them to the right and then I can just copy this formulas down. And then I suggest you have different figures and just play with the four. My team and my table is done so that as we look up, that's a very basic application of the look up, and I'm gonna show you Maurin Mawr advanced formulas as we go along 14. Quick Hack #11: Vlookups For 100% Accuracy: quick hack V lookups with maximum accuracy. So I hope that by now you've mastered V lookups from the previous lecture. You know exactly how they work. If you don't, if you don't feel 100% sure, then go back to it. Go through it again and make sure you know everything. If you have understood how the work, then you're also going to understand this the way we built our we look up formulas in the previous lecture, although accurate, still doesn't provide all the safe cars that we would want and let me explain. So what we've done here is we've referenced the cells correctly. However, when we asked for the column that we want the value for were quoting a fixed number. Now let me ask you what happens if, for whatever reason, I need to insert two columns here. As you can see, the database has shifted. And if I go here the formulas. Havas Well, so now they're pointing to the same columns column three and column four. However, now in my database. Columns three and column for our blanks. So how can I actually prevent that? And how can I make this V look up completely bulletproof. Okay, Mistake proof. Whatever you want. Nothing's gonna change if you add new columns to it. And the answer is easy. I just need to change this number here and somehow generated through formula. And that formula consequently is gonna be called him. Now, what column does is a will return the number of the column that I'm referencing. So that's saying fixed number, but through a formula, and that's going to make the whole difference. So what I'm gonna do now is I'm gonna delete this to here. I'm going to go on the database tab. I'm gonna delete the database name from here whips, and now simply start customizing it. So I'm gonna right column press tab. And I want to obtain Janu now. Right? So I'm gonna choose column B. I'm gonna leave this one fixed notice. I'm not playing with the absolute or relative references. I'm closing this formula minus. Call him again, and I'm gonna put call him a Now, pay particular attention. I'm gonna block column A Because if I want to drag the formula around for the next month, I won't call him a to stay in place and I won't be to shift to C to D t e and so on. And now I'm also gonna add one. Why am I adding one? Think about it. This formula right here. Call them. Be right. Is gonna return the number two because it's calling beats. A second column column of database A is gonna return to number one, not zero, which is what I need because column B is the second column and I need the number two for the V. Look up said I tell it, Return the second column. So it's always gonna be plus one simply because the first call them a has a value of one rather than zero. And it's gonna be the same column C is gonna be three minus one plus one. Now the reason we're doing this with the minus and not just simply putting column B here is that this is a particular scenario where our table is starting from calling a have our table started from column D, for example, then this would have bean six minus four or five minus four, and not to minus one. So that's why this is going to be universally available. It's gonna work all the time, regardless of whether your database starting column A or not, I hope that's clear to you. And now I'm gonna press, enter. And as you can see, the value is the same. Now, if a copy this formula across for all three instances, you're going to see that the column shift. So now have column C minus column A And here I have called the minus called a. So that's how I put everything in there. And now if I insert columns here instead of CND, watch what happens? Absolutely nothing, because this formula has pivoted to column e minus column A and this one has pivoted to call him f minus column A. So they've both shifted two columns for exactly the number that I inserted in the database . So everything is fine now, so that's the way you actually safeguard against these things that you might not even notice. They add certain columns, and that's the way you ensure maximum accuracy when you deal with the lookups and excel. And here's what else it helps you with. What if I want to get the budget now, imagine me writing the formula right now, we look up, I want to get this model car. I'm gonna block this on the column, and then I go into the table rate in the database. Right? So I basically do everything again. I select the whole table. I lock it with the roads of references. And then when it comes to the column, I go like, OK, so it's 123456789 10 11 12 13 14 50. Okay, so any column 15 and I start counting all these columns, Imagine if I have 50 of them, Like the stakes are so easy to make when you do that. So instead of doing that, I just go call him. So, like this one here, which is what I want, keep it like that and then minus call him a lock it in place plus one. Put the false for an exact match. Close the formula. And that's that I have the budget. I know February and March are next to it. I can copy the formulas across play with the formatting, change the numbers, make them look nice. And there we go. That's how easy it is. And that's how you should use we look up. I did it on purpose, showing you the column with a number in the previous lecture to make it easier and then just making it a bit more complex, but a bit more accurate. This is the way I would prefer you handle the lookups when you work in Excel, because it will help you avoid a lot, a lot of errors going forward. 15. How To Identify Row & Column Indicators With Unique Values: identifying row and column indicators by having unique values. So I want to show you a formula that I use most of the time in Excel and that by itself it doesn't get you a specific result. But it's extremely, extremely powerful if you use it in combination with other formulas. So, basically, if you have a set of columns here which have unique values for your database or a set of rose here, you can use this formula to get their indicators relative to the database. And once you know their position so you know exactly the row and the call of the things you want to express, you can obviously find out the value, but for now, we're just going to focus on getting the row and the column indicators. And this function is called match Now. Notice how these values are repetitive. These column values are repetitive, so I'm not gonna be able to look up January in the database and expect to have a different result than looking up January from the budget because it's absolutely the same. So then that's why I actually built these coats here at the very top in the database sheet so that their unique because you need to have unique values for match to work. So what I'm doing now is I'm just going at the top and copying just the first quarter. So january through March 40 actuals and now for the budget. Notice how now I have unique coats. Okay, this is complete. Different then having this on the top. And now I can actually show you the match function. So I'm just gonna write match, and it's a very easy function. It's similar to be look up, but even simpler. So it asked me for a look up value where I'm going to select the unique coat that I have Pretty straightforward. I'm gonna lock this on the row even though my formula is not going to go down in this particular situation. But just to get used to the way you should be thinking about formulas in Excel. And then it asks me for a look up array and let me explain how I do this to look up array should only be a row or a call him. Okay, so I'm gonna select the whole row number one because I'm looking for these values, right? here and I wanted to tell me the number of the column and I selected from the number one here. And my reasoning for that is the following. If I selected it like this, so I just dragged it all the way to the end. I would select a one to that one. But what happens if I actually add columns after that? If, for whatever reason, I need to add 12 more columns in my database, then obviously they're not going to be included in the match function. And if I search for values, they're simply not going to be included in the range that were searching. So that's why, in order for these formulas to stay universally available and functioning, if you change the database, if you add to it, we're going to select the whole role, and we're gonna lock it in there with the absolute and relative references and then the final argument toe. Our function is the match type. And here you have three possible values zero minus one and 1 99% of the times. You're gonna use zero, which is an exact match. Now we close the brackets, we press enter and As you can see, it returns the value of the column. And here only the only thing that we have left is to copy that, copy it across and pasted as formulas. And as you can see, I have all the indicators for the columns in this table. So all of these values here occur in these particular months. However, these formulas, I'm going to show them to you another lesson. These air still the V lookups that we did in the previous lessons. So here's how we're gonna get the road now. We're still going to do a match, and we're going to look up the value that's to the left. So we're gonna look up the car, name the model. I'm gonna lock this on the column just to get used to the way of thinking. And now I need to input the look operate. I'm gonna go to the database. But I'm not going to select the row now because now we're looking for the role in this is so I'm going to select the first column. I select the first column, I lock it in with the referencing. Instead, the coma put a zero for exact match and closed the brackets. And now I have the Rose copy this pasted across its formulas. And there we go now have the indicators for absolutely everything that I want from this table because this value here, even though I found it with a V look up, this value is the value in the 10th row and in the third column from this database. So here, if I go to the 10th row and the third column, that's the exact value 4,322,000 that I have here. And the reason that is extremely powerful is that you can pinpoint to a certain value by building a system where you have these unique codes that indicate the row in the column and makes everything much easier for you to discover. The match function is extremely useful, and I just want you to be familiar with the way it works. So remember the unique values. Remember the way we select the road, the entire oh, or the entire column, and remember the way the function works and what it returns. And if you know that, then you're going to be very well prepared for the next few, like 16. Quick Hack #12: The VLookup & Match Combo: quick hack using V look up and match to pinpoint the column number. So a few lectures ago we used. If you look up to obtain these values here, then we used we look up and we used the column formula, which is still quite useful to determine the column that we want to look at. But in the previous lecture, we looked at the match function and how the match function can return the column number from a certain unique value. So if we have that, couldn't we use we look up and instead of using column, use the match formula to identify the column number? Of course you can. One thing to bear in mind is that the way we used match here is gonna return. Tow us the row number or the column number from the very beginning, because if you select a strong number, it will start counting 12345 etcetera. So if use this approach, make sure that your V look up Range also starts from the first column of that sheet. Otherwise, if it starts in the second column and your match function starts counting 123 etcetera from there then the column Labels are gonna be mismatched, and it's not gonna work. So this is simple case of simply taking this column syntax that we put in there deleting it , and you could put the match function straight away. Or you could just put this cell right here, lock it in on the road because we're gonna go down with the formula. So we only had locked on the road and press enter, and it's the same value. We can copy this formula and paste it across to the whole table, and everything stays exactly the same. Now as you can see, everything will reference the name here, so it's kind of similar to match. It would look for the name in the table, but it will look for the column. It will take the column indicator from the match find function rather than having the column function. And the reason this is even better than using column is that this references the unique values here. So rather than been pointing to column hate and inserting or deleting rows before after column age were actually pinpointing to the actual name. So we know pretty much for sure that we're nailing the column. So we know the column is good. We know that row is good here, so therefore the V look of formula is going to be extremely useful. 17. When To Use Nested VLookups: mess that v lookups. So in this lecture, I want to bring to you to your attention a particular situation where you might need to use a re look up as part of a V. Look up. Now that might sound complicated, especially because you just learned the formula. But let me just do it step by step and I hope you're gonna understand. But that's bogus, firstly on the practicality of this topic. So let's say I have this table right here, so I only have the serial number. I have attributed a serial number to all of my cars, all of my car models, and I want to get the actuals for this serial number. However, as you might have seen in the database, I have no serial number. I only have the model name, So if I go back here, maybe you have another table which has one common field. So in this case, are common. Field is the serial number, so we can use that in order to get the model name from this table and then in the same formula rather than complicated and do it in two separate formulas to separate cells in the same formula. We then go in the database and take this name and find out the actuals. So here's how that's gonna work. And I've done it here already, but I'm just gonna do it again, step by step, just so you understand. So I'm gonna do a V look up, and obviously I'm gonna look up the only value that I have here and I'm gonna set this on the column and the table array where I'm going to look at is this other table here where I have the same value. Notice how again? The serial number is the first column in my destination table. I'm going to fix this year because maybe I'm gonna want to drag the formula across. So I'm gonna fix the whole thing in with a relative referencing. And then I'm just going to use a simple example here you can use match. You can use column, but I'm just gonna put the number two here for ease of use and then a false for the last argument of the V. Look up and it's gonna return, as you might expect the model name. Now that I have the model name, this is not the end result. I actually want the revenue, but the model name is what I want to look for in the database. So what does that tell you? Pause the video for a second and think about it right now. What is this? Right here? Pause it, think about it and then play it again. I'm gonna tell you the answer. So this is what I want to look for in the database. So because that is what I want to look for in the database. If I write another V, look up here and put some more spaces to see it more clearly, this is the actual look up value. So this is what I want to look for in the database. So we're just gonna proceed as if we're dealing with a normal If you look up and simply go to the database, select the whole thing for the table array, lock it in there Column, index number. I'm not going to bother with it for this example. You know, by now how to use call him or how to use match to get that. I'm just gonna write number two, and then I'm gonna put a false. And as you can see right now, it gives me the value. But I have to be Look up. So if you look up within a V, look up to show for it and then, as always, we simply copy paste special formulas. We can play with the formatting, and that's that. So it's not something that's too difficult. And if you do it step by step, if you did the first we look up initially and then you do that, we look up in the Vehicle Cup than it really makes sense. I hope that was clear for you, and I hope you're gonna final of practical applications with these two formulas in. 18. Excel Hack #4: Extract Values From A Database With Row & Column Indicators: Excel hack extracting values out of a database when we know the row and the column. Guys, this is really exciting because this is one of the formulas that I use the most. It's actually think one of the most important lessons in this whole course. I want to take you step by step through high use this combination of formulas to get values from databases just by knowing these different, unique values. And I've alluded to this earlier when I showed you the match function. But I want to show you exactly from beginning to end how you can deal with such a table. And I'm sure you're gonna encounter countless examples in your day to day Excel work where this can be extremely practical. So let me just take it from the beginning. I'm on the index sheet and I have the same kind of table build here. Now, remember what I told you in the match lesson? We need to have unique values everywhere. Now I can make sure that these car models are unique. But January, February, March, January, February March, repeat themselves. So I'm gonna do the same exact thing that I did in the match lesson. So I'm going to copy the codes for the actuals. I'm gonna pace them here as values, and I'm gonna do the same for the budget. So I'm going to copy that. I'm gonna paste it here as values. And now what I'm gonna do is I'm gonna perform to match functions one for the columns and one for the roads. So I'm gonna do a match based on this code right here. So the actuals, I'm gonna fix this on the road. I'm gonna go in the database and look for this in the first row of the database. So I'm gonna look for the column number, and I'm gonna look for an exact match. And once I have this, I can copy it across as a formula, and I'm gonna have all the column numbers. So far, so good. Let's go to the Rose and identify the row numbers. So I'm going to look at the car model. I'm going to fix this on the column. I'm gonna go to the look up array by that I'm just going quickly through this because we've covered it before. I'm gonna fix the column in here. So it's gonna give me the row number, and I'm gonna ask for an exact match. And now I have both the row and the column indicators for my values. So what am I gonna do now? I'm gonna use a combination of formulas. Now, you could use these match formulas inside this main formula, or you can just reference them like this. But basically, the combination is match and index will actually index and match. So let's see, what index does that strike to insert index in here and see what it does? It returns of value or reference of the cell at the intersection of a particular row and column in the given range. So basically what index will do is if it sees call, um, wrote 10 and call them to it will go into database. It will go straight to row 10 and then to call them to, and they will grab this value from here, and it will put it in my table. That's how easy it is. 1,992,000 will be here. Similarly, um, calling 15 row 18. This intersection here, it's gonna determine it by going to row 18 here and Colin 15 here. I must admit, I have no idea which letter corresponds to the column Number 15. And that's exactly to my point. That's how much easier it is with these formulas. So right now, I'm just gonna do a simple index. And here is just a matter of playing with the references. I'm sure used to this by now, but I just want to make it even clear. So, Index, we're gonna indicate an array. And as always, I'm gonna go on the whole database here because maybe I'm gonna add columns or add Rose later. I'm gonna fix this in place. So as I drag the formula, I want the database to stay the same. I'm gonna start a coma. And then this is where you have to pay attention to the row number and the column numbers need to have the absolute references and check. So the row number will be this here. But we only wanted to go up and down, so we're gonna block it on the column. So we've blocked it on the column and then we want the row number, which is this, but we wanted to go right and left we don't want to go up or down, so we're gonna block it on the road. It's not have done that. We can close the formula, press enter, and there we go. The value start popping up. 764,464. Same exact value. I can actually copy this across as a formula and then just play with the formatting. As always, I like it to look very nice. So going around the formatting put it as a number and serve the separator. And that's that and noticed now how everything here is referencing the road and the column for all my values. That's how easy it is to build these tables once you have these unique values built into your database. So once you have these coats for the months that you know are unique and you know all of your product codes are unique as well, or you can create product codes for them or ceiling numbers, whatever it might be, that is a unique field for you. Use those as sort of as an anchor. As some pivot, you identify the rows and the columns, and then it's just like a matrix Bam and Bam! And it gives you these values. And if you're wondering, OK, but this doesn't really look too good. Well, you have two options. Either use the match function from here instead of these two arguments here. So we're gonna make a longer formula which might or might not be easier for you to maintain or simply take a lesson from one of our first sections, which is to go to the Data Tab Press group and then do the same here, press group, and then collapse everything. And look how nice that table looks. This is how I used to build all of my tables when I was working in finance. So everything looked like this. Everything was grouped and on grouped, and I always have it here. I mean, you might think it's more efficient to just put the match functions in there and just use one formula instead of two and have all these different cells here. But I think this approach is actually better, because every now and then, these names might change. That might be an error here, or there might be an error here, and you're going to detect it straight away. if you have the rosy, rather than seeing an error in these cells and then going to rose and seeing what's up. So this is how I recommend you build your tables, especially if you have large databases with a lot of instances, and you have unique fields that you can use to use this wonderful combination of formulas, which are index and match. 19. The Tidiest Way To Deal With Errors: the formula for dealing with errors in Excel. So here's a scenario for this lesson. I've created a new sheet, which is called errors and a new database, which is called database Errors and basically, in this database. I took away some of the values in here, and in this sheet I modify tone of the names of the cars here so that they wouldn't match with what was there before. So I have all of these end A's all of these errors. How can I deal with them now? You might say, OK, I just delete these cells that it looks nice. But what happens is if you delete that as soon as the database updates, if there's a value in that cell, if there's value correspondent that sell now, it will not be there anymore because you've deleted the formula. So what we need is something flexible that will still allow us to show the value if it's there but show on air or a blank or whatever we wanted to show. If it's not there and we're gonna uses a formula called if error, here's how it works. It's very, very easy. So we simply right If air here at the top press tab and open the brackets. Now this is the way it goes and asks you for the value that you want to sell to half in the value if there's an error. So the value that you want to sell to have is what we had here already. So that stays the same. And the value, if there's an error, will just put a blank. And as soon as I closed that, as you can see, it's simply blanks out everything in there, a copy, the formulas and everything else is blank. So that's how easy this formula is. Now, what if I want to ditch all the zeros as well and have it very, very clean? Then we just have to insert in function. And I'm sure you figured this out already. So we're going to go on this branch right here. We have the value because if there's an error, then there's nothing we want to do about it. We're already inserting some blanks, so I'm gonna put two rows in here now. What I'm gonna do is I'm gonna uncertain if before this index function and open it now, the logical test is still going to use this argument. We're still testing whether this formula right here gives us a zero or not. Now, if it gives us zero, then I'm going to go on the next row. And the value if it gives us a zero, shall be a black. So if the value of the Formula Zero I wanted to be blank and if it's not zero and I'm going to go again two rows below, then I want the same exact formula. I want the value right in there. So I'm just gonna copy everything here, all the way up to here, paste it here and then closed the f I've closed the F. So as you can see, I have the f here. It tests the condition. If this is zero, if it is your indeed, it blanks it. If it's not zero, it returns this value. But prior to that, it's checked if there's an error. So if there wasn't an error, it goes in this. If that I mentioned here, if there wasn't there, it simply blanks it out. From the beginning, I press enter, and then I copied the whole formula across, and you're going to see all of the zeros are going to disappear. And again, as usual, I just format everything as a number. Get rid of the decimal places, put the 1000 separator, and that's that. That's how easy it is to correct all of these things. And right now, if anything happens in my formula that, for example, these names are going to be OK rather than the leading the formulas as soon as thes values match or the database gets completed, then I'm gonna have these values back there. So as you can see, everything pops up. And then if I modify database, just look at a snapshot of this. If I put values here, you're going to see them pop up in that actual table. So that's the value that these two errors can have. As you can see, some of them got popular. Some of them are still blank. So that's the way it works. And that's how it will ensure accuracy for you and the best of both worlds, because if there's an error, it won't appear on there 20. Excel Hack #5: Looking Up Values With 2+ Unique Criteria: looking up values with two or more unique criteria. So as of now, we know how to look up a certain value in a table and return one of the columns here. We can either use V look up or we can use matching index. However, what happens when we don't have that table with that unique field, like a serial number or the model of a car that we know is unique in the whole database, and we have a database of 1000 12,000 entries, and we just want to find a combination of different unique factors? What happens then? Well, those farmers are not gonna be enough because, as I mentioned before, we look up and match will identify the first occurrence of a certain value. So if I look for comfort line, we look up and match are always going to stop here. They're never going to go and look at these options right here. And there's no way for me to, for example, identify the role where this comfort line and then ask if there's the engine size 1.2. I could do that, but it will never reach the one point for the 1.6 and the two. So what I want to do is make excel, check several criteria at the same time. And we're gonna use again a combination of index and match. But we're gonna expand the match a bit and we're gonna use an array formula. But 1st 1st things first. Let me just show you the match for Miss. They understand exactly what I mean. So here's how the syntax of the match formula is gonna work. I'm gonna write the match, and the look of value that I'm gonna ask for is a one while asking for one, because I'm gonna put a condition in the look up array which is gonna check SEF different values. So separate values two or three or or however many I want. And I wanted to return true or false so that a test is going to return a one for true and the zero for false. And I wanted to look up the true value. So the one that I'm gonna go to look up array and this is where it gets a bit tricky. So just bear with me for the look up array. I'm gonna open brackets and I'm gonna look at the first array that I want to check. So the 1st 1 is the range I'm just gonna selected. But I'm not going to stop there. I'm going to select the range and then I'm gonna press equals and I'm going to select what I have here. So these are the things that I'm looking for and I'm gonna close the brackets. So basically, what I did here was tell the match function, looked through these values here and identify Highline. Now, that's not enough because I want remember, I want one or more conditions to be met, so I'm gonna add an asterisk. And what asterisk will do? Is it will? It's like an ant, so it will check that both instances are correct. So then you guessed it. In the next instance, I'm going to select the engine size as an array, and I'm going to say equals to the engine size that I have here, and I have my two conditions, and I know they need to give me a what? I'm gonna press the coma here and put a match type of zero. So I want this to be an exact match. And once I pressed Enter. Look what happens. An error. We didn't really expect that. This is why I said we need to use it as an array formula. What is an array formula? Basically, if I insert the former like this, it's not gonna work. And you're gonna make this confusion. A lot of times you're gonna forget that you have to enter the as an array former. They're gonna think the formula is wrong. However, that's not the case. But for Excel to be ableto work in different arrays at the same time and juggle these two different variables, this needs to be entered as an array formula. That's just the way Excel works. And the way you do that is, you click on the formula and you press control shift and enter. So again you go on the formula and you press control, shift and enter. And look what happens when I do that. It tells me it's row number seven, and what it means by that is this one. Because I've entered the range on Lee through here, so it's actually rope number eight says you can see this is the high line with the engine size 1.4. So the matches actually returned the row that any its return and notice how you can tell it's an array formula because you have these brackets here at the very beginning, so you can see there's a bracket before the equal and right after that has been inserted. Which shows you that that is an array formula. Now, obviously, you need to take these value away from there, and we've learned that we can do that with the index function. So I'm just gonna use the Indus function. Make sure to select the same exact range that you selected in the formula so I could drag this up so that it returns. The seven that it returns is actually roll number seven. It's actually got a terrible number eight. And then for the index function, the array is gonna be the whole table. Okay, I'm just gonna lock that in. They're gonna press the coma, and then the row number is gonna be the match function. And that's that. The column number is the only other thing that I need to put in there. And it's column number five, as you can see which is the price? Close the brackets. Don't forget control, shift and enter. And this is how you get the price right there. It's actually the same formula that I built before here. Now, what I want to do in this formula here is to actually insert one more criteria. And maybe I want the engine type. And maybe I want the high line 1.4 TSC. So this is gonna be the same exact fear. Actually, Maybe I want to change it up. Maybe I want to show you how this will go to 1.6. So notice how If I put at 1.6, it's gonna return 33,770. Although I put here the engine type t aside, these two criteria are the same Highline 1.6. So it's going to stop at the T D. I, which is this one. But I wanted to go for the DSG, so I'm gonna write DSG here, But that's not gonna do the trick. It's still going to go on the first occurrence. So what I'm gonna do is I'm gonna insert another criteria in here, and I'm only gonna press the asterisk and insert this new range right here. So I'm gonna insert the engine type, and I'm going to say the engine type must be equal to DSG. Close this press control shift and enter. And as you can see right now, it goes on the DSG model because it shows me the same price 35,459. And you can use this reasoning for whatever you want. Now. Pay attention to this formula because it's a very because it's an array for me that's very complex, and you can use it for tens of thousands of occurrences. It's going to drain your computing power. But if you have a large database, 1000 2000 and you're stuck with this issue because I've been stuck with it before, Andi, I had to find a creative way to do it. Then it's a great way to deal with the issue, so it's not a format that you're going to use as frequently as you use the normal index and match functions. But keep it in mind because it's a very, very nifty trick, and it's gonna help you a lot if you encounter this type of situations 21. The Fastest Way To Sum Up Different Sheets: quick hack using three D, some in Excel So three D some is a very useful feature. It can save you off time. The where he it sounds more complicated than it actually is. Here's a scenario. Let's say you have different sheets where you keep your financial situations for different things. So you might have. For example, I used to work with the same exact templates for different countries, so I'd have one country here, like up to 20 countries, one of the other on separate sheets, and I'd have to do a total at the end of all of those values. Or maybe you have them per month, such as I try to show here. So I have four different models of cars and the revenues for the actuals budget and the actuals from one year before for the same month. And let's say I want to add all those up and put them in Q. One. So I have all these models. I have January, February and March, all of these different indicators, and I want to add them all Here. Let me show you how much time this takes by me adding up everything individually like he would do it initially. And I want you to think that these are not three examples were using this for simplicity. But let's say there's 20 such sheets now. I'm gonna do the 1st 1 Plus the 2nd 1 plus the 3rd 1 All right, That took some time, but it still did the trick, if there were 20 of them were of taking significantly longer. Not to mention the fact that I could have made mistakes. Now I copied everything across. Everything looks good. That's the long way to do it. Let's look at the easy way now for the easy way. Bear in mind that you need to have all of these sheets one after the other and you're gonna understand this right now. So I'm going to use this some function, and I'm going to go again on the first table right here and put January C three. What I'm gonna do is I'm gonna delete the C three and exclamation point I'm gonna put to full stops here. I'm gonna write March M A R. Which is the sheet name here. So basically, I'm summing up everything from January all the way to march and then I'm gonna add the exclamation point, which is Excel. It's Excel language, and then the C three. Let's see what happens now. I have the same exact value. I can copy it across, and I have the same exact values everywhere on Lee, it adds up three dimensionally, so through the sheets it adds up every such occurred every such sell. Now I hope you realize how much more efficient this is. And again think that if there were 10 15 20 sheets, this will be much, much faster than summing up everything individually. Not to mention that the formula itself, it's much, much Tidier. Rather than having 20 arguments here summed up, you only have a very short some function. So that's three D some. It's very useful. And if you encounter any scenario that is like this one that I presented to you here, I hope they're going to remember to use three D some accordingly. 22. The Easy Way To Transpose Tables With Formulas: transposing with formulas. So we've seen how it can transpose if pay special. But I want to show you uneven quick a way that you can do this with formulas And the advantage for the formulas is that whenever you modify something in the source table, the transposed table is going to shift its values as well. So here's how we're going to implement this. Simply select the area where you want this table to be transposed. Now, bear in mind, we're only gonna have four rows here because the columns are gonna become the Rose. Ever gonna have six columns Because of the way that table is built, we're going to shift the rose, the number of rows and the number of columns between them. So once you've selected your area, simply press equal, right, transposed, and then select your way that you want to transpose. That's how easy it is. Close the brackets and pay attention. Remember I said, it's an array formula. So what do we do when we have an array formula? We simply press control, shift and answer. And as soon as I press that you can see everything has been transposed, everything looks, looks exactly as it should. I can simply also shape this and formatted as I want one other thing. They I wanted to notice that as soon as I modify some values here, you're going to see them get modified in the actual table itself. So those are the advantages of transposing with formulas. And if you actually need to do this than just remember that instead of space special depending on the situation, depending on what you think is more efficient and mawr custom made for your particular scenario, then you can use the transpose function as well as the pace special function. 23. Quick Hack #13: Excel Goal-Seeker: quick hack Excel gold seeker go seekers of functionality That can be quite useful if you're trying to figure out some valuables. If you're making projections anything of the sort where you have a system implemented in the place, you have certain formulas that lead to an end result, and you want to see what you can input for a certain valuable to reach that end result. Maybe that was a bit vague, but I'm gonna explain to you really quickly. I'm going to show to you how it works and you're gonna understand better. So basically, what do I have here? I have all of these different model cars and some revenues for these cars. I also have a profit margin and then a calculator profit. So this is a very simple example. Now I have the formulas built in for the profit. So Excel knows how I calculate the prophet with the profit margin and the revenues, and I also have a total. So have some of these five cells now bear in mind. This one is zero. And the question that I'm asking myself is, how many revenues should I have for this particular car at this profit margin said I hit my target. Right now. My son is 889,000. I want to hit 1.2 million. So here's what I'm gonna do. I'm going to go on the date to tap at the very top and I'm gonna click. What if analysis and under what if analysis I'm gonna hit, Go seek And this is what I'm prompted to fill in. Now I want to set sell e nine. Look at the logic here. I want to set sell e nine to the value of unfortunately competence selling here. But I can feel it in manually 1,200,000. So that's my target by changing cell and here I indicate which sell I wanted to change. And obviously I wanted to put a figure for the revenues so that at that profit margin I get a certain profit that summed up with this whole column will give me my target, which is 1,200,000. Look what happens when I press this cell here and press OK cells are stars calculating and a fraction of a second. It reached a conclusion that I need \$4,987,781 or whatever it is to reach my goal. So that's how you can use gold seeker to fill in these values. Maybe have your custom made spreadsheets at home. Maybe you're trying to figure out some financial projections, whatever the case may be, if it's not too complicated. If you have all the formulas put in, an Excel can tell what the algorithm is. Then you can use go secret to tell what these values should be.