Excel Formulas & Functions: Master 25 Essential Skills

1. Welcome to the course: Want to work smarter in Excel? Are you tired of manually calculating, formatting, or searching for answers across spreadsheets? What if I told you that just 25 Excel formula skills could completely transform the way you work, saving you hours, improving your results, and making you look like an Excel pro, even if you're a beginner. I have helped learners like you master Excel to solve reward business problem and work more efficiently. In this course, I'm giving you the exact set of formulas and functions professionals use every day in offices, online jobs and freelance projects. This course is designed to be beginner friendly, fast paced, and 100% practical. You will learn the 25 El formula skills, which include some subtract, products divide, Min max average, mean median mode, standard deviation, large, small, count count a count blank, total, subtotal aggregates, if if and if, if error, if, count I, average if, some ifs, counts, average ifs, look up, index, match, index and match, X look up, proper, upper, lower, Learn left, right, mid, text split, and text join, text before text after, trim, concatenates, dates, sorts, filter, sequence, unique and distinct. Whether you're student or intern, business owner, office professional, freelancer, or even a complete El beginner, looking to finally understand formulas without confusion or stress. Discuss this for you. The best parts, you don't need any advanced El experience. We'll go step by step with hands on examples and downloadable resources you can follow along with. If you are ready to take control of your time, your skills, and your confidence in Exl, let's start mastering the most powerful Excel formulas today. I'll see you in the next lecture video. 2. Difference between formulas & Functions: This lecture video, I'll be teaching you formulas and functions. Yes. That is formulas versus functions. You might want to mistake formulas for functions or formula is also the same thing as function or formulize function, function is formula, formulize function, something like that. I'm going to clear that for you right now. You can see this. This rectangle is formula, while function is the circle inside. That is, fraction is a part of formula. That is just the minute. That's all. We don't know what formula is, right? Okay, Formula is what is a user defined calculation. You define the calculation, this is just like normal mathematics we know A plus B or one plus, two, three plus four, you know, A minus B, zero divided by this, something like just as an example. That is just a normal calculation we know, last formula for you. Okay? Now, let's talk about formula first. Firstly, what is formula? Formula is a user defined calculation. Just like we know in mathematics, A plus B, one plus two, 4/8, two minus seven, things like that are called what they are called formulas, and we have them here in itself. That's why we said function is also a formula. Function is also part of a formula. Now what is function outcome? So now, Formula as I've said, is a user defined is a calculation, say it's calculation, one plus two things like that. What are we talking about functions. A function is predefined. It is in built in EL. Formula is user defined. Why function is predefined? You tell it, A plus A plus B one, you define it like you define it yourself. But functions is already what is in built. You just say, is it cost to some. I'm going to show you the examples in EL right now, say, is it cost to some. Okay, you defined it. I want some to use some function, some function. Also the aggregate function. A use the lookup function. A use the trim function, the length function, the sequence, the sorts, the filter function, lot of functions we have in exorb. Okay. So now what we need to know and understand clearly is that going back, formula is the O, why function is part of formula. Now you can see when I have these branches here that is formula. In formula, you see formula. In formula, you see formula, and in formula you see function, it's very easy. That is function itself is a what is a formula. Everything there is a formula. That is the meaning. From this diagram, formula is everything. But function is part of formula, and that makes function a formula. Function is part of formula and therefore, it is a formula. Okay? Looking here actually, you can see formula. From formula we get formula, equals to cell A one plus B one. Then from formula, we get what we get functions, equals to sum. This sum here SUM is a function inbuilt. Is predefined. That I want the sum that has been predefined in Excel. Let's move into Excel now. So now we're in Excel. In the next lecture video, I'll be teaching you the basic functions and operators in Excel, arithmetic operators, statistical functions, and so on. But here is just a quick example. Let's say for example, let's say, for example, have five here have four here, for example. Now, what do we do? Now, for us to understand what actually we're talking about, okay, we're talking about just a normal formula. We're talking about formula function, function is a formula. Let's just see how it is. This is A one plus B one, for example. So coming over here, you can see actually just click here and press equals two. Before you actually bring about any formula or any function in El, you use equals two. It's very, very important. You can see what I actually taught you. When you select her hand press something here, it shows in the formula bar, very, very important. You can see the bases are very, very important. We have this. Then you see what you select here, which is what F six, you can see it here, you can see it here, plus you can see, very important. You can a plus. You can use a plus on your keyboard, very important. Plus, then you bring your mouse here, then select this. You can see very perfect. You can see exactly F six plus F seven, then you pause what, you press Enter. What do you have? You have nine. This is exactly what we have here. A one plus B one. Coming over here, we have A one plus B one. But here we use what we used F six plus W F seven. Let's come down where you're going to see. You can see F six plus F seven, and you can press F two just for you to look at it here, F six plus what? F seven. Very very perfect. Now for the function. We can use anywhere around here. That's why function is very powerful. You can just come here. You can just come down here and say what? Equals two. What function do you want to use? Subtraction division, multiplication, function you want to use, Concatenate some sequence, whatever you want to. Let me just out to some. S, you can see what is very, very important and interesting about function that you must know how to use function. When you actually putting in let me say A, A, for example, you can see, there are a lot of functions that start with A, you can see, very perfect. Let me say E, for example, if there is, yes, you can. You can see. Let me say J, for example, there is no function that start with J. Let me say K, just one function start with K. But what do I want an sum. You can actually search and start going down to look for no sum. But if you know how it is spelled, just say SUM. You can see, what do you do? This is why function is very useful. It will give you options. There's one for you to start cracking your bread actually. You want to some if, some ifs, some products and the s, or just some. So what do you do now? If you just press Enter, it's not going to work. So what do we do? Just come over here. You can see it's selling us calculation error, but it is still showing here. So what do we do? According to what I said, the shortcut is what F two. Very perfect. It's back. You can just backspace. You can see perfect. You just press on. When you press on, it's very important for us to know that this is not just an ordinary option, it is just an intelligence. Intelligence helps you to complete your functions. S like in Python and the s and HMR CSS, it helps you to complete your code. So this actually also helps you to complete your what function. It gives you idea, you have to complete things like that. The next thing we need to do we have three different ways of doing this. Okay? The first one of which is just the manner one, which I use less often. I don't really use it to that extent. It's for to press shift that you press was the opening back bracket, which is nine. Okay, you praise that. Then from there, the intelligence continue, which gives you more options, number one, number two, what do you want to like? I get to arithmetic operators, you will actually understand more about functions. That is the first one. The second one is the backspace, delay this. The second one is you can bring your Cusso over here where we have the sum, then double click twice. You can see, perfect. The third one is just use tab, and this is what I use. I have equals to SUM. Immediately I just put tab on my on my keyboard, and I'm going to Okay? So it's asking for number one number two. For you to be a guru for you to be very perfect in Exil. What do you do? Whenever you have a function, just try to understand the options it gives you. Just try to understand the intelligence it gives you. What the intelligence is trying to say it's telling you number one number two. Now that you go to number one number two, it's telling you what is the first number? What is the second number, and it continues like that. So this is the first number you can click this. Okay? Press on what? Comer. Then click this. Then come up, then click this. Okay, we're not going to use it because it's total. That is one way of doing this. Let me. That is one way of doing it. Then you press on sheets, then you close the bracket. Most times, yes, most times. You don't need to close the bracket because that is the last bracket at the back. Most times. Even if you don't close the bracket, just press on Enter. What does it give you? It gives you nine. When you click here, you can see this is nine, but the formula shows here because it's a formula. I've said this before. When I was teaching you containing the formula, the formula is here showing underneath underground, but the result is here. When we press on F two now, we see how it came about. This is the blue color. This is the blue F, F six, blue. Why F seven is worth red. You can see very plain. Function is very powerful. Apart from the way I did it, I can just come down again and say equals to sum press on tab. Then instead of me clicking this, then using comma I won't do that. I'll click Old and drag down. Very perfect because I can have hundreds of numbers. Clicking and clicking Cammer that will be a problem. I can click Oden drag down. That's what we call Range of numbers. Range of numbers. You can see how it is. Is selecting this and using comma. I'm not going to do that. Press on the scape it's gone. If you want to delete it without actually continue without actually to continue. Let me say I have different numbers now. For example, let me say I have four, seven, three, seven, this that's under. Just as an example, when we get to arithmetic operators, we're going to understand more. I can actually come over here. Just come down anywhere you want. Just come down, you just press on Okay, then press sum, then tab. Instead of me saying this, come at this, comma this, como this, come this, cod this, comma this. It's fine. It's equals to 28. Instead of doing that I'll just say it's equals to sum, then click or then drag down. That is a range. Then enter is the same thing or say equals to sum sum present tab, then you click come over here. You remember the shortcut I used then, present shifts and control, then words, then the dow arrow, then once, and, Enter. Very, very perfect. We have lots and lots of formulas in Exile. Formulas are very powerful and important. Formula is also a function because it is part of functions. So formula is the whole. Function is also a formula, formulize function is part of formula. That is the difference. That's the difference between the two. Everything is a formula and function is a part of a formula. So I will see where the real practical begins. 3. Download your Resource files: So guys, download this resource file in the lecture after this lecture from the link in the lecture. You double click and you file the resource file here. Exactly, this is the EOL file you're going to be using. All the EL file, everything. The practical files, everything is here, for the basic function of operators, logical functions if l, vertical function to the advanced one. Little validation, look up till the end. Also, we have what we have the diagrams here for you to use for you to be able to follow along in this course. I'll see you in the next lecture video after you've downloaded the resource files. 4. SUM, SUBTRACT, PRODUCT, DIVIDE: In this lecture video, I will be teaching you arithmetic operators, starting from this particular worksheets. Now these are just normal mathematical calculations, things like that. Very, very simple and very easy. So, we're going to come over here and say equals two, which is normally the first thing to do, equals two, okay? Now we want to calculate sum. Yes. The sum, subtract this multiplication, this division. The normal calculation we do, equals to, then bring your custo around the just clicked on this equals to this. Click on this. Then say plus, using the normal plus icon on the keyboard, then plus this. Perfect. Then press what? Then press Enter. Very, very perfect. 67. Good. Now, instead of me doing it again, equals to this and that. We don't do that in Exo no. We don't do that in Exle. There is something we call autofill. Yes. When we get to relative and absolute reference in El, we're going to understand more. But the thing is just that this is what we call autofill. Instead of you coming over here and saying equals to again, equals to this, sorry you start from here this plus this, you don't need to do that. You don't need to do that. Exls actually made it very easy. Delete this. What do you do just look at this little square around here. You can see this is the cus When you bring your cuss around there you can see it changes to a small arrow like, North, East, south and west. Just click on it, hold it and drag it down. Let's see. What happens? You can see. What is five plus 25 is what? 30. You can see what exactly do we need to understand here? It is called relative referencing. When we get to reference, we understand mom. But we just have to know that, this is used to what to autofill. Very important. Instead of just doing on this one, we can actually everything together at the same time, select here, clickO and drag it down and everything works out perfectly well. Now look at how perfect this is. Very, very perfect. But what we need to understand is when you click here, you can see B five, sell B five, plus cell C five. Look at this is B five, B five plus C five. If you want to see it exactly, when you select here, you can just press on F two, you have said before you can see. This color blue E is for B five. This color red e is for what is for C five. Then you can press what come down here and press here also, press press on F two, you can see. The same thing. Come down your present F two. Come down your present F two. Come down here present F two. That is using this particular this type of fill stuff actually just as an example. It understands, it continues. It knows this is exactly what you did you did. B five plus C five. When you drag it down like this, what happens, it follows and understands just like an intelligence. It knows what you want to do. Very, very perfect. Let's delete this. The easiest way to do instead of you having this and clicking like this, you don't need to have to take time. Sorry. That will take time. What other ways can you do it? Very, very simple. Just come over instead of clicking Odin and dragging down, just double click. It runs down itself. Automatically. Let's see. Double click. Very, very perfect. Very, very perfect. Good. So so subtraction, select here, press on equals two. Let's subtract B from A. Select here. I think this one. They all have the highest number it set from this. Let's G five and G eight. They have they are smaller than the H colon, but no problem, just minus this. Then press Enter. When you come over here, then double click, what happens, you can see, very, very perfect. It subtracts everything because you can subtract 25 plus from five. It's going to give you -20. Subtrasting 40 plus 40 from 24 is going to give you -16. That is why it's -16. The calculation is very, very perfect. Now, apart from that, we have multiplication. Come over here press equals to this, then the asteric the asteric actually instead of using times there is no time here. We use asteric then select this, then press Enter, then come over here, then what then double click and it goes down. Then for division also equals to this divided by the slash, divided by this, then press Enter. Come over here, then what come down here. Very very perfect. Very, very perfect. Let's come over here to operator two. Perfect. Here we are actually going to be using functions. We're going to be seeing how function is much more easier than using the normal arithmetic operator or just using it normally without using a function, function is almost the best. I'll just select the best. Now come over here and say equals to always equals to don't forget some Okay, we have some I, some Is, and the lights, but we're going to be using some, which is a normal just adding something. Then as I've said before, press on tab. Perfect. So now we ask me the first number and the second number. Let's ask him this is the first number or I have this as the first number anyone. I can actually select this first. Then press on what? When I pres on comma you can see this is black. And this is actually when I put my co it, it is showing me blue. It is telling me this is exactly what we are doing right now. This one is faded out. We're not actually doing this right now. But what makes it jump to number two is when you put a cammer let's put a cam, let's see, you can see, perfect. This becomes blored out and this one becomes what? This one becomes black, hugely black. You can see that this one there is no when I put a costo on this when I put a cost on this, it's still blue. But because I've actually dealt with this, I'm just dealing with this, and this one has not been dealt with, I can add a lot of numbers. It's not composite one or two, now I want to add a second one. This is the second Okay, then I, then I press on Enter. So it is not compulsory. Again, it is not compulsory. So I've written all the codes, it is not composory you put what? The last the last closed bracket, the last I mean, the closing bracket, it's not compulsory. But if I put it, there's no problem. If I want to go on further, I'll just say coma can see it comes to the third one. But what I'm not adding anything to it again, just delete this, then what? Then Enter. Very, very perfect. What do you do you double click. But instead of me coming down again and say, Okay, some tab, then present this then comma, it's taking long. We need to be efficient. Efficient, there's no time to waste. There's no time to waste for anything, delete this. Now coming over here, this preso present equals two, then some Present tab, then click Oden drag this like I did before. You click Oden drag to where exactly. If it's from here, let's take, for example, if it's from here, you click Oden drag to the end here or whatever I want to do. That's what we call range from a range of number, probably from a ten to Z 29. It's going to go like that. That's how it is. It makes it efficient and very very fast, equals to sum, present tab. Then click Oden drag this like this, then enter. Very, very perfect. When you click here, you will see E eight range E eights to F eight. That is what E eights to what F eight. Very, very perfect. You can come over here and what and double click and it goes down. Perfect. Very very perfect. Very, very perfect. You can see it is the same thing as what as this. Very the same thing as this. You can see very, very perfect. But some isn't some function is the best, no waste of time, no string attached, no waste of time. Okay? So for product, what we need to understand here is that for sum and multiplication, we have their functions. But for division and subtraction, we don't have their functions. We don't have their functions. So good. For products we have Equats too, product using multification, products. I've not even completed it. It's showing me already. Then I press tab. You can see very perfect. Click O then drag like this, then Enter. Multiplication of that, then double click. When you come down here to check, you can see 718 is the first one. 718 is the first one, the middle is 1,224. The bottom is 744. Sorry. So when you come down here, you can see, very, very perfect. Okay. Good. So that is it for that. The next one we are going to be looking at after that, okay? We've done the normal calculation then using the function. This is the third one in this particular lecture. Okay? So I want us to know something that there are a lot of ways you can actually you can actually achieve one thing in different ways. So you're going to choose the best one that is very fast for you. Shortcuts and, you know, you got to go, okay? So I want to add this like this. I want to add this like this, how to add this like this. Then also add it like this, add it like this, add it like this. I can just decide to say equals to some. I'm using it's bolding, no problem. Equals to sum, press on tab, then I'll do what. Instead of using the mouse, I want to use the keyboard now. I want to use the keyboard now. I use arrow, the left arrow, the right arrow, it's here. Then I press on shift and control normally as a shortcut. Then I press on the word, the left arrow, then it goes down straight. You can see it covers Asia. I don't want Asia, I only want the numbers. Just press on shift. Take your hand from any other key on the keyboard. Just press on shift only, hold it, then use the word, the right arrow in order for it to leave Asia. You can see. Very, very perfect. Let me repeat myself. I say equals to. Then what do you do you say sum, the tab, then your left arrow. Then you press on Control and shift together, then you use your left arrow just once. Then it actually covers Asia and you don't want it to happen like that. Press on shifts, you don't you don't use control here because we are highlighting. We've done exactly what we want to do with control and the shift. You need to highlights when you press on shifts, now you press on shift, then use the right arrow, then it only contains the number it leaves Asia. It is just the movement because shift is for highlighting. Then use the left and left, right, top, bottom arrow to to direct it. Very important. Now what do you do now? Very simple. You just press on what? Enter. Perfect. When you come over here and press on F two, you can see, that's exactly what we did. I can actually decide to double click this and it goes down. I actually is going to affect this. So you can control z it. Just click O and drag it down yourself. When I come over here to the middle, let's say, for example, F two, you can see it covers the place. You can see it covers here also. Instead of doing that, there is another way we can actually do it. Yes. When you select around here, the shortcut for sum automatically is what presents outward O equals to. Like when you say, the first thing we do here, for example, first we do is equals to e. This particular equals to, that's what I'm saying. Press on hold it then press equals to. You can see, very, very perfect. That's the shortcut. Then press on what's, Enter. It gives you exactly the sum, perfect. You can actually double click it down or just click Wooden drag. Same thing here also, click here. Press on out then press on Okay as usual, Enter, perfect. What do you do you click Oden drag to displace. Because if you double click here, it's not going to work the way exactly what you want to do. It's only works. When you autofill it comes from top to bottom. Click Oden drag like this. Perfect. Now, what can you do now? Just bring your cursor here. The Presson out equals two. It's going to actually add this. It's not going to add this one because most of the time things come up from the top to the bottom. Only if it doesn't see that, it would actually add from left to the right. Let's add it and let's see. You can see 1138, 1138. Good. Let me try to delete this away. Let me delete this let me delete this apply it out, equals to, you can see, it's going to recognize that that is the only thing there. You can see. Perfect. Let me control it. Perfect. That's nice. That's good. It depends on the one you want to use actually. So it depends. If it's coming from up, you can just use out equals two, but if you have two here, this one would actually be different. So you want to add for this and can delete this. I would just say as usual, equals to sum, then press on tab. Then up this present shifts, sorry, shift control. Sorry, the present shifts, reduce this, then enter, very perfect. I'll see you in the next lecture video. 5. MIN, MAX, AVG: So in this lecture video, we teaching you statistical functions. Yes, we have different type of statistical functions. We have max mean, average, and average is also mean, okay? Then we have the other ones like mode, standard devisionV simple, very very fast. I can say equals to MAX Max we're looking for the maximum and that max itself is what is the function name. Then what do you do? Press on tab. Then we up there, then you can use the left arrow, come like this, go up, then press control and ship then you get a picture. Perfect. I know the maximum right here is what 96. You can never see the maximum excel from 96 here, you can see 96. You can see something more than 96. It's not possible. Okay, so present. Okay. Same main present tab, then we have the numbers come over as usual. Okay? Enter. That is the minimum. That is the lowest thing you update. Which is 14, you can see there's nothing lower than 14, very very perfect. So now say equals to average. Okay, then what? We have average average if average if we're going to be using average. Okay? Perfect. So you can click Oden drag. Perfect. I use different ways, three ways right now, then present Enter. This is the average of all the numbers we have here, and that is also called mean. When we want to calculate mean there's not main equal equals to main open bracket, no, it's going to be average you're going to be using o 6. Mean, Median, Mode, Standard Deviation: Now come over here to statistical function. This also statistical function, but, come over here. Now we're going to say mean of the total sales, equals to, let's say mean can you see men there? No, you can't. You see what average. Then before you even finish typing, you can just press on tap, it's gone. Then use the up arrow go like this. The press on shift and cutter go like this. Then press on shifts come down, it's highlighting and I want to highlight total cells unhighlight numbers. Then press and enter. Very very perfect. We have equals to. We have median, median is there, press on what tab. Then click W and drag, use this. Enter. That is the median of the numbers there. We have equS to we have mode. There is a new type of mode. You can see this particular yellow stuff here. It's telling us actually it's just like you don't need to use it, just like it's disabled, but we don't need to use it. This is the one we use now, Mode. Like mode singular or something like that. Just press press on tab. Then this is it. ClickOn drag, Enter. Perfect. That is the mode, actually. When you say mode, don't forget that the one that occurred the most. The number that occurred the most, that is the mode. Okay? So for standard deviation, we are equals to ST D. That is it for standard deviation. Then we use the S. This is done. This is gone already. We don't use this anymore. We use the S. Perfect. So tab then come over here. Then enter. That's it for statistical functioning. I'll see you in the next lecture video. 7. LARGE, SMALL: In this lecture video, I'll be teaching you the large and small function in EO. Know the largest highest score, second sll, third score, probably in the class, lowest core, the second lowest, the third lowest. So this is the first lowest second lowest west, first, second highest told, something like that. Come over here and press equals to large. Yes, large. That is the function. Now the first thing he's asking us array. When you say array, array means range of numbers, is it 100-1 million? It depends. Click O then drag this down. That is the array. Then what? Present comma. Very perfect. Km is contart normally actually. But KM is position. So what position are we actually talking about? Talking about large, that is the largest, the highest. Talking about the smallest is what small. Talking about the highest is no, it is large. Okay? So K is position. What position? The first position. You can say I put one there, the press enter. Now I'm going to tell me what the first position is 96 is the first position. No other number is over 96. Okay. Now for the second one, equals to large? Perfect. Then comma, the second, which is two. The position is K, which is two, then press what? Okay, perfect. After 96, you won't see anything except for what SA four, 87, which is what? Which is the next number, which is this. Perfect. Okay? Good. So equals to large. These arrays of number, say array of numbers, then comma then position three, the third largest. Just the same thing as this also equals to small now we have the array of number, then press on comma. Then the lowest, the first lowest is what we're going to put one. This is the first that is the first lowest enter. The first word is 14. Just like we have when we're doing what minimax on average, equals to small. Come over here, comma, then second smallest is what that is. After the lowest itself is 14. The lowest next to it, the second lowest before 14 is 25 and the third lowest before 14 is what I? Let's see, comma then let's say third, 32, very perfect. That's how to low the highest number in this is the first, second, third, fourth, I mean, the highest. For the lowest, this is the lowest. This is the second to the lowest. This is the third to lowest. I'll see you in the next lecture video. 8. COUNT, COUNTA, COUNTBLANK: So in this lecture video right now, I'll be teaching you the count, count A and blank. Normally, you know, this is supposed to be the last one. Let me put it. Let me click and drag it to the last year. Yeah. Okay? I've arranged the worksheet. Perfect. Good. So now, now we have what we call count count A and count black. For you to count numbers for you to count words like alphabets, values. For you to count blank blank sheets, blank cells, there are different ways we do that. When you use numer count, this is number count because that's why it's four, one, two, three, four, because we have one, two, three, four here. We have one, two, three, four here. Okay? So equals two. Counts. You can see, count A, counts black, count I count if counts blah, blah, blah. I have never used some of this before. So just know the important ones. Then what's cap? Good. Now it is asking you for value one. Sorry, excuse me, value one. Value two, and on and on. Good. Now we're going to use the range. Now what is the range? Click here. I can click O and drag down. Let's see how long the range is. It's not along fine with along, no problem. Let's just keep on going there. That's the reason why you need to be using shots because 60 is so long. That's perfect. Then press Enter. What do we have? We have zero. What happened? When you're using the count function, it only counts numbers, not alphabet. That is it. It counts numbers, not alphabet. Now what we can do now is click here. Click ODN drag this like this and let's see the results. You can see, very very perfect. Now, what I'm trying to do here is when I came over here, I clicked OD and Drag This down. Okay, to the right, rather, to the right. It is what it is normally double clicked, normally double clicking would actually auto fill down, okay? But if it's to the feel right to the left, you can actually click then drag just like this. Okay? Perfect. So now what it means is that I have actually achieved this count for this particular place. When I press on f2c, it covers this side, right? So when I come over here, Oh sorry. When I click around here, when I click on F two, you can see it covers quantity because it's already know the intelligence in El knows that okay for you to have actually dragged and did autofill to the right, that is exactly what you're doing here actually. After doing this one, it will come over here to quantity, analyze everything you can see from top to bottom, exactly the same thing. Come over here to product and analyze everything from top to bottom, come over here to quantity and analyze everything from top to bottom. Very, very perfect. You can press on Escape. Good. Now what we need to understand from counts here is that all these are numbers for cell B, column B. They are all numbers, it was able to analyze it as 49. All the roles there are 49. If you come down, you would see what you see 51. That is 51 minus this two. Two, this is adding, this is empty. 51 minus u, sorry, 49 plus two, that's 51. This 51 um, yeah, that's, that's 51. So all the rules we have here for the product are what 49 and it's correct. For this particular second colon, it's correct. Now for us to count again, when you are counting here you're counting you counting the empty spaces, the blank spaces and the alphabet it's not counting black spaces and the alphabets. If we discounted the black spaces, it would have given us the amount of blank spaces there. Okay? So now we come over again to another quantity again. We have some empty spaces here. So if it count the empty spaces, it would have been 49 Okay. But it's 37. When we count this empty spaces, you would see when we add 37 to the empty spaces to give us 49. That is when you use a count function. When you use a count function, you can see the counts here you can see. When you use a count function, it counts numbers only. Even if there are alphabets there or even if there are blank spaces there, it won't count them to only count the D numbers. But for count A, count A would count numbers and alphabets, numbers and alphabets. Let's see. Equals to counts, use down arrow, then tab then click present Shift and Control, then down, then Enter. Come up. You can see Alphabet, 49. Click On drag like this. Alphabet 49. What is this? The numbers are also 49, perfect. But for count A, it's counted the alphabet, but not the blank spaces. Very very perfect. Then for count AA also, it's counted the numbers, but not for, not the blank spaces. Count is for only numbers. Count A is for numbers and alphabet. Why count blank is only for blank. Equals to counts so use a down arrow. It's going to count blank, then tab. Then what is the range? This is the ranged control and set. Then use the down arrow, then enter, very, very perfect. Then come over a cliqdn drag like this and see the black spaces here, no black space, zero, no blank space, zero. Number of black spaces here is 14. Perfect. Numbers of blank spaces E is 12. Perfect. When you at 37 plus 12, what does it give you, it gives you 49. When you have 35 plus 14 what does it give you, it gives you 49. Very very, very perfect. That's it for the count functions. I'll see you in the next lecture video and also don't forget the count is used for counting things which I've actually done for you to count things, just to count. I'll see you in the next lecture video. 9. TOTAL, SUBTOTAL, AGGREGATE: In this lecture video, I'll be teaching you total subtotal and aggregate. The main thing we actually after is subtotal and aggregate, totally just like saying the normal sum, this is two, total sum and two tho, we're actually looking at the normal sum. Okay, perfect. Press equals to sum, tab, then sum of everything here. Perfect. The sum of everything here is 43,860. You can decide to put a comma f no problem. Check away the decimal presses. Fine. That's not a problem, okay? So now we have, this is not an empty space if this is an empty space. It is not going to count the empty space, the count the normal numbers. But this is an incorrect an incorrect value. It's an arrow. You can see the error here, 1/0. It's an arrow. Okay? So now, when we come down here, select this when we select this and press equals to sum, press on tab, then tab. Sorry. Sorry. I have not chosen the range of numbers. This is a phrase enter. What happens? When you're using sum, you would see that when you calculating sum, normally calculates numbers, okay? But when you have an arrow there is cannot calculate the arrow. Same thing as equals two is easily equal to just come over here and what then autopil actually, you can see. When you click and press F two, it's here. When you click and press F two also, it calculates everything here, so the autofill is done the work? When you have arrows like this like this, because there sometimes might come across arrows, you might come across different things. What do you use? Whereby when you use the formula, it would even calculate the numbers and omits the arrow and you're good to go. You don't need to start saying, Oh, there's a mistake. Now, for totals, it's a no go area when you have arrows. So but for subtotal and aggregate, we use it for advanced function. Yeah. Let's say subtotal because normally, the one thing subtotal will do that some cannot do, okay, is for it to omit. Subtotal we actually ignore ding rules. That's why you see that we have here, we have the words, the SOT. Sorry, the filter. Okay? So you can decide to come over here and take away the filter, actually. Normally, I'll teach you the filter and the SOT, but just come over to data, Te it's clicked, just take it away. You can see. Let's say it's like this. I want to SOTs, actually, I can click around, sorry. Let me just come over here, click Wd and drag like this. Sorry, click W and track like this. That's a mistake. Then press on what filter. You can be able to filter whatever you want to filter. When you say filter, that is whatever you filter, let's say, for example, this is everything here, press on this arrow. Everything is selected, the first you check everything, then say, Oh, I only watch headphones. I only want keyboard. Then I press Okay. You filter out those other ones, then you actually leave headphones and keyboard. So these are the main things you need. Phone or keyboard, like you filter them like this is exactly what I need. I need headphones and keyboard. That's usefulness of filter. That is the other ones that are not necessary. These are the only things that are necessary, okay? Just as an example. So just back. Example, Boy. Good. So now for the subtotal, before I begin actually, the total we know the sum, actually, perfect. But for the subtotal and aggregate, it is an advanced function. Yes, an advanced function. What we need to understand is the aggregate is the best of the two. Yes, it is the best of the two, and you know the reason why we get there. Okay? Now, most importantly, most importantly, now, when we're talking about subtotal, use equals two sub total. Okay. Perfect. Then we have different types of functions like average counts and the likes. Okay? So now, when you want to use the average when you want to use the subtotal. Subtotal is majorly used when, as I've said, aggregate can do the work of subtotal. It will do it perfectly well. It's the perfect dual. So you should always use aggregate when you have an issue, errors, r value arrows like error values, you have hidden rows. Just use aggregate straightforward. Subtotal is just in between. Okay? But subtotal is actually majorly and most used. It's majorly and mostly used when you are Eating rose, like when you filter. When you filter something, Eating rose. That's why you can actually see that the filter is actually applied here. And when I teach us filter and salt, don't worry. Now we have some nine sum, number nine sum, and also some for 109, okay? So to understand more about the number nine sum and also 19 sum, they are different. For you have them there, they are different actually. For the number nine sum, which is this, okay, it helps to include manually ding rows but exclude filtered out rows. Okay? So that is when you're using subtotal, there is a subtotal. When you're adding, it adds together, the manually ding rows, but does not add the filter out rows. But when you're using one or nine sum, it adds the sorry, it's exclude, it excludes both manually ding and filtered out rows. It's quite complicated. But that's why you need to use aggregate. That's why Crosof themselves actually put aggregator to make it easy. Even when you have any type of arrow, just use aggregate. If you feel like you have any type of error, just use aggregate, you get the right answer. You don't have any arrow. Okay? Good. Let's use this. This is the best we can actually use 19, sorry, one oh nine, then Cam then Rf is what Ref one is all the available, what's it called? All the available numbers. The press on Enter. Very pop. You can see, it's the same thing. Yes, it's the same thing. Let me give it Cammer. Take away the The same thing. Fine. Very very perfect. But when I come over here, let's say, for example, and I praise on smartphone, this filter, I want to remove the smartphone from the filter ones. The filter ones are the one that are going to show, the one I check is the one that is not going to show. When I press on Okay, what happens? You can see that some would actually still calculate everything. Let's praise on do. The normal calculation for everything is 43,000 eth 60. But now I don't want smartphone to be there. Probably I don't want smartphone. I want some other stops. You can see one, two, three, Rufo is out. Afos for smartphone, right? So actually still has the same thing. Still has the same number. This is incorrect. But for subtotal, it gives me that O for the only things showing here that is we're not calculating anything eading. We're only calculating what is visible here. When I say I take away, let's say, I want to take away, um, headphones, keyboard, laptop and press Okay, don't forget it's for 3,860. That is for everything there. You can see. It's still the same thing for some for 3,860. But for Subtotal, it would only calculate what is visible here. Do you understand? That is the main thing subtotal is used for eating rows. It doesn't calculate this eating rows, to calculate for you only thing that is here. But aggregates will do the better for you. Let me back. Okay. Let me just redo. Just only one, one, two, three, four, row four, actually. When I come over here, click over let me say this equals to subtotal, subtotal, double click. Then I use the last one. Then comma I use range of numbers. Don't forget the what? These as error values is not going to work in subtotal, never. Then press. You can see, very very perfect. Never. Let's try this and see. Never, never. But it's going to work in aggregate. That is why you need to be using the aggregates. Every time we know probably there is an hidden row filtered. There are filtered stuffs there and you want to add them or they are arrow arrows and you want to add or do some other calculations. Put them equals to aggregate. Aggregate. You can see tab. We have average counts. Whatever it is actually you want to use, just come over a double click sum comer. You can see. For this, we have much more options. It's now asking me that what exactly do you want to ignore? If you say, I want to ignore nested subtotal and aggregate functions, fine. Just press on zero. I want to ignore Eden rows, nested, keep on. If you want to say ignore nothing, go to ignore nothing and give you errors. Ignore ding rows only. Just like subtotal. Ignore error values only. Fine. Just ignore Eden rows and values fine. But the best way you can always choose. Don't bother choosing it. Just choose three. Ignore Eden rows, error values, Nestle subtotal and it houses everything. What is the essence of using all these alluns where you have everything? Let's double click on this. Then comer. Then what is the option? This is the option. Now what apples, Enter. That is the normal number. Come back here and see, let's see what apples. Aggregate. Let's say sum, double click, comma, three, double click. Come you know the numbers, the start double clicking, press it. We have everything here, Enter. You can see it already calculates the numbers and omits the arrow, it doesn't need the arrow. Equals to aggregate. Let's say nine. Know number, just say nine, comma, then three, comma, then what? Then come over here. And you got to go. Aggregate is number one, no errors in aggregate, not at all. Not at all. So is just for number of things. But you know there will be an arrow. There are some errors in some things missing numbers, labels, sorry, missing numbers, the row is this, there are probably some strange stuff just use aggregate and just go straight to the point, use aggregate, use number three there and you got to go. Aggregate is the best. If you have errors or some other things in a particular table or some other arrows or something like that or colon or whatever it is. Okay? So that is it for subtotal and aggregate. I'll see you in the next lecture video. 10. IF Function: Yes. In this lecture video, teaching you logical functions. When you say logic, you would have heard about that variable. Logical function in Excel are functions that return through or for, just tell the Oh based on a given condition, it returns true or based on a given condition that is it's just like you're giving it an instruction, saying if this is this, then true. If not, false. If this is this, then reply this or give me this. Then if not, give me the second thing. It is logical. So how do we go about it? Now, this is just like, let's say, we want to give bonus to our employee. So now, eligible for bonus. We want to say, okay, if the customer rating is greater than 70. Let's say equals to if then I use my tab. You can see the intelligence logical test value if true, value if false. The first thing we have to now is a logical test. Logical test if this is this, if When I say if this particular cell is just a representation of everything, but this exactly actually, because I had to choose when I need to select one before I can be able to apply it using autofill. If this C three. The customer rates in this particular cell is greater than 70 is greater than 70. That is the logical test and that is all. Then we want to say value of two should be what's eligible and if not, it should be what's not eligible. Very simple. Present come then you say eligible. Mind you, if you're writing numbers in EO, writing numbers like this, you don't use double quotation because it is not a string. But for strings like letters, alphabets, or combination of alphabets and numbers, they are strings. We say what's eligible. That is the posing is what's gib close quotation. That is value if true. You can see that this was black before. But when I put a comma, it's now gray. So now I'm doing this actually. This is why I am pre that's why it is blue, very much blue than this with boldness. Then when I put comma, it moves towards value if force. I decide to put anything there. But I want something to be there. Okay? That's what sorry, not eligible. Not eligible. Then close quotation, the press Enter. You can see, not eligible. Should I start writing this one by one. You don't need to do that, double click, autofill and it goes down so that you can see this. Because this is 55, not eligible. This is 80 eligible. Nice. That is it. This is computer. Is it understands whatever you're saying. Sorry, skip. You see, not eligible 68 eligible, not eligible 70 because you said, greater than 70. Okay? We can come over here and say what? Come over here, come back here. You come back here. Don't do it here. You do it here in the first place. Greater than or equals to 70. So you see that this 70, this one will become eligible or equals to the press Enter. What do you do? Use autoflG back down again, you can see, now 70 eligible eligible. The two. But before when you undo, you can see before it was what? Not eligible, not eligible. Comp is, you know, you talking to the compat, do this, and it does it automation. Do this. Logical, perfect. That's the beginning of the If statement. We can actually have I and statement together actually, they are all logical. I'll see you in the next lecture. 11. IF, AND: So now in this lecture video you can see we're in the second worksheets. This is the first worksheet. This is the second worksheet. Now we'll be using the I and the function together. So now let's start from here as usual. You say I, you say if first, okay? I Oh, sorry. Equals to I. Okay? We're going to put it in Lolicar statement immediately, yes, but you just put CF plus this or equals to whatever. Because you want to use the function, you put the immediately. Yes, you need to put the immediately. That's how it works. You want to use the, you want two statements to go along together. The reason why you're using, using, which we are going to use later, is that when you use and, you mean two different types of conditions must be met. Otherwise, the answer will be false. We don't want it. It's cannot be true. But if you use all, that is, it is either it fulfills one condition out of those two condition. It might not be two, it might be three, it might be four might be five. If it fulfills one, it's okay for all. But for, it has to fulfill everything. That is what is very simple. Understand it like that. That's how it is. Now, instead of saying, let's say, sell C four plus this c, you don't need to do that now. Just say, you need to put end, you can see, you can see, press on what tab. I actually show you can say logical one, logical two. Perfect. Now, what is the first logical statement? Then you put comma and you put second logical statement. I want to say firstly, I want to say this is it. This is customer written. This attendance rating. Before we only add customarty now attendance rating. You customer rating must be at least it must be greater than equals to 70, greater than equals to 70, and also your attendance rating must be greater than equals to 4.5 Nice logic. It's quite summer, but it's simple. Just put it and two, it must be two must be together. The two must be correct it's not zero, you don't have any bonus, you have zero bonus. You have zero bonus. Zero bonus. Good. The logic one is this, which is BC, the intelligence is actually covering, but you know it is ABCD. C three, which is what? T cell is greater than or equals to 70. You somehow put in double quotation because it is a number, not a string. Now you put Cammer Okay, Logical two. You can see we have got three and they like you anyone you want, logical two and also the words D D three, D three, click on D three. Is greater than equals to 4.5. Then if you put cama again, the logic continues, but you are done with that particular logic, which is that logic. Also, the first logic, the logic for if statement, then you use the closing bracket, open bracket, using closing bracket. Then what happens, you can see it goes back to the I statement that the logical test you actually want exactly is this end. You fulfilled it already. It's very simple. Even if you can't memorize it or cram it or something like that. Don't cram, don't memorize com it to memory. When you want to use the, you want to use a particular logical function. You want to use and just immediately when you use the if you open the bracket, put the straight. When you're done with the you close the bracket, then it takes you back to what takes you back to the If statement where you now put it through or the force. Even is the same thing. Wha is and O, just go straight to the point where we've done before. Now put Cammer, you can see now. Let me erase this cammer. You can see still logical test before. When I put Cammer, you can see it now what value if true. So the value if will be what? Then I would say bonus. That is you have Whoa, you have a bonus, hair thumbs up. Come then come on value if f was zero. So pres on Enter. You can see perfect. Now it's telling me we found a typo in your formula and tried to correct it. Even at press, yes, we would know what rely upon. Let me just press, no. You can see it has actually corrected it. Then press on no and press on. Now the thing is that because there are a lot of formulas inside and smile actually, it's not difficult for Excel but Excel actually wants me to do what to close that bracket. I need to close it. If it's normal formula or just one formula, you close it, you don't close it just press and enter, it works, but there's one formula inside already and so you need to close it. When I close apples, press on Enter, then perfect, zero. So what do I do you double click and fuse it down. No bonus, no bonus, then bonus. Reason is that this is what more than 70. This is more than 4.5. This is 4.8, there is no other 4.5 or whatever, you can see, there is nothing like every one of them gets zero bonus. Why real marketing department actually as a bonus. When we use the O, when we use the I and the O, you would see because it's going to say it's not composer, you actually have this, but you should have this. So to say you should have this, but it's not comps you have this. But for the end, it's very strict and it's very strict. You must have everything, everything composer, everything. So that is it for the end. Actually, practice more until you get it up explain. I'll see you in the next lecture video. 12. IF, OR: Now this lecture video just like we did the last time where we're using, very simple. Perfect. Just say equals two, I tab as usual, then tab. Perfect, gslogyqanology, two then. We can say, let's say, the use the first. Perfect. Let's say customer rating. Let's start from customer rating as usual, actually. C, C three. Fine. No problem. Let's say, Click here. Okay, greater than or equals two, 70. Comma good. Then D three, greater than or equals to 4.5. Then comma, you can see to go to logit log four and we don't want that, just pull, close the bracket. Then it's moves straight back to the I. Now what we do we need done? Put comma value I two, then say bonus. Okay. Then comma Sorry. Then comma, then what value if force then say zero, fine, then close it, then press on. Then press Enter, perfect, you can see, good. Then click Od and just double click, you can see, perfect. You can see this one is more than 70, but this one is not more than 4.5 because it is going to give us bonus because we're using the O. Unlike when we were using the and nothing. The darn only this as it. If you are using the O, the one we use the for is going to work also because it has the two find doesn't a problem. We might actually feel buyers, but we don't care. We've said Oh, all this actually, let's say, this one has 68, not even 70 but this one has 4.7, so that's why it's working for it. That is the O and that is the art before what you've done before. Very simple. It's exactly the same method. But because we're using the is going to change the function and the art is different. I'll see you in the next lecture video. 13. IFERROR: In this lecture video, I'll be teaching you I error function. Yes, there's a function called if error in Exo. That is, if there would be an error, you would actually put it there yourself that yes, I want this to be here. I want this to say no bonus. I want this to say not found. I don't want a particular type of no error, exon error error stuff. I want something myself. I would just say equals to, let's say, this times this present enter. Okay. Then down. So we have number, we don't have a number, is definitely going to give us something that is wrong normally. But if you don't want to see this Excel arrow stuff. What do we do? We use I arrow. We put this formula. We put it inside the If arrow function. It's very, very simple. Put it inside the If arrow function. Now, instead of having this arrow stuff, we'll come over here to the beginning of the equals to. If arrow, this would actually come in and the I arrow you can see, press on what tab. That is the value. This is the value you can see value. You come over here and press Cammer. Value if error. This is the value you want to show if there is an arrow to actually prompt, arrow. Let's wait and see. I'll put double quotation. I'll say arrow, press and enter, then double click down. You can see. I'm the one detecting for it. If there is any error, you say arrow or say, I can change it and say, not found. Sorry. No found. Enter. Double click, go down. Let me increase this. You can see not found, no found. If I change this to five and press Enter, give me the normal number, the not found is gone. Because here it wasn't the number before. When I say five, you can see, it's all gone. It's all gone. It's very same. That's how to use iferror. Whenever you have a function or a formula, however long it is, when you want to use it just come over here to the front here, you would then put error I if error exactly. If you want to use if error, if error exactly, sorry, would be the first formula here. Whatever formula you have before would actually be in what's in the bracket in the parenthesis of if arrow. If error will be the main one, the when one are the fault, and the other ones would actually be inside the bracket of iferror. That's how to use Il. It's very, very simple. I'll see you in the next lecture video. 14. IFS Function: In this lecture video, I'll be teaching you if Tan. We have ifs, some I I count I and the likes. When we said I in the past section. I is that, if this is this, if this is that we joined and things, three things they must be together. They must be correct. They must all be true together. But for the function, is that even if it's ten or 100, if one is correct, fine. But for A, even if it's 1 million, all the 1 million must be correct. I is just I. But now we are seeing if statements, the S, very important. Now, when we did this the last time, we give them bonus, we said eligible, not eligible. We're quite biased. Yes, because you have a customty of 92 and we're going to give you, let me say $200, just like someone that has a tin customer retin of 43 is buyers. You've been bias, is not good, it's partiality. So we want to make it right. W to correct it and make it right. We don't want to be bias anymore. Good. Now we come over here. We not go to you be using the I function to be the Is function. Yes. Because it's going to be multiple. Now I'm going to start with Is. That is the formula. Sorry, equals to Is. This is the Is double click. Now we have logical test one, value if true one, that is not like value if falls here. It's very, very simple. The logical test is if this, which is C three, is greater than or equal greater than or equals to 90. Because once you use 90 as equal to or greater than 90, you would actually have $1,000. Comma, we've done the logical test, then if true, that is you're going to have $1,000. Then comma the second logical test. That is if this C three itself again, is greater than or equals to what or equals to 80, greater than or equals to 80 comer what should happen? Should be given 800 pounds. That is the value if true. Then, logical test three. If C three again is greater than equals to what or equals to 70, then what should be the value if true? The value if should be what? 500. Come up. Analogy could test again. We don't want to be bias, we want to be like, you know, at least good enough. Now, what are we going to do? Now, if C three is less less than 70, if there is anything below 70 right now, would be giving zero bonus come if value is true, zero. Very perfect. Then we close we close the bracketed Enter, very, very perfect. Don't forget, we're going to use what relative referencing. That means we're going to what double click and it goes down. Perfect. Good. Now we are 55. John Doe has 55 customer rating, and the bonus is zero. Sera at 80, the bonus is what 800 reason because we said 80, if this is AC, or greater than 80. The person will be having 800, this is 68. This is less than 70. This is 70 itself. I person will be having 500. That is, if it is equals to greater than 70, it'll be what it will be 500. You can see from here, 500, you can see from that's why. That is why this is 500. This is 98, greater than 70, greater than 90, sorry, 65 67, less than 70, and it goes down and down and down, perfect. That is how to do it. We can keep on going that if C three. I use C three. Okay, I use C three because I'm going to use the relative reference in here by doing this. It's going to recognize it. Okay? Good. So we can say if C three is greater than or equals to 50, the bonus should be $200. We can do that and continue doing that, but that's where I prefer to stop. So that's perfect. So the next one is some if and some if. I'll see you in the next lecture video. 15. SUMIF, SUMIFS: So in this lecture video, I'll be teaching you some if and some ifs. It's very, very simple. You know, when we sum, we actually sum just one thing, one single thing entirely. But here we want to sum. You got this is a product itself, the number of products. I want to check how many you got high here, and all the yogurt here want sum everything. It's different from some, some just some whatever you have, Sos everything you have here. But we want to choose just yogurt, only got and not just one got, not just one you got. We have countless of an we have another one, so we have me if it's only we have three. We want to sum every now equals to sum I open bracket or just press on tap as usual. Then what do you do? That's why you need to understand the intelligence. The intelligence is very important. It's asking for the range range of what? Range of that product. It's only going to select. It is only going to select Yugots. Accepted. Only going to select got. But it is asking for the range where that Yugot is. This is the range. You click here. Shift and control, use the down arrow, then you select everything down as usual. Then com criteria. What is when you say, what is the criteria for something? What is that vowel? What's that thing exactly you're looking for? What is the criteria? The criteria is what this you got. Okay. I thecide to say, I will write UGT, Transmosiite it exactly the way it is in the colon wherever you're finding it and put it in quotation because string. Alphabets are string. Numbers are integers, don't you don't need a quotation. But here, you can actually put it. This is the criteria then comma some range. The sum range actually, what you're actually trying to sum, because we are not just summing only one thing, one yogurt. We are summing all the yogurts we can find here. We're going to sum it. We're not counting, we are summing it. We're actually looking at the cells. We click here, Shift and Control, then down, very very perfect. Then what do we do? We press on what? That is the last one. You can actually close the bracket if you feel like then press on Enter. Very, very perfect. You can see. When I come over here, you can press F two. You can see, look at the blue for product, look at the red for sales. It's very important. This intelligence is very important. I always emphasize. Range means the range of yugats the product exactly, the range of where exactly it is from where to where does it cover. Then the criteria is the Yugot itself. Then the sum range where you want to sum. This is exactly where you want to sum. Very, very important. Another thing we can do is instead of using Yugot Instead of me writing Yugott. Let's check on carrot also. You see, equals to sum if oh, sorry. You can see, press on tap. Range, this is the range carrot or carrots, This is the range. Now this is what I'm trying to say. Then comer now criteria. Instead of me doing like this and writing carrots, which would actually work out, carrots, Who actually work out. I'm not going to do that now. I have the name carrots here. I'm going to click here. You see? Because the name it has been written there it will recognize that cell GNI as carrots, instead of me using the quotation. That's another way of doing it very simple. Then come over here, then Enter. Very, very perfect. Now when you sum all the carrots here. Now we have yogurt, we have zero. Now we're going down, we have another Yugot we have 11.94 pounds dollars. Sorry, I'm used to pounds. Sorry. When you have got here, $199.96, then we have this. When we have this and we have this, when we sum them together, normally manually, we actually get this. When we sum all the carrots together, we would actually get this also. That is the essay instead of you using the calculator and doing everything, it's a waste of time. This is the best way, okay? There's some if. Now we have some ifs. So if, just like we did if before, a lot of things. But here, Is not being biased. Now we're talking about some if we have only for yugats. We having everything about Yogurt, all details about yogurt. Also concerning carrots also, we're adding everything, all the sales we made about carrots only, Yugots only. But here, it is very simple. We have two criteria. We have country, we have product. So we're going to choose the country, we want to chose a product. What country exactly do we want? What products do we want exactly from that particular country, we want from Canada country Canada, and press Enter. What do you want from Canada? This is Canada. Okay? What product do we want from Canada we want to calculate? Let's look about let's say body wash, from Canada, let's do Canada Canada, body wash. Canada, body wash, okay. Very, very perfect. Good. Okay. Perfect. If we have another product from Canada, it won't calculate it because it is not body wash, okay? But we are going to be using body wash, okay? Body. Wash, enter. Perfect. Okay. So now what are we going to do? It's very simple. We have two criterias like criteria one criteria two. Unlike E one, we have only one criteria, go on. You got one criteria, just carrots. But here we have two criteria together. So we're going to be using what some Is with So you're going to be able to add lots of criteria, lots of criteria. So Is, perfect. The sum range, this is asking for the range, the sum range before. But for the sum if, then it was asking for the sum rage at the end. But for sum Is is asking for the sum rage at the beginning. You're going to click as usual, come down here. Perfect. Then come on. Criteria range one. That is asking for the criteria range one. The first criteria. The first criteria is what? The first criteria is country. Look at country? Product. First criteria range. Sorry. First criteria range one, that's criteria range one. The criteria range one is country, then down highlight. Then com What is that criteria? Canada. Comma criteria range two. What's the criteria two? Product. Light it down, then comma. What is that criteria itself, body wash? We can keep on going by saying comma, criterion three, then criteria three. Keep on going criteria four, but we're not going to do that, we're okay with it too. Perfect. Then press Enter. You enter too few arguments, sorry, W is the mistake? We have this that's a mistake. Coma, then we have body wash for the criteria to body wash. And we press Enter, very, very perfect. Is there a problem? Yes, Canada was not spelled very well. I need to spell it very well. Canada then Enter, you can see, very, very perfect. Let me explain again. It's very simple. Here we have Canada. The country we're looking for is Canada and all the products from Canada. They are different products from Canada, but only word I only want body wash. I put the body wash. Then come over here. I want to some. I want to get that only products and from which country from this only country called Canada. I'm going to be using some ifs. Okay? Good. So Double click. Now we have sorry, escape. Now this is it. We have sum range. The sum range is this blue, blue, you can see blue sum range, then coma we have criteria range one. Criteria range is different from criteria. Criteria range is the range where you find that criteria from, and it's country. What is that criteria itself? The criteria, sorry, the criteria itself. The criteria itself is what Canada. The input, you click on Canada, which is what IHL, H. Then next one, we have criterion two, which is what product. Criteria two, which is what product. Then what is that criteria itself is I nine, which is what body wash. If we have another criteria range and criteria, we put it there, but we only have two. That is actually how to do that. I'll see you in the next lecture video. 16. COUNTIF, COUNTIFS: So in this lecture video, we teach you, I mean, counts even counts ifs, just like we've done before, okay? So just come over equals two. Oh, sorry. Count I? What is the range of exactly what you want to count? This is where it is. Then as usual, then coma What is the criteria? The criteria is you got, just click here. This is the criteria, present enter. How many you guys do we have? We have only six. We just counting one, two, three, four or five, six, perfect. That's all. That's all. We coming over here also equals to count I. What are we counting? This is the range. The coma. Okay. What is it? Carrots. Perfect. Only two carrots. Perfect. So two counts Is very fast. No waste of time. Okay. So for counts Is, you say, equals to counts Is. Okay. So now, what is the criteria range? This is the criteria range exactly. Counts, right? Criteria range one. Okay? And what is the criteria? Gonna be there. Okay? So criteria range. This is the criteria in the country. Come down. Comma. Okay. What is the criteria from that country? I want from Austria. Oh sorry. Austria Quota. That's what I want Austria. We have Austria. Let me come down and let's see if there's Austria again. And then from Austria, we have Apple, then comma. Yes. Okay. That's the first criteria criteria one, the first criteria criteria range one criteria one. For the criteria two, criteria range two, this is the product exactly, comma what is that criteria to itself from that criteria range? We have Apple. Sorry. Apple. Then we press Enter, close quotation, press Enter. What we did was count if I multiple, you have to know that, from what particular range this country Austria, then what product exactly? What product? Apple product? It might have different type of products, but we are actually focused on only Apple product from Austria, and that is the answer we will get for. Perfect. That is concerning counts if, very simple, just like some if some ifs then move to average if and average ifs. I'll see you the next lecture. 17. AVERAGEIF, ABERAGEIFS: So for average I, it's very simple, just like counts if some I yeah. It's very simple. Equals to average I, okay? Okay, perfect. So now we are looking at what we're looking at the range criteria. Then if we want, we can actually look for the average rang, but we are going to be looking for the average range also. So looking for the range. This is the range itself. Comma. What is the criteria for the range? Is you got the criteria is you got, comma. Then the average range, that is where that's like some range count range, average where we actually find it. This is where we're going to find. Thats is the average range. Then here, then enter very, very perfect. 52.3. Okay, very, very perfect. We have this also for carrots, equals to average average if. This is the range, comma. This is the criteria, comma this is the average range. Perfect. That's for carrots and we'll go to go. Nice one. For average if coming over here, equals to average ifs the average range is the first thing I go to select. This is it. Just like the summary you selected first then comma come back up. We have criteria range one, criteria range one. What's the first criteria? The first criteria is Argentine, is the country, come down here. Comma is that criteria itself? We can actually choose any country we feel like, let's see. Let's talk about let's look at Austria, Austria. Sorry. Austria. That is it. Perfect. That is the criteria we're looking for in that criteria range of country, this will finally the average comer. We are adding other criteria to it again. The criteria range of what of product. Come down here, Comer. Then what is the criteria out there? What exactly are we looking for in product? We're looking for from Austria, look for Apple. Apple. Apple, then we will close the quotation then Enter, very. That is the average. When you say average main the average of the sales of Apple from Austria as a country. Okay? Very, very perfect. I'll see you in the next lecture. 18. Data Validaton: So in this lecture video, I'll be teaching you data validation. Yes, the name, data validation to validate a data, something like that. It's quite simple. Not really. It's quite simple. Fine. Good. If you don't actually embedded in data. Well if you can't find it, just come over here to search? Then press on data validation. Okay? Then you can see this arrow here. You can see. You can actually apply it here directly. But just click here and let's see what happens. Can see. It takes us there exactly directly. But I want you to do this. But in case you can't find it fine. That's not a problem. You would find it, but to make it very easy, I just search here. But for me, coming here is easy for me. Just go straight. We have the manua. Go to data. When you come to data, so you find different type of things here. Here we have what we call the data validation. Just put your mouse, you see data validation. You can press on this here. You see data validation, saco invalid data, clay data circles. The only thing we need is data validation, just click here. Let see perfect, click Oden drag here. So the s for data validation is, for example, let's say we have this particular name here and want to know it salary. So once we changing the names, let's say, for example, when we get to the Vlocb, we really need it. We really need to use the data value. And the next thing I'm going to be teaching you is what is the lousy the lous. The Vlocub, the H lookup, the X lookup, the index, and the match. This is very simple and it will actually come in and it's very, very easy. Now we have this particular name, Olivia. Olivia Davis and want to choose the salary, something like that. We don't need to This is Olivia Davis, right? So now we need a round name, start saying Mac sorry. Oh, Marc Lee, something like that. Then we change the name again. Mia Thomas, we don't need to do that. That is a waste of time. Contrast. A big waste of time. That is why data validation has actually come in under for us. What do we do? You come over here, come to data validation. You can see settings, so you can see input message error alerts. Firstly, settings, we are not going to be using any value or number decimal. Just come straight to list. You can actually use the other ones also, but the most use is what list? Select on list. Okay? So ignore blank, yes, leave it like that. So what is the source of that list? So click here. When you click here, you click Oden Drag because we're talking about the employee name. Ways department is the same thing, okay? But for the employee name, click Oden drag down like this. Very, very perfect. Then press Enter. So we have it here. It has actually captured it, then press on Okay. Okay, you can see now. You can see this arrow exactly. Very perfect. What does it mean? Let's click on it, TNC? Perfect. This would actually help you. You have lots, thousands, hundreds of products and you need to choose the need to start typing them. You've done data validation, actually, it actually help you. So now we're looking for what we're looking for My Thomas, click on My Thomas. The salary would show immediately. The slide is not showing right now, okay, so there's no loop applied to it. We need to do that in the loop section. So when you click here again, under name. William Clark, very easy. You changing the names very easy. So far, you've actually done the data validation. You can see very, very perfect. Instead of you type in, start typing, Sofia. You have it here, just click on it. Very, very fast, perfect. Another thing we need to know here, just come over data validation again. Input message, error alert. It's very simple. Now, let's say, for example, when you select this cell, first, you would have selected the cell already. I've selected the cell. Okay what would I say? Tito. I didn't need to put it tit two. Let me just. For the size, I can see. Message, Enter. I'll just select employee name. I present. That is, you can see because I've selected. Let me just select it. You can see so tell me message, select employee name. Perfect. I can come over here and just clay. I don't need it just Clal I don't need it's present it's gone. Even this is gone, actually, perfect. But let me just go back again. Settings value list. The sauce, this is the sauce. Can come back here present Enter, I just click on this present, Okay. You have it back there. For the last one, let me come over here. I'm going to use again also. Just come over here. Error alerts. Okay, we have settings input message, error alerts. The stop this is the symbol for the stop. Symbol for warning, symbol for information, we wouldn't be using the stop error alert. Let me just use message. No title, just error message. Let me see. Not found. Like to, not found. Something like that. No Excel, Microsoft. Okay. Now when I come over here and say, just type your name. Press and y. What happens? Microsoft Exel, not found, retry. You can see perfect because the name is not there. Okay? Very, very perfect. I'll see you in the lookup section. 19. VLOOKUP: This lecture video, I'll be teaching you Lou. In this section majorly, we have look up. The major reason for look up is to look up for something in a particular range of numbers. We have thousands, millions of data and we have to look for a particular thing. Let's take, for example, we have here, we have employee ID, and this is the number. We have to get the name of the employer. Employee ID, and this is a country of the employer. We need to get a country employee ID. We need to get the department of the of the employer of the employee, sorry, of the employee. Okay? So we have range of datas, just like one to 20 to 30 datas but we can have millions, thousands. Look this is the beginning we know look at we have huge amount of them, but this is the first one. From simple to complicated, actually, it's very, very easy but quite complex. But when you understand it, you would actually get it better. Now this is it, it's very simple. So it is the function they look up we are using. Now, as the employer, you want to search for employer we have the employer ID. What is his name? Thousands hundreds of name. What is his name? It's very very easy. What is the country? What is the department? Come by equals to Vil look up, present. Then this is the intelligence. It helps you a lot. Look up value, table array, call an index number, then we have the range look up. This is a bracket like a square bracket that is not necessary, but you can actually. The first one is look up value. What are you looking up for? What value are you looking up? What is that value? You're looking for something, right? What is that? Now, let's say, I'm looking for, I'm looking for Africa from the continents of the world. What am I looking for? I'm looking for Africa where from the continent of the world. Good. Now we have look up value. What are we looking for? We're looking for employee ID one oh one, you select here. Present, come on. Then to the table array, where exactly? I said, we're looking for Africa, where in the continent? In the continents map or something in the map or something like that. Now, we've chosen Africa comma, then now from where where is the table array? Where would we get it from? This is what we call the table array. We're going to get it from where from this table here. You click, not just clicking down like this like we'll be doing. We click everything here till the end. You can use a short cut using the shaves and control using the left and the down arrow. It's fine. Then comma so colon index number. Now, this is very important for us to know. There isn't Vlocop. It's quite no. Index match is better, easier, simpler, efficient, effective than Vlocop locop is much more better than index and match index match and Vlocop. Okay, so it is gradual process. But for Vu, you move from the left to the right. You don't move from the right to the left. Very, very important to know. It is very important. You move from the left to the right. Now, we're being asked colon index number. Now we've highlighted depending on what you've highlighted, we've highlighted four colons here, colon index number of exactly what we are looking for. We're looking for Employee ID one oh one, we'll look for his name, his name exactly. But how are we going to get there? Now we have the employee ID. That is a link. That is exactly what we are going to use to search. Oh, we have something that represents him. Let's use that to search for him. That's the lookup value. Then from where the lookup R, then colon index number of our answer. What exactly do we want to return? When the return value. Where is that colon? That is the return value. The colon index number. The return value. Where is the column? This is name? We are looking for a name column one, column two, column three. It is column three, right, press on three and press Enter. Evan. Empler ID one oh one. W is it? Ivan. That's his name. Perfect. Let's look for something else. Good. Now come over here. Equals to look up. This is very simple. Then we have the lookup value. This is the lookup value. This is what we are looking for. Exactly, we're looking for the country right, but we need a link. A link that actually represents that thing. We actually looking for employer ID 114 to be able to arrive at the answer of what country does it come from? Which country does it come from? Then comer the table is table are supposed to use a short cut down. You see perfect, very fast. You press on shift and control as usual, you press on the left, use the left arrow, then the down arrow. Very simple. Then comer colon index number. This is country, one, two, column two, then write two, enter 114 employer ID 114, you can see United Kingdom. Very, very perfect. Good. Okay. Up two. Okay, good. No worries. Now we have equals two, Lou, very perfect. This is the lookup value. This is a table array, here, here, here, perfect. Cammer. Then you have the colon index number, department one, two, three, four, and you write four, then enter. Very, very perfect. That is how to use Lou. There are many other ways we can actually use it, which we are going to understand in the next lecture video. I'll see in the next lecture video. 20. VLOOKUP (1 result): In this lecture video, I'll be teaching you the lookup also, but I want to understand the meaning of colon index number. Let's come up by equal. We're looking for florit equals two. They look up. Perfect. Look up values flat, comma, table array. Yeah, this is what I really want us to understand. If we come over here, this is table array and we choose this like this. Instead of choosing this as usual as we've done, I've decided, I don't want to start from here. I'm starting from here. Depending on where you start from, that is the column one. Then short cut like that, going down. Then com column index number for the price, this is a product name, but the price. We're not going to say column one, column two, column three. Let me say column three let's see what happens, Enter. You can see. But when I say column two because this is column, column two, and when I say two, what happens, it gives me the right answer. It's very important as I've said. Moving, you need to know where you highlighted from the beginning to the end, the first column to the last column, and you need to know column number. It's very important. Let's say for exa I say equals to up again. This is the value, then table array. This one is a table array. Let's start from here. Down like this. Now when we ask for the colon index number, what are we going to put three and press, that's it for you to understand better more about what colon index number. I'll see you in the next lecture video. 21. VLOOKUP (2 results): In this lecture video, we're going to continue with our View Lookup. We've understood this, we've understood this, and this is what we need now. Before we were actually looking up for just one particular a result, one, one particular result. But now we are going to be looking for two results. We need two things exactly two values, exactly. Two types of values. Here we have the product name. We can actually have different products here. Instead of we changing it and saying, Oh, I want Apple something like that. We don't need to use that. We've learned what we call data validation. Very, very easy. What do we do? Just control zed Cooking oil. What do we do? We move over to data, then data validation. As I've taht you before. Come over to settings here to lists as we've done before, then we try to bring about all the all the values we need. The product name, we need all the products name then down, perfect. Then come back here. All sets, then okay. You can see perfect. When you click here, you can be able to choose any product you want. You need to start writing them down. You have them perfectly well. What do we do here? The same thing we do the same thing here. Again, Perfect. As I've said before. I've touted the shortcut already, perfect. For this also we have here, also very perfect. Let me have the cooking oil back again. You can actually come over, just click here sometimes and just praise the name like Apple. We give you options and there's Apple there. Also you can actually let's say we have different you can actually say C, for example, it's going to give you different type of Cs. So as you're clicking, you're going to see it then, you're good to go. Perfect. It's very, very simple. Now we're going to be using the Vil cop to look for price and quantity together. We can do in two ways. Two ways. The first one is you don't need the number with just Vil cop, as I've taught you earlier, okay? What is the u value as you said, the product name, right? So here you can see it is covering normally before it doesn't cover what we have here. Just use your words, your left right up and down arrow. I want to use the left arrow. You can see it's already on it. It is selecting the products name, not the VL up en you're saying there, no. It is selecting the product name. This is up value as I've explained before. The comer, the table array, don't forget. Starting from the left to the right because of the colon number. Then the shortcut, as I've taught you, so back up, then come out, what is the colon index number? For price right now, that is one, two, then two, then enter, perfect. Now the next thing is the same thing again, low up, then we have the low up value, the table array, which is this. Then puma. The colon index number is what? You can see here. The colon index number is what. Colon index number is three, one, two, three, as I've aught you before, three, then enter. Perfect. You can see cooking oil. This is what is cooking oil here. Is cooking oil. The price is worth 12.99 and the quantity is worth six. When I come over here and change this, Come down here. Let me see to the last one you got and change this. What happens? 14.99 quantity is four. You can see product price list. Now, let's say toothpaste, you can see, 4.99, still four, sugar, 8.99, three the quantity. Very, very perfect. No matter the number of other things you have here, you have this, you up that and you want to just put the formulas there manually. But there's another way you can do it without putting it manually, just put it here and it applies to the two. How do we do that very, very simple. Come over a course Vu. Okay. V up the VL cp value. If you know this cell name which is here, which is F 13, you can actually choose it or use the left arrow. Just click on the left arrow you can see, it's going to appear there. But if you can't figure that out, that'll be F what F 13, as I've taught you guys before. That's a cell F 13. Then comer. The table array, what is the table array? This is a table array. As I tout before. Good. Then comer colon index number. This is exactly where it happens. So we're going to give it a cool braces. Cole braces would actually help you to be able to put a range of numbers to be able to achieve something by just using one and achieving a lot of things, a lot of result. Now what we are trying to look for now, the colon index number, we need colon index number two and three. Let's say two, commer three. Then what do you do? Close the cool braces, then now close the bracket itself, then enter. Perfect. You can see automatically. When I click here, you can see there's something like a blue highlight here. When I click here, it becomes dull like there is nothing here. But there is, it has been applied here already and it affects this. I didn't do it manually like I did here and I did here. I just did it here. Then use a coil use the Calases then say I want it to return colon Index, colon index number two and colon index number three. Very very perfect. Now when I come over here and change it, let me change it to sugar. Let's see. Let me change to sugar. You can see exactly. Changes everything itself. Very, very perfect. You can see it is very simple, very perfect and lovely. You can see Vlocp is very actually looked up, you know, array of about eight or seven data, look at the number of list there and you're able to just in seconds, let me just say in seconds, umbrella, you just got a price and quantity. Straightforward. No waste of time. Vlocop is very powerful. Index match, much more powerful than Vlocop while X Loup is a top, much more powerful than index match and Vlocop and H lookup. A we see in the next lecture video where we'll be joining tables together using Vloc. 22. INDEX & MATCH: In this lecture video, I'll be teaching you index match, index, and match, then index match. It's very simple. Index actually returns the value of a cell at a specific row and colon in a range. Okay? It returns the value of a cell at a specific row and colon in a range. Select here. We need to practicalize, equals to index. Then it's asking for the array. Okay. So these are all the array. For index, these are all the array, then comma the row number. What are we actually looking for? Let's say we are actually looking for banana. Okay, what is the row number? Row one? I won't come from here. I come from where we actually highlighted. Row one, row two, row three, row four, row five. Row five, right? Five, Come, this is banana. Then going to what is a column if I say column one, going to give me banana. Column two is going to give me 5.99. Column three going to give me 31005 is going to give me four. Column four is going to give me 1005. Press on four, the press enter 1005. If I come back here and say, row number seven, or let me say row number 11, or let me say row number 14. Sorry. Row number 14, is going to give me 1014. Then I say, I want to change the colon number to one hand tuna. Let's say one, you can see that is the essence of index. Index would actually help you to find it would actually help you to return values of a cell at a specific row and colon in a range, specific row and colon. What is the specific row? We have Row 14 and colon one. This is row 14, what's colon, colon one. That is what index normally as index as singular. That's what it is used for. Then for match, because it is very powerful when you join index and match. But when you use them individually, they're just normal functions, but very powerful. Very powerful the value coupon use them as index match together as a formula. Now for Mach, match helps to return the position of a value in a single row or colon, the position of a value. When we treated index, index was actually helping us. Index actually return. The value of a cell of a cell at a specific row and colon. Look at this value of a cell. What is the value of the cell? Can't tuna. Can't tuna. At a specific row and colon, what was the role? We said row row 16, sorry, row 14, and what colon one. Let's see, row 14, column one. At a specific row and colon. But for March, march would actually return the position of a value in a single row or the position, not the value this time around. Why enjoy those two together, it's very powerful. Now the position. Let's say it costs to march. March. Okay. Perfect. So what's the low value? Let me say I'm looking for bread. Okay, I'm looking for bread from here. Oh, sorry. Bread. Bread, Com, that is the lookup value. What is the lookup array? This is the This is the lookup array. We don't need to highlight from here to here. Because we don't have anything like what is the column number? What is the column number? We don't have it here. This is the lookup array, then comma then we can have EAT match. You can click and press on tab, then press on Enter. We have 11. What does that mean? That is? Don't forget it gives us the position. Position of a value in a single row or colon. You can find it here. This was what I actually highlighted. You can see 11. This is 111 year. If I had said something like, sorry, let me see. Local value I'm looking for here is, let's say, tuna for ten, you can see, it's going to work perfectly well here and it's very powerful when we get here. We are here already. Index match. It may be quite tricky or value cop straightforward. But index match is straightforward also, but you have to pay attention to it. There is no such formula as index match on its own. Let's see, equals to we have index, you can see, we have match, you can see, but we don't have index match like index march, something like this, and say equals to open if we don't have it, but there's a way we actually deal with that. Firstly, you have index. You open brackets, then you have rarow number, color number, and the likes, okay? Now that we have the array, the array for the index, that's why I said it's very tricky but not. The array you're going to put here is the result. What are we looking for? We're looking for the quantity here. We have product. We're going to be looking for the product, to be able to search. We're going to get a product to be able to search for the quantity. We're looking for the quantity of this particular product. What are we looking for? We're looking for the quantity? Quantity exactly is the result we're coming after. Quantity is the end product, is the result we actually looking for. Now for the array, normally when we're using the Vp, the first thing we look for actually is a lou value. But here in the index, when we're joining the index and the match formula to function together, we are going to do what? The first thing we need is actually the result. Where the result is the array result. The result array. That is where we would get the result from. Which is displaced, which is the quantities after the quantity, you click Wd and drag it down here, comma. Now the row number for the index will not be what you are going to apply the match. That's how it works out the match. From the march, you're going to look for the word Lou value. What is look up value? We'll look for the value actually here in the words, GG 11, cell G 11, press on the left keyboard or you can just press G 11. Just come over here and say, G 11, comma, you can see it came here by itself, so you won't just disrupt everything here. Then we have the look up array. The look up array for that G 11, which is product is this, as we all know, then comer, match type will be exact match. Then click. Just click once or click, double click twice, fine, then press on Enter. But before that, you can actually decide to close this and close this. When you close this for, you've closed this match for you to close the index, you can actually put under and press Enter, very, very perfect. For Bluetooth, we have what we have the quantity products for Bluetooth, we have nine. Okay. Then we have, let's say, for example, now we can actually use what's it called? The data validation. Just come over here to list. Come over here. Come over here. Perfect. I can change it to something else. You can see very perfect. Very, very perfect. Now, another thing we need to know is that when we're using the V lo up, we need to make sure that we do everything from the left to the right. But for the index match, you don't need that. You can actually choose from right to the left depending on what you want to do. Even in the lookup, anyhow you want to do it, just fix it. You got to go. Now we have or ID and we're looking for the Pdot. We're going to click here equals to index. But so don't forget when you're talking about the index match together, the first array, which is we're talking about the array, the first thing is the result. We're looking for the result array. What are we looking for we look for the product? We're going to click O and drag this here, the product, then comma that is where the result is. That is the result going to get. That's where we're going to find the results. The match the match, Lou value. That is G 16, G 16 16 comma Lou array, which is or ID, perfect. Comma, match type, click here and press on tab, then press on was Enter. Okay. I have to put another comma has actually corrected it for me. Sorry, closing brackets, it has cret that for me, and that's perfect. So 1009 actually is what body wash. I can actually come over here also, use a data validation. Very perfect. You can see the data validation would always come in and for us Enter. Then, perfect. I can actually change the number again 1006, 10131013 is what Butter. You can see very, very perfect. That is why the index match is very powerful. Normally, you have to relate from left to right. But here we actually got it from the right to the left. This is it order from the right to the left. Very very perfect. You can see how powerful index match is. 23. INDEX & MATCH (Nested): In this lecture video, this is a continuation of the index and match. Now we have two different type of things where we have product, which is in the row and we have continent, which is in the colon. It can be quite tricky actually. It's not as simple as the last one we did actually. But it's very simple. As usual. So it's a equals to index. As usual, we're looking for what the result array. This is where the result exactly is doing the quantity. Okay? This is the result. Don't forget the array. When we're using the index match, we look for the result first. Array, the array is the result array, the comma. Then the row number, that is where we're going to fix the march, okay? Good. Then for the march, we're going to look for the product and also the continent. Now, firstly, we look for the row because you can see here we have row number and it is from the row number we put the match, we look for the row. This is the row. We look for the look up value. This is the row product. We click here, then Cammer. Then look at this, this is the lookup array, then Cammer, then double click Exact Match, then close bracket, then Cammer. For the colon number, which is this colon colon BCDE. We're going to watch another match match, then the lookup array. The lookup array is this, which is the continent itself. That is Cell C 15. You can just use your left arrow, then comma then look up array. This is the look up array. Perfect then comma, then exact match, then tab, then close bracket then enter. We found a typo in your formula and try to correct it do you want to, I need to put another closing bracket and that's yes perfectly, we have rice rice from South America is 250 225. Now, if for example, let's say, for example, we change this, you can actually come over to data as usual, data format, data validation, sorry. List. Then we say this is the list. Present. We have South America also, that is continent, Litter validation. List, come over here and present enter. Sorry. Yeah, good. We have the rice. Say, peanuts in South America, peanuts peanut butter in South America. That's 59. Let's say, peanuts from another country. Let's say in Asia, that's what that's 32. Now, using the index and match to actually get two different things row and colon results actually, it's very simple. It is what we call nextedFunctions, next ed formula. That is it for index and match. I'll see you in the next lecture video. 24. HLOOKUP: This lecture video, I'll be teaching you the H look up. It's very simple. You don't need to waste time actor. It's based on horizontal. Vloop is vertical, looking up vertically. H look up is looking up horizontally. It's not really being used actually. It's very easy. We don't deal with column one, column two, column three. Yeah, we do what you can see, little row one, row two, row, row four. Now come over here and say equals to H look up. The lookup value is worse. This is where the lookup value is, cal value is cell B ten. Say B ten. Okay, then comma. Perfect. The table array, this is the table array. This is everything, everything, everything. Then comma. Then we have row index number. What are we looking for? We have the order ID. Then we are looking for the product. The search itself. The result we are looking for actually is the product from what we've highlighted from all this role. The product is what? Other ID is row one. Don't forget, we're using column, column two, column three, column four. We're using row one, row two, row three row four. The product is what row two. Just put two and presenter, and that's all. You can see 1013, this is 1013, it is for product is what butter. If I'm looking for a price, I have to change this towards 03, and it gives me the price 6.99 for other ID 0.13, 001013. This is something we've been doing in actually Velo cup, actually, but look up now that I used Vlocop still the best Vlocup best index match and X L up it's very easy. I'll see you in the next lecture. 25. XLOOKUP (1 result): So in this lecture video, I'll be teaching you X, look up just like the Lou. Bring your cosa here. Let's say equals to X, look up, you see, press on tab. The lookup value is a lookup value the employee ID, click on it, then Cammer, the lookup array. It's very, very simple. Just the column. You don't need to start clicking Odin and drag and something like this. You don't need it, just the column. What exactly is the color? The employee ID. Click here, press on Sheet and Control, then what and down, okay? Come up, scroll up, then comma the return array. What results do we want? We want the results from the name, click here. As usual, the prison will enter, perfect. 113 is Raj. Let's come down and see 13, the name Raj. Now per country, come over your preson equals to X, look up, you can see, Lou value, the comma. Lo up array. Where are we looking at right now the employee ID also. Click then comma. Then country, this is the country. Then it was the name but now country and preson. Enter, perfect, you can see, 123 is from Egypt. 123 is from Egypt. You can see perfect Egypt. Again, let's come up for the department, three q two, look up. The lookup value, click here then Cammer, the employee ID, there's a array, then Cammer. Then move up the return array, our results, department. Come down. Perfect. 129 isn't what is in marketing, 129, marketing. I'll see you in the next lecture video. 26. XLOOKUP (2 result): So in this lecture video, we've seen how powerful X look up is and very easy. Okay? So come by, we're looking for the price and we're looking for the quantity. Instead of, you know, check it. Looking for the price separately and looking for the quantity separately. We're going to do that together at the same time. So equals to X look up, okay? The lookup value is all purpose cleaner. Okay? So we click this. So from where the lookup array, this is the lookup array, click. As usual, come on. Okay, move up, then what is the return array? We can decide to click on this and go down only. But if I choose the second one simultaneously also, it understands that, after giving the result for the price, the next one to it is what is quantity. Vil coupons do that for you. Except to use coil brass and also except to do it individual. But for this perfectly, even if it's three, four, five, you can actually highlight all of them. Highlight and come down. Perfect. Then what do you do? Come up the press on Enter. You can see perfect. A purpose cleaner, all purpose cleaner, the price, the quantity. When I come over here just as usual, come to data validation. Come over here, litter validation. As usual, list, then give us the list. This is where we have the list. Click Wood and drive down or use a shortstop, then press them, Okay, and we're good to go. I can decide to say, Oh, I want to choose something else, click here, then come down, say flour. This is the price per flour also, 3.99, then quantity is four. Perfect. Decide to come down potatoes, potatoes. Let's say potatoes, 6.90 90 quantity, 6.90 90 quantity. That is, it remains only zero quantity, so we won't be able to sell it for you. Very perfect. Let's look at something else. Let's look at something up here. Let's go batteries. You can see 6.99, then how many quantity, 25 quantity. X, Lou is very powerful. I'll see you in the next lecture video. 27. XLOOKUP (Advanced): So in this lecture video, we have a quite complicated table here, look up table here. So it's using this using X Loop is much more better, faster efficient than Vo. First let's come over here. Let's come to data as usual. List. Okay, select the product, right? Good. This one, enter, so that you can be able to choose any product you want. The last one we did was two answers, two results, but now we have two questions to answer here. Okay? So come over to data, data validation. List, okay? So click Code and drag like this, then press on, Enter. So we have the different continents here as I've taught you in the data validation. So this is very, very simple. We need to get number one, the product, any product we have here, number two, and the continent at the same time. So we want to get a particular product and how it is actually, you know, performing in a particular continent or how many quantity it remains in a particular continent. You probably have lots of companies in different places all over the world. Okay? So we have two questions. We need a response for it. Okay? It's very simple. Just come over here. We're going to use X Lou only drop X look up. What is the lookup value? The first one? Because this formula, the two answers is going to be in one formula. The two questions are going to be in one formula and we're going to answer the question once. Click here then press on Comer. What is the lookup array? The lookup array for product is this, comer. What is the return array? The return array is meant to be something like this. You click O and drag everything here. But you won't give it. You would give it another X look up. What is the next thing to do? X look up, then open brackets. Oh, sorry. So instead of me giving a ton array, I want to return the array now. Then I give in the second formula as we call nested X look up. The looku value is this, but this is being blocked by the formula. So that's C 16, C 16. Cammer. Then the lookup array is this click Oden drag and Cammer. Then this is the return array. Now we have to apply the return array. It's going to affect everything. Click Oden drag like this. Then close bracket one, then close bracket two. The bracket one we close for this is for this, sorry, it's gone. In order to make mistake bracket one is for the second open bracket, closed bracket is for the second next lookup. This last one is for this first one here. Then press on Enter, very, very perfect. Now when we look at this plastic wrap forward for Europe, Europe, you can see, perfect. When I say, let me change it. Let me say umbrella for Europe, umbrella for Europe, 62. Let me see I don't want umbrella for Europe. I want to check umbrella in South America. Is South America umbrella to the quantis that remains or the quants that we sold or something like that. X lookups make it very, very easy for us to be able to do that. Nest another X lookup in the first X lookup. Wever you have your what Wever you have been asked to give a return array, then put a second X lookup. Perfect. I'll see you in the next lecture video. 28. XLOOKUP (Joining tables): In this lecture video, we will be joining these tables together. So come over here, it is scattered. This is one oh six, one oh three, 11, but this is 11, one oh two, one. So love the employee ID, we have the name. As usual, the employee ID is connecting the two. Actually, there's something connecting the two, so we're going to be using that actually. Then we have the country department and edge. So we want the country department and edge to be here. Come over here and say, costs two X, look up. Okay, perfect. What is the lookup value? This is the look up value. Perfect, select on this and press on Cammer. Then the lookup array. This is actually this what we're looking for the lookup value. In what in the lookup array, where is the lookup array? We have a lookup array here. I just come over here. You can see it has this already. Then click On drag this down. Then press on Cammer. Before pressing on Cammer, use a F four, as usual, you log everything, the column and the and the roll. Then Cammer perfect. Then what are we returning? We're returning the Cundra, click On dragged down, then press on F again, then ends up, perfect. So come over here, then double click down. Very, very perfect. The next one solar for department equals to X, look up. Perfect. The lookup value is the lookup value commer then come over here the lookup array, click Oden drag F four, important comma, then come over here, click OD and drag this down, then F four, Enter. Perfect. Then what do you do? You double click down and it goes down. Perfect. The last one equals to X look up. We have the words, the lookup value. The lookup array come over here. Click Woodnratdwn F four, important comer. Then click Wood and drag this down also. Four, Enter. Come over here, then what? Then double click. So we have everything set already. We took this table, we imported, we linked everything here, we linked it here, we connected the tables together. Very, very perfect. X lookup is very easy. It is the fastest and the best. I'll see you in the next lecture video. 29. PROPER, UPPER, LOWER: In this lecture video we'll be using different cases we have uppercase, lowercase, the normal case. When you come over, this is proper, upper, lower. B using the proper function here. The proper function is just like the first letter of each word would be what would be in capital letter. Equals to Proper. Then you use a left arrow. It chooses it, then press Enter, perfect. Then click here the double click. You're good to go. It's modified this into this perfect. Upper also equals to, upper present tab, then use your left arrow as usual, present Enter, click, then, double click. Perfect. Come over here for lower equals to lower present tab, click this, Enter. Then come over here, then double click. All this lower, upper, then the proper way of writing, actually. I'll see you in the next lecture video. 30. LEN: So in this lecture video, we'll be using the lens function. That is to find the lens to know the length of a particular character. So bring your cusorPresen equals to LEN, presen, tab, then this text, present enter. That is we have eight characters, that's one, two, three, four, five, six, seven, eight, come over here. Click O and drag or you just double click. So we have ten characters here, and also we have 13 characters here, one, two, three, four, five, six, seven, eight, nine, ten, 11, 12, 30. Perfect. I'll see you in the next lecture video. 31. LEFT, RIGHT, MID: So in this lecture video, we're teaching you the left right and mid function. Yeah, it's just for you to retrieve a character from the left. You need just the character from the left and how many characters. You need some characters from the right, how many characters, and you need some characters from the middle, how many characters? El makes that perfect. Come over here and equals to left then text. This is a text, then cama number characters. That is how many characters do you want from the left? Okay. I can't say I want. I want two. It's present two press Enter, it gives you only AB. When I come over here, I can actually click on the drag it gives me ORN because the character is two. I can come over here and say, I want four characters, present four, present enter, OR, OR, the mind is actually. OR, D, E. Okay? I can come here and say I want just five, six. I say I want ten characters. So I can actually come over here, but I don't think it's up to ten actually. Let me say seven characters. Okay? So one, two, three, four, five, six, seven. You can see? Perfect. For the right also, see, equals to RIGHT. Text. This is the text. Present text, then comma, number of characters, one, two, let me say three, that's XY. Let me say three. XYZ. In say oh I want only the numbers one, two, three, four, five, that's five, Let me double click down here. Come over here and say five. Perfect. Here, I want only. I want 24 u one. That's what that's four, 24, Qu one. But let me say I want thousand and 24 quarter one. That's six, right? So present six, sorry, yeah. Perfect. You can see, very perfect. The mid function is quite is not complicated but technical. Equals to mid, present tab, text. This is the text. Then commer you can see it as two other options. The start number. That is the start number for that particular middle character you want to take. Okay, so I only one, two, three, the start number for one is, I mean, the character, the position, that is what they mean. A is one, B is two, then one here is three. The start number is what? Three? Okay. Then comma then number of characters you want. How many characters you want in the middle? I want three characters. That's 123 then press Enter. You can see, very, very perfect. Let's practice again here. Equals to mid, tab. This is the text, comma starter let me see, the start number is E, there's one. Let me choose only E, or let's say let's choose ER seven, eight, O is one, R is two, D is three. E, which I'm going to choose from the middle is number four, the position number four. That starts number means the position. That's four, then comer. Number characters. How many characters do you want? That is the meaning. So one, two, three, four, that is E seven, eight, there are four, so press on four, then enter. Perfect. For this, I only want 20 from here. Equals to MID, Perfect, the text, then commer the start number one, two, three, four, five, six, seven, eight, there's eight. And how many characters? Two characters. Only 20, perfect. That is left right and mid function. I'll see you in the next lecture video. 32. TEXTSPLIT & TEXTJOIN: So in this lecture video, I'll be teaching you the text split and text join. It's very, very simple. Firstly, come over here to view pres on grade lines. Okay? Very important. Okay, so just bring your cursor here equals to what? Text splits. Sorry, you can see. Perfect. Where is the text? Click on this. That is a text. Then what commer? We are being asked call on delimiter. When you say delimiter, it is actually something you're using to limit something else, something in between the two text, you want to split. What exactly is in between them? That is a, that is a space. How do we identify a space? It's very simple. Use your open quotation. Then what do you do? Pres on space. Then use the other quotation, the closing quotation, then press Enter. Very, very perfect. When you click here and double click down, double click down. If it doesn't work, click and drag down and it works. So it's very simple. The first one is a text, and the second one is what the delimeter column delimeter that is the space. So for it to create a space, you open the first quotation, then use a space pa, create the space inside, then use the closing quotation. That's split text. That's the text split function. For the text join, you join them back. It's very simple also equals Text join. Okay. Perfect. So now the limiter, what is the delimiter? The space. Okay? So open quotation. The space bar is to create a space. Then close the quotation. So that is a delimiter. Then comma, ignore empty cell. You can double click this. That's true. Double click, then comma then text one. This is the first text, then comma, then text two. Then what do you do? You press Enter. Very, very perfect. So what do you do? You double click? It comes down. So that is text join. 33. TEXTBEFORE & TEXTAFTER: So in this lecture video, I'll be teaching you the text before and text after. Okay? So come over here. Normally, come over to view, then use the grid lines. Perfect. So click here equals two, okay? Text before, press on tap. Where is the text? This is the text, okay? Then comma. You can actually use anything for the delimiter, anything you want to use. So Vt is text before and text after. You can use anything for the delimeter. So what do we use? Let's say, for example, you say text before. I want to text, anything text, anything character, even character and text, not just only text before do. That is, we have John, then what the underscore, then Do. What do I do? The delimiter is what? Local quotation, then do sorry. Do then close quotation. You have to put this in quotation because it is a string. Very, very important. You can press on Enter, and let's see. You can see the text before do is John then underscore. Very simple. Let's take, for example, the text before UCT equals to text before. Easier left arrow and comma. The imeter will do what? There's UC t. Enter. You can see. Very very perfect. So for this also, it goes like that. So equals two. Oh sorry. Perfect. You can see, perfect. Then the last one equals to text before. Since you text then comma, then let's say the dots, then enter, very, very perfect. Come over here to what text after the same thing, very the same thing. Equals to text after, you can see, this is a text, comma. Now what is the delimeter before we have the text? Let's say the underscore. Okay. And you're good to go. You can see. The next one, text after. So this is a text the comma delimiter, I can actually make it XL, sorry, XL. Then enter because it doesn't recognize the small letter, the lowercase XL, it only recognizes the uppercase XL, we interchange to uppercase, then perfect. Then the next one text after you have this then. This is the ah, just like we've used before. Enter, very, very perfect. Next one equals to text after. This is the text. Then coma sorry. The t Perfect. The last one equals to text after the text comma, then the full stop. Then that is it very perfect the text before and the text after in Exil. 34. TRIM: In this lecture video, I'll be teaching you the trim function. It's very simple. Come over to view. Sorry, the grid lines equals to trim. You can see perfect. Where is the text? So what does strim does? Trim actually when you trim something, you take away the unnecessary ones, the ones you don't need, and you leave the necessary ones, the important ones you want, we have a lot of species here which we don't want. It's going to modify that for us. Excel is fun. After we need only one space and fun. When you have too much of species here, Excel will actually help you to bring it back to one space, and all and all, it would actually modify and take away the spaces for everything here also prese and enter, you can see perfect and double click. So actually work for everything. I'll see you in the next lecture video. 35. CONCATENATE: So in this lecture video, I'll be teaching you the concatenate. Yes. So come over here. Before that, come to view to grade lines, okay? Equals two. So concatenates is the old way of doing it. So the new way is concat. So that's why you see a yellow warning sign, something like that. Actually. If you use concatenates, fine, go still. It's going to work, okay. But let's use the latest one, which is concat. So press on tab, okay. She's going to ask me text one. What is the first text this? So click here, then comma. She's going to ask me text two. So but before text two, let's try text and Let's see. Then comma ask me text three, then press Enter. You can see, very perfect. But we need space. Yeah. After the post code, we should have a space should have a comma and space and after the state, we should have a comma and a space. After 10001, there should be a comma in the space the New York. Then a comma and a space then USA. How do we do that? It's very simple. Instead of just having this like this, I'll start again actually equals to C cards. What is a text? This is a text then comma. What is the next thing to do? We need a comma so for us to have a camera after the pose code, you put a quotation, then a camer, you can see that is done. So that is your text too. Then camera again. Okay. The camera in the quotation is going to show, but the normal cammer we have in here is not going to show. Okay. So after the camera, we need a space. So what do we do? A quotation, then space ba, close the quotation, then cammer. That is, here we have a space. Then after 10001, we have a camer then we have a space. So that's how I go to continue doing it. The next one states Okay. Then comma, then open quotation, then comma, close quotation, then comma again, open quotation, living space, then close quotation, then comma. Then the last one is what USA, then enter. You can see very, very perfect. You can see. You can see we have the comma then the space New comma then space, perfect. When I double click, it works for every thing. So Concatenate is very powerful and it is very technical, very simple. It joins words, characters together. 36. TODAY & NOW: In this lecture video, I'll be teaching you the today function and now function. It's very simple. Just have equals to today. Okay, present tab, then presenter. That's today's date, very very perfect. You might decide to change it to a long date. It's fine. Sorry, click here. You decide to change it to a long dates, fine. So just double click and you have it. Perfect. The next one equals to now. So this will give you just present tab. This will give you today's dates and the time as of now presently. Press Center, you can see, very, very perfect. That's how to use the today function and now function. 37. DATEDIF: So in this lecture value, I'll be teaching you dated E. There's a function in Exam called dated E, but it is dent, not known, okay? It's very same. It actually helps us to know exactly to calculate the full years full month, full days between two dates. You want to calculate the span of days, span of years, the person is going to pay the money. If it's month, the span of month 2024-2025. You're going to know the span of dates in between the two dates to know years, the month, the days, the person is going to use to pay the particular price or to return something exactly, probably borrow something. This is going to help us to know the span of years, months, days in between two particular dates when something started and something is going to end. It's very simple. Just click here, okay? Presser equals to dated. You can see dated if it's not showing. Yeah, it is ding. It's not showing but it is ding, present you can see, it's now showing down here. Open bracket. The first dates, which is this, then presen Comma, then what? The end dates, press on comma, then open quotation and press on Y Y, then close quotation. Calculate full years between if you want to calculate the full years, that's what you go to do, close your brackets then enter. You can see, perfect. It's worked out three years. 2021, 2022, 2023 and 2024, three years. The same thing actually goes for these two, just double click down. But we're not using we're using month. What do we do? Come over here, change it to what M, and that is four months price. The month in between March 2024 towards June 2025 is 15 months. The person to pay it back or return the product in 15 months, here in three years and here we come over here, use D for days, then enter. Okay, 731 days from 2022, which is May till May 2024. Okay? So it's very significant use it for anything actually to know the span of years span of months, there is in between, a particular month. The first, the particular date something started and when it's going to end. The particular day something was bought and in, in between, okay? So I would say in the next lecture video. 38. SORT: In this lecture video, I will be teaching you the sort function. Yeah, once you sort out something, just to reorganize something, to rearrange something, probably in the ascending order or descending order, ascending order from A to Z. And descending from Z to A, just like that. Or ascending this from the smallest to the largest and descending from the largest to the smallest. It is very simple. We want to sort everything here using the sort function. We can just come over here equals to sort. Perfect. We are being asked for the array, so we're going to choose everything. Click here. This is shortcut. Perfect. That is the array, then comer. Okay? The SOT index, which column exactly do you want to sort? You can't just say, Oh, I want to make this ascending, make this descending, ascending and descending. Column A ascending, column B descending policy. So it's not possible. You have to use one particular column, to sort. Okay. So we're going to be using the A column. Okay? So the SOT index is what is one. One and comma. So the sort order should be ascending order. Double click. So you can see, we up WogS's going to start from ABC down. Just press on Enter. You can see very perfect. A, B, C, C down to what two. Y, you can see that is out of sort. It's very, very, very simple. Okay? So I sorted using the column A, so I can sort using column B. Okay? So click here, come over here, then this is what SOT index, that is one. The SOT index, for column B is two, delete this write two. It's going to sort it. You can see it only affected column A. It didn't affect every other colon, it's only going to affect column B. It's not going to affect colon A or any other color right now. Okay? So column B, press on enter. You can see, perfect. You can see B, B, D FG. Sorting actually when you sort, use a particular index, probably column A, B, CD. The index is one, two, three, you know, the arrangement, actually. So here I use column A, B, CD, any one actually. So that would be the main colon that will be sorted. It will be the one controlling every other column. I can decide to say on to sort colon five, come over here. I would say five, Enter. You can see 37, 44, 49, 50, 52, 57, 60, 60 going down. You can see it is one in control, so it controls every other thing here. That doesn't mean every other thing here would actually be well arranged. The main thing you want to arrange is this. The main thing we are actually controlling is this total here. So that's how to use a sort function. I'll see you in the next lecture video. 39. FILTER: In this lecture video, I'll be teaching you the filter function. When you say filter, actually, you can see we have this already. I can decide to remove this filter, just click around here. Come over to data. You can see a filters being selected, so I can remove it. It's unselected. When I click it actually can see when I come over here, I can filter. Sorry, let me click outside. I can filter the product name. I can select everything then say, I only want to see apple. When you filter something that you filter away, you only filter a particular category or a particular item. I only want to say apples the sales of apples let's say okay. Only apples would be shown here. Okay, that particular product. Okay. So let's say to say apples and let's say banana apples and banana and say, Okay, you can see, that's out of filter. We have the function for it also. Let's go back here. Let's select everything back. Okay. Perfect. We actually have a function for it. Let me click like this. Good. So what is the function? It's filter. Oh, sorry. The function is also called what filter. The array as usual, just click, press on Shift and Control. User writes arrow, then down arrow. Perfect. Then what do you do? Come on. So this might be quite tricky. When you say include, it is asking you, where exactly do you want to filter? We've done the array. Everything inside here must be affected. Fine. But there must be something controlling it just like we did for the sort. Filter, where exactly do I want to filter? I want the array. This array is going to be affected. Everything in the array is going to be affected. But where exactly do I want to filter? What exactly do I want to show? I want the total price, I have cancers of prizes here. I want to see the product that actually gave us a revenue of around $150 upward. What do I do? Include when you want to include, you click here. Use the shortcuts as usual. When you do that, actually, as I've said, it might be quite tricky. When you do that actually, this is what I want to filter. I say equals to, I can say greater than or equals to 150. Let's try that. Let's say, greater than or equals to 150. Then lose brackets. Then enter. Perfect. Now, the first thing we go to look at is this equals greater than or equals to 150. You can see 157, 216, 180, 150, 180. That is the product that actually gave us the revenue of 150 or more than 150 is cereal box, coffee beans, olive oil, rice, shrimp. Very, very perfect. In the category of breakfast, beverages, beverages, cooking oils, grains, seafood. I can decide to come down here and say equals to filter again, filter. Then what is the array as usual? Come here. So I want to use something else, sorry, that was a mistake. This is the filter. Come down here, perfect, then comer. Include this exactly is what we want to include, here, and we say equals to apple. Don't forget when you're using string, when using characters, words, you put them in quotation. Apples, okay? Closed. Then what? Close the bracken. Enter. So we actually filtered only Apples. You want to see all the apples. Okay? How was the sales for Apple? Okay? The revenue, it's broth for us. Okay, so 60, $60, $70, $102. Okay. So it's very simple. It depends on the way you want to filter, sort filter. Okay? It's very, very simple. I'll see you in the next lecture video. 40. SEQUENCE: So in this lecture video, I'll be teaching you sequence, very simple. When you say sequence, like one, two, three, four, like, you know, just to have a well organized number. So there we have products name category, and there is not like, you know, sequence number here, a serial number, something like that. Okay? So I will just write click here and press insert to create a No color. Okay. So I'll come over here I will say SN. Sorry. S N, press Enter. So click here, press on me, then come over here to the background fill. If you are finding it difficult to get this particular color, the particular color for this, okay. So firstly, you click over here, when you click over here, come over to what two format painter. Then after clicking here, you click Format Painter, then click on here. It's going to copy all the features we have in this place here, the background color, the font color, and the likes okay. So let me remove this away. We don't need this, come to data, remove the filter. Perfect. Come back to home. So now, you can get the number for this. It was saying, one, two, three, four, five, we don't need that Contra zed. Let's go. Contra Zed. We don't need that. It's very simple. Just write equals two. We need to know the amount of list we have here. Let's say, for example, we have it from here, from three. Let me escape. F three, so we have one, two, three, aside from three, right? Okay, good. You can see it here. So let's go down. We have 76 minus two. It's from three, we have 76 minus two. Definitely we have 74 products here. Press equals to sequence. Sequence, the role. So it is asking us for the row, the number of rows. That's 70. Let me go back. That's 74 here, 74 because we have this T we have this as the headline, the title, the heading, we're going to go to count this with it. So we have 75 minus two, sorry, we have 76 minus two, that is 74. Come over here and say 74 74 then enter, very very perfect. You can see 74. It's very easy. Then also, we can come back here. We can have a starting and then ending. The ending is what we have here the rules you come. We don't need for colon. You can omit it, then come again. He's asking me for the start. What number should I start from? That is what he's trying to say. What number should I start from? So let's say I want to start from, let's say, 2001, for example, probably sorted probably sorted one to 1099 to 2000, sorry. Let's say, 2001. The present. It's going to start from 2001 until it gets to 74. You can see. I can say start from 500. Let's say, 632 23, and it goes down. I will calculate the 74 rules for you. It's very simple. It's not compose. You start from one. It can actually be any number you feel like. It can actually be any number you feel like. Also we have another one called step, come up come on. For the step that is going to omit, it's going to omit 11 step, two, two step, three steps. Let's say one, for example, let's say one, 623, then 623 is 24 and the fine. Let's say two. Come over here, S two. You can see 623, then 625, then 627, then 629, it's omitting two, two steps. Like, okay after three, four, five, it is omitting it is calculating plus two plus two plus two steps. So it's very, very simple. That is actually how to use sequence. 41. UNIQUE: In this lecture video, I'll be teaching you the unique function. Yes, unique function. Unique function as we all know, unique, something that actually is the only one. Let's come over here, click here. Present, equals two. Unique. Perfect. Then what is the array. Then click here. We want this. I want to show. I want to ask I want the unique product. Apple has been repeated probably twice, trie banana probably twice, twice. But I just want to know, what are the products? In this product name in this product list, I want to know those products. It's only going to show me one of those products, just one. It might have been repeated twice, triice in the list, but it's only going to show me at list. Fight has been repeated many times. Even if it's once, twice, thrice, four times, five times, it's going to show me. Fights actually appeared is going to actually give me just one out of everything that has appeared. Okay so let's shot got down, and that's all. Press on, Enter. Perfect. It's actually showing me here we have apple, burgers, banana, beef, everything here. We have all the products that we have. We have them here. That's what Unique is meant for okay? It's very, very unique. To show you, Apple is the burger is the bananas, beef, bread. Broccoli is the cereal box, butter, chicken breast, chips, chocolate ba, the other there. But they appeared like twice, tries fine. But we just want to know what type of product exactly is there. So we have everything we have here. It appears actually. So fide has appeared once, twice, three times. No matter what he's going to give me. These are the products we have here. Okay? So in the next lecture video, we're going to be using the distinct 42. DISTINCT: So in this lecture video, I'll be teaching you distinct. Firstly, what we need to understand is that there is no function called distinct, yes. What we use to achieve distinct is unique. Okay? So the other options under NIC would actually give us a distinct value. What's the difference between unique and distinct? Yes. Unique would actually give you a result from a list. I will show you a particular product that appeared at least once. It might appear once, is going to give you. So it might appear twice, tries, four times five times six times, it's going to show you it's going to give you oh it's appeared twice. It's appeared once. I appeared tries fine. But you said I should give you a unique value. I'm going to give you just one of it. Okay, so importantly, Unique actually shows us a particular product just like this, just like we've done here. So as I've said, Apple appeared, let's say one, two, let's keep on coming down. I think that's just once, right? Perfect. Okay. So apart from Apple, let's say, banana bread, you can see, bread appeared twice here. What else? Apple. Apple is, Apple appeared like three times. Wow. I'm just seeing that. Okay? Well a lot of things that appeared twice, thrice. Look at tomato, for example, tomato appeared twice and the like. If it is unique as I as we've used, it's going to show you only one. F has appeared once, at least once. But for this thing, it's going to show you the products that appeared only once, not at least once. Unique will show you at least once that it's going to appear twice, triice. But for this thing, it will show you the product or whatever you want to ask that appeared only once. If it has appeared twice, Trice and above it is not going to show you. It's only going to show you. That's why it is called distinct, distinctively different. It's appeared only once, not twice, only once. So that's how to do it. Come over here, equals to unique. Oh, sorry. Unique. Perfect. So array. Okay? So this is the array. Okay. Perfect. Cammer. So Bclono we don't need to do use this, just press on Cammer, we don't need that. What we need is exactly once. Okay? So we've said return items that appear exactly once, then result every distinct item. So we want return items that appear exactly once, once, not twice. So double click, then press on, Enter. So you can see, these are the product that appeared only once. So when you start checking, bugle, it's appeared only once here, you can't find it twice. Cereal box, only once here you can't find it twice. Never, keep on checking and checking you won't find it twice. Just once. That is the meaning of this date. It doesn't have a function of its own, but it has an option here, exactly once. It has an option here in Unique, can see perfect. Mango only once, not twice. But the other ones like let's say, for example, Apple appeared more than once, banana more than once. Beef stick, Made roma. Let's look for beef stick again. Let see. Beef stick, beef stick, beef stick, beef steak, beef steak. You can see here again. These things just once, but unique meta occur twice, tri four times and the like spot is going to give us just one of the product. Okay? So if you're looking for something that appeared only once in the list, use distinct which is under unique. But if you are looking for something that appeared more than once. But you just want to know, at least it showed, it's appeared, use unique ordinarily without adding this without adding the second and fourth option and third option. That is it for the distant