50 Supercool Tips and Tricks for Every Microsoft Excel Addict and Expert

1. Introduction to the Course: Hello and welcome to the court. Do you have the desire to make excellent? Easier for yourself. I want to have one and lots of cool person. Except don't only by export. What? The standoff hasn't been exposed. I read a book before work for anything and everything except want to boost your productivity and take corrective gives the for your level of equipment. Save a lot of time on Get more complex will be short time if the answer to any of these questions is yes. And this is just the right course for you. The report on 50 Secret Tips and Tricks for every Microsoft Excel what you learn in this excess amazing barber centric that I have picked up for the past 50 year off Using Exit, the court has 50 easy tricks, shortcuts and tax that would put you on the part off becoming and excess for you. You will learn the really cool tricks like sparked. Look up what he look. A picture scroll locking, self auto updating drop down. I think, anytime drop down dependent drop down and many more tricks are only known by Excel power users on exports. For your convenience, the court is divided Like every lecture towers five major gives tricks in except along with the court. There is also a resort fight that you can down on using for your practice. That is so five has a wonderful index. You can click on any of the topics that you want to look on you with the respective she that you can use for your practice. So what are you reading for? Be the go to person at work on We looked at by your beliefs and balls as an exa goo. I'm waiting to see you inside the court. 2. Tips and Tricks 1 - 5: Hey, welcome back. Now many a times in Excel we have to hide a particle of worship and we all know that the quickest way to do that is right. Click and then click on high. But the only drawback over your is if anybody wants to have a look at the video file, it's really easy. All the person has to do is rightly simply gone on high, and he would be able to see the list of all the video files were on. All he has to do is click on that, didn't fight and click on OK, on the file gets unheeded. But let's say there is a confidential document on there is a reference walk shoot that you want to hide permanently on even when somebody clicks on on high. This particular walk ship is not visible. So then how do you do that? So this is where the very hidden function off Microsoft Excel guns highly. So how do you do that? All you have to do is this right Click on the world sheet that you want to hide, click on bugle and oil. I have this particular fight so you will see the list of all the world shoots within that particular file over here. So I want to hide this particular bush very hidden. So what? I'm gonna do this? I would highlight this and then click on fo This will bring up the properties will go away you. And if I go to the bottom or this particular menu over here, I see this option. Korda's visible. Okay, What I'm going to do is I'm just gonna click on this particular optional you and you can see that there is a drop down away. If I click on this drop down, there are three options visible it I'm very hidden on admirably gone very hidden on. Then I will close this. And as you can see now, my file is here in a way. And even if I right click and click on on High, I do not see that particular file or you OK, so it's completely hit it. And just in case, if I want to go ahead and on high that file, then what I will have to do is I will have to rightly look on view once again goto that particular file which is very heated Okay, Come down to this menu, aware, rolled out and tiller visible. And then you can close on, As you can see by a very hidden file is now visiting. So this is how you can use the very hidden function in Microsoft Excel. Let us go to the next person you go. So now we all know about look up tables. But look up. People have some limitations. Let us understand that. So what I have over you is a set of data. But I have the name of the employees on the number of seats done by each of examples on what I have in my second table is a list off some of these employees. And I want the respective sales figures in this particular says so very simple. I would go ahead and use of you. Look up. Formula on, that should give me the required out. So that is what I'm doing away you. Okay, so my data is that this particular data set from selecting it on my data is in calling number to give. It was in reference. And then I will close the bracket, and then I will just please these rules by pressing F four and I will drive this particular problem. But if you observe the output very carefully in many of the cells, I have got a endeavor. So let's look at the post. So you I'm trying to get the same speakers for Captain America, but I will enter week civility. Have a rose for Batman, a woman on being like. And why is that? If you opt out of this very carefully, you will optimum that this is happening because I have made some spelling the states for you. So I have spread Captain America incorrectly are still bad man incorrectly. A woman has been spared indirectly, and so is real life on Microsoft. Dixon has this limitation when you're using the lookups. If you reference sheets have the names sparing differently, you'll be, Look up will not picking. And this is one problem that happens in many large data sets were two different refugees will have the names spell it differently. Quasi. Look up is something that comes very handy and helps us solve these problems. So let's look at how do we use a body so use a party Luca Buster, for what I'm gonna do is encourage Post Read this entire later. And now, if you observe in my excel worksheet I have this pack orders open. Now, you may be wondering How do I get this particular tab in my excel? Basically, just go to Google and type But there you go the concert and this one pop up the first option over year down Nobody look up. I didn for excel from Microsoft officials. When you click on this it without you this particular website which is my consulting download center All you have to do is this download this added from you on install it in your system and then you will have just restart your Microsoft Excel and you will see that your Microsoft Excel also has a fuzzy you go. All the instructions about the installation have been given on this particular website, so you can follow that once was he Look up has been installed on it is available on your cheeks. You can click on the body, look up and then click on this particular if you're although it is asking me a reference for some kind of a table. Okay, I don't have any neighbors over here, but just to give you a heads up quasi hook up with you did with data with infrastructure in a form of a table compostable. When I will have to do is I will have to select my data on I don't have begun work this into a table. So for that what? I'm gonna do this every select the data click on insert and then we're gon Davis injured that you give this particular option. My table has traders. And then you got similarly whatever. Do it. I will select this particular taking on. I will once again go to insert and then pick on people. And then we got okay. Now what I'm gonna do is I'm gonna name my so whatever I do is I will select my table over your work with the design tab. And I want all my table as even one, okay. And I will go to my neighbors do on. I would select my second table, both of the design tab, and I will call this as people. Okay, So I have named the mighty. So now what I can do is I can give the reference off my people in this fuzzy look up brought down so well, you know, what I would do is have a select table one as my left table on your I will select able to as my second on. If you look at the bottom of the screen or year, there is something called other similarity special. Let me put put the similarity to a short one on I would select this party said I think now , if you are so, I have got the output in this particular passion. But I still haven't bought any reference for Captain America in my favor. Why? Because I have kept the similarity threshold at 101st. Now then it's strike Reducing the similarity, Tracy. Okay, because there are errors. My similarity will not be hundreds. Let me try pulling down this similarity. Who may be when he puts it on? I would select the cell again, and then I would people. Now, if you opt, though it is telling me that Captain America, which has been spared like this in my table one this spell like this in my favorite with 97% similar. And similarly, if you open up a woman has been spelled with a U in my table. One part with the W in my table do because 94% abuse okay, If I go ahead and use this further, it may give us more hetero. So changing the pressure off the similarity will give us the necessary up with the fuzzy. So if you off the similarly for green lantern also, it has given a sent out with 95% similarly. So this is how does he look up can come very handy especially if you're handling large data set where the names have been spelled differently on we look up is returning it. Let's move to our next making the decks invisible Now you miss it importance or difficult? Noble. All I can do is just select the particular scent on. I may just change the point color to white on I may change the same color White on my data is invisible. But if you observe the data is not in visiting, it is still there. And if anybody just goes here and changes the form color to black, even be able to view this particular So how big this entire data invisible basin. All I have to do is just select this entire data that I want to make invisible right click on it. And then I've been gone for that says, Come down with this option, which is custom on under the type that it says general, this select on Beat it on, replace it with three semi cools, let me called semi colon and then click on. And now if you Although this entire data is now in visit even if I try changing the form color, their data will not be. This is if I just want to make this data visiting again. I would just like these cells, right click on it before my sense. And I would just believe this. And then it goes on my checks. Is that so? This is how you can make the text invisible. Microsoft Excel. Let's will want to our next using double the local. Now we're all familiar with using the vehicle. But if you know being a cup has a limitation whenever you're giving a reference in beatable , the reference data has to be in your post column. Okay, So what I mean by that is if I want the name of the employees based upon the i d. Then I d have to be in my post called. If I move, I'll be from column 1 may be calling to our fallen three. Then we look up, will not function. So what? I'm trying to go over your is. I have these four papers on my first table Has ID's names on each on my second people has a name Saturday on location. So what I'm trying to do over your is if I change the idea over you I want, except to give me the salary off that respective person on the location off that respected person. Well, so let's look at employee number 1010 borders role on. If I said in this particular table, I find Rob away, You and Rob salaries do. 17 which is being captured on his location, is called data, which has been captured. That's right. Changing this on 107 is Fred. Fred is in the fifth row. In my able to do on his salary is 346 to 4, which is being captured. Over here on his location is robot, which has being captured. So how do I get this. So I'm gonna select these Header go. William, I wouldn't be step. And what I'm gonna use over you is the idea. So let's select one deal. He has a record, and what I want over you is First of all, I want the name of the person who has any number one you and put back. What I would do is I'm going to use the we look up on. My reference is based upon i e So I'm gonna select this Particular said give a coma my date eyes in this particular data set. So I will select this entire day. Does it give a coma on the names off these employees is in my problem number two. So I'm gonna give the number to give a coma on. I want exact match. Fine. Go to select CEO and I will close this. So now I know that one day or pre is sunny If I change it to 10 for its changing the name now, remember to do this. Here is where I didn't make use off my paper on based upon the names, I would try to find the salad e on the location off my employees. So what? I'm going to do this. I'm going to use a double. We look up all. So what I'm gonna say is equal. Look up. Open brackets. Now, I want the reference on the basis of the names on this we look upon, you know, will you is gonna give me my knee. So this will become my reference. I will give up Goma on my data. Isn't this particularly? Doesn't. So I'm gonna select this. Give a coma on my salary is in calling number two months. I was, like, on I need a exact mike Stumbled with type A zeal. And then I will include the bracket. So 10 food is Raul, and it's at least 2117 And I have God required How people. Now what I'm going to do is I will copy the CME formula, select this particular said, and I haven't beast. And now my location isn't column number three. So all I have to do is just change the reference from column number to call him number three on it. And now, if I change the employee, I d over a year. I get the salary on the location based upon the name my people. So this is how you use a double? We look up formula, let us move to our next match and index. Now, as we discussed in a previous be look up has a limitation. Because whatever reference we are giving for running off well, IPO has to be in our first follow. If it all the data is not in the post, follow we look up, please. So now, for example, based upon product, I d. I want the name of the product are probably I wonder the trade off that belief in a point. If I changes toe I can name, I'm getting the name of the border. And if I take this toe read, I get the trade off. That particular but my brother is in Poland number. If I try to find out this particular outward using of you, look up formula, I won't be able to do that. To get the required output, I may have to shift my product. I need to call him number one and then I will be able to use a real form and especially in these type of scenarios it is the imagine index function that comes Kaindi. So that is understand, How do we use the match? Any export. So what I'm gonna do is I'm gonna select this entire data on put it on a blank. Except she Similarly, I will copy this and basically what? You Okay, so now my product, Ivy, isn't this particular location for you? So whatever I do is I will create a small drop down on to know that whatever do is I will select this particular same click on data tab. Could get a validation select list. Definitely gone sores. And I will select this Di vita. So I have the list of the products we and I want to know based upon the part of I d. I got the name of the product. Order it off the park, so whatever to do it, I will select this particular Celje once again. Click on data, the corn data validation. Look on list and oil you every time I need drama and then you got So I have two options over here. Okay, so I have the item. Me and I have the report now, based upon my selection off the product on better. I still agree. Or I tell me I wonder output over here. Okay, so what I what? I would do it like this. Pretty good said and I would say is equal this particular value. Then I will use the on function give up double boots face is on, then give double good. So now if I select item name it is I mean is on if I select Great, it is just for my this once again. And now I need the necessary output away. And this is where I will use the match and export. So what I'm gonna do over you this I use the match function on my function both legs match is asking me for a look up that so my look of value is over a year and then it is asking me where is my look up? So my look up at it is Oh, you're Andi. Then it asked me Match that So I'm going to say exact and then I So now what it is sending me is broader. B 001 is at position one in this particular if I change this to maybe brought a full. It's telling me that product, the 00 pool is at the foot position in this entire. Now let us go ahead and find out the match for this particular selection. Okay, So I thought I will select. Then I come name or I will select lead. So now what I'm gonna do is I'm going to select a match country once again on I've been saying my look of value is this give a comma on? I wanted to find the value in this particular okay, because my data editors are horizontally. Please. I'm going to select this entire horizontal rain, give a coma, and then once again, I would say exact match on, then lose it. So now what it is telling me is ready is in call of number three. If I go ahead and select item name, it's telling me that I mean, isn't calling number one okay with reference to my and now what I want over your is I want the item name off. Part of Easy Does your fool in this Particular said So how will I get on this? Is there? I will use the index, so I'm gonna say index open brackets. Now it is asking me that. No, that is my Eddie. This is my entire at. Okay. So my entire data said, except the headers is my entire. So I will give that as a reference. And now it is asking me what is the role number? So roll number is something that it has to pick up from this particular Okay, people is in row number four in my head. So I give this as a reference, give a coma. And now it is asking me what is the problem number? All our number is something that it should pick up from this particular about. Because my item name on my reads are nothing but my Collins. So I will just blow this I So now what it is telling me is B 004 item Name is morning. Okay, let's look at our table. Yes, Easier video four is morning. If I go ahead and change the story, it's telling me that the rain is 1 to 40 on. As you can see, this is exactly matching the target. If I change the product from your maybe he's evil view of six. It says the rate is 14 by beetle. And if I changed it so item name. It's telling me either name his policy. So this is how you use a match and index function? I hope you enjoy this lecture on. I will see you in the next one. 3. Tips and Tricks 6 - 10: Hey, welcome back now. In our previous lectures, we were trying to understand the limitations off the we look up. As therefore, we were the head on down the index on my now building further on die. Let us look at this. One more condition. So I have the name off Salesman, will you and I have the data for your monthly sees from January to December? So be that the names off my salesman And these are the sales figures that they have managed to achieve four respective months. Now what I want to do away you is if it'll I select the name off a particular salesmen in this particular drop down. So let's settle. It brought you and I select a body with a month. That's a September. Then I want my V Look up toe, pick up the value for Roger for the month of September. Okay, so if I highlight this and I highlight this particular room, you will see that he had managed to achieve 8784 seals in the month of September. And this is what is getting captured or you. Now this is something that you cannot achieve using the We look up formula alone and therefore Oh, yeah, What we will do is leaving use a combination off vehicle as well as the match. So let us understand how we do that. So what? I'm gonna do this number copy this entire or data onto a blank excel. She okay? And now whatever do is have a copy. These headers and I've been place it all. Okay, so now you I need the list off my ces. So whatever you do this, I will click on this. Particular said, Well done. Record data validation. Click on list and oil. I will select the source as this particular okay on it. So now I have a drop down off all the names off. I see it's middleweight. And similarly, when I need a way, you is the mr. So Now, one second I will see like this. Particular said Gandhi thought that goto data validation, select list going sores. And now I will select this entire arrange off my well, months on hit as a. So now will you. I have a list of all the 23 months. Okay, so now let us select what you see is let's see Edward on. Let's select a particular month. That's you. So what I need over your is the revenue that Edward has managed to achieve for the month of do so. Oh, yeah. What I'm going to do is I'm gonna use a V look up formula. Okay, But before that, let us understand that Oh, year, there are four different combinations. One is the role. And what is that column on? We have to find the intersection off the sea. I'm for that. We will have to modify our We look up for me a little, so let us start writing the poem. So for Malays, you look up open brackets. I wonder data for Edward. So I'm going to select this particular scent as the reference Give a coma on. Then I will select my entire marriage. Okay, because my daytime is in this pretty good at it. So I was like this entirely, daddy, and then every give a coma, and now it is asking me that. Call it in. Next number. Okay. On to give the call of index number. This is where I will use the match function. So what? I would do is I'm gonna say it match open brackets. And now it is asking me what is the look of value that I want to look out for. And I'm going to say I want to look aboard this. But you know what? Dead, I would give a coma. Then it is asking me where is my look up at it now? I have to find the month in this particular. So I'm gonna select that a diary, you a coma. And then I was a exact match. I haven't closed the brackets, but I have to remember that there is one additional sent over a year. It is also a part off this particular addict because when we do, if we look up, we look up with consider this particular colors as call him number one. So what we have to do is we just need to add an any still one or you give a coma and then give exact match, and then we can close the on it. So now if you're the Edward or the month off you over a year has made 5269 in C is heavily and this is what is getting captured over you. Now, if I've changed the name off my since one, that's how I select general. And I changed the month of October, General, for the month off Cooper has done. See, it's about 13,360 and this is what is getting captured, boy. So this is how you can use a combination off we look up on match, get their 11 output out of your deed. Us. Let's move on to our next Smart. Really? Now that is understand? What do I mean by spot you? So what I have for you is a set of data. Okay on. Oh, you're if I select a particular broader. Okay, so let's say I said next product be zeros. You know, pipe, I want the output for brother B 00 fights. I want to know the item name. I want to know the rate for getting on board. So now you will say that. OK? Running of you look up will suffice the purpose. But that is a gradual you. Now let us understand. What is that? So what I would do is have a first coffee. This entire data onto a black exist. And now what I would do is have just copy these headers, aware I place it will you. Now if I want the list off my products away, you Let's say 55 p 001 I should get the respective needs. The quantity I thought so. Now to get the item name, whatever do is I would give the popular as well open bracket. I would give this, but even a part of ideas that actors on Then I will select this entire Addy. I will freeze it using f four give a coma on my item. Name is in college number two, So I'm gonna select two. Oh, my uh So I get that item name over you, But can I go ahead and drive this formula to get the lead or deep? I'm told. No. See, I'm getting an editor away. Why? Because my reference is changing over here. Oh, you have taken the reference as well. But a year whatever is is changing. Now you would say, OK, I can freeze the reference as well. Okay, so let's say if I freeze the reference that it's all my purpose. No, it doesn't. Because now we have frozen the reference on We have frozen the retiree, so it will pick up the similar value for my entire Dari. So now the only option that I have for you is manually going on changing. Okay, so now you're it seems very easy because we have only five day does. It's okay. But what happens if the data said is true law. I cannot go ahead and manually keep on. I think these numbers. So I need some kind of speaks for And this is where the columns function comes Andi. Now, first of all, let us understand what is the columns, so Oh, yeah. If I died, Collins have open bracket, and I select this particularly and close it. It returns me a number five. Why? Because in the league that I have selected there are five different colors on. What we are going to do is we are going to make use off this particular function to get the required out put in my relook. Okay, without manually giving the Golan. So now let us learn more about the pollen. So let's say if I go ahead and by colons, open brackets I still like this as my starting point. I managed to give a polar and select the same cell as my moving. So what does it say? It It says that there is only one point. Why? Because we have selected the same starting form on the same ending point. But what we're gonna do is be able modify this formula a little. What we are going to do is even select this first be do ou. I've even please it. That is the 2nd 1 Okay, The second veto. We're not gonna be easy. And I just got a hit. Okay? And now if I drag this, I'm getting the value. S 2345 respective. Why? Because every time I'm stretching it, it is taking into consideration one additional. So you if you are the my columns have kids from beautiful to see and therefore from veto to see. Do I have to callers? If I move it further, it has taken the reins from Vito Di Do on. Therefore, I have three callers. So what we are going to do now is we are just going to use the same columns function in our vehicle. I'm leaving Ghenda. Quite so let's believe the formula that we entered a were you. And what I'm gonna do is I'm gonna play it a small drop down off the broader ideas over a year. So let's say I go toe data. You're going to get that validation. We can live sores, and I'm going to select these products, these broader babies on him. I would just highlight the cells so that they become visible. I'm over you. I will select a part of a broader I. And now what I'm gonna do is I'm gonna make use off a combination off the logo on columns function, get me the desires out. So what I'm going to say? Oh, yeah, is we look up based upon this particular value, Will you give a coma? That is, my data in my data ranges over you. And now I was just Please, my Deatherage best effort given Homa. And now I will use the columns. Okay. I'm gonna see Collins open brackets on, but it's like this first cell or your B two. Colin, we do on I will praise the post. We do. Were you by pressing F the Panchen Key And now I've just lose this. Oh, my, you know, because I want exact match and now I lose it. So now I have the part of idea. Well, and now, if I go ahead and drag this, I get the respective off. If I change the part of idea from your I get that necessary out. So this is how you can actually use a smart we look up function in Microsoft Exit to avoid manual entry off the column numbers when you have a large data set where you want to know. So the column function can come really handy. Let's move on to our next logical functions. You must have heard about logical functions when you were is or maybe at times when you might have known mathematics status. Basically, what a logical friendship does is very sick. There are traditions. Andi. Get a desire output. Certain conditions have to be so. What I've done away you is I have taken an exam. There are two conditions for a particular country. If a candidate has toe appear for elections, he has to be a graduate on. He has to be ready for years and about office better there is a country to, but in either the candidate should be glad of it all. He should be about the either. When you so all your he has fulfilled both the candy that has over your he can put any one off the conditions and therefore or you have used the and that's over a year I have used all which means if the candidate is graduate and he's 25 years off is he can appear for the election. If his age is ready for it returns the value s falls because the cage is less than 25. However, over a year he has the pulpit any one of the conditions. So I think he has to get graduate all he has to be more than 25 years old. So let's say he's a graduate. But not 25 years old. Began still appear for the on. How do you get this kind of an output is by using the logic can function in. So what I'm going to do is I will just be the leader to a blank extension, and now, to get my desire off, What I'm gonna do is I'm gonna use the add function over you on my function was like This is equal and open brackets. Okay, then it's asking me what is my first logical condition. Okay, so my first logical condition is this particular sin is equal to coma. And now it's asking me for this second logical condition. My second logical condition is this particular sin is greater than or equal to 25 and then I've been okay. Now let's test it. If I change this value to win, it returns the value U S Force if I change this value between six of us because both my conditions are getting Oh, yeah, What I'm going to do is I'm gonna use the off. So what I wanna do is have just copy this same function or you I instead of and more. Now if you observe even if he pulls this one off the conditions, the value is However, if it all, he's unknown that I do it on. He's below gratified. It means he's not satisfying any off the conditions. Then the value will be force. So this is how you used logical functions in Microsoft exit. Let's move on to our next It allowing entries with only specific. No, you're if you also I have type into needs a month on agent. But if I go ahead and try by any other name, it's returning me as editor. Why? Because I have set a condition over a year. Such that the entries that start only when the alphabet a should be allowed in this particular If I go ahead and type address its exiting your privacy, all of it it will exit. But if I go ahead and type name as heaven Similarly, in this particular range, over a year, I get only enter entries would start with ABC wanted. If I have anything else for you, it will return me up. But if I go ahead on time, ABC one people by six, it will exit so many times in. Excellent. You have to set these guys restaurant. So how do you achieve that? Very soon. Let's go ahead on copy this entire data onto a blank exit. So now that I have my raw data onto a blank except you, what I'm gonna do is I'm gonna make use off the left function. Okay, so I'm gonna say left open brackets like this. Particular eggs give a coma and now it is asking me how many characters on the left hand side should it. So I'm going to say just one. So now it has returned the value as it because the left what left function does this? It will return you the number off characters in any fix starting from the left hand side on this particular number one that I have given over you. It means that it returning one character from the left. Now what I have to do is I have to say her condition away. So my condition is gonna be it left off. This particular value is equal to E. Then returned the value as is returned the value as and then I. So now if I go ahead and type and your it is true because it is returning the value as e. But if I go ahead and cry, it will return the valuables force because the left most first character will you is be that as the conditions says, it should return a value us to leave. The first character is not similarly. What I can do is I can set the same condition. So what I want is I want to attack the values only different starts with a B C 12 between the left. Six characters Sure always be ABC one. So what I'm gonna do is I would first use the next functional. You select the characters. I don't say Give me six characters, Brandan. So it returns. The value s gave easy one. And now over here. What I'm gonna do is I would act up if one So if the value off left six characters is equal , ABC one door, then returned the value U S. Hence Oh, here. Now if I go ahead and buy B A C one in returns of Oliver's Falls if I go ahead and type A B C 12345 it will not. What I have to do is I have to ensure that in this entire lead it should fulfill this particular Spanish. So what? You're characters that start with a should be allowed on oil. You The text should be allowed only if it starts with easy. So how do I do that? Very simple. What I want to do is I'm gonna click over here. I will select this entire formula, and I've been for me using controls. Okay. And now what? I'm gonna do this. This is a diary. Google Data Validation. You're gon gusted and all your average this press control and then you. Now, if I go ahead and type anything over, you will return. But if I type anything that starts with game, it will accept about similarly. What I'm gonna do is I'm gonna select this entire formula, and then I'm gonna like this entire. You got data, you have allegations. You go on custom and in the formula, this piece, the formula, and be gone. And now let us drive. Testing it, anything that stops good A, B, C. 123 So this is how you can set up a condition that you can allow entries that starts with you. Let's move on to our next custom condition for my Now you must have heard about the term conditional formatting based upon certain conditions. You can go ahead on the highlight of particular cell are probably a day, Danny, but oh, yeah, we're able to learn is custom conditions might know what I mean by custom conditions for my fingers. I have a drop down on all the products that are listed a were you on? Every time I change the product, human, see that in this particular table? Only that brought up get highlight. However, in this particular David, the entire rule showing that particular product will get highlight. So if I were changing too hot, right, you will see that in this particular table. We'll need a cell that sense. Heart drivers getting highlight. But in this particular tables, all the rules which out are hard drive entries. I'm getting highlight. So how do we get this kind of any effect? It might result. So to do that what I've done is I mean, copy this entire raw data onto a blank exit. So what we have all your is the force paper where we will be highlighting leader particular cells and this is the second table. Then it will be highlighted. This is our set off products that we have all the item needs. So what I'm gonna do is Oh, yeah, I'm gonna create us both dropped out. So you haven't select this particular sin? Legal data for data validation. Click on list in the source. Select these particular brothers and it. So now I have the list of all the products that I have a way. And now what I wanted. Let's say if I select a boat in this particular cell, I want only the keyboards to be highlighted. There, as in this particular cell, I want all the rules holding. I turned him as people get. So what I want to do is I'm gonna select this particular date arranged for you. And then I would go home that click on a condition for my team and then click on New on under new. I'm going to select this particular option over. Use a formula to data mine grid cells to former. And now, over a year, I'm going to give the read. So Well, you're gonna flip on this up, arrow. I want to select this post sent over you, which is the fight. Okay. When I select this particular cell, you will observe that the dollar sign comes by people dollar. So I will be the dollar sign from you and I would be the dollars from and then I would say this is equal. Do this particular said on based upon this particular condition, I want maybe a blue she So I will click one and then you can. Okay, so now if you observe if I cynic a party full of broader, only that particular said towards this value is getting highlight. Now, over. You know, what we need to do is we need to highlight the entire. So what I want to do is I'm gonna see, like, this entire Didot said, Oh, you then I was because the home that click on can be still for my thing. And then people knew once again I will select the option or your used off formula to beat the mind with cells to call my click on the up arrow. Andi, select this particular sent over. Okay, on what I will do is decibel Billy this second dollar sign away. I'm gonna keep the post one. What I'm going to do you the 2nd 1 I would say eat will do this particular value. And then we're going for my on based upon satisfying the condition I want green chain or they die and I really got So now if I go ahead and change the products over your in this particular table, you will off the that the leader cell gets highlighted. There, as in this particular table, the entire crew gets highlight. So this is how you can use custom conditional for my to format your data said based upon certain. Crazy. I hope you enjoy this lecture. And I didn't see you in the next one. 4. Tips and Tricks 11 - 15: Hey, welcome back. Now, in this particular lecture, what we're gonna do is showing and hiding in a based upon dropped off. So what I mean by that? So you're I have a drug dog on this drop down. Had the name or all the products that are listed out in this particular beat us. So let's see what happens when I select one of the products over here. So I selected people on the woman is selective, or you will, although that the other products have become invisible. If I, jane the product will get that particular part of is visible and everything as just vanishes from this lead us. So how did we achieve this? Using my consult takes it. So let's understand that. So, first of all, what? We will do this as usual. We really got me this entire data set on. We've been pasted on a blind exemption. Okay, so now I have copied this entire data were on. These are the products that are listed in this particular I don't need. I have four products over here on the products have brought up babies. They have been able rates on that. Is that sales figures over there. And what I want is I want a drop down away. You on whatever I don't I select in that particular drop down. I want only that bodyguard begin to be visible in my data set, and everything else should just become in visit. So, first of all, what I will do is having create dropped out. So what I'm gonna do is I'm gonna go select this particular does it over. You and I would also I show all this. Yes. Okay, Andi, let me select this particular colour because the others where I will create their dropped out. I was like on the day that both who did the validation click on a list on I would be a condom up at all. Will you on? I would select this particular data as my data source. So now you're you will. Although that I have all the items listed as dropped out. I'm the moment I select anyone off them. I want only that particular item grow to be visible and everything else batch. So you I have selected keyboard right now. So now what I'm going to do is I'm going to select all the other roads. Except for the ones where I have people as the fight. So I'm selecting these rules. I've been restaurant job. I haven't selectv easels. Okay, so I have left only those rose there. The item name is people. And now you know what I'm going to do is with these particular rules highlighted, I'm gonna click on the home tab. I haven't got the conditional formatting and I really gone new on. Then I will say, like this option. Use a formula to data mine. It says Former. On what? I'm going to see where your is. If this particular cell is equal, double courts on average type keyboard dead. I want to former the other says. And how would a format that I would click on this particular tab away? You It says number. Have a click on custom. I will select the general over here, and I will believe it. And then I've been tied. Please. Semi colons over here. Semi colon seven cold. Cynical. If you remember in our first lecture, we use this to make the text invisible. So we are using the same feature over and I think you're gonna hold and keep on. Okay, one more. Okay. So the moment I select a bow on leaky borders visible over similarly, what I'm gonna do is I'm gonna select hard, right? And then I will highlight all the other Rose where item name is not. Okay. So I'm gonna best the control key and I'm selecting all those roles. And once again, I will go to conditional formatting. Look on new look on, use a formula. Then what I'm gonna do is I'm gonna use the formula that this said is equal to hard. Right, Then format called the number tab. Click on custom Remove the general and I three sem equals going. Okay, I got one more. I would do the same for monitor. Select all cells where the rule does not contain monitor gone conditional form. I think the core new rules, like use a formula to beat the mind. It says to format and then give the formula where this cell is equal toe monitor. Then for my look on the number dad custom, we moved general on dive three sem equals Come on. Similarly, we can set the rules for memory. So what I'm gonna do is I'm Melissa. Like all the roads which does not have memory indicted need using the control key. I'm selecting all the Rosalia. And now, once again, I will sleep on condition for Marty. Click on New Goto. Use a formula on Goya once again. I will say if this particular cell is equal to Mehmeti, then click on former but a custom. Delete the general on by sending Golan's any seven. Okay, and then we're going OK, one more. So now, based upon my selection over your I will be a vacancy on the does which has the same item name in my drop down, if at all. I want to see all the items aware. I can't look unsure on it will show me all the items away. So this is how we can show on high data based upon a drop down. I hope you enjoyed this trick. Let's move on to the next day. Locking scrolling area. So now if I get this particular she I can go ahead on move my mouth anywhere on this particular issue. Okay? I can move to the left right now anywhere but let us see in a particular bolshy. I want to lock a particular schooling media. It means that if it all I looked this particular area over a year, any user off this particular fight will not be able to move the mouse out off this particular here. The movement will be restricted. So how can we achieve that very soon? Now we have lost the area from B six do ej 80. Okay, but this is the area that we want to look on. You want to restrict the movement off the mouth within this particular area. So whatever you do is I want to see, like this particular works. You right. Click on it. Legal vehicle. Once I'm into the Vidia Environment, all I need to do is I need to select this particular sheet. Oh yeah, And then I will press Air Force, which will take me to the properties manually. And now what I need to do is scroll down to this particular option, but is growing area. And right now it is lying. And in our chief, we want to lock the scrolling area from B six to EJ 80. So what I'm gonna do is I'm gonna manually type this rate with B six give up, call it on by edge getting and then just loses. And now if I try to move my mouth, my mouth will only move it in this particular region. I would be able to move my mouth outside this particular region. So this is how we can go ahead on lock scrolling area in Microsoft Excel. Let's move on to the next. Now, if you get this particular watching, where you're my data is only in this particularly okay, so I do not need the rest of the world. She So what I have done is I have this plane only need this particular area. But I have been doing any sort of manual work and I do not need the list off the work. So how I achieve this outward display only limited roads at columns in my wash very said, What I'm gonna do is I'm really just copy this entire data onto a blank. Except she And now that I only want to see these rows and columns in my world ship over your whatever it is, I'm going to select this particular for them over here and then I will breast going drone ship and the right arrow key against control. Shift on light. And that will select all the region on the right hand side off by walking here on, all I have to do is just right. Click Someday, will you Antley gone I and similarly, I did not mean any region below this particular No. So what I'm gonna do is I'm gonna select this particular William once again pressed on drone ship under Donald Roky that would select all the region which is below that particular room. Right? Click on it and click on high. So now you will be only able to work with this particular your mouth will not move anywhere outside this. Okay, so this is how you can go ahead. And this lady Liberty rules and orders. Couple this step is very easy, but there are times when you need the steps. Andi to make your work is let's move on to the next. Now, many times, what happens is whenever we try to great a particular fight, let's say by people file and then likely conference. So that will create a kind of a print preview. For now, let's say I did not want to play at this point, so I go back. But when I go back, you will also that there are beings brings that happy created on your bush. Okay, as you can see that our page breaks over you will, your were. And now you want to remove them. So how would you do that in my face? Very simple. Only have to do is just elected. Watch you that you have created of each race Just right Click on it Legal Bugle Once again it will take you to the vv environment. Select the work she better page breaks have bean And now with this particular worksheet selected press f this will take you Judah Properties menu And now what? You need to do it This come down with this particular open It is the sleeping leaves and we are it is true. All I need to do is just changing Do falls I was and as you can see, the page breaks have bean removed from my wash. Let's move on to our next it five important brain functions no many a times. It does happen that you have a lot, Sepulveda But you only need maybe the post full characters off the input or maybe the last four characters and many a times it might be also possible that you want, except to create passports based upon certain criteria. Maybe something like 1st 4 letters off your name on the last four digits off your phone number. So you're the left, right? And bill function can come very handy that designed this time these functions one by one. So, yeah, I have certain data on as an output. I only want the 1st 4 characters off the entire data. So what I'm gonna do is I'm gonna use the form below. We it is quitting. Let open brackets. Then it is asking me to select the text. And then it will ask me How many characters do I won't. So I would say, I want the plus five characters Rome my entire bi does and I wouldn't lose the So now if you opt So it has returned the 1st 5 characters from all the gators why it has only returned home over you because there are two spaces after the word Tom on before hangs. There are two spaces over on Microsoft. Excel will also consider space as a guarantee, so we can always use a left function to get the left side characters off the street. Now let us use the right bunch. Not similarly. Let's say I want the last three characters off my fixed. So once again I would die, right? Select the text. Andi. I will say the number of characters that I want, his three and then I will close this. So now once again, what it has done is it has picked up the last three characters off my street. And once again it has really picked up t and s because if I cilic this particular string over your will, also that there is another space after Tom Hanks, that is a space and therefore it has considered that space as the forget it. Now let us move on to the next screen function, which is now what it does. Is it written certain middle characters in any off this drinks that you have? So now let us try using. So we were type the formula as this equal made open brackets. Then it will ask me to select that text. So I'm gonna select these particular texts. Give a coma, and then it will ask me the starting number. So what it is asking me is, what is the stocking number? So should I start from the Your character? Both won for the 5th 1 So let's say I tell except start from the poor guy. And then it will ask me if it starts from the poor character. How many characters should it for law? So I say, Forget it. And then I would lose the brackets and a And, as you can see in all my data sets over a year, it has started from the poor character on picked Up following old characters in my street, the L D and order legs. Late function will return the number off characters on the ground off characters in a particular spring. So it's very simple, I thought is equal to. Then I select the particular cell. It was the bracket on. If I drive it, it will tell me how many characters are dead in each of my street. Okay, if you want, you can also go ahead and believe it. But just remember, every space is also considered as a additional to this is one function that can go really handy. Especially when the data is all investor. What do I mean by a messed up did? So let's say I have the names over you. So I have my policy on after my first name. There are 1234 and five spaces, in fact, six pieces. Similarly, if I look at this particular string over you after the work, Tom, there are 12 three spaces on cleaning This type of data can be a very cover seuin past. What I want as an output is a force me then a space and then it last me. And this is where the dream function can come very highly. So let us understand How were you so I would say sequin Jim open brackets. And then it will ask me what is the fix that you want acceptable? So I was just selected Andi. And if I drive this, you can see that over a year now it has removed all this piece If I tried how the leg off this particular strain over your it is needed dunning the value as nine because it has removed three originals pieces. Similarly, if I used the same function or your You will love, though, that the six spaces have bean removed on, therefore my stream length, which was 19. As bean renews your partner. So basically water brain function does is cleans up your data on removes. Any unnecessary peace is within your people. So this is about the left right, made late on the drink that gives you all the left side characters, right. We'll give you the characters on the right side of the stream. May will give you the strength bed will give you the characters in the middle of the street . Link will give you the length of the stream on a trip will help you eliminate any Arista's spaces on cleaning up. I hope you enjoy this lecture on I will see you in the next one. 5. Tips and Tricks 16 -20: Hey, welcome back now in this particular lecture of even love how to prevent duplicate injuries in my consult. Except so what? I have a Well, you are to daytime TV's. And as you can see, I have 1/3 duplicate entries in this particular at our table, and I can keep on adding the sea on it will accept, however, in this particular data table, if I go ahead and try to enter the same value, it gives me a better. And it says, enter a unique numerous quiet. So let's say if I type do 35 it will accept it. What if I type 2 to 5 again? It will not accept a duplicate. And similarly, if I like any other, even or you, it will accept it. But if I live to see you again, I will get this same. It is enter unique. But so many a times in excel. We need to set these kind of restrictions to avoid duplicate entries in our exceptions. So how do we achieve that? Using Microsoft Excel. So what? I'm going to do this. I'm gonna believe this data from you. So what? I'm going to live with a selected entirely that and I would be going the data data validation and over here in the drop down and select that guts demotion And all year I'm going to write the following punch. The function goes like this. I don't get it. I've been open brackets. I will select this entire days. Then I will give a coma and then I will select the host cell over you. I will close the brackets and then I will see less than go. So basically what? I'm trying to go over you, IHS, I'm telling except that it's your only accept a unique while And whenever a duplicate value is entered it should give me up at a and then with a duplicate value, go if the value is repeated wise and therefore I have selected the number two on my condition is for this particular range the count off any value that I entered will you should be less than I will please this range using the F or Function t and then every play gone. Okay, Now if I try to enter any value over a year, it was accepted for the four stuff 25. Do you read it. It will give me up. And I would go ahead and put the same formula over your as well. So now what I'm gonna do away of this, I will once again select this entire lead. We'll get that bad. Nobody. The validation. Click on custom on once again, every title formula, will you? Just down here open brackets. I would select this entire the give up coma. And then I would select the four cell over year lows, the bracket. And I would say that's that. No. Okay, I will once again use the F four on Chunky, and I've been pleased this then. So now if I type a particular number were it was accepted for the first time. What if I read the number it will not take, but that is a gradually I want only numeric characters who defeated in this particularly 25 feet and somebody could carry it. Is accepting why? Because it is not duplicate. So I want to say another room over you which will ensure that I insert only numeric values . So for that, what I'm gonna do is once again, I will select this entirely. What about my date that Dad. Well, today the validation. I didn't modify my function a little bit. So what I'm gonna do is every site and open brackets and I will set one more condition. So basically, on an function can be you if you want to set up by people criteria. So my first criteria is definitely I do not want any duplicate values. And now I will give my second condition on my second condition Is that only numerical values should be allowed in that particular. And for that I'm gonna make use of that is number the basically what it is Number one chamber Lewis with ensure that only numerical values are entered in that particular be so I would say like this was saying No, will you and I will blow the brackets on hit. And now if I try to go ahead and enter any other non numerical value u, it will go and I don't give me it on at the same time with pain. Sure. Black duplicate values are not allowed in this particularly. So This is how you can go ahead and prevent Do get entries in Microsoft. Except let's more to our next separating numbers from text. Now many attended does happen that chemicals. I'll find you very bitter. It Microsoft takes it. And sometimes need arises that we have to separate the alphabets on the numbers from each other. So in that case, how do you do that? Especially if you have a long list of. So this is where the get number function can come very high. So let's say if I be this year and I only want the numerical values appear away you I've been making is off the get number. Okay, so I would just double click on. I will select this particular cell, lose it. And on if I drive this even although that except has picked up only the numerical values from this particularly does it? I'm is showing it over. You basically wanted his lingers. It is separating the numbers from the X. Now achieved this. You will have to make up your modifications in Your Excellency. So let us understand. How do we do that? So what I'm gonna do is I'm gonna pop eat this entire data onto a blank excess. You So I had my data away. You. And if I try to use the get number function over you see that the function is not appeared . Why? Because we have to create this function using the vehicle. So what you gonna do over you is I have already given the function in this extra shoot. All you have to do is just copy this entire function. Okay, So what I would do is I'm gonna call me this. Let's Petrosea both my blank extension or your And so you know, what I would do is I've been right. Click on the worksheet tab, Nicole View cold. And then once I'm in the visual basic environment, I will pick on insert look on more you. And then I've based this particular entire school in this particular sheet over here and now I've And now if I try to use the get number function, it will appear over by because we have already created more do for this. And now I can just go ahead and use the get number function. Select the cell from there. I want to separate the numbers from the takes. And then if I drag this party Philip formula, you will off the that. The numbers are getting separated from there that this is how you can go ahead and separate numbers from eggs. Let's move on to our next it Did you know that you could even run a picture? Look up in my consult. Texas. I'm sure you must have heard about the local, but this is something that very few people know. So what I have for you is I have four different countries, and I have the perspective lags, Louis. And what I've done over you is I have created a drop down on as and when I changed the name of the country, the respective flags off those countries appear in this particular said. So this is something that we call it a victory. The letters land. How do we see a picture look up using micros optics? So what I'm doing is I'm gonna copied this raw data onto a blank except okay, so I have copied the names of the country, and I have a copy the flags off those particular countries just next to each other. And now what I'm gonna do is first of all, I would need a drop down. Okay, I drop down off the gun. So let me select this particular person or you going data and then I will go to beat a validation. Look on list. Look on this up, Adam, and let me select this as the source. Okay, So this is that list of the country and I will hit Painter. Now, I have the list of the countries over you. Okay, let me just format this. Particular said. So what I'm gonna do is I'm just like this. Pretty little said we're going for my friends, and I didn't Boeremag, okay? We just enlarged this particular cell over. Yeah, And now what I'm gonna do is I'm gonna select this. Particular said OK? Just remember, I'm not selecting an image. I'm selecting the entire sent over You okay on every pest control Z copy. This said I will come to this particular sent over you rightly border based special and I will select this particular link picked and I would like for you. Okay, so now I have India and I have the Indian flag over here. But if I change the country, the respective mags are not appearing as of now. So we need to make up your changes over And if you remember, we learnt the match and index function in our previous lecture. And that is what is gonna come handy over you. So what I'm gonna do ou this anger make use off the match and index posh. So what I would do over your is I've been first working the form without staff. And then I've been click on Name Man. And what I'm gonna do is I'm gonna create a new name for you. So let's say I call this particular name Country fly. Okay, on all year is where I will use the index on match funds. So let's look carefully how we use the index sent match. So I'm going to say index open brackets and from very life fetch the flags. I'm gonna face the flags from this particular okay. And I would give up Goma and then I will use the match function open brackets. What do I want to match? I want to match the flag. Wilder name of the country. Then I will give a coma. And there is the list off the names off the country. It is over a year, so it will match this particular leave from this particular list. Accordingly, it will give me a number on. Then they index function will patch the respective country life. Okay. And I will give a drama. I'm zero for exact match, and now I will lose the back on it. Okay, so now you can also that I have created a need. The country black Now I've been fueled. And now what I'm gonna do is I'm gonna select this particular said you and oil you where it says c three. OK, because I have picked up the flag from cell C three. I don't need this on every type country life. Okay, you can see that it's already appearing away. Have a double click on it, and I will hear. Okay. And now if I go ahead and change the names off the country's, you will observe that the respective flags appear according. So this is how you can run a picture. Look up using Microsoft Excel. Very interesting, isn't it? Let's move on to our next auto updating drop down. Now, let's look at what exactly am I doing over you? So I have a list of products. I have created a validation table over here, which has a list of all the products. But now let's say if I go ahead and I'm a few more products to this. So let's say I charge a paper and maybe colors. Does that reflect in my validation, people over your No, it doesn't. Which means I would have to manually go ahead and mortify validation. But now let us look at this particular condition or you. So you also I have created a validation people and I have to sit down. All the I think is over here, up to writing by as everything appears in my list. But now, if I go ahead and add the job people on the colors to this particular list, let's have a look. What happens? A. Will you You can see that my drop down is automative. You getting off? So now let us know how to do this. Auto updating in your drop down using Microsoft's X. So what I'm gonna do is I'm gonna poppy this particular data world blind exists. Okay? And now that I have copied my data to the blank exception, whatever do is I will select this particularly look on inside, and then I'm gonna work. This particularly does it. It was a table. I will ensure that I keep this particular option. Okay. My table has Eder's and I really gone. Okay. And now what I'm gonna do is now is that I will be a data validation. So let's create a little relation, people. Will you click on data like a data validation? Look on this for resource. And now select this entire and hit. And now, if you although all the articles are appearing in this particular drop down list Now, let us try. I think more our chickens to this particular in this. So in our biggest example, the Allen charge paper and colors. So let us, right. I didn't that. So I I did more articles to my list. Now let us look at the marination Devi. And as you can see, the validation table as automatically being abated Tactics on colors. So this is how you can do orto abating in your drop down. Using my consult exit, all you have to do is can work your need outside tables and it gets done. That's more your next. Now let us look at this particular example over here. I have many different countries. I have quite different cities in the respective countries for you. And what I have done is I have you did to drop down the way. So I select the country. Let's see, I select Japan. And if I go, your what I see over here is the list off all the cities in Japan. If I select in yo, you according me, I will see only that Indian cities over. But this is an example off defendant dropped out and many a times in excel. You might have to go ahead and create it. Let's say you want to select a particular team on then based upon the team. You want the names of the players. So how do you plead? So once again, when I got to do is have just copy by raw data onto a blank except you. So now with my data api to a blank checks and she what I need to do over your is I need to create different drop down. Okay, so what I'm gonna do away you is, first of all, I'm going to select this particular be on. I'm gonna name my how I will go to the trouble. That's that. And I will people create from selection. And now it is giving me some options over here. So it says create names from values in the So I'm gonna select top Cruz. Why? Because my taco give me the header over you. Okay? What I want is the list of the countries on my top row has the header. So what? I'm going to select the stop You and I will click on. So now what has happened is if I go to this fall last that on I got my name Manager given also that I have created a need by the name off countries. If I click on edit, it has selected the reins from G four g six. Okay, which is my data. Similarly. Whatever do over your is I've been stilic this entire range once again work with a bomb without style. We're gonna create from selection on Goyo once again. I've been siddiq the end on the basis off dull Prue. Okay, because the names off my country's is it and obviously gone. Okay, so now what has happened is if I go to my name manager you will, although that for each of the respective countries, it has created a list. Okay? And it does give any perspective need. So now what I'm going to do over years, I'm gonna create my first drop down. I create my drop down list. Haven't sleep on the leader. No data validation. You're going list at all. You know, what I'm gonna do is f And when I click on every, it gives me a list over here. And as I had mentioned, I have created this particular name countries for the list of the countries over here. So I'm gonna select countries aware. Click on. OK, and then you got okay. One more. And now, if I go ahead and check my drop down, I have the list of the countries appearing over a year. And now what I need or you is based upon my country, it should give me a drop down off the respective cities. So we'll do that once again. I will select this particular cell photo I d. That goto data validation. Go on list. And what I'm gonna do over you this I'm gonna make use off the indirect bunch. Why? Because Whatever our foot appears in my selected list category depends upon what is the input in my select a country garden. So that is why I'm going to say like that indirect. I've been open brackets and then I will select this particular cell and I was bracket. Now, if I go ahead and check my drop down because I have selected Japan, all the Japanese cities are bearing in mind If I go ahead and changes to India, you love the all the cities in India are appearing over. Similarly, if I select Pakistan, all the Pakistani cities appear so basically, this is how you can go ahead and create dependent brought down using Microsoft. I hope you enjoyed this lecture on. I will see you in the next 6. Tips and Tricks 21 -25: Hey, welcome back. Did you know that you could delete anything in Microsoft Excel not using the people? Yes, that's possible. You can do it with a flick off your mouth. All you have to do is just select the data that you want to be. Let's see, This is the leader on this. I just want to believe this number off till Simon. I like this leader after the bucket monkey on. Once this last time appears, I will select the data and I bragging, And as you can see my guys now, let's say I want to believe the last three entries in this particular issue Sybil selected data come to the Martin. Do you see the plus sign on now back that you don't go on my d days. That's how you believe data with, um out without using people. Let's move on to our next moving self direction in Microsoft Excel. It is a before feature that every time this end of it will move in the sandwiches just below. Okay, you're Carson moves in the sandwiches below, but there are many times that you have to input the data in a horizontal select sick in my case. If I have to import John and then right manager, then so then for the item number and then see it. Okay, so I want input the data in horizontal. But how do I know that it's releasing? I want my culture to book in the right direction every time. And to achieve that, I will click on five border option. Would you advance on Do you think this option? It says After pressing, enter moved selection. Currently our default setting is said to down even changing too. Right? And then look And now let us try. Every time I pressed Enter, you can see that my curse. It is moving in the right. You can even set it to left on up, depending upon the kind of situation on the way you want to use this Your That's how you move cells in Microsoft. Let's move on to our next didn't commands using F. Microsoft Excel has an inbuilt feature. Very. It remembered the last operation that you have done. So let's see, I selected this Dita and I went ahead and four back this to a green. Now, this was the last operation that I put on my clothes off exit. Remember that. And if I want to repeat that last operation that I want to highlight this send exactly the same fashion. All I need to do is select the cell and press. Okay, let's say I want this data to be I lighted as board. Okay, So I will selected and make it boots on. I want to do the same for my for my this. Then I can just select the leader and breast. And as you can see, this data has also become so you can be any operations in Microsoft Excel using the F word . Let's move to one. I'm sure you know how to copy information from but said to another. But did you know that you could also do that using the entity? Let's say this is the data that I want to go. Usually I would press controls on then control. Now, in this case, I'm going to use that so going head of selecting the leader at rest control, see, select the sale of my choice on now it is best. And as you can see, my data is now called so you can always copied it up from one sex to another. Do Linda ended. Let's move on to our next speaking. So did you know that Microsoft Excel actually Street? Yes, that's it. Has a member feature God's desire which comes very handy, especially if you want to tell you the data that is there on the extension with the new does that you have in certain peoples. So rather than looking at the screen every time, you can ask excess toe without information on the street while you tell you with certainty coalition or so how do we do that? So let it. This is the information and I want to accept to weed out this information so that I would week access to more Andi. Then I will come down through the minds, not in the living on. Do you see this option, which is speak. Let's go down a little bit more. Yes. Yet look, Wannsee says, click on I and then because do you see this ball I got has appeared over a year and now we can use off this. We'll make that excess shoot. I'm gonna select this data and I'm gonna play this option name designation zone item number of sales. John Manager East, item one 500. So did you see how wonderful this feature? I hope you like this lecture on. I will see you in the next. 7. Tips and Tricks 26 - 30: Hey, welcome back. I'm sure you must have definitely used the salt future in except for the data in ascending order. Descending Very simple, Isn't it all you need to do it. Just select the data that you saw. Vehicle data, both thought. And today attending order descending mother. And and you're Do they get started? But have you ever come about the situation? But you have to solve the date of the next two days. And if you have to do that, how do you do that? Basing directed. It doesn't do what they thought in a horizontal passion. Click on data. But it's our And then click on options and all you you will see this option that is salt from left to right. Select option, Nicole. And now select a rule that you want. I want to start with number I have selected, and then I'm gonna be a car and advocacy. My data is now sorted from left to right or dinner. Or is on. This is how you can do it. Using my let's move to our next it Have you ever come up off a situation where you have to add a zero before the number that say, I want to say that my number is zero b bull, but every time it miss enter my deedle vanishes because excellent does not consider yours. But that time, very it's necessary to act zero to yourself. So how do I go back in my and simple both of the center? You want to add the number just before the number and then by the love. And as you can see, it shows the number with the zero. Let's move to the next form, I blank says. Now let us say I have this attendance data or these remember and I have marked present for everybody that worked, but I won't be Mark. The ones were absent in there. And then I want to type absent doesn't wives. So how do I do? It's pretty soon. Select the range Okay, where you want to go ahead and the and then press F five on your okay. That brings up go two million, then click on Special and Select Bland's Definitely, as you can see, all the blank says are selected now. Now I can go ahead and do that required for my home and let's say I want to give this and then I want to buy my form in white so I'm gonna die absolutely on that day of cap and then less control on your people on this. And as you can see, all the blank says are now for my with the red background on the points in white. So that is how you can do for my thing for your blank cells. Let's move you up next, billeting blank cells in excess. So let's take this is the date other. But there are certain members for whom I do not have the phone number, and I want to date these records, which do not have. So how waiting very simple selectively from where you want to be. The information. Then goto pita on Click on. Then people don't believe the gator There. Phone numbers are not of it. So you do that we're getting on the phone numbers. Remove, select all and select blank. That will select although blank hole number records on your now. All I have to do is select the says beautiful Silicon Valley and under the lead that is this option. Be cute, have it and advocacy only dogs. Records have been removed. Fair enough for number one. Let's move to our next. Adding Blanco's. So let's say this is the data that I have on my and after every erected. I want either line. Okay, this week. So now that we have only seven or eight seconds, this become these. But what if I have 1000 on? I want to add a blind self after every out waiting. Simple. Let's bring our beat up back to the lead walls. I've been just state this as CD alone and then I will give it serious study from one. Okay, so I have seven now. Whatever I do is, I will copy this another. Now that I have added and copy the serial numbers, all I need to do is select the data. Okay, Look on data, but its start. And then you can start by media. Okay, African See, the blind cells are mouth inserted after everything. All I need to do now is the selectors on my what is that? How you add blind fruits after every record, just in case you have to do this but any of your body. So I hope you enjoy this lecture on. I would see you in the next one 8. Tips and Tricks 31 - 35: Hey, welcome back. I'm sure you must have heard about the strong schools Ritchie used very often in exit. No transfers. Basically used. If you want to arrange a horizontal data into a vertical What? What media? Vertical data with your little and that has been used Monsters. So, for example, this is a data which is arranged. Particulate on if I want to arrange it to be selected selector sent. But I want to be sit right, but based patient and all your I will select the special and then select France. But you can see the data which was arranged vertically, is now. But the only drawback that transports has is, let's say fighting is data from 500 to 600. The changes do not over here. And I want some kind of a problem that is defending any changes over here. It should reflect in my forties. Then how waiting on that is there at one starts post. So let's understand. How do you I just wanted to need this According to data elected, this is the data that I want. You arrange horizontal fashion then with a selective data. Not this data is using around eight rules when I select a Forlan's okay and the data is using to call somebody like to. Okay, now I have selected eight by do Celery, right? My former. No. You know, I don't like anybody. Just I This form John schools open bracket, select this entire range loon brackets and then Chris here on and okay, controlled ship. And so as agency, my what? It really does not transposed it. And now, if I make any changes to my detail with you, that's a change to fix it. The changes are reflecting in my heart it artillery. That's a genius. Wanted to do 200 and as you can see, the changes refit the horizontal ladies. And that is how you use up advance Frantz School future. Except let's move to our next using the on function. Now let's say I have this data in particular for much, but I have before on the last name. And I want this data to be addressed as a full name, but it takes the first leave the spacing between on. Give me a lot some old lady. It's very simple. Let's say I want the skin on Inga as one need cannonade. So I'm gonna select this said And what is it? Equal this value and then life in I and because I want to space in between, I would give golden base hold it and then I'm going to die one more and and then I'm going to last. Okay, If I drag this, I get them to be quiet out. This is how you used up on function. You can also use the concatenation function. But hand on function also comes it that at times very mean exit. You might have to use the same function bullet and on Let's say I want to use data validation. So every now and then I would have to go through data. Let me go on validation and then this menu. So how well, maker shocker. So that data validation is every living for me with the one. It's very simple. I haven't got a quick access to, but do more commands do and then come down to by that issue and then people on bad and then people. Now you can see the data validation. It's appearing in my quick system. Every time I needed that validation. This button on the data validation. Similarly, I can add any money, let's say by like format painter in my quick access money. Then I can go toe That's the mice quick access to but work a month on of its for my freedom people inspired. And now I have I did for my feet that brush my quick access so I can't get a shocker. What? Any off those functions which I use very open in my extension and I can't leave them high. This is how you create up shark. Let's move to our next date. What is that? So let's say I have this pretty good issued and I start putting some information on this. So I don't know, and I want this information will be recreated on another ship. So as and when I keep typing anything on on my existing on my life extension, the same data needs to be lovely Video onto another exit. So how old were you? So let's see, this is my life off my except shoot on the general worker and I want my data to be that riveted on shoot. So how will I at uber I want to say like this, You heat one. I'm one of this country and then I'm gonna shoot Now, As you can see, a group has been created for these two. She's And now if I go back to my shoot one and stocks fighting, I I love takes it. And if I bought my sheep, do you can see that the same way that is getting that that refuted on shoes. If I want to go ahead and on group it, all I need to do is just click anywhere outside on. The new sheets are now I'm anything is yet after me. And now if I bought a sheet, you don't see that it's getting This is how you can group and on group to shoot. I achieve date that application. But in you I want you on the same data. Get 78 on the other one. Now that you get this pretty legs, that means to market to put certain groups. And I bought some apples, oranges, finals that waas on dragon. And this is the quantity that No, I'm just trying to tell you how much money did I speak? But this pretty good issue is giving you okay because I'm finding the point. He's a Leo. I'm writing the price over yet, and then I'm going to my people. But this is getting in. Know why? It is because I have type. Then pieces at the C s is recognized as except by US alphanumeric. And that's the reason. Cannot it. So how do it achieve adding on my plane, these type of body using X It's very simple. So how am I gonna like you? So let us say this is the data that I would go ahead on the company. Okay, so I'm gonna believe this data. Okay. I have the price for unit and I just need to find, but I want to Active values is this foot. So I've been select this lead right click on it, would you? For Max, it under formats is there is a tap on number because it and then come down to this option, which is well, you see another option get for bead. And then I dio why have you typing zero? Because our values are with stocks number after the video double courts and then give us peace and then double nick. Okay? And now that it's start typing. So that's it. I produce. I protest 25 pieces off oranges with 25 by those 30. You can enjoy it. Okay, As you can see, it's now going ahead and thinking of the one from you. Begin the price from you on giving you the that. This is how you can use checks for numerical value in my I hope you enjoyed this lecture. I will see you in the next. 9. Tips and Tricks 36 - 40: Hey, welcome back. No legacy I have but set off records on the record is just laid on my screen in a very give Irishman. So there are no spaces that are left in between. There are. Some says that I left as blank. Okay, so this data is not in a proper structure, and I want to give a profit structure. I want remorse. These blanks and whatever data is there in each of the policy, I want to arrange it properly. So that didn't just one below the other. Because we need blanks in third loss. New. So how do you achieve that? I cannot do this money, but yes, Excel definitely has a future which can help us with. So in this case, what we can do is we can select this entire range where we have our data, but it's f white. What? You pressed f right. This go to many will open up on this simply gone special until it blanks the gun. And now what do we want to do with these lines? People would be So What I'm gonna do is I'm a big and even on the selective life anyway, right click on it. And and then it is going to ask me, What do you want to do? I'm gonna take shit sells up, which means wherever there is a blank, you need it and move the cells and I'm gonna play book. And as you can see, all the data that WAAS scattered all over the chief is now hurry, you know, need This is how you can remove the court with lines and the danger buta in Microsoft. Let's move to that. Next. Do you know that you can actually take a screenshot next? Yes, it is Because except has enabled feature, you can actually take screenshots for you and convert them into pictures. So let's get this is the record and I want to take us green shorts. So how can I do so? For that? I will once again go to my quick access Bob, and we're more than And then I've been select Come on, which are not in the and then every and I would find this option with this camera you can add and the so now you can see my quick access tool, but as a camera. And this is the things that I want to make a picture. What it selected and I'm gonna play gone. Yeah, As you can see, it has already captured an image. Now all the base damage anywhere on my right, and then I would. And as you can see, this is now you mention I can format it as an image later on our baby basted in any off US present, the let's move on to the next. Now let's look at this particular people. So what I have is I have the beautiful these students and these are the months that they have scored in individual subjects. Bad times. And this is the truth. So I have used to some functional will you. And then if you score more than 35 in each of the subjects, big boss, if this got anything less than 35 any off the subject like Sandra has got in English, then they feel they really click on these. Sell Julia, you can actually see the formula now. Excel has a feature that you can go ahead and I these farmers. So how can you do that? So for that, let us select entire existence. So the entire she does know. Highlight it. Right. Click on it under four marches. That is an option. Because it's efficient, and as you can see by before the law option will have a check. Mark. Okay. What do you need to do is you remove this chick, ma, and click on OK. And now this is the cell range that we want to hide the formula. Fabulous selected speed, right click on it Formats is once again I've imported prediction, and I really good. No. And now I will also put a check mark on it. John. Okay. I can still see the formulas. So now what do we need to? That is a step. What we need to do is let's select this range, but don't give you that. And then we go on protection. And then let's without password away. So I'm gonna put a possible and select this option. Select locks is select a Nazi. The garden. It will ask me to the enter. The password will be in the past, like on. Okay. And now if I click on any of these cells, I cannot see any of the formulas. Though the former was still there. I will not be able to see it. Let's try it. Let's say if I change the mats core off can from 44. 35. I used to enjoy that. What if I use this widely from 35 to 44 then it changes from past if it because he s scored less than 35 in one of the subjects. But as you can see, the formal ever still there. But the allocated and in this case, nobody would be able to. Genius, I'm feeling on that. Does the possible one protective formulas all you have to do this once again, select three, go to protect. Then it will ask you for the passport. So input the password make okay. And now you can see that the formal, other visible once again on if you want to edit it. This is how you can hide or high formulas in extent. Let's move on to our next. Did you know that Microsoft Excel hasn't invaded another system But you can use for doing a lot of status Pickles on Come person be the calculation. If you do not know it, let's exploded right now. So this particular data analysis to back is available under the doctor right now. It started believing because we haven't activated it. However the activity activated, you're going to fight that. What options? And then click on Patties, Come to this particular option, excellence the Congo. And you see this option and on the system back and click on OK, as you can see now that data analysis to unpack, its probably we can always use it for any type of us that is speaking English. Let's do a small example Over here. Let's find out the mean 1,000,000 bull on the standard deviation for this of liver data. So what I'm gonna do is I'm gonna be pointed out that this is on. I'm going to select maybe descriptive statistics. Click on OK, this is my foot. Range it enter. I want somebody statistics and then it will last me for the Ark would be So let's I went out with you. And as you can see, it has already done the calculations for us. And it's joined us a lot off statistical element off this particular. So that is how you can use that data analysis to get Microsoft. Let's move on to the next creating a drop down. So let's say I'm going in a prison for three off my ankles. John Kerry and I have to give them a rating on these. These three problems on this is the old already. Okay, So hold way through that the skin is only one toe and I should not be able to put any other values other than 15 45 input six. Then it should say that No, this particular data is not valid. Picture only, Allow me. I would want to know how to do that. Haven't said that the screen booger data click on data validation. And then, in sort of any value selector this, then it will ask me for the source of my click on that. This is the source. I have the values from one to pipe. Click enter. And now when I go to input the values these cells, we give me a drop on the drop down will have one value from one side. Be able to select it from you. But if I tried to put any other value other than want to fight okay, so I would be able to go ahead full of these cells using a range from one. That's how you create a dropped out in Microsoft. So I hope you enjoyed this lecture and I would see you in the next. 10. Tips and Tricks 41 - 45: Hey, welcome back. Finding the fire that many a times was working with Microsoft accent, we tend to forget Well, we have to save this particular fight or have access it for at that point of time, this particular punching that every journalist? Very so. For example, I want to find what is the final destination that are working on. So then sell open bracket double books double good and then and hasn't can see it is not doing that. This fire is located in the following, so this particular function can come very handy, especially if you have to find out that while location next move on to our next using the quick and other systems Do you know that there exists of weak analysis? Tomb in Microsoft Excel Let's look at it real quick politic. This is it. You know that I have almost okay and let's and I highlight the number upsets. If you look at the bottom off this particular selection, there comes a quick analysis and if you click on it, you get various options like for Martin. Well, according you can even create Jarque's made by the elves lying jugs scattered lords line you can even do certain about the medical functions, like some average call. What's indeed, you can convert it to people's in what People's on may be formatted tables, and then you can also be okay. But this guy can come very handy, especially if you have to do some kind of very quick analysis off data that you've just elected. Let's move on to the next, using the slicers, as you must be aware that in Excel, if you want to see a particular data or a particular selection, the picture who comes very handy. So, for example, if I only want to see the I won related records again, if I want, I want to see that could related to marriage as a designation. I gotta do that and I for that I used okay. So let's say if I only want to see the data about the manager, I can. OK, but there is another very hungry, too, that you can use on. Many people are not available, and that is something that we call a tough slices. Do you just slice it? What we have to do is select the leader good. Insert the gun beavers and then use check this option. My table has headers. Has hope. Once you do that, you would see that you're selection is not gonna work getting started. And now we can go ahead and you license. So what I did amply gone slices. Let's say I wants license based upon designation. So and I think on directly as you can see, three different slicers are available on my screen. Now on how we use them. That's let's see, if I only wanted to have a beautiful managers, I was on it. Let's see if I want to see the records only for managers. In a result that I said acknowledges please, I wouldn't want to see the record for managers in diesel who are selling item number three . And if I want to clear the victim I don't know. Is that from this is how you slices in my consult Texas. Let's move on to our next using the same humanity. No medicine. Get this. In the old year, I want a board. Use it, Bob. I take some loan from the back and I need around 40,000 as both the biases. Okay, you can be about the law in 16 months installments. So it's giving me the father of five years at the rate of interest that the bike is judging use on it. I want to understand what is the installment that I would have to be the one and therefore , But being this is a little among so far back, I will use This form is equal to minus by using a minus. Because I'm giving up this. Be empty for a minute. Open brackets. Okay, I want to understand. What is it for? Melo? Click over here on the problem in circle that is asking me for the read. My weight off interest is 8%. And since this is a new here, I'm going to be widely by Well, then it is asking me for what? Tell you Are you taking this? I'm going to say I'm taking this one for 16 months, collecting this president 77 and then it wants to know what is the principal. But I knew that taking this girl. But I select this that beef and the ugly gone okay. As you can see, it has calculated that installed metal that you have to pay every month to pay back this. And if I want to understand what is the what All among that? I didn't end up pain. I will just take this installment and while deployed with dark tenure with 60 months, So I come to know that. Okay, I end up in their own 48,663 600.35 as the board a llama, which means being around 8663.35. Now, let's say I would do who other bands on these two banks are offering loans, actually competitive. Okay, so one bank is ready to give you a load of B around 6%. And the other minds is also ready to go ahead and give you, but they're charging 09%. So I want people a kind over neck to neck comparison as to how much will be my installment on how much will be the or Lamar that I would end up. And that is when scenario Manager comes. So let us go ahead and understand how to use this in. Don't use this scenario. Management people go to data and then literally gone. What if analysis and then click constant value money. So once I click on scenario marriages this in a room Now I need to quit and Adam, I see not used. So let's say, plus, I might currency on my parents in. Are you is I'm getting this doing for in person. Okay? Fasting water body. Should I change? So I'm acidic. This okay? I want when you change these simply gone. Okay, So my current rate of interest is 8% on our farm. Make any changes, then I'm active. Best enough. On best in audio. This if I'm getting the law. 6%. So I'm gonna select this range again. It's enter. Okay, I change 8% since. Okay? And now I want Adam. Worst scenario. Weston are years. If I think there's no at 9% so I'm gonna give the seemingly okay, just the values for yourself going okay on. I gotta say, instead of people that I wasn't mine because I'm thinking the boss. God, I hope now if I will see the values for this same formula, what I need to do is click on ship. So with my current weight off in dressed, in fact, we can show. It says I have to be an installment of 811. 48,000 if I click on the best scenario but 6% I only have to pay $773.31. Everyone end up around 46,098 310.7. Now let's look at the worst scenario. Click on worse people. Shoot the worst thing you I may have to be in the 800 feet over, he says on. Then I will end up with what you know houses $300 has a little demon. And if I want to see this somebody off this, I can click onto somebody Dub, and then select this option scenario somebody. And then we got. For as you can see, it has octopus some Rio the entire can pollution. So it's saying that at in person I end up in 40,000 approximately with six person. I pay around 46,109% B 49,000. So it is doing a kind of fun next to make some partisan off the courage best. I love waas scenario, but this is how you can go ahead and make use off us in our your manager in my using that gun work when you're times you might have to use different units of measurement in your exit. So, for example, in India used in us, you might use in India for eaters us will use Yeah, on these type of convergence are sometimes necessary to be done. And that is where the gun work function comes. Very so, whatever you is, haven't sure you would then example. So let's say this is by Hewlett. Still needed How god, Where did they gallons I would take on. Then it was asked me What is the unit that you would like to go? So I would say I won't work this unit. Then it will ask me what does this year. So I'm gonna say this is okay and I want to go on. What do you do? Yeah, give a coma and I won't get so what? Get okay on a good And as you can see, my leaders allows working. I can just black this bomb and all my units, which I haven't. Let's look at one more example. Let's say I have my units in grams and I want to go. Where did you do? That's so Yeah. Once again, I will use that word function is equal. Gonna work open brackets. I was gonna work this units and selective give a comma. And I've been saying, this unit is in grams. Someone is selective biblical moments again. And then I would tell God, Where did you do? Over Losersbracket. So, as you can see, my units in France is now what do you do so you can actually can work Any units, Any units using the gun work country? This tool comes very handy, especially revealing with bullets in different countries or different schools. So I hope you like this lecture on Let's move on to the next. 11. Tips and Tricks 46 - 50: what? God? Working numbers now, many a times in excel, Especially when you're dealing with currencies. You might have to gun work the numbers into words. So, for example, over your I have some numbers away, you and a part of me, except has converted these numbers into words. If I have any number over a year and I use the spell number function, Excel gives me an output in the world. Former. So how do I get this particular feature incorporated in my exit? Let us land. So what I need to do Oh, you is I need to just google Google on. I need to type numbers, the work in exit that will give me a post link or you, which is a Microsoft website. If I click on this particular link, it will take me to this particular beach, which is a Microsoft support. And on this particular peed, I will find a core which has been written down that would give me the necessary off. So all I need to do is just need to copy this particular cool Come back to my excess. She just right click on the world you tell Click on you both. And now what we will do is we will click on insert, click on More you and then we will just based that core or you. And now we will close this body. And now, if I tried any number, will you? I can't get it done working with Google's using US lumber. Okay, on that, we just by a few numbers over here, and all I do is I would just drag this particular formula on. As you can see, I'm getting design out. So this is how you can convert numbers words using Microsoft's exit. That's more your next year, highlighting on removing duplicate and peace in my. Now it sometimes does happen that when you're dealing with a large and cumbersome data, some entries might get entered more than once on finding these entries and believing them gradually can become quite a cumbersome us. So Microsoft, Next said, hasn't in bare feet, which helps you plus to identify if there are any duplicate entries, and then it also provides your feature to believe them Direct. First of all, let's look at this pretty good, and let's see in there already duplicate entries over So what? I'm gonna do is I'm gonna select this particular date. How well and then everybody, My home, that look on conditional formatting click on highlight cell wounds and then I will click on to beget violence on oil. You I have this option bidding against the next my book before my for my duplicate entries . Now let us see. What is it doing? What? So you so keyboard with the product I did B 001 on price. 120 is appearing over your as with similarly hard drive with product I d p 00 PM Price 1200 is appearing over your on it is appearing or what? So what is happening over here is Microsoft Except this telling us each other entries. We talked to pick it on it. Yes. You want to believe these entries? It's very simple. Just I like this particularly dirty, both with the data. And then he got removed. Then it will ask based upon what criteria do you want? If I select all three, I don't mean productivity, Andry any MP because all of the options Goldman will be deleted from my date of this order . Let's try that that's click on OK, and now all the duplicate entries have be able. So this is how you can highlight on remove the biggest entries in my exit that can save you a lot of let's move on to our next using the some product bunch. Now let us look at my data set for you. So what I have over you is a day of eyes seeing off certain brought us and these are the list off my products that happy sold on, respectively. He's got to say it's quantity on. These are the prices. So I definitely what I want to know is during this political figures from Paul Jackson what has been my Topol sees? So you know, what I have done is I have forced by deep like the sea. It's quantity with the price on that gives me the sales value for that particular are getting for that particular day on. I have repeated the same close is for all the products that have been sold during this particular time. And in the end, what I have done is I have used this some punch. So then I add all this, it gives me the value as 63,409 people. So what happens over Europe is I would have to manually type in the form Low William, get the total for that particular day and then finally sum it up on Get this final is up. So basically what I'm doing over years, I'm finding the broader and then I'm summing it up. It was the end. Get me the final value. And this is with the some porter function off my Krystle exit. Get very especially if you have to do some part of my application, get the values and then active are using. So that is understand how you this out boarded function. The function goes like this is equal. I've been some product Open back it. And now it will ask me Give me the force at it that I have my did. So my first at it is you okay? The same quantity. Then I give a go. Now it is asking me That is my at a few. So I didn't do this for you. Okay, so basically, I want to find a broader And then finally the some off these two carries on That's why I have selected this particular ideas might close study on this particular fatty as my second . And now what I need to do is just move the brackets and hit on if you although I get exactly the same. But this is how you get used a some product function in my consult Fix it, especially when you have to multiply certain going to use to the price and then finally find this some value that is the one who are next learning some financial folders can come very handy in Microsoft and what we're gonna learn over your is the BMP function. I'd be empty on BPM people. So basically what off the empty function I e of the r b p Every function is us. Let us understand that. So let's see, I approach a pint and I want alone for five black. The bank says it is going to judge me an interest off being present on my loan period before for years. So I want to understand that's how much monthly installment I be. I also want to understand how much interest will I be paying for months and then out of this file Black how much money will go towards the principal? Everyone. So let us. I'm this time these formulas as these can come in very handy, especially when you're dealing with and I'm sure, people. So what I'm going to do over here is I'm gonna go Pete this raw data onto a blank. Except she So now that we have a copy this particular or get over you let us go ahead and use the PMT function for us and find out how much installment with ivy Pay for one. I'm dead. As the function boards, Lakers is equal to be empty movement brackets. Now it is asking me for the They talk in this by the doctor. Address is 40% um, in a year that are well once. So what I would do is I'm going to divide this by I will give a comma. Now it is asking me for the BD for how long am I taking this particular? So I'm taking this known for four years. So I'm gonna select this particular set and once again for your as 12 months. So when I would do is I might be polite despite one give a coma and now he wants to know what is the principle? That borrowing. So I'm borrowing alone off five lacks. So I'm gonna select this and I will know the brackets, and I will it. So now it is telling me that the ninth instalment for months is one of the 13,663 point why the during a minus sign? Because this is the amount that I will be paying back. I guess I want to remove the minus side. All I need to do is just out of minus sign or you and that would make it for now. What? I want to understand this about this. 13,663 0.4. How much will I be being as an interest on how much would be going towards my principal? So let us use the second bunch. I'd be in on the mountain, goes like this is equal. I be empty, open bracket. It's asking me for the lead off interest rate of interest is 14%. I'm going to divide this by 12 because once in a year give a coma. Now it is asking me for which particular one Do you want to know the interest? Because if you understand financials every month, you get variable in on. That is a different. Among that goes were the office. So for the past month, I want to know what is going to be my interest. So I'm gonna type one now. It is asking me for the duration off the Jewish off the loners. Four years I multiplied by 12 and now it wants to know the principal by. So I'm gonna say this is my principal value and I would lose the brackets. Okay. Once again, I would just add a minus. Sign over. You get this value U s. So out of 13,660 people do for I will be paying and interest off. 5833 points. Now let us understand what will be my principal. So for that, I'm gonna use the BMD and upon chin boots like this is equal to BBMB. Once again, I want to know that he so I divided by April. I want to know my impress for one month coma I The duration is four years. My dislike bite Well, give a coma on This is my principle about you and I with the brackets. Once again, our never do is Ivan. Adam minus sign a William on I get my monthly. Please. If I go ahead and some these two values, you will also that it is coming to my installment. So basically, this is the interest that I will be paying everyone. This will be my principal. And this will be my installment if I go ahead and change these values over here. So let's say instead of my life, I'm taking a loan off. Back to you will also know that the numbers visited According okay on this particular value will always be equal. Do this because your installment among will always be the some off your principal last your So this is how you can go ahead and calculate your monthly instalments. The monthly in dress on monthly principal, using the financial formulas that we just That's the one you are next using the gold see function I love This is a part of a board. If analysis off Microsoft, I'm at times this can come very handy whenever we want to predict a certain off. No. What do I mean by that? So let us understand. So that's it. I'm running a fact, and I manufacture four different rodders in my fact product guarded. Be products. See part of the Andi. I want to ensure that I hit that target off to lack to $3000 in revenue for my annual. And each of these brothers would have contributed a certain person between product. It can get 50% contribution started to be pretty fight. Rolex gives 40% contribution. I'm part of the gifts. When people say on this is the group revenue that I just and out of it. If I like this by 50% I will get 22,500. If I might like this particular value with 25% I get 31,250 and so on. And so so I know begging the production off part of A, B and C. I have managed to achieve one black 19,750 as my daughter contribution to my 11 and now I want to understand how much revenue should brought a D generate. So I hit my target off pool active 3000 as well. So I cannot go ahead and do this manually. I cannot just go ahead and type, okay, that we drive it through. Next. Do I catch the figure? No. Still like to lack 33,000. Then let me try using this Polack 10,000. Do I managed to achieve my revenue? No, I still don't. So this manual process off trial and error can become a very calm person. Bus. And this is where the gold seek future off Microsoft excel. Very So what I haven't do over you is I want to know how much production or how much revenue broader d generate so that when I might replace it with this 20% I get the required contribution over you on. I managed to touch my target off that. So what I'm gonna do over you is I'm gonna click on the data tab goto water analysis. And I know you haven't select this option. Bulls no one will seek. Wants to know is what value do I want to say? I want to set the value off this particular cell over your so you lack people's by changing the values in this body. Okay, let me repeat that once again. I want who said the value off edge 11 because this particular to our value off to lack of beetles, which is my on how we want to achieve that by finding out the revenue for our city, which isn't said f So I want to change the value off this particular self so as to get this particular out and I'm gonna click on and it is doing the calculation. So now what it tells me is I have to generate a revenue off like 81,250 if I've worn toe touch my target off lack to be 1000 contribution to the So this is how you can use a gold Z function using my consult takes said, I hope you enjoy this lecture on that also brings us to the end off this particular leader cities. I hope you enjoy what you learn on. You will definitely implement in your day to day except work so happy learning on God. Listen 12. Congrats: