#### Transcripts

1. Using Lookup functions – easy demonstration of VLOOKUP and HLOOKUP-Part 1: Hello and welcome back toe. Get another Marjorie off Microsoft Excel 2013 for the Panelists. So in this morning will be talking are we will be demonstrating how toe perform complex calculations very efficiently. So when whenever we talk about analysts is definitely complex calculations stumps in do mind comes into picture. So how to deal with such calculations and how toe use those data in a very effort, efficient and effective manner We will see a demonstration in this particular module So let's have a look at the outline. So in this particular model we will be talking about how to use look up functions in some easy demonstrations Regency So what are the two different look of functions that we have? But other perimeters that use will talk about the perimeters in detail on But then definitely we will see what is the difference between we look up a local Onda. We will also talk about some reverse look up techniques like there's a combination off two functions with which will be using for understanding the concept off reverse. Look up. So we will be using index and match for this purpose, Onda. Then be really say in the process. Off using this, we look up a niche. Look up our rivers, look up techniques. We will end up having some errors. So we'll see in the process how to avoid are how toe handle those errors. First we will see how toe handle those errors on using the concepts off tracing formula. We will see whether we can actually understand how the formalize working, which are the references. How are the references working So those things will be checking in this particular Marty will also on. Finally, we will see how like or reading or any how to avoid such areas. Like whenever we are using any formula like that. So that would be a base off this model we will be talking about mainly into calculations and going toe how to check it very efficiently. So look up, function the syntax off, Look a function we we'll see. We'll explain on then, in this particular section, we will also talk about our differentiate between we look upon the h Look up on. We will see a very good example set of examples for look up. You will require a data set to work with this particular what you Those data set that particular later said will be provided to you with this morning. So let's understand how this look up books. So what does it do? This particular function actually scans down the row headings. So just understand one thing. If your data is in vertical Former, how do you say vertical former vertical former means that if whenever you're adding a record, if it adds one ballou another one below so the word is below. So if you're deciding one below evidence, then it would be termed as a work vehicle. And if it is adding on the right hand side, then it will be toned as a horizontal data. So vertical daytime, horizontal data. So if it is particle, then we will be using we look up if it is horizontal on. This means if the data is increasing on the right hand side, then we will be talking about it's looking both off them the perimeters, the same Texas almost similar. So one uses vertical approach and then uses horizontal average. So we look up so these other parameters off we look up, it takes like them to find the range on the column. The column number on the four perimeter is also very important if your data is sorted or unsorted. So based on that, it can also do some other kind off look up applications. Similarly, we have Etch Look up as well. So and she look up actually works with horizontal data that could be a just told you. Andi here, all the things that we're talking about columns or taking a data from column instead of that it is a rope become so instead of column become it is row pickup, rest all the perimeters that absolutely same for extra. We'll see a good demonstration off the same data. So it's look up also has similar kind off perimeters, therefore perimeters, the 1st 1 being the value to find 2nd 1 being the area from where the value is to be looked up. The third is the rule that I have to select from those data points on the 4th 1 Definitely being whether it is sorted are not sorted, so we call it as a ranger. Look up at times. We also say that's appropriate match at an exact match so that those all things have you seen the is when we proceed forward
2. Using Lookup functions – easy demonstration of VLOOKUP and HLOOKUP-Part 2: So this particular data, what we have, we have your sales person. You can see number of customers. Onda. We have something call Net sees on profit and loss. So this is just a demo data about sales person, the number of customers that are handling on made sales and property loss. Now, in case I just wanted toe get some data based on this. So what I can say? Let's say I wanted to find out how we need customers. Is John handling so somewhere? Let's see. I can simply say like this. I wanted the number of customers for Johnny's handling. So I understand one thing. This John is actually sitting under first column. So if my question is based on first column, then we look up, can help me toe do the needful. So here, what is happening? John is in the first column on Definitely what is happening on the number of customers that we're searching is on the right hand side off my actual column. So we look up, can health and what I was talking about is like data in vertical former. So what I mean to say tomorrow, if you want to add some more. Five regards, Definitely. You will be taking this section. So what is happening here is data is being added in vertical. Former so next role you want to add it will be a vertical option. However, in X look up. What happens? The data increases on the right hand side. This is not an ideal scenario for understanding. Edge took up. We'll come to that particular part just for the time being. You understand that this is vertical data. On the moment I go down, let's I simply show what is horizontal data. So, like, I have copied that data on DWhite. Basting what I do. I go to base special, Andi simply a safe transpose on. Say Okay. See, the same data is now in what is on 10 format. So right now, if I say that I need to add some new records. So this records will not be added here. This records will be added here. So in case your data is expanding on the right hand side, you use it. Look up. So we will be using here. It's look up and in case your data is expanding down. So in that case, we will be using. Yeah, really, the vertical kind, Alexander. So this will be used for vertical. Look up on down. The data that is there will be used for your horizontal look up. So let's go back to our scenario wherein we won't further data off. John. Do be visualize over here. So before we move on toe the function, actually, we wanted to know where is the number of customers sitting? So in our data, just understand, for our reference, what you will do, we will put the column numbers. So this is called him. Number one, This is Scotland number two. This is column number three on this is calling number four. So just for our reference, we have just for the column Numbers Rick see subsequently will see that there will be a great use off this column numbers as and when we proceed with this particular table. So gotta numbers here of 1234 And we are looking for data which is present in the second column. So call him number two. So let's see, How do we map it? We say equal to because it's a formula. Definitely. We have to start it with equal toe. They say V look up. See the moment? Do press V l automatically excel gives you what I say, I promise you, but there's a function. It starts with V and ill so automatically you CV look up still. Now what you can do here is we can just press tab as well. So you can just say tab on what will happen if we capture this function over here. So what we will do, we will actually pick up. See? Very look up. Takes four parameters as I already the discus. So the first parliament being the value. So what I'm searching for? I'm searching for John. So then it is asking okay, If I find John, then where to find John from. So I say that this is your area from very refined John. So whenever you are specifying an area, make sure that this particular data recites in the first column They don't leave. You look up with folks. So what I'm saying here is that this particular sales person, John is there in the first column and hence we look up, will vote absolutely fine. Second perimeter being the range Third, Param Eter it us. Give me the column Index column index means the column number So column number is actually here to I say to the moment I say to Andi, I give coma. It is asking whether you require an appropriate matter an exact match. For this time, we will be taking a passive exact much. So I know that John is there so I can search for that exact match. We will come toe the appropriate match also. So I say exact much so exact match. See, there are two ways of looking into this particular fourth perimeter. If I say true, this means I'm looking for appropriate match on for looking for appropriate match. Your table has to be sorted. So if we distorted, you can also search for appropriate match. If it is not sorted, you go in foot Exact match. Right now, my data is not sorted. And the second thing is that I'm looking for the exact value off John. I know John is there in my table and I am looking for John itself. So what I can do? I'll say I need an exact match. Select falls. Even if I can just put 00 refers to false. True for true. You can also write one. So I'm just saying false. I can put false over here. Also, on the moment I closed my bracket and press enter, I see that 23. So 23 is actually the number of customers that John is handling Now, there are multiple inquiries around this. We look up. So the 1st 20 that we ask is let's take an example if there are multiple, um, names off John that is appearing here, what will happen? So in that case, definitely the first option the first instance will be picked up. Fine. So this is an example? Very. We have come across the column number on column number was smoking absolutely, perfectly fine. Now, what if I have some other type off date aswell on, but I'm not sure whether it is John or Johnny our Jonathan, I'm not sure. So in that case, excesses that please sort their data. So now what we will do. We are still looking for John, So I see. I'm still looking for John now. See what happens. Um, let's say I say hell, Johnny, instead of John Andi, I type another name has, Let's say Joel, something like this. So two names now, if you use the same function down, it will not give any dessert free. Why? Because we're talking about exact much. Now let's see what happens when I say here that I need are appropriate touch. So for that, first of all, you have to sort your data. So starting has to be done based on sales person. So I see Peeta. Andi. Then I say start, I need a starting based on sales person on. Then you see ascending order started. So now what happens? You are the salesperson, shady being starting with C. It comes at the top. So now what? I say that I'm looking for Lexie. Johnny, if you say equal, do we look up on? I'm looking for the value, Johnny. I'm the column index that I'm searching is definitely number of customers. So, no, Before that, I just say, This is my area. The ari and then I say column index to because number of customers hear what I'm saying that I need an appropriate match because I don't know whether John is there are Johnny is there are joys there I'm not sure. So I say that Give me an appropriate match. So, in other words, I'm saying that I already have sorted my data in order in ascending order so you can actually look into that particular option. So what I can do instead of saying falls this time I'm saying, true, on the moment I press enter. So it still gives me 23. Why it is still giving me 23 because it sees that Johnny is an appropriate match off John. So let's try the same function here. You hear that? You can see properly. So this time I'm saying we look up on, I want to search for this particular data. My table Ari remains the same. Andi, I'm also looking for a number of customers. Let's say it is column number two on my date. I soccer. So I say appropriate match True. See what happens now. Joe is not there in my sense list, but still it is giving me a data. What is happening? The rule is if you are saying that if your data is in order, former and you're looking for appropriate match what it will do, it will search for the best possible fit. So things that everything is sorted. So Harry is actually you can see that it is becoming an appropriate or something less than John. So what is happening? They're searching for something less than John. So Geo X So it thinks as it isn't working as an algorithm, actually. So if you say gox so in order, Geo, it should come before John. So it is not saying seeing that whether it is Harry or Funar that is appearing before John , it simply sees that anything less than John should be selected. So it finds John and it stops there. And then it goes one step ahead, one step sorry back and then picks up the number of customers. So I'll just give you the same example we can see here for Lexie Peter on a mission. Fine. So let's say I'm searching for feet. Okay, so there is no name called Beat. However, if I write the same, we look up, you will see that it will give me the date off. Michelle seek. So we look up on the idea remains the same. I'm also now looking for fame customers, and this time I say, true friend. See here what does the value it is returning? So it is not seeing whether it is Michelle or Matthew or Mary. It is seeing that if this is Pete, it has to come before Peter. So by that logic, it takes up the previous value off Peter and then punching that particular data. So for me, I can see that. Okay, what is this? I have Michelle over there, and it is giving me the customers off Michelle. But know what is happening? I say that this is in a sorted order. So based on sorting this particular Pete should come before Peter. Based on that, it is just going one step backward and giving me the values. So this is what the difference between your true and falls when you use the perimeter for the fourth section off we look up. So the fourth perimeter basically to say so, if it is sorted order and if you're looking for appropriate match, who ahead with your true option that to appropriate match on? However, what happens? Most of the cases, even if you're late, I sorted. We're looking for exact matches, and in that case, we can actually say false over there
3. Using Lookup functions – easy demonstration of VLOOKUP and HLOOKUP-Part 3: Now we will make this particular from think a bit complex. What you could do will take up these Collins will copy it. We go to some other sheet on, then you simply based it. Now what we do, let's say like this. Uh, let's for my foot like this. Now what I have done, I have just based it, though. Hurt him. Now there is a twist What we will do well, jumble up at these headaches. The property loss times here, Here comes the next he's on. Here comes stuff that's too. So what happens automatically you are order changes. Now this remains number one. So this is column number one, which is which was here as well as seem now who identify them. What we have done, just numbering them things. Volume, number toe thinks is column number three on This is basically column number four. So all of this particular options what is happening is you have taken the column headings, and we have numbered it properly. Now, I'll tell you the story behind this particular changing. What I'm saying that let's say my manager gives me this template and say, Is that, um so just populate, right? These data. So this randomly gives me some names like this and be like this, Andi, like this. So let's do this. 56 names, and it's a spot this populate. Um, these names are these particular value. So I take it, John this. Now, if you want to put this profit and loss and customary nixes, the idea is that we will be putting one. We look appeal, and it should work for all off these particular data points. So there are multiple ways of doing it. So one thing what we can do whenever we are putting the function we can say equal to Let's say we look up on here, I'm searching for this particular data getting No If I goto table Ari. So I have already discussed in this previous models also, like, what is the significance of using name here? What we can do, we can without using the name. We can also select this particular range, Andi. Then what we can do the third perimeter being I know that profit and sales is actually call the number four. So what I can do column in next, which is full on then I say I want an exact man so I can just type zero and press enter the moment I person until what happens is that I see that you catty is not this. So that's why it is giving me a hash any. However, if I copied down, I can see that. Okay, Matthew is there. I'm Sonny's there. So McEwan's I lied. It I'm getting I know Johnny is not their fine if it's not populated. Roger, it's not They're not populating. But what happened to this? Eric? I was sure that Eric is there in my list. So how come Eric is not coming in my re look up? So what has happened here? Whenever you were copying and pasting this particular for polar down, this particular range was also getting changed. So first time it was prompting to see three to f 18 because my actual data points e three to f 18 but eventually even I copied this down. This particular thing it is pointing to see a to F 23. So ch two f 23 is actually if you see ch two f practically is this area something like this ? So, Victor, my cells when I'm populating it down, my references also changing. That's why this Eric was out off my range on hence hash ending. I want to resolve this because this is an error. So how do we resolve this? There are multiple ways of resolving this particular issue. The first wave off resolving this issue is by using this as a frozen area. So what I can do? I can simply press four to freeze this particular location F three to F 18. The moment I press a four automatically, what happens? The areas so no. If I offer it down, you can see that Eric State that is absolutely visible. Why? Because wherever I go, I see that seek leave to f 18. This part this part city to affecting remains same. No changes now. I wanted to write my We look up in the movie that it should directly book when I copy Paste it. So what we are going toe do here is we will definitely talk about copying and pasting. But before copying and pasting, we have to do some more modifications. The mortification being Let's take an example that if you copy and paste it here in the subsequent, um, subsequent cells. Then, in that case, you will again have the same problem. The C four will become Defour. Default will become, e 44 will become 84 which will again give me up either in the daytime. So now what's to follow? What I want is that I need some data which is valued. Let me show you something. So that gives you that idea. So you can see about you is having this particular numbers what we can do? We can actually put a dollar in front of this four. So the moment you put a dollar in front of this four, see what happens. You copy this at light, you don't get any better find because all these functions are pointing to the same problem . That is absolutely done. But now the problem is that we have an issue with the number of customers and sales because it's not signed 63 that we're looking into. And this is not the sales. Even so, what is the problem? The problem was that we were actually referring toa a fixed column. So with what's column number four. Now, how do it is? All this. I've warned that whenever I change my column my column number should also change. So how do I do it? So reporting for today we will do We will simply change this four to this place. Now there is a catch. The catch is that if you scroll this down if he copied this particular formula down what will happen? This detour will change two D three d 45 physics. So here I don't want this to move, but I definitely want this d to move to e f g. So what I would do? I would mention it as dollar tow 100 says whichever party want to fix, just put a dollar in front of it. So now I am ready for this vehicle. The best central are to copy this, right? I like best control D two populated so I can see the profit and loss number of customers and next cells is populating absolutely fine. No issues with that. So the data is populating, but it is not possible. Don't president this particular date up directly like this, but for the time being, just understand that we were able to successfully put this we look up function on. This was a particle data so we could do it. I think we look of function
4. Using Lookup functions – easy demonstration of VLOOKUP and HLOOKUP-Part 4: if we goto this particular section way, have data in, um What we say horizontally format. Then in that case, what we will be doing, we will be using edge Look, a function. Let's say I wanted to check how many customers is Eric handling? And my data is this one. So in that case, I won't be using We look up. I will be using that because my date eyes and also former trust all the perimeters remains same. So look up Value is this table Arians vertical It will be horizontal table. So this is my table. So on third perimeter being the ruin next number. All these fine. We were talking about column in next number. Since the data is getting expanded on the right inside on my rules are actually nothing but headings You are You can say the field names. So that's why what we will be doing it will be putting ruin next number. So row index number We will be saying Suppose I want to check number of customers. I will definitely say two on Then I want exact match so I can even see zero on press enter . So I see that It gives me the value off Eric. The number of customers are exactly. And I'll put this like this. So this is the major difference. We look up on edge hookup, list all the things that so we have understood the concepts are we? Look up, Look up.
5. Reverse lookup functions: we'll move on to the next topic, which is reverse. Look up So reverse. Look up. It's not a function, actually. So actually, we used some functions off Excel toe, achieve this scenario off reverse. Look up. So there are two functions which work simultaneously to provide output. One is index another honest match. So index and match we will be using in the following example. So it's a bit more time, so no, we would be using the concepts off reverse. Look up in this demo. So let's say the situation is somewhat different. Hear what I'm saying that I know must see My boss comes and stays that I know that somebody is handling her customers. Who is this person? So it is. Give me the name of the person who is handling QWERTY customers now. All these while what we were doing feel new though main data and we were searching for reliving data based on the main column. Now, this time, what is happening? We are having the actual data point and we're trying to go back to find out the actual place. From there this data has come so here in our case, we're searching for who is handling means. I'm searching for sales person. So all this while I was searching based on sales person. Now I am searching for sales person based on customers. That's the reverse concept here to handle this particular situation. We look up, cannot help. We look up or extra cannot help you. We have to use two functions. What is index and what is much? So first off, let's understand how these two functions for on then people combined these two functions together. So let's understand how index and how match works in big cities that out off any area, you give me the position and I will give you the data. So let's take an example if I say that this is my salesperson, Ari. So I say that OK from the sales person at a Give me the fifth me So they're index can help me. So it says equal to index, and it asked for Inari. Be careful by selecting an area. Don't select heading. Otherwise it will be considered as a position. So always select the data. Andi. Then the second option says give me the room number means the position I say show me the fifth person in the list off salesperson in the list of since person. So the moment I press enough, I see John. So if I calculate health, this is frustration. Second question her for perfect. Absolutely. Right. So Index is working absolutely fine. No, let's on this time what matched us? So Matt says that give me the daytime. I will return. Knew the position. So let's take an example if I wanted to check What is the position of John in this particular salesperson? Harry? Then I could have used the match. I can see match. Look up for this data, Andi. Then this is the area from where I know that this data is there. So this is the area on. I am looking for exact much. The moment you close the bracket and press enter, it gives you 55 is nothing but the position off John. That name within the area. So you can see that both off them how smoothly they work. One stays. Give me the position. I will send you the data on one says you Give me the date. I can send you the position. So what do you do? we will utilize these two concepts. Toe do something called Reverse. Look up. So what I'm saying? See, now we will merge these two things. We say that. Okay, I know somebody's handling 30 customers. Who is that? So what we have to do? First of all, from the list of customers I have to find out. What is the position of this number? 30? Andi. Then, after doing that, what do you have to do? We have to actually find out the real evident position in the name off salesperson and returned the valley. So what will I do? I'll say, Actually, I'm looking for data, so definitely have to use index, so Index can give me the data based on the position. So I start they x function, I say this is my addy off numbers are Saudi Addy off salesperson. Okay, when coming onto the road number, I'm not able to give them a particular number because I have a data instead of a number instead of a position. So here what happens? Match comes into picture. Max is Give me the data and I will tell you the position. So we open match function and I say that this is the data that I'm looking for. The second bottom reduces. Tell me the area where this particular value is There. I find this is the idea, Andi. Then the third para meter us. Whether you want exact my child, less than or greater than I say. Want an exact match so exact match Come soon and they close the bracket and I close the bracket once against to understand their true to of brackets that I have used the 1st 1 being in the black color that is the main one on inside that black bracket were also used Another record which is coming in the US this orange color like of this. So when this blackmailer comes, I'm sure that we have used this index function. The moment I pressed Enter see what happens. The name Jonathan comes Soc Jonathan is hanging on a particle service on the name is for being up here So you can see that how index and match works simultaneously. The match. What is happening here is Max is searching for this 30 number on then in the range off customers. It is searching very 30 and then returning value to index as the position index. What it is doing, it is looking for that particular position and returning the value. So these other toe base through which index and match works Now let's make this a bit more complicated, so we say that is the complicated data. Looks like a very simple data. But the complexity arises when I say that, Can you give me? The manager walks in and says that Can you give me who is handling maximum number of customers Now? Last time he said 30 I could get some place. This time, he's not even giving me value and asking me directly. Do get the output so says maximum. So now I know that there is a function called maximum, which can be done the maximum number of customers within that area. So I will take help of that particular function. If we did this index. So what I will do. So here is the position. Let's say here is the place where I want to go, right that function. So I would say, equal to index so indexing stock. So, Ari, where is the Ari? Ari is always output backyard, uh, you require output from. So this is the same person attic. And then I'm saying that I need the room number, but rule number can be given by much. So Matt says that give me the look of value. But I say don't have the value because I have been distorted maximum number. So then says, Ok, fine. Instead, off using direct value, we can also utilize the function called Max. Yes, this is possible. See? For which data said you want the maximum number. I said this is my date. Okay, fine. So this max function will actually return a value. That value will be used by much. So now Matt is asking whatever data Max is giving where I have to look up, I say, I think this is the area from very look up. And then it is asking. I want the exact match. I see. Yes, exact much. So I close the bracket. This is for much. I closed the next one. Which his father index. The moment I press enter, it should give me the bag. As Michelle as I can see from the number of customers she's handling 48 customers the maximum So this without foot So here This is actually your mega formula that we have written. Also, we can say that we have returning nested function. So using the state functions we have actually returned omega formula. What is happening here is this is the first battle. Me don't. So what we do this max function will be executed first on the output will be sent to match match what it will do. It will take that output off Max on. Then search for that particular value in this given area off number of customers with the exact match on whatever match returns match will return. Definitely are position so that position will be taken up by index on index will actually give the output. So this is how this mega formula is working. So we have used index match and Max three off the functions for demonstrating the concept off reverse. Look up. So this was reverse. Look up
6. Effective error handling: Let's see. No, um he was looked up. We have understood. We have understood that we look up unethical. But if you notice like, then be insulted these data In the second sheet, we encountered some errors here so it can be trapped on so it can be handled hell, if you go back to this particular sheet, you see, it is ah que we will simply What do you handle the other for handling errors. We either use the function called if or use something called Is A. Both of them were used for hand lingers, but it depends. Like if you want toe some logical testing with the error handling, then you use if is there. Otherwise, if you simply want toe handle and then providing output in that case, you use if ever I will show you the demo off. Both of them. First of four legs. And this time is there because that's so like Lindy. So if you find it as a lindy formula on nothing so far using easier, you first copy this particular Barbara on. Then we enclose it within if conditions did any of condition. What I'm saying is it so is it will test for this vehicle. So what? I'm saying that if there's an editor while walking with this we looked up. Then what do I see? Just keep it blank. However, if it is not on it, then what to do? The next day we look a function. So what we are doing absolutely writing the same. We look up function one second. That's why I say just copy that we look up so that we have to use it one second. So what I'm doing here, I am simply utilizing if and instead of to work with this, we look up handling the area. So now here. What happens? Since it finds the data, it absolutely is absolutely fine. However, if I got beat, don't you can see that these places where there was no data in error was coming, that it has been 100. So what has happened? Excellent, actually absolutely trapped the concept off hash in it. And it says that if Dr Student doesn't either, so what it has done, each actually converted that I checked that and say, if it is an error, then just put it there, supplying something similarly, we can utilize the concept off if error for doing the same work. What do you can do? We can simply inclusive landscape. So if EDL is actually a combination off, if I is it a so I can It takes only two parameters. First parliamentary tasks. What is that you are trying toe track with, if with her? So I said This is the formula that I think there's an error there. So it is returning some error value. Also, at times I want to trap that. So this is the first parameter then it says, OK, fine if I get an error than what to do. So I said No, this is I have to put the value if either. So I say I just need to put it us blind and I close the bracket. See, they're only one pair off brackets. The black backers that have used we look up bracket was already there. So now what I do if I simply copy down, it gives me the same output. So you can see is there on a feather in this model is in this particular example is giving me the same output. I can still since if it is very easy to understand what I will do. I will still go ahead with the Fed, and I will copy this On the right hand side Control are if I put it copies like right inside. No, what I can do. I can simply drag this formalized simply second authority on dragging this particular formula down. So this particular place you can see during this point until this point it is it will both as a template. So now what we happened the moment you put any name here, let's say I am putting it. Does Jonathan so automatically populates the number of customers and it sells. However, if I put some name, which is not this, we still see blank if I put something which is there in the list. So let's say I know Peter is there in that list so automatically populate. So this is how we can handle errors in excel so either by using ephedra or is it her? So the major main difference is that if you are using is error, we have a scope off utilizing the enter partof it also. So let's say if I if I say that if it isn't better than do something if it is not another didn't do something else. That is also possible with even is it. However, if there are only takes one, uh, this time this particular what do you say? The formula or the value, so it will only take one value and takes the second perimeter is what to do if it is an error. So this is the major difference between if error on is there. So in the process, we have learned how to handle our missing data as well.
7. Tracing formula dependents and precedents: So next buck is to show how to trace the formula that we have written so many form will also many functions we have written. But at times what happens? It is very difficult for us to trace, like where these formulas advising from which other cells which are actually responsible for this formula. So what we can do? We can actually go ahead on book with this options. So formula tracing for the racing off, dependent on presidents, that two different factors. So we can also what president and we can also put different president is actually to check a formula on dependent. This the process off, checking a particular value with a particular formula. So we will go toe our excel sheet. Andi, let's take an example like this wanted to check like see this particular cell? It's not giving me any output, but I know that it is refering toe a particular city in my sheet or in my God, bro. So if I wanted to check who are the cells of each other cells, which is actually responsible for this particular formula, I can simply go toe formulas on there. There's an option which say's praise residents. The moment I say praise President, you can see that it gives me on blue color off arrow. So second pharaohs out there, one off the arrow points to this number off the number column number. Because this V look up is also dependent on this column number. This we look up is also dependent on this particular value that we have chosen on this with Lucas is also dependent on a hard please, which is not within the sheet, which is somewhere in some other sheet. So it is just showing it to us this way. So I can I can check any fun off these formulas so you can see like this all these formulas we'll show up like, where are they? Or which all are the president's. So all of these cells have the same president like this, and they're all pointing towards a separate sheet are together at any point of time. If you wish toe I remove these arrows, we can simply say the move arrows. Now let's see this particular, I think, can be checked for our data as well. So if I just wanted to see, let's say this is a sales person just I have typed it. I wanted to check, which are the cells which is affecting this particular salespersons. So what we can do, we can say place depending. So this is affecting these. All three says, because we are using the local function and all of them are using these T cells. So these three cents will be highlighted like this. Even if you see a blank cell. If you say trace dependent, so it will show all the dependents is over here. No, you can just say remove Adel's now what happens? At times, you just wanted to check whether a particular sheet has got any formulas are not, in that case, what you can do. You can either go toe formulas on breast show formulas from here are from your keyboard. Also, you confessed control on and grave accent signed. The grave accent sign is just below the escape symbol escape key from keyboard. So what you can do? Take your custom reliever within the sheet, and the moment you say control and relaxing, it converts your sheet into a list off formulas wherever formalized their it will point out like this, so this means that It also means that let's take an example, you have opened a sheet on. But it shows something like this all everywhere, formula written like this. So what do you understand that somewhere? Show formula. Stripper. So you can always again click on this option. Show formulas are from your keyboard again. You can press control on the vaccine, so it takes you back. So control, Graham accent, our show formalizes actually doubling, uh, option so you can go back and come back one second. Now, if you goto this sheet so I can see that cities are simple, merely numbers. However, I know that there are some formula that there are some functions that fear views and were written on formula. So I can see like this. So anything like this, I can see precedent so I can see that these other, um, places which this particular cell is pointing toe like this. So trace presidents we can see similarly for that Look up as well. Whenever we are talking about this, we say press president. So it shows me that this is the range which is being used by this particular formula. You can remove all the arrows you can see the dependence. Also, let's say I have written Eric. So, Eric, this particular send the dependent solicitous particular sense. So if you make any changes here, this will change frame. So this is the X look apart So you can see here. If I write Sally, it changes the number. So this is dependent. Remove the arrows at any point of time. If you want to evaluate a formula so you can see the defense. Like what if I change or let's say right now, the evolution Is this on the references? Actually, G 25 the salesperson Gate 25. So the value off this particular cell is like this. However, if it is a formula on, do you value it formula? So here you can see how you are. Data is ended, it will show you step by step, how it works. So if you say step in so first step, what happens? It checks for Sally. Fine. So Sally is the main perimeter for EJ. Look up on, then. You can see that automatically. It say's here that this J 25 is having the value s Sally so like this, you can evaluate the formula and you can see what is the output off this particular formula that is written in G 25. So this is the way Do that. You can also put some watch on your data. So if you say that, say, I'd watch on if you say that this is my formula that I want to watch. So what happens when you actually put here, Eddie? So you can see that the value changes on the formalized like this. So it is dependent on this particular cell A 25. Whichever sell that we have taken here is situated 25 it is affecting 25. And this is being watched. So it is also possible to delete the watch once you have checked out and then close the watch window aspect. So at times. So this is how you can trace the formula. You can do some evaluation with the formulas and show on removed that arose as well dependent on President
8. Using formula auditing tools to avoid errors: so far, the formula auditing. We really see this error checking options also. Andi, In the process, we really valued the formula. So both the things first of all, we check how toe identify how toe check the presidents and dependence on. Then we will move towards auditing that particular formula and using some tunes as well. So we will go toe our third sheet. So if you have returned some functions and you some formulas than definitely what you can do, you can actually do some body thing options and use a marketing tools available with Saxon . So does do some advance. Silas is on. Then what you can do. You can proceed with other checking and other tracing options. No, let's take an example. Um, let's say I have three numbers like this 12 13 14. Like this on. What I'm doing is in this particular city. I'm think you could do this number. Bless this number. Bless this number. This this number So usually it is not possible toe include the same cell within a formula. Why? Because it won't be it going toe on in finite loop. It won't be able to give you the correct value. However, if you still but maybe by mistake, on maybe by the requirement of the formula, If you just press enter, it gives you an information. Understand? This is an F Y. I. For your information, it will not restrict you from writing that particular formula, but it will show you and other that this is a circular reference. This this in terms off X alone is secular reference. So what happens if you go to this particular cell? You can see that it is refering to itself, and it creates a set clear difference, like suppose what happens? You have similar kind off issues in some other cells. Andi. Even if you change the output, let's say you're expecting that if you change the number of this output for change, but still it issuing it at zero. So you wanted to see if there is some issues with these two places. So what I can do? We can directly goto error checking. There's an option, which is better checking on there. You can see that some obviously some error checking options appear, so it says that there is no asset formula related error in this particular sheet. However, When you goto this error checking and go to sell circular references, it say's that there's an option. There's an error with this Q six. So if you go to this letter, he says that there's an option are better with this Q six. So it radically takes you there and say is that there is an editor there. So if you want to remove it, if you want to resolve this particular circular reference, you can do that on then what you can do, you can remove them. So the step off resolving circular references to be sure that this particular cell very, are putting the result is not a part of the calculation. So always make sure that particular thing see here, if you put the dependence people, the president's Okay, so the problem is that it is actually pointing to itself, which is giving up invalid references. So this is how you can analyze some complex data in Excel complex calculations in this particular session, we have gone through these topics like by the end of this session, we're now able toe, see what our what is look up function, how it works. Some easy demonstration off look up functions It shook up and we pick up the to look up functions that we have seen rivers Look up techniques. We have also seen how to use this index and match as reverse. Look up the pig! Andi! In the process, we ended up having some leaders. So how to resolve that? They using visitor if error We have seen that as well on how to trace the formula. What are dependent Sort our presidents on how to avoid errors. So how to check for the references, invalid references and how to avoid errors in excel. So this is what we have checked on. This is what we have learned in this particular morning. So I But this we have completed this sixth modern off this course program program out playing. So thank you for watching on seeing this temple.