Advance Excel Mastery | Beginner to Advanced Skills in Excel

1. Introduction to Course: Hello, everyone. This is Sad, and I welcome you to this advanced Excel training program. For those of you who don't know about me, let me give you a brief introduction about myself. I'm a certified Microsoft office expert and an online trainer and consultant. I've been training companies and individuals for nearly eight years now. And in this training, we will cover 55 plus formulas, and we'll cover practical scenarios that people face in the companies, which will actually give you a real life touch of the practical Axel work. Goal is not to learn formulas, but learn how to implement the formulas in any given situation. So we will start off from complete basic, and then gradually we will move towards learning of advanced formulas and techniques, in which we are going to cover, we look up, index match, if scenarios, conditional formatting, macros, pivot table, dashboard reporting, and many more techniques. We will also learn about dashboard reporting, which is the most popular tool nowadays. So we're going to see how you can create interactive dashboards with pivot tables in Excel. We will also cover date, time, and array functions. So if you want to complete hours of work in just a few seconds, then this is the course for you, in which you will learn Excel skills that will take you to the next level. And from this course, you will be able to explore the power of Excel formulas and functions. And you will get to know up to which level Excel can perform task automatically. So I believe this is the best opportunity for you to learn a new skill. Thanks for watching, and I'll see you in the class. 2. Fill Series: Hello, and welcome back. In this video, we will be discussing some of the very basic stuff just to get you going. I guess that most of the people know that stuff, but just for the sake of introduction, I'm doing this. Now, Excel is a blank worksheet that means that you have to a building on it. It doesn't contain any values, any formulas. Formulas are there, but you need to apply, and you should have the knowledge about this. So we need to understand what kind of spreadsheet is this, what contents does it have? So Excel has some number of columns. That is denoted as alphabets, and we have some rows here, that is denoted as numbers. Now, if we go on to these little boxes, you can see that when column and row intersects, it makes a small box, which is known as cell. So in order to read the cell address, you can just notice that the column has automatically highlighted, and the row is highlighted too. So we can easily read that we are in H column and four row. That means the cell address is H four. You even don't have to see the highlighted ones as you can easily see the cell adress right here. That is the name box. After knowing what is cell, We have to understand a very basic but very important stuff of Excel. Once you do even a single task in Excel, Excel will try it maximum effort to avoid the repetition of work. That means that you don't have to do each and everything manually. You have to imagine while using Excel that there is only one laziest person in the world, and that is you. So how you will work in that situation, that is the key to use. You have a lot of options available. For example, the very basic and important function of Excel is it understands a patron, if I write one here, and I want to generate an odd number series. So how will Excel know what numbers to generate? Obviously, we have to give a patron to it. So after one, I will write, Okay, there take a gap of one number and write three. And then we have to record these numbers in Excel's memory. Now, what you need to do is after identifying this spatron, you just need to go on this little small green point. As you can see that normal cursosine is at plus in Excel, as we go on to this small green box, you can notice that the cursosine changes. Hold it with the left mouse key and drag it down. So that's how you can generate the even numbers sorry, odd number series. If I want to generate the even numbers two and four, I can select both of them, go onto this small point, and then instead of dragging it manually, I can also double click and send it down. Now, let's try one more time. If I write five and ten, and identify both of them. And now try to double click. It is not working. Why? What do you think? Yes, you noticed it that the column in between these ranges is a blank column. There's nothing written on it. So you have to analyze, you have to understand that in order to double click and send it down, it needs a stopping point. Or otherwise, it will drag it to one leg 48,000 rows. We have a lot of rows in Excel. If we go right to the bottom, control down. We can see we have one lag 48,576 rows and control home for going to first point on Excel sheet. Now, in that situation, it means that if the cell is not filled, you have to drag it manually or otherwise, write anything, just literally anything in the cell behind that, and then we can double click and send it down. But what do you think it will be dragged up to this point or this point? There is only a requirement that in order to drag, the left most column should be filled, but when it is filled, it will not follow only this column, but it will compare it with three columns and it will see which range has the maximum numbers, and it will follow this one. For example, if we write 62 and 67, This range contains the maximum numbers. As we double click and send it down, it will follow this one. Now Excel also has some of the other functions, like, for example, if I write January here, and I try to double click and send it down. It will automatically generate all the months. And as the month ends, means in December, it will start the numbering again. And if we write the short form of it, double click and send it down, it will also work. Let's try Monday. Double click and M O N. Yes. All of them are working. Now, I just told you that Excel understands a pattern. So in that case, we had to give a pattern. But what is the pattern in any in February? How does Excel knows that when using one word, the other word should be this. By the way, if you want to generate a consecutive number series, that means it doesn't have any of the gap, you can just write one and then drag it down. So as we can see that all the cells are filled with one. But we can also notice that we have a little box here? That means it is auto filled. The dragging means the fill ces and the auto fill options we have, we will just see. Right now, it's copying the contents of the first cell and pasting it all over the range. What we will do is, we will choose, Okay, no, I want to fill ces in that range. So 123456 and Boom, you have the answer to 27. Anyways, what is the pattern in January and February? Just think for a while and we will see you in the next video. 3. Editing Custom List: Okay, you might have thought that maybe it's a pre generated list in Excel that is pre installed in Excel's memory, right? So let me tell you this that your answer is absolutely right. At this point, I might think that, I heard that Excel is fully customizable. That means if it has this list in its memory. Maybe I can record, I want to mention some of the name, for example. Smith, Atul Sad, Omer. As I write the first name All the list of these names should pop up automatically. But as you can see, it's not doing because we haven't recorded this list in Excel's memory. So we need to go where this list is inserted. So we can go in file options, and we are in Excel 2013, and 2013 and ten have quite a similar options, so we'll go in options. And you can also go in advanced. So we have a lot of options in this area, but we will go step by step. Means as we need something, we will discuss just at that point, okay? So I have to search for general general options, general options. Yes, I have the general options. And here I can see create list for use in sorts and fill sequences. Okay, that means edit custom list. I have the list right here. Now, what I can do is I can click on this list and start making a new list by myself. But if I already have written that in Excel sheet, and now I want to use this list and define it in Excel memory. That means I don't want to write it all over again, especially if there are 500 names. So in that case, you can import the list from Excel sheet. What I can do is, I will just delete this, import list from cells, and then you have to open the list in the background and just go the cursor should be here, and then just go in the background and select the list. And there you have J two till J six. So we use little column sign for this and just ignore the dollar sign for a while, as we'll discuss it later. Okay, So import, and there we have the list. So as we try to write it now, We can see we have the list. So now that this list have been recorded in Excel's memory, it will even work when you open a complete new workbook in Excel. So that's about it. This is how you can insert the custom list. 4. Excel Short keys and Golden Key: Now, in order to work efficiently on Excel, we need to know the short keys. Just imagine if you don't have mouse and you have to do all the work by keyboard, you must know the short keys. So for example, if I want to highlight, right now, I'm using the left mouse key and just dragging it to highlight the whole area. But if I want to do it, by keyboard. What I will do is hold control shift and then move towards right. What control shift does is it will drag it up to the point after which comes a blank cell. So automatically it will highlight up to this point, and then when all the row is highlighted, I can just hold the control shift again and control shift down. In that way, you can highlight the whole area. Now, let's try some of the very common short keys that has nothing to do with Excel, and you know all of them. Let me just ask you what is the short key for bolding this sex. Control B, for underline Control U, for italic control. If I want to copy the result of this cell to the right. The short key is Control R If I want the same results to be copied down, the short key is Control D. So that's just the basic of short keys that most of us know. But if we want to use all the Excel based on short keys, how much short keys will you remember 365420, or maybe you will prepare a register for, you know, and you will write amazing 600 short keys of Excel. And whenever you need the short key, you will just open the book. Oh, this is the short key, and close the book and then use it. No. So obviously, if you have the full software, you can't remember each of the short key. So what you will do in this situation. Now is the time to discuss one golden key in Excel. After that, Excel will guide you automatically with short keys to use and what is the combination of that short keys. Let's say if I highlight this whole area, and I want to fill this cell color and make it like this. But with the keyboard shortcut. So I don't remember or know what is the short key for that. I have only one golden short key that is t as soon as you press alt key. You can notice that all the menu controller will open. So I have to note that this option is under which tab home tab or insert tab. If it is on a home tab, I have to press, And then it will guide me automatically. Okay, you want to color, then press the again. You can notice that. The, and which color do you want to use? I want to use this color. So I'm just moving the right arrow key, and then space bar, you can also do it by enter. So that is an amazing short key. So it will take you time in the starting, but as soon as you get used to it, it will not take much time. For example, if I want to align center, I know it is on the home tab, so alternate and then. Now, align center A C, A C. So that's how you can align center. If I want to apply all borders, I know the short key all H B A. So in that way, you can get familiar with most of the short keys, and it will save you a lot of time. 5. Introduction to Absolute and Relative References (Fixed and Variable): Hello, and welcome back. In order to become a superhero in Excel. You also need the formulas to do the data analysis and calculations. Only the short keys will not take you to that point. Before understanding the formulas, we need to understand one logic. If we have two numbers, for example, two and four, we know that every formula in Excel starts with equals to sine, and then you can select two plus four. This is also a formula, but we have to understand what is the logic of fixed and variable references. Okay, why not you tell me what is fixed, what is variable? Fixed is that doesn't changes in value. So it will be static. It will have no impact even if the other values change. Variable is that is dependent on the other conditions. Okay. So in this situation, I have fixed and written that number in this formula. So it will give me the answer, but as soon as I change this to ten, it will not show me 12. Why? Because I have already fixed that only add two with four. Never see this cell again. So that means I have made that fix. In order to make it variable, and that's what you will use in most of the situations, you should ask Excel, Hey, just take this cell and add it to that cell. In that way, M two and plus n two, this references will be automatically generated as you click on the cell. You don't have to write this. Press Enter. That means if I will change the value to 30, it will immediately change the answers. One other benefit of doing this is as I write any other numbers, here. The formula writing is just a one time effort. And then I can double click and send it down since it is variable reference, I can see, in this cell or in that position, I will add this cell with that cell. But in the next cell, I will drag the references and now I will add this cell with this cell. So that's the complete beginning of it. Okay. Now if you have a lot of numbers, would you like to do some kind of thing like equals to this cell plus this cell plus this cell? Obviously not because you know it is going to take you a lot of time. That means you have to define some predefined formulas that is used in Excel? A formula like equals to some and press tab Now, you always have to follow the tooltip, but not at all times. For example, there are some conditions where tooltip doesn't suggest the pattern as it should be applied. It is suggesting in this one that you have to select number one. Okay. Let's say this one, and press then, then you have to put a comma according to the tooltip, and then press this. You are on number two now because it is bold. You have to press, number three. So this is the same thing, and it will also take a lot of time. So instead of selecting it one by one, I can just highlight the whole range like this. Hold one cell and then keep on holding the left mouse key and start dragging it up. It will automatically generate the reference that it has selected the range 2-28. Close the bracket, and even if I don't close, Excel will close it for me. Now, if I want to do it again by a keyboard, equals to some, and whenever you write the formula, you will see a little window opens here. For example, if I don't want to use some, rather I want to use some product, and as soon as this window appears, I don't even bother to write the full formula. What I can do is I can just scroll down by holding the down key down down. And there I have this formula to use. You can just press tab. So what it will do, it will auto follow and take that formula right away, and it will also put an opening bracket for you so that you can get started right away. What I want to use is sum. So just remember and keep a practice to use tab function instead of enter. Don't use enter in Excel. Only use tab. Tab, and then I can go on this cell, control, shift up, and then enter. Now, there is one other short key of doing this, that simply looks like a magic. Alt equal sign. It doesn't require any of the effort. You don't have to write equals to, you don't have to write some, just alt equals. It will do all the things immediately for you. So if we have the blank cell, Alt equals, you have to see that whether or not it takes the range up to this point and when it does, press enter. And then you can move it towards control r. Control r y because I want to drag this formula in each of the other cells. What I will do is I will just select this one and start moving towards right so that every cell has a common left and it will take the values from here. As I start considering this cell, and press control r. It will automatically take this formula in all the other cells. And you can also delete this and highlight the whole range and press immediately all equals. It will immediately total all the ranges. Isn't that amazing? So that's how you can use Excel positively, and Excel will guarantee you a lot of time saving. Thank you for watching, and we will see you in the next video. 6. The Marksheet: All right, now is the time to apply what we've just learned from the previous situations to work efficiently on Excel. So let's just do this. The new assignment is called Mark Sheet. What I will do is insert a new sheet by clicking on this plus sign and double click to name it as Mark Sheet. Now, you have to prepare a mark sheet for five subjects and 50 students. So whenever I write, for example, student one, Excel identifies by default that if the first is alphabet and after that comes the number. What Excel will do is automatically generate the number series. I'll show you right away. When I drag this down, you can notice that the number changes in the tooltip, you can see that. I'm at student nine right now. What I'll do is drag further to 50. Oops, This is 51, so 50. That's just perfect. Now I will release the left mouse key, and there you have it. I will go on the top. Okay. What I can also notice is student one is just about a perfect size. But if I go on student ten, it requires a little bit of more space. You know, it's a little bit congested. It's just outside the cell. So we need to adjust this. What we can do is go between these columns as this sign comes, it means that you can hold the left mouse key, and then you can drag it towards right to adjust it a little bit. But if I have a size adjusted like this and I don't have to do it manually, what you can do is go between these columns, and whenever you see this sign, just double click it. Okay. Now, we have to take five subjects. Take any subject, for instance, I will take English. I will take economics, business, math, and you can notice that it is just outside the cell accounting. And let's say finance. Now, I want to auto edgest all these columns. What I'll do is just select first column and start moving towards right by holding the left Musky. And then just go anywhere between any of the columns and double click to auto. See, it's quite easy to do this. All right, now is the time to test your skills. You know how to work efficiently on Excel. So I'm giving you an assignment. The assignment is that you have to prepare a mark sheet, give any random numbers. For instance, let's just say that you give Mark 32 64, any number. But keep in mind, the number not be less than 30 and should not be greater than 90, and you only have 1 minute to do this. Just try it by yourself. Let's see whether or not you can go through this and we will see you in the next video. 7. Random Number Generation: Okay, have you done that? No. Or maybe you have done that, but not within the same time frame, right? That's because you don't need to write each and everything on Excel by yourself. Excel is for you, not you are for Excel. Excel basically serves you with a lot of formulas. That means for nearly each and every situation, there is a formula. When I just said that give any random numbers, there is a formula for that equals to between equals rand between. What rand between does that it will automatically give any random number, but within the limit you define. The bottom limit would be 30, because the tooltip suggests that, and now the top limit is bold, so I have to mention the top limit. What is my limit? 90 maximum max, and inverted, sorry, bracket close. But even if I don't close that, Excel will close it for me. There's one number 83. What I will do is just rag it towards right, and then I need to see first that whether or not I can double click to send it down or might be not because there's a rule for double clicking the drag option. You have to see whether or not your left most column is filled with anything or no. You can see that it is filled with student names. That means I can double click to send it down, and there you have it. All the results right away. 1 minute? No, it's a lot of time. I can do it in 5 seconds now. So that's how you can do work on Excel. 8. Clearing Formula With Intact Values: Well Moving one step further, we have some other components in Mark sheet. What are that other components? Marks obtained? Yes. Marks obtained. Enter. I have to adjust this and double click. So what do you think that if I double click to adjust the size of, you know, the cell, do you think that student one marks in finance is 72? Do you think it should change when I double click on this? Obviously not. But when I click, it changes. Why? Because the formula suggests that ran between. It means that Excel sheet refreshes and processes the data again whenever you change any of the cell contents or whatever you do. So on every refresh, Excel checks the formula again. It says ran between, I will again give a random number. Excel sheet refreshes, It checks the formula again. It again suggests the rand between, so it will give again a random number. So, you know, we need to avoid this situation. What you will do in this situation, just delete the formula? No, because the values are coming directly from the formula. So if you don't have the formula, you don't have the value. So control Z, Control Z is for undo. What I will do is just select all the cells containing this formula. Now, what I have to do is do something like keep values, but remove formulas kind of thing. So for that, you just need to copy this whole range. You can see the dancing selection. Now, what you will do is right click, just anywhere. No, not anywhere, but in the first cell. Right click and go to paste? No. Pasting is the same thing as you did before. So it will again, paste the same thing. I'll go in paste special options. Now, you also have some shortcuts at the top, but I will go and see full options. In the paste special. I will click on this. Now, you can see that you have some options. What you want to do, paste all means paste all the same way that it is. Paste formulas or paste values. I need to paste values only. So I'll just click on this and click Okay. Wow. You have removed the formulas, but you have keep the values. How you know this? Because this is the content bar. Means it's mentioned that this is formula bar, but I call this a content bar. Okay. Now I want to clear this dancing selection also. So just press Escape, and there you go. That's about it. We will see some of the other important steps in the next videos. 9. Key Components of Marksheet: Now, we have some further contents of the mark sheet. That is Marks obtained, how you will calculate the marks obtained. It's very simple. We have discussed it before that the formula is equals to some and I will press tab to auto follow and open this tooltip. Always remember to use tab button. In this way, you don't have to write the formula, for example, some product. I've just written some. I will go on this selection and press tab button. It will auto follow and it will open the brackets for you. What I'll do is not hit tab and do all this stuff, but a golden short key. That is all equal sign. It will automatically select all the range, and then you can just confirm that if it is fine, then hit enter. Now, you have the totals. If you want all the totals, just double click to send it down. Now, there will be total marks also, total marks. Double click to auto adjust. And the marks for five subjects will be 500 because all the marks is out of 101, two, three, four, five, 500. Double click to send it down. You have the auto fill options, but I will not click on Fill Series. Copy cells is just fine. Now I want to calculate the percentage. For percentage, there is no specific formula, rather, It is approximately the same that you do on calculator. How you do that in calculator, divide marks obtained by total marks. Means marks obtain, divided by total marks, multiply by 100. So that's the formula. This is a manual formula that you have to create equals to marks obtained. I will not write 350/500, rather, I will just click on the cell so that it can move down and show the results for all the other cells, and divided by total marks. When I hit Enter, you can see that 0.7 is the percentage. I didn't multiply it by 100 because I want to use a percent format. You have all the formatings in this area, home tab, this area. You can see all the formatting, so I will use person style. Person style has multiply by 100 by default. So I will double click to send it down that's a fairly simple formula. Now, let's just say that if the student one marks changes from 79 in finance to 80, when I hit enter, you can notice that the percentage is not changing. Why? Yes, of course. Because there's a very minor change. So I can only be shown if you have the decimal places with it. To enable the decimal places, what you will do is just select all the range. The short key for this is just click on the first cell, then hold control, shift, and move downwards. Then go on the top and increase the decimal places. One, two, I think two is fine. That's about it. In the next video, we will move one step further. 10. Introduction to IF Then Conditions: Now we have to mention the status. The status literally means we'll just mention heading first. Status means that based on a certain criteria, we have to define whether a student is pass or fail. Let's suppose if I have given you the papers to check. You will reply me back, Well, I don't know how to check the papers because you haven't tell me any criteria. What I will mention at that point is C. If the student has greater than 50%, then he is pass, otherwise, he is fail. Excel is simple enough. What you have just said, write it in a formula equals to if This cell is greater than 50%. Then we will mention coma for then inverted commas because we have to mention pass fail, all the answers we want to print under inverted coma. Then he is p otherwise, we will mention otherwise. He is fail inverted close and enter. There you have the result. If I double click to send it down, you can see that some of the students are p, well, most of the students, and some of the other students are fail. So we will check it randomly whether it's okay, all right. Now, if I try to mention 47.2%, instead of 47.2, I will mention 50. What should be the answer? Pass or fail? He should be pass. But when I enter, he still fail because you haven't provided the correct logic. You have just said that if the student has greater than 50%, then he pass, otherwise, he's failed. So you know, it's calculating accordingly. It is your mistake that you have mentioned the formula incorrectly. So we can just change that. Where you make the changes, you have to go to the first cell, double click on that, and then you can write greater than or equals to, greater than after that, you will mention equals to and enter, double click to send it down, and then we will review this again, 50%. It seems fine. But it's not calculated according to the formula. What I will do is just drag the formula which we have above to all the other cells, double click to send it down. That's about it. These are some basic logic of if then criterias. 11. Nested IFs (Looping Function): All right. Moving one step further after the if then conditions, we have a marking scheme, we have to give the grades. Grades mean A B C A plus B plus C plus fail. You have all seen a marking scheme like this in your result street. What this mentioned is, if a particular student has less than 40%, then he fail. But if he has 40-49%, then he obtains D. If he has 50-59%, then he will get C and moving forwards like this 70-79% A, and if he has greater than 80% or maybe greater than or equals to, 80%, then he will get a plus. So this is very simple, and you have all seen that scenario. But what if you have to mention all this marking scheme in a single formula right there, you have to write that formula. So we will start off with what kind of condition? You tell me? Yes, equals to if if this percentage is less than 40%, then the student will get fail. Otherwise, otherwise, what? Pass? No. Because if you mention pas, the logical test will just end because it could have two answers, whether the condition is true and false. So you have to mention either pass or fail. So I would rather do a trick here that if the student has less than percentage, if this condition is true, then he will be fail. Otherwise, otherwise, I have mentioned this coma. Again, check again, what? Heck that if the student has less than 50%. If that's the case, then he will be he will get D, otherwise, check again. I this cell is less than 60%. If that's the case, then he will get C, otherwise, check again. If this cell is less than eight sorry, 70%, then he will get B. Otherwise, check again. If this cell is 80%, if that's the case, then he will get A, otherwise, because I've mentioned all the less conditions, if less than 40% than fail, if less than 50% than D, less than 60% than C, if less than 70% than B. So in all the other conditions, in all the other scenarios, it will obviously be above 80%. So in all the other conditions, I will not mention if conditions, but I will say that otherwise, give a plus So this is called looping. Looping means under one if condition, there are multiple ifs. If one condition doesn't satisfy, it will move to next one. If that not satisfies, it will move to next one. If that condition is not fulfilled, it will move to next one. So it's also called nested ifs. So if anyone ask you, what do you know about nestedfs? This is the nestedfs. You've also noticed that We have just opened the brackets in all ifs. We haven't closed it yet, because it has to run together. So what we will do now, we will count the brackets, one, two, three, four, five, and we will close the brackets together, one, two, three, four, five, and hit enter. I think there is a mistake. Oh, I forgot to put the inverted commas here. So inverted commas and enter. There you have it. The results is A, and if I drag it down, you can see all the results. Isn't that amazing? And also, if you forget to close this brackets. Excel is smart enough, and it will suggest you enter. Well, I have checked your formula and found some mistakes. It seems like you have forgot to close the brackets, and I've tried to correct it. Am I right? Yes, of course. How could you be wrong? So I will accept this. Yes. Double click and send it down. Whoa, you have all the grading system automatically calculated. That's the power of Excel, and that's just the basics. We will move steps further as we progress through this course. 12. Ranking Positions: All right. Now is the time to give positions to the position holders and all the other students. So we will write position. What other word do we have for position is rank. So the formula is simple enough equals to rank, and you have to use this one. But this functionality is particularly compatible with 2007 and earlier, but don't worry about that. It will work just fine. Press tab, and then you have to understand that what is your tooltip suggesting. So it's saying that give the number that you want to rank. So rank is normally given by percentages. We'll see how much percentage a student to obtain. This cell, and reference, it means in comparison with. In comparison with how many students rank this student percentage in comparison with, starting from this point and control shift down. So it means we have to compare the percentage of this student with all the other students in the range. Only then we can say that okay, his percentage is quite good, and he's, for example, on eight position. Okay? If I hit Enter, you have the position. So I will double click and send it down. Spend 1 minute and see if you think that all the positions are okay. Just spend a minute and identify the percentages and according to that, their position. Is it okay or not? Yes, of course. It's not okay because 76.8% is first, but 58% is also first. How is that possible? That's possible. Now, I will ask you one question. If you think that you can get a first place in the class, what are the chances and what are the possibilities? You don't understand my question? Okay. There is a possibility that you can stand first in class if there is only one student, right? So this is the same case here. If I have compared two student, one has obtained 95%, and the other has 90%. So who is first and who is second? Obviously, 95% is more, he will get first position, and the other person will get the second position. If I remove that person from the class who holds 95%, then the other person will be first. Similarly, that's the case here. If you are ranking first student, it should be in comparison of 50. But if you're ranking second student, It should not be compared with only 49 students. It also should be compared with all 50 students. So that means that if you drag down the formula, the cells will move, which is good. But the range will also move from here to here, here, here. So it will not include all the other cells in the range. So the comparison will be lesser and lesser. That means when applying this formula, I have to select the whole range of reference, and I have to fix this. That means absolute and relative references. Fixing is that you put a nail on that thing, so it won't move. To fix the reference, I will just select it and I will press a four key. And then press enter. Now I can drag it down. Notice that results has changed. Now, this seems to be fine because obviously if the student has got C, he should be 31 or so. Ranking positions is just like that. You have to use a cell reference, the number which you want to rank, and the reference in comparison with all 50 students, so you have to keep that static for all students. That means that you have to select the range and then fix it by pressing F four key. H. 13. Conditional Formatting and Data Bars: If you have a lot of results, for example, in the status, a lot of students have passed, but only a few have failed. That means when you read through all this data, there might be a chance that you might overlook that someone has failed. Why is that? Because all the students have pass, that might be the case that you overlook, you happen to ignore this. So in this case, what is the better solution for that? If I color fail by red and I color pass by green, So in this way, you can never ignore this thing. But do you have to do all that manually? The answer is no. Because, you know, it takes a lot of time, and especially you have to keep in mind that if I've given example of five students, you have to multiply it by 100. So if you have more results, what you will do in that situation, you have to think according to that. So that's not the solution. I will press control Z under control Z d. Now, how will I color this automatically, so that when pass comes, it will automatically get green and when f comes, it will be in red. That is conditional coloring. That means all this is related to formatting things? That is conditional formatting. What you will do is just select the whole column, and then go on this conditional formatting tab in the home tab option. Go on this option, and you can see a lot of conditional formatting options are there, but we will go step by step as and when the situation comes. So I will highlight the rules, not greater than less than between, equals two. Equals to, when the cell value is equals to pass, highlight it with red, No, Highlight it with green and press. Now, I've selected the whole column. It means all the cell which has the value pass will be highlighted in green. But even if I go down up to 97th row, and I mention pass within this cell, as soon as I enter Notice that it has already colored this thing. That's amazing. And that's just the basics of it. You can go way further in that. I will go on the top control home. To highlight fail, you have to select this column again and give an other conditional conditional formatting, highlight cell, and then go equals two. Right here, if the person has failed, highlighted in red, it's quite good and press. Similarly, if you want to highlight the grades, you have to follow the same steps. Just select the column, go to unconditional formatting, highlight cell rules, equals to A, highlighted with green. Okay. Conditional formatting, highlights cell rules again, equals two. B, highlighted with for example, yellow. Conditional formatting, highlight cell rules equals to, again, C, highlighted with, let's say, you don't have much options right there for coloring. I have the custom format, and it works similarly like you do it on paint, world, PowerPoint, whatever it is. So I'll go on fill tab, and then you can select any color. I'll just select color. You also can fill effects. I'll go on fill effects to give a little bit of more amazing look. Diagonal up, and you can select any color combination. And. That's okay. Then D, D should be in, for example, custom formate, and orange, D, and then conditional format again. Then go again in conditional formatting and go to highlight cell equals to p. Light red is just fine and. That's how you can use the conditional formatting at a very basic level. What we can also see at this point is, I want to see a little data bar or a graph thing that if the certain percentage is more than 70 it should be filled up to this point and if the percentage is less, it should be filled up to this point to have a better graphical representation. What I will do in this situation is I will highlight the whole column, and you can go on conditional formatting use data bars. Now, as soon as I select any of the data bar, you can notice that it is automatically setting the graphs itself. Now, it's not according to 100%, but the maximum percentage that a student has, according to that, it is filling up to maximum point or minimum point. Solid fill is something like this. It will be filled in solid colors, no gradient fill, no shading. I think this one is just fine. This one. That's a basic session on conditional formatting. 14. Freeze Panes: Now, just think if you're analyzing the data, and I'm checking the results. The overall results of the students are like this like this, and I go on this point, and I've seen that student 46 has quite a good marks in 88 is very good marks, but in which subject, in which subject. Yes, you have to go on the top to see the heading. Oh, it's business maths. Okay. Now, I'm seeing further. Student 41 has very poor marks, same like his name in which subject. Go on the top again. Oh, it's business maths. You know, you can do this, but that's very annoying. Seeing the result of a student, if it is 90, guess the subject. So we're not guessing anything here. What is the problem with this? When we move down, the headings disappear. We don't want that. We want that when Data moves, the heading should not move. The heading should be static. In order to freeze this, we use freeze pains. Pain means if I want to freeze this row, it will be freezed like this. If I want to freeze the column, it will be freeze like this. I want to freeze the intersection point. When I move down, the headings should not disappear. But when I move over, the student names should not disappear too. What I will do is highlight the intersection point of this rows and columns. As soon as I highlight this and go in view tab, you can see the freeze pains option. Now, you have two options, top rows and first column. Usually in the top rows, there's a heading or company name, so you don't have to do anything with freezing that. And the first column is normally serial numbers, so there's no point in fixing that. So we will use freeze panes based on the intersection point. So it is based on the current selection. So my selection is this, this column and this row will be freezed as soon as I click. You can see little intersection point between these two. If I move down, The headings are not disappearing now. But if I move at the right, the names are not disappearing. That's it. This is freeze pains. 15. Table Formatting and Range Conversion: Now we know that. Presentation is also very important. What if I want to formate this to format? What I'm thinking is, if I select the first row, it should be bold and the color should be this in the second row, the color is white. If I move further, the color be little lighter than that? So in the other row, one left white and one left colored. So that's better presentation. Even if you cry hard, it will take you a lot of time to format that by yourself. What I will do instead of this is select the whole table. Control shift down from headings till bottom. From headings means this heading, not the company name. Okay. And I will go in home tab and use the option format as table. It will immediately format it for you. You have some predefined table formatings, so you can select any of them. For example, I like this one. The medium gradient, table style medium 12? As soon as I click this, it will ask me to highlight the range. You can also highlight from here, but as I have already selected the range, so it's showing the range. Now, it will ask you, does your data have any kind of headings? If you have headings, you will check on this. My table has headings. Consider these as my headings. If you don't have headings, Excel has to put an automatic headings. For example, like column one, column two, column three, that would be the headings, because formate as table works according to the headings only. So when I click, Okay, Wow. You have the formatting right away. If you want to change the format, notice that whenever you will click on the table which is formatted, you will get one extra tab to design it further. So if I don't like this color, you can just drag over and check all the results which seems to be appropriate. Okay this is fine, you will click on this and it will immediately change the colors for you. Now, let's just test your knowledge. If I click on this cell and write equals to and select this cell, what will be the cell reference? L column and sixth row, it means L six. But we have a problem here. It's not actually the problem, but if you don't like to mention formulas in this, situation. In this type of references. You have to remove the format as table. That means removing all the formatting? No. It is exactly the same as copying and pasting values only. What I'll do is, I'll just select the whole table, control shift right and down, and then click right click anywhere in the table. Go in the table option and convert to range. Converting to range what it does, if you click yes, then it is like you have format it all by yourself. That's how you can formate the cells and tables. 16. Sort and Filters: Let's just say that all the students that we have comes in different batches. So I need to insert one column between these two. To select the column behind, you need to select the forward cell. B, I will highlight, and then insert, it will be inserted behind that. After that, we will mention the heading batch. And there are different batches for each student. For example, someone is coming in morning batches, some in afternoon batches, some in evening batches, and you can repeat in any manner. Just give a random one. For writing further, I will just select this and double click, so it will automatically repeat the patrons But I can notice a problem here. The problem is, I've copied and pasted the formating, but, it seems to distort all the formating that we had, and it doesn't look good. So what to do? Do we have to apply the format as stable again and then convert to range again? No. See if any column have the right formatting. Yes, all of them. The column which has the right formats, you will just select this column, and select format painter. You have little brush inside this. It's just like you have dipped the brush in the paint, and then as you selected the column, you have to paste on the column. That's how easy Excel is, and that's what we are learning. We are not here to do a dead onry and a donkey kind of work. We have to do a smart work, not hard work. Now we have to apply filters to it. Now we have to see the results only for the morning batches. The students who are coming in the morning. I'll just need the report for that only. But these are all mixed. What to do? What you need to do is just hold control, shift, and move towards right. The easiest way of doing it is that, select all the headings, go in the insert tab, and then go on sort and filter and select filter. You have little drop downs at every heading. Sort and filter means sorting means arranging in alphabetical order, A two z, z two A, and ascending order, descending order, if you have numbers, and filtering means that filter the particular results, means like it's the case here. I don't want the students for all batches. I need only for the students who are coming in the morning. As soon as I click on this drop down, you can notice that you have little line here. That's a boundary line where sorting options end. It means you have three sorting options. You can either sort it by A to z, z to A, and you can also sort it by color. But we have some filter options here, also. We are not going to see all of them at once, but we will see the basics of it. So you have the data gathered. It suggests that in this column, afternoon criteria is there, evening criteria is there and morning. These are the only options used within this column. So what do you need? Do you need all? No, I will uncheck this. And what you need instead is the morning badges. So I will click on this morning badge, check it, and press. Mm. You are a genius person right away, you can filter this. Filter and sort. Believe me. It's such a powerful option that even if you know sorting and filtering, you can do a lot in Excel. Similarly, if I press control Z, or, if you have applied filters on multiple headings at a single time, how you can clear them all? For example, I have the filter here And I have the filter here. So one filter is there and one filter is here. I also put the filters in pass. Show me the students who passed press. Now you have three filters. How you can them all together. Going on to the sort and filter options and clear. So that's how you can apply filters. Now, what I want to do further is take the students on a party. Take the students on a party. But with students, the students who have between 75% and 80% or let's say 85%. Because I'm quite sure that I don't have to take students because my class is not very good. So what I will do is just select this percentage heading. Click on this sorting option and go to number filters. You have all the options. You can highlight the numbers who are equal to 70% does not equal to certain percentage, greater than certain percentage, greater than or equals to, than less than or equals to, and between. You can also sort top 10%. So I think I might be taking maximum ten students. So if I click on this, you have top ten items. If you want to take much lesser students, you can click five, and then okay. You have the top five genius students. Even the genius have 76%. That's it. If you want to fill between, go to number filters and go on between is greater than or equals to, 70% and is less than or equals two. Is less than or equals to, let's say 85%. Notice that you have to mention the percentage exactly, not right 7070, 70 is not equals to 70%. Just keep that in mind. That's how you can sort and filter. I will clear this and we will see you in the next video. 17. Max Min Average Countblank Count if and Much More: Need to calculate number of students present subject wise and number of student absent subject wise, minimum mark subject wise, maximum marks, and average marks. I can see that it's not fit in this cell, so I can just double click it to auto dj. Now that I want to calculate the number of students who were present in a particular subject. Before applying this, you need to know that these formulas are very, very simple, what we use in daily life. I want to count the students who are present subject wise. The formula will be equals to count and tab. Now, I will just move one cell up with the up arrow key and up to this point. And then I have to select up to the top. So control, shift, and upwards and hit enter. Now, you have 50 students present. If I want to apply in all the other cells, you can just grag it towards right or otherwise. You can just select this cell and then start moving to right by holding the shift key like this and control. Now, all the students are present right now, because if the student is absent, I will just not put its mark. I will remove some of them So we have number of students present for each subject. Now I want to calculate the number of student absent subject wise. Now it looks fairly simple that if you have total 50 students and 46 are present, others are absent, obviously. So there is a lot of choices for writing a formula. You can just hit equals two, and then click on this cell and sorry, you can just write 50 and minus sign, and you can just minus this cell from that. You have four. You can move it towards right, and then you have the answers. But if you want to calculate directly, what you can do is because I know that the criteria which I want to count is blank, so the formula of L b equals two count blank, count blank. I will hit Tab key, and then I have to select the range as the tooltips suggest. So click on this key and move by clicking Control shift up. Notice that if the cell is blank, the auto dragging will stop there. So you have to pass on by pressing the up key again each time the selection stops, up, up, up, up, up, up like this and hit enter. Then I can drag it towards right by selecting the first cell, which have the formula, control r. Now, I have to calculate the maximum mark. These formulas are really, really simple. Maximum mark is equals to max, and I will hit tab, and then I can select the range. And we can move right Control R. Similarly, for minimum marks, the formula will be. Yes, equals to men and it tab, and then you can select control shift up. Just keep pressing up until and unless it touches the top and hit enter, and then you can move towards and control r. Then I have to calculate the average mark subject wise. The formula is Yes, equals to average. Control shift up, and then we can control r. That's how you can calculate the simple things. If I don't want blanks to appear. Rather, I want that if the cell is blank, absent in that or write A for absent. And I want to do immediately for all the cells. What would you do in this condition? I have to write absent or A in the cells which are blank. And let me give you a hint. It is exactly the same option which you are using continuously on Word, PowerPoint, and all the other softwares. Control F. Control F for find. But first, I have to select the whole range from headings to bottom. Control F and rather finding it, I need to replace the cells with cells. Blank cells, so I will find blank. Finding blank is just not writing anything on that and replace with A. Spit letter A, and replace all. A done. We have made 17 replacements for you. Thank you. And close. Now, you can see that it's not aligned in the same order. To arrange all in the right sequence, what you need to do? Use this option. But what if I want to just highlight the whole table and want to use a short key for operating this. We have discussed it before. Yes. On golden key that is alternate. Now, this option is in home tab, and AC. That's how you can use the short keys easily. Now that I have changed the criteria from blank to A. You can notice that number of students absent is not showing any result because you have mentioned a formula count blank. There's no blank now. What you will do. We will be using conditional counting, which identifies, if the criteria matches means there is a anywhere in this range, then count it. Conditional counting or any kind of conditions is if and I want to count so equals to count if count if tab. First, we have to select the range the same way. And coma, then you have to mention the criteria. What to add? You have to open inverted commas and write A, and then close the inverted commas and hit Enter. You have seven, I will drag towards right and I will get all the answers. So that's very simple. Now there are some other functions as well. If I go on the top and I move on, let's say this position, just spell for me how many grades do we have? I have A plus some of the A plus students. So A students, Some students who get B, C, D, and some of the students who failed. Now, I need to count how many students got A plus, how many students got A, how many students got B, and so forth. What you will do in this situation. This is again, conditional counting. How I will count is just equals to, mention count. Count I I'll go on this and hit tab. Range means, according to criteria, This is your criteria, but where do you have this data in the data set? So this data I have in this column. If you select the whole column, you don't need to freeze this range. So it's better in most of the situations. Okay, and what is your criteria now? I can mention a plus inverted close. But if I drag down this formula in other cells, what would you get same results as for A plus? Because you have to open and change the criteria in each and every cell if you want to see the updated results according to this. So that's not the way how we do things in Excel. Rather, I will remove this, and I will say, Okay, pick a criteria from here. So when we drag down, the criteria automatically changes from this, this, this, this, it will move down automatically. So it enter, and then we don't have any Aplus. We don't have such bright students. Okay. Just double click, and you can see the results. How much is the total for that? You can see right below. This is a tool bar which facilitates immediate results. So is 50, means total is 50 students. Count is six because there are six different criterias, average is 8.3. If I want some more tips, maximum, minimum numerical count, you can just right click on this green ribbon and you can see all the results. Just check, check and check. 18 are maximum in this and zero is minimum in this. If I select these three cells, then 18 is the maximum, six is the minimum. That's about it. Our first assignment is complete. You can find the completed assignment within this course, named Mark sheet finished. Thank you. 18. Prepare a Sales and Bonus Report With Manual Totalling: In this video, we are going to go one step further. What you have right now is the sales report for the quarter ended on March 2015. Now, just have a look at this data and try to understand what it means. Yes. We have recently started a business for selling a unique kind of bags in the market. So we have only one product right now. So the price is 275 for all because that's what we are only selling. So obviously, to make the sales, we have hired some salespersons to promote sales, and their target is to sell the units and increase customer reach, that means increasing links with customer so that we can get more business in the future. And we have some sales amount and profit. Now, if I want to calculate the profit by myself, I will just delete this Sales amount is the simple calculation is equal to unit sold, multiply by selling price. If I hit enter, I can see the result. But if I double click, notice that there is no result. So there is an easy way of checking the errors in the formula. You can just double click where you feel there is a mistake and oh, you can notice that the sale price has also moved with the sell value. It should not move. It should be static at 275. What we'll do is just click on this cell and press F four key to freeze it. Rather I will do it here, F four key to freeze it and double click and set it down. Now, we also have the profit margin, which is applied on the sales amount. What we'll do the same is the calculation here equals to sales amount, multiply by profit, and we can freeze that to enter and double click and send it down. Now, what I want with you is, this is totally a raw data. What I want you to do is just compile a report for me. This is the report for three months. I want the totals at the end of every month. For what? For units sold for customer each for sales amount, and for profit. For January, February and March, I want a totals. Now that's very simple task. But we can see that we have raw data. So what is the first step? Obviously, if you have to make the totals, your data should be arranged in months. So the easy way of doing it this is, I will just right click on this month heading or anywhere within this column? Right click on that and select sort and click HUZ. Now, the data is arranged, but you can notice that still it's not in the right order because you have already said that you need to arrange it in alphabetical sequence. According to Alphabetical sequence, it's just fine. February comes first then January then March. But we don't want that. We want January to be first. So in this situation, we need to use a predefined list that Excel has by default. Do we know about that? Yes. That is the custom list that we have checked before, and we can also make that list, too. So what we'll do is, we will just select the whole table, including headings. So when I tell you headings, you don't have to include this heading, only the data headings. So, Now, I will go in the Data tab and click sort Option. So it's giving me some criterias. How do you want to sort your data? By month by salesperson by nitsol? I want to sort by months. And the values should be in order of A to z, no, Z to A, no, but custom list. This is exactly the same list we have seen before. So it is already in the right order. So what we'll do is just click on this, select, and you have the order right there and click. It will automatically arrange. Now, I want you to insert a total of genery Unit sol customer ch and sales amount. What you will do? How will you determine where to put the totals? Yes, you're right. You will see that when a month ends, I need to insert our row here. I will just select this one because it will insert the row upwards, right click on this and select insert and write January. Totals. What we can do is just add this data, t equals. Okay? And we can drag it towards right. So I will just select this one and start moving towards right and hit Control R. In this way, you can have the totals. Now is the time to select February. I mean, you know, the month changes here, so I need to insert one row and insert February. So as you can see, that, I don't need to write all the data by myself because Excel automatically detects, Oh, this value is used before. So do you want to write February? Yes. So I want to will just click on this and move further and write totals. Okay. So I want to add this too. But do you think that all equals our golden key will work here? Yes, you might think that no, because it adds all up, but this formula is smart enough to identify itself. Were there is a formula in between means this is not the value. This is formula, so it will automatically stop up to this point, this one. This is a very intelligent formula. At equals. Yes, I'm right. So tab, and then you can Control R. Now, same is for March, totals, and then t equals sign, and then control r. If I also want the grand totals to appear, you can just add the totals for each month equals to Gen totals plus February totals plus march totals. C eight plus C 22 plus C 33 tab, and then we can rag it towards. That's how you can make the totals and arrange the data. 19. Grouping and Auto totalling: If you want to prepare a summary report, which is to be discussed in the quarterly meeting, your director might ask you, Okay, Mr. Alex, just do one thing for me. Prepare a summary report, which is easily expandable so that we can discuss further or, you know, compressed if we don't want that details. So I need the summary for just January totals, February Totals, and March totals, and the grand total. So, you know, we are discussing the performance just month wise, in which month we have more sales, in which one we have less. So One way of doing is that you can select all the rows and right click on any of the cell and click hide. But hiding the rows is a little bit complex, if you want to unhide, you have to identify where the rows are hidden. So you can see that first row is two, and the other row is eight. I know that there are some rows hidden behind this. I need to select again, first identify where the rows are hidden, select again, and then right click, then click unhide. There might be a better solution for that. The solution that we have expand collapse option, like plus minus sign. When we click the plus sign, the data will be expanded, we hit the minus sign, the data will be compressed. We can just select all the rows without the totals and then go on Data tab and select group. This is minus. That means this is expanded form, if you want to collapse it, we will just hit this minus, plus sine, you can expand, you can collapse. Similarly, I will select all the months again without the totals and then grope it, collapse it, and then select March totals without the totals and then grop it and collapse. In this way, you can have a summary. But let's say that you have prepared this summary for the whole year. So does that mean that you have to collapse one by one? No. You can notice that you have two tabs now. One is the full details, and one is the summary form. Details, summary, details, summary. So that's how easy to prepare the report via groupings. All right. Now, I want to now. All right. Now, I want to ask you a question that if you have to do the work like this, what is the use of Excel? Yes. So Excel reduces your effort. So Excel makes a difference when it comes to, you know, data processing. Excel makes a huge difference and time reduction when it comes to data processing. That means you don't have to do all these steps. What I will do is just cont, do. And undo all of this. So I'm on the same situation after undoing all that. You can also start over with a fresh rep. So what I have right now is the same. Now, count the steps which you have done before. You have in arranged the data, then inserted March total, then you write all equals, sum, all this, then February, then March, then you applied the grand total by yourself, then you apply groupings. No need to do all this stuff. Because what you can simply do is the first step is the same that you have to arrange the data. Select the whole table, and then you have to go to Data tab and go in sort option, sort by Months, and order will be custom list. January February March. Okay. Now the second step is Excel can identify where to put the totals automatically. Now, how you see Excel C is the same. You identify, when January ends, when month ends, I need to put the totals. Subtotal can do the functions for you. You will go in the data tab, and then you can click subtotals. Now, this is the same suggestion at each change in months. So that when month changes, you need to make the sum. Use function sum, you have, you know, a lot of them. So I need to use su now. And what you need to add, I need the totals of profit, sales amount, customer rate, number of unsold all together in one step and boom. You have the totals for January, February, and if I go down, you have the totals for March as well. Oh, you have the grand totals as well. So that's how easy. It is to apply the subtotals. If you notice, you also have the groups. This is detail. This is summary, this is more summary, so this is literally amazing. You have to develop a mindset how to work efficiently on Excel. The most efficient, there's a saying that The most effective programmer is the one who write the shortest code. We have to write the shortest code possible. That's how you can apply the simple subtotals. 20. Hiding and Unhiding Rows Grouping and Auto Totaling: If you want to prepare a summary report, which is to be discussed in the quarterly meeting, your director might ask you, Okay, Mr. Alex, just do one thing for me. Prepare a summary report, which is easily expandable so that we can discuss further or compressed if you don't want that details. So I need the summary for just January totals, February totals, and March totals, and the grand total. So we are discussing the performance just month wise. Which one we have more sales in which one we have less. So one way of doing is that you can select all the rows and right click on any of the cell and click Hide. But hiding the rows is a little bit complex, if you want to unhide, you have to identify where the rows are hidden. So you can see that first row is two, and the other row is eight. So I know that there are some rows hidden behind this. So I need to select again, first identify where the rows are hidden, select again, and then right click, then click unhide. There might be a better solution for that. The solution that we have expand collapse option, like plus minus sign. When we click the plus sign, the data will be expanded, we hit the minus sign, the data will be compressed. So we can just select all the rows without the totals and then go on Data tab and select group. This is minus. That means this is expanded form, if you want to collapse it, we will just hit this minus, plus sine. You can expand, you can collapse. Similarly, I will select all the months again without the totals and then grope it, collapse it, and then select March totals without the totals and then grop it and collapse. In this way, you can have a summary. But let's say that you have prepared this summary for the whole year. So does that mean that you have to collapse one by one? No. You can notice that you have two tabs now. One is the full details, and one is the summary form. Details, summary, details, summary. That's how easy to prepare report via groupings. Now, I want to ask you a question that if you have to do the work like this, what is the use of Excel? So Excel makes a huge difference when it comes to data processing. That means you don't have to do all these steps. So I'm on the same situation after undoing all that. You can also start over with a fresh report. So what I have right now is the same data. Now, count the steps which you have done before. You have in arranged the data, then inserted March total, then you write all equals, you know, some all this, then February, then March, then you applied the grand total by yourself, then you apply groupings. No need to do all this stuff. Because what you can simply do is the first step is the same that you have to arrange the data. So select the whole table, and then you have to go to Data tab and go in sort option, sort by months, and order will be custom list. January February March. Okay. Now the second step is Excel can identify where to put the totals automatically. Now, how you see Excel C is the same. You identify, when January ends, when month ends, I need to put the totals. Subtotal can do the functions for you. You will go in the data tab, and then you can click subtotals. Now, This is the same suggestion at each change in months. So that when month changes, you need to make the sum. Use function sum. You have a lot of them. So I need to use su now. And what you need to add, I need the totals of profit, sales amount, customer ch, number of unisol all together in one step and boom. You have the totals for January, February, and if I go down, you have the totals for March as well. Oh, you have the grand totals as well. So that's how easy it is to apply the subtotals. And if you notice, you also have the groups. This is detail. This is summary, this is more summary. So this is literally amazing. So you have to, you know, develop a mindset how to work efficiently on Excel. There's a saying that the most effective programmer is the one who write the shortest code. So we have to write the shortest code possible. Okay? So that's how you can apply the simple subtotals. 21. Removing Duplicates and Sales Summary With Sum if Function: If I want to remove the subtotals and groupings together in a single step, what you can do is just click on the sheet, means, select the whole sheet with this little triangle and go in the data tab and select subtotals. Now, an error will appear, ignore that error and click. Remove all. I will instantly be removed all the totals grouping all together. Now that we have removed the subtotals, I need to prepare a summary for all the salespersons and see how much each salesperson have sold in the whole quarter, how much is the customer reach for that particular salesperson, how much is the sales amount and profit. Now, the problem is that we have the salesperson who are again, and again, the name are coming, so we have the duplicate values. We only want the unique values to appear in the salesperson summary report. What we need to do is identify the duplicates and then delete it. You don't have to do it manually. What you can do is just select the heading and control shift down, copy, and then just paste it right there. Now, what I want to do is go on the data tab, and then select remove duplicates. Now, it's confirming that the data you have selected has the heading salespersons. Yes, just click, and there you have 24 duplicate values found and removed, and five unique values remain. This is so awesome. Now I want to arrange double click. Now, what I want more is number of Unsold customer each sales amount and profit. All the data I want the same here. Double click to auto arrange. Sorry. Double click to auto range. Now I have the data. What I want to do is, according to this sale person, it should pick the data from here. And identify that in all three months, how much units Assad sold. So Assad is our main criteria of checking here. Customer reach is for Assad, I want to find sales amount and profit. Similarly, I want the same report for all others for Sad Farhan, Avaz and Omer. I want to calculate the totals means I need to sum But according to a specific condition or criteria, so when condition comes, you have to think in your mind, Okay, condition means if, I want to su, according to a criteria, that means equals to some if. Now, the range means that, you have the criterias of salesperson. But where is that range or you know, set of series in which the salesperson names are there in the data. So I have the range right there. And, now I have to identify the criteria. There are lots of salespersons here. Do you want to add them all or you want a specific results to be picked out of this list? So I want to calculate according to a criteria that is this. It will just identify from this range wherever ASA is coming. It's just like you're, you know, doing it manually and highlighting by a calculator, you have the criteria in your mind that I have to calculate the totals for ASAD you will highlight all the SAT and then you can make the totals. And comma, now you have the sum range. The sum range should only be for figures. I need to total number of unit sold, so the sum range would be this column. And then hit enter. There you have the result. That is the power of some if function, and it is so powerful formula that all the audit firms and finalization of all accounts, journal entry, trial balance, balance sheet, all uses some if. So we will discuss that later, but you have to practice now. 22. Understanding Absolute and Relative Referencing: Okay. Now that you have one result, you need to apply this in all the other cells. That's the best use of Excel. You don't need to calculate here again, write the formula here again, here again, here again. That's not effective use of Excel. But when we drag it towards right, we can see that we don't have the results as we want it. So if I double click and send it down, we have the results for only this column. So I want the results for all of them. What I will do is just remove this and there is one golden step and condition that you need to identify. This is the most important lesson of Excel. Even if you learn a lot of functions, you cannot do anything until and unless you identify what is fixed and what is variable. So I have $1 sign and dollar sign means fixed. So any cell reference has two contents, for example, C five. I have white text like C five, it means it has a column and a row, five means row, C means column. If the fixed or dollar is not applied in either row or column, that means that column and row both are movable. It can be moved anywhere. When we drag it towards right, for example, when we drag towards right, the column value changes, and when we drag towards down, the row can change. There is one other condition that I put C dollar five. That means behind any variable if there is taller, that is fixed. It means in this condition, five means row number is fixed. I can write in this situation, column is what? Yes, movable, and row is Yes, fixed. There's one other condition. We can have dollar C five. Dollar is right behind the column. It means that column is fixed and row is movable. Now, you understand this point, and there is one other situation, dollar C dollar five. That means whole cell is fixed. If we move downwards, the cell will not change. If we move towards right, left, the cell will not change its position. That means both column and is fixed. Now, you understand that point? Okay? If you understand, let me take your test. Okay. Let's write a scenario with Rn between ran 10-90. Drag it towards down and drag it right. And I can hold right click and move anywhere, come back, and then release the button, copy here as values only. Okay. Now, what will be the answer if I write equals to sine and then click on this cell. The reference is 21. Yes, the answer will be 29, If I move towards right, the answer will be 27, I I move down as the cursor is now selected here, means the cell number is here. So as I move down, the answer will change to 85. If I move there, the answer will be 16. So that's all movable because I haven't fixed any of the reference. If I open this one and press f41 time, it will fix all. Press F four second time, and it will fix like this. Now read what is fixed. Column or row. Yes, row, row is fixed and column is movable. What will be the answer if I drag it to left Now, you have to keep in mind that the column is fixed and row, sorry. The column is free and row is fixed. That means column can change from j to I. Just spend a minute and think. The answer will be nine B row cannot change, but this is not row. If I move here, the cell reference is moving from j to, but the row is same, 21. Moving within this row, but the column is changing. So that's how you can apply this. If I move down, now the row is fixed, so it cannot change 21-22. So the answer will be. Same 27. Yes. If I grab again towards left, the answer will be same 29. So that's a simple test. Let me take another test. If I go again and select this and press again, we have Some other references fixed. Now what is fixed now? Column, and row is free. So if in this situation, I move left, what will be the answer? Here, column is fixed, and row is free. So the answer will be 27. If I move down, now the row can move. So the answer will be 85 because I'm on this position. This cell is selected right now. So it can move its position downward. So move down 85. If I move towards right, the answer will be 85 because the column is fixed. That's a simple test. What we need to do now is apply what we have learned in this situation. 23. Applying Sumif With Absolute and Relative Referencing: Now, how to apply a mix of relative and absolute references so that I can get the results from here. As we have seen in the last video, that the answers are not automatically coming. We don't need to apply all the formulas by ourselves over and over again. So what we do is go on this open. And just identify what is range. Your range is salespersons. So we are extracting all the records according to the salespersons. So if I move forward, means if I drag it towards right, as we have seen in the previous situations, that all the connections with that sales or ranges will start moving as we drag. That means if we drag towards right, this will move to this, and this will move forward and forward and forward. So that should not change because we will always find our criteria of salesperson only in that list. You cannot find the salesperson name in Unit sol. In the next cell, it's unit sold. That's why it's showing zero. What we need to do is we will go on this range and select the whole range and effort to fix it. Now, criteria, Now, the criteria should also be fixed. But when we move over towards right, this criteria should not be moved in horizontal sequence. That means that the column should not change or row should not change. The column not change its position, because we are in row nine, row is not changing, but the column changes. So we have to fix this on columns. So as we move down, do you think that criteria should change so that we can get the results for SD now, and moving further, it should change. Yes. So the row should change, but the column should be static. So what I need to do is behind this column, I will insert $1 sign. And for some ranges, what we need to do now is Just identify the sequence of your data as you want the sums to appear, and if the data is in the same sequence in the original data set, then it's fine. First, we need to calculate the sum ranges according to number of Niles. That's fine. If I move further, it will change to customer reach. Do we have to find the customer reach in the next cell? Yes. And if I move further towards right, It will go in sales amounts. So do we want the sales amount total in the next cell? Yes. So this should not be fixed. I will move again to this point, and then I can hit enter. Now it's perfect. This is the ideal situation. So as I move over, it will automatically change the sum ranges and calculate the totals for us. Now, if I open this cell, so we can see that as we drag the formula towards right, the criteria is not leaving its position. It should not change the columns, so it is not getting here. So that's perfect. But if I move down, it should change. So once I'm done with this, what I will do is just select all the rows, and then just double click to send down. So we have all the results. That's amazing. Double click on this to check. Yes, I want in this cell all the results according to Fer Han. So this criteria of salesperson should always be checked in the salesperson range to identify whether we have Fan and how much of them we have. And then I need to check according to this, and then some. That's all. That's how you can use the combination of absolute and relative references to make formula work in each and every situation. 24. Sumif with External Sheet References: Okay, now that we have learned how to use SMI with absolute and relative references on the same sheet. We will move to our bonus report and extract the same results by using the SME formula, but on the other sheet. That means there should be a combination of this sheet references with this sheet references. So what I'll do is just start. As you can notice that we have the same kind of data, and I've already extracted the salespersons. So what I will do is apply some if equals to some if, and the range. The range is where's the list of salesperson in the original data set. So we will go in sales report. And you can notice that it's showing that now whatever we will pick in this data is coming from sales report sheet tab. I will select this. This means that sales report sheet tab, B column. If you want to move between the sheet tabs to apply some criteria from other sheet and the data from this sheet, you have to keep one thing in mind. For example, if I move to bonus report, notice that the sheet references has changed. That doesn't mean that it's sacking B column in the sales report, rather, it's specking B column from the bonus report, which is completely wrong. So if I want to fix this, whenever you want to change the sheet reference, you need to put a comma sign before you move to other sheet. Then I can move to bonus report and there you have it. Now it will pick whatever you mentioned from bonus report sheet. So I will pick this And now I have to move to sales report to select my sum range. So I will hit comma, and then I will go in sales report and select what? Yes. Number of units sold is our first criteria. So if you want to fix the references now as we did before, you can also do that, but first press enter to save that formula. Before moving to any other sheet reference. Now, I will double click and fix the ranges as we have seen before. This is the range of sales person, so it should be fixed all F four, and this should not change in columns, but in rows. So I will hit one time, I will hit F four second time and F four third time. Now, this is just perfect. The column will not change and the row will move because there's no dollar sign with it. The sum range is just fine and we don't need to fix this enter, and then we can drag it towards right and then we can double click to send it down. Now, that's amazing, isn't it? 25. IF Conditions With Multiple Logics: Okay, now is the time to give some logics to Excel. But before we give Excel the logic, we need to understand by ourselves. Now, this is a new business. So, you know, we are very generous with our sales persons, and we are saying that C, if you will sell more than 1,500 units, boom, you will get 5% bonus based on the sales revenue. Just ignore the customer reach target for now because this is a start up of business, so we are not very strict in our bonus policies. Now, we need to state here that based on how much units each salesperson sold, whether they qualify for bonus yes or no. We will use F This sale person has sold more than greater than 1,500 units, then he qualifies, means yes. Otherwise, he doesn't qualify, means no. So how would we apply this equals to if Tab logical test is this cell should be greater than or equals to this cell. If that's the case, then he will get the bonus yes. Otherwise, he will not qualify for that and hit enter. Now, what other changes do we have to make? Do you need to fix any cell? Yes, of course. Because as we drag down, the unit sole will drag its position and move down. We don't want that. So what we will do is just select this cell and press F four. Enter, double click to send down and then move forward. Now, for bonus amount, we need to see that if the person qualifies for bonus, then we will pick the sales amount and multiply that by bonus percentage. What we need to do is put an equals to sign and then write if this cell has a value that is equals to. Yes, means if it is written, yes, then pick total sales value and multiply it by bonus percentage. And what we need to fix now. Yes, you need to fix the bonus percentage because it will drag down. So press F four and enter. Now you have the bonus percentage, double click to send down and 77,316, that's correct, but we have a problem here. It's showing falls because we we have just given the value if it is yes. If it is no or some other value, We have to mention else statement. Otherwise, show Nil, mean show nothing. So for nothing, we will just insert inverted commas open and inverted commas close. Nothing written between that. If you want to write, you can do this. If you want to write NL, you can also do that. For now, I have left it empty. Click enter and double click to send down, and now you have the correct answers. Now that our business is going good, I have restricted the policies of bonus percentage. That means that the good old time is finished. I will copy this thing. Now that the good old times is finished, I have introduced a new bonus criteria. That is that no fil That is, now salespersons need to meet the two criterias for getting a 5% bonus. Their unit sold should be greater than 1,500, but their customer reach should also be greater than or equals to 55. Only then they can get 5% on sales amount. That means that they have to meet both criterias. Equals to if Tab, and then you have the logical test, which should be greater than which like that. But do you have only one logical test? No. Now you have two, this and this. This should be greater than this, and customary should be greater than this bonus criteria. We will write and tab. Now you can mention unlimited logics. Logical one, two, three, four, So our first logic logical one is this cell should be greater or equals to unit sold, and we have more criteria that this customer reach of a particular salesperson should be greater or equals to the customer reach that we have in the bonus, the threshold. Okay? If both of these conditions met, then yes, else no, but we have to follow the tooltip. When as we hit, it's saying that, now you are ready to enter logic three. We don't have logic three. That means you have to close the brackets. Now our logical test is complete. It will check two logics within the same condition. If both of these logics met, then He will qualify, yes. Otherwise, he will not qualify, no, and hit enter. It seems that we have left something, I think we forget to apply brackets after that. Yes. It's auto correcting this. That is okay, yes, and double click and send it down. Okay. We have a problem here. This sh be greater than this, and this should be greater than this. What problem do we have? Because it's all showing yes and this criteria is not meeting up. The unit sold is less and customer reach is 55. M. Let's check. Oh, I forgot to fix. You can see that it's dragging down. So I will open my first cell and fix J three because both are in red color, so I can easily identify F four, and then green color green color. I can easily identify F f. Full fix, and then double click to send down. Now we have the correct answers. Okay. Let's apply one more time the if condition equals to if this cell is equal to yes, then take this amount and multiply it by bonus percentage. This is the same formula as we applied before, but bonus percentage should be fixed and enter. Otherwise, Nil. Double click to send down. That's how you can save a lot of money by putting a strict policy on your sales versus. So 77-25 thousand. Moving further, we have chased the director and he's saying, Look, you're doing very unfair to your sales employees. In this way, you mean to say that no one will get bonus, that's not fair. So what you need to do is, I'll just copy first. And paste it here. Yes. He's saying that what you need to do is set a policy to meet either of the criterias. Either this should meet or either that should meet. No both. Because if the salesperson are going in the market and they are increasing their relations that is customer reach, maybe they will sell less units. If they're selling more units, maybe they're unable to increase the customer reach, either should meet and they should get the bonus. Either means or in Excel. What formula do we have equals to f r? This is means the application would be same as for. We can take this cell. This should be greater than or equals to this cell and s, this cell should be greater than or equals to this cell. And we have to fix both of them. F four and F four here also. If both the conditions met, now remember to close the bracket when you apply the logical test. When the logical tests finish, you have to put brackets. If either of the conditions met, then Yes. No. Auto correction. Yes. Double click to send down. Yes, this is the same formula as we applied in the other situations, so I will just copy this and paste it right there. 29,000 for first person because ASAD has more than 2013 units and for eight is the customer reach. This, but this doesn't. So either should meet. So he's getting the bonus. Double click to send down, and we can see most of them are getting bonus. So although it's 97,000, but we are being fair to our sales employees. So that's how you can calculate our sales and bonus report. 26. Introduction to Aged Debtors Analysis: Welcome back. Now, this is our first real coined assignment, so we are going to see aged debtor analysis. Now, before we move to the practical assignment, we need to understand what is aged debtors analysis. Now, debtors means your receivables and aging means for how long the invoices from them are due. For example, we have a credit terms with our customers that they have to pay us money within let's say, 45 days. So the invoice will be due to be paid by customers in 45 days. So if I have sold some of the items on first of January 2016, when it will be due, Yes. It will be due on 15th, February. But after the due date has passed in this situation 15th February, the customer is supposed to pay us. Otherwise, there is a problem. We need to see for how much time after the due date, the customer has not paid the balance. We need to distribute that is it one month passed after the due date, two months pass, three months passed? That is basically aging. The categorization of the months as the invoices are due. Now, let's move on to our practical example. We'll click on this tab. We can see that you have some data that is extracted from an accounting software, why we're not doing in an accounting software because if the customer aging terms is not given within the accounting software terms and conditions, it will not show any kind of report, especially if you're doing a work for a client, it's not possible to edit each customer in the accounting software and change its credit term to obtain this result. It might take a lot of time. What we can do in these kind of assignments is ask our client that, can you give us the report of the customer invoices due so that we can see how much is still outstanding after the due date. So the due date is this and the outstanding balance is this. We need to categorize according to the aging. The period for this report is from first of April 2012 till fifth 31st, May 2013. Now, you can have a little bit of confusion because it's arranged in month date and year format. If you want to change the format, you cannot change from here because your system date is showing, it's in the same format as the system date. You can change the system date by clicking on this, change date and time settings, go to change date and time, and then change calendar settings. And short date. Now my short date is month, date, and year. I need to change this in date, month and year, and I don't want the short form that is gen, rather I want 05 or 01 to appear instead of the short short alphabets of the month. Click Apply and click. Apply, click, and. Now, it's changed right away and not for this, but for all the Excel sheets. Now we have the report from first of April to 31st, May. That means for a whole year. But that is 2012 and 13. Now, aging is calculated in many different ways depending on what policies does our client have. This client has extracted the report for us on 26th, June 2013 at time 10:24 A.M. But the client has a policy that we will not take in aging the period which is not completed. So 26th, June 2013. That means it is not end of June. So June is not completed yet, still has four days to go. So we will not consider the June dates in aging. That means when we are analyzing the aged data report, we will consider that we are on this date. So that will be our current period. Okay. So if you understand this, let's get started. 27. Arranging the data to get Started: Now, as we can see that the report is already extracted from the software, but still it's not just about right. Why? Because some of the cells are merged. That means that if we write aged deters analysis, and we are seeing that it's just outside the cell. So what we can do is make all these cells into a single cell. So merge and center. It will merge all the four cells. The problem with this being if we test to apply some of the formulas from ran between function, and we apply copy here values only. Okay. Now, if these two cells are merged in between, and we press. When we apply any kind of formula, for example, equals to some tab, and then start selecting, you can notice that while pressing shift and we move upwards, it's selecting a single column. But as we move up, it expands the selection, and that's a big problem. So what we need to do is before starting, Whenever you export our report from an accounting software, you need to un merge all these cells. As we can see, some of the cells are merged here, some of them here, some of them here. So how do we identify which cells to merge and which to un merge? You don't have to do all the un merging manually, rather, what you will do is select the whole sheet. Then as you can see that merge in center is highlighted, that means this sheet contains merge cells as well. What we'll do is just click once and it will take some time and we'll merge all the columns. Okay. Now that the cells are unmerged, we can see some of the problems here. Date. It's showing in hashes. Is it a problem? No. It's simply saying that I'm getting so much congested in a little cell, I have more content, so you need to extract this size double click to increase the size. One other thing is I can see some blank columns in behind. So I need to correct that all. What I can do is select all the columns and just double click anywhere between the columns like this. You can see all are expanded. Now, we need to arrange our data a little bit. That means, for example, I don't need this column because it contains only name name name name. I'll just select this column and write click to delete it. I will adjust a little bit of the size here and I can adjust the size here. Now, here I can see there are only headings, and if I cut and paste it in the other column, This column is no more important because it's blank. So you can delete this as well, and then we arrange this contact. We don't need this column, delete it. This is also an empty column, right click and delete it. Now, here I can see a big problem. The amounts of the total invoice is here, but the heading is here. So I need to, you know, cut this or copy this and taste are. You might think it's so easy. Just go on this cell, control X, and control V. So that's right. It's easy. But what to do in this lot of data. You need cut and copy cut and copy all the data, and you have more than 3,000 transactions. So that's a big problem, right? You have more than 3,000 transactions. What I can do is just identify what to paste and what not to paste. Just think for a while. Mm. Okay. So if I think that if I copy this and paste this column on this. Do you think that results will be okay? No. Because it will overwrite the existing data with this column. So at this point, we need to analyze what we need to paste over and what not to paste. We want to paste the headings only, and we don't want to paste the blacks. While pasting, it should skip blanks and paste only the contents that we have. You are lucky enough because you have this function in Excel. What I'll do is just copy this column and right click on other column, paste special and go to paste special and skip blanks, and then click, and there you have it. Press escape. Now delete this column because I don't need that anymore. Now I will cut this and move backwards, arrange this a little bit, and then I don't need this column, also delete. The same is the problem here that some of the customers have paid some amounts. But the heading is here, numbers are here. So I need to copy this column and then paste special on this and Skip planks, click. Double click to auto range, right click and delete. And outstanding same is the problem here. The outstanding values are here, headings are here. So I need to copy this column and paste it over this column. Special special and skip planks, click. And I don't need this column also. I can also cut this and paste it backwards. So that's how you can arrange the data before starting the aging analysis. 28. Automate data arrangement: You can see that now I have the same report as we had before and not the arranged one because whenever I will export this kind of report from the software, I will have the same data. I mean same kind of headings, same columns, same each and everything. Only the contents and transactions would be different. I have to do the same task over and over again each time I'm preparing the A stator analysis. So that might take time to arrange and unmerge and delete and pay specialized values only. So I need to save it, save what whatever work I have done. And that's macro. The macro simply is that whenever you're doing anything, just ask computer to record that steps in Excel memory. So that it can auto program itself, and the next time, if we need that content to be arranged, it will just auto arrange this in seconds. So how we will apply this. First, we need to enable the macro. Macro can be enabled from developer tab. As we don't have this, we need to enable that first. So we will go in file and go to options, and then go to customized ribbon. If you have Exel 2010 or 13, Exel 2007 might have some different options. And you can see at the right hand, you have the developer tab, but it's not checked, so we will check it and click Okay. Now, here we have the Developer tab. What you can do is just start recording. It's like recording a film and then performing the steps. So as you record your video, you can play and view your acting. So Excel programs itself as you do things, and that is auto programming by macro. We will just record this. Before recording, I need to do one step. Whenever I export the report, what I want to do is select the whole data, go to home and unmerge all the cells. That's the first step we don't want to record in macro because otherwise the macro will be too long. That's we can do in the starting, but we don't want the other steps to perform. That macro will do for us. Once I'm done, I will go in Developer tab and start recording this At and enter. As soon as you start recording, you can see that it has the stop sign. That means it's recording now. Whatever you do now will be recorded in Excel's memory. I will double click this, do all the same task. Double click this to arrange, or even you can do double click and auto arrange. As we have recorded our steps, we will just stop recording. We can check that how Excel preprogrammed itself in the background. Go to Developer tab, click on this Macros and you can see the macros recorded, but I want to see the commands behind it, the programming. You can click on Edit. The there you can see that all the steps that we have performed, Excel has programmed itself to do the same task over and over again. Okay. So I will close this and I will close this. Now, as this data is already arranged, but I need to test this on a fresh sheet where the data is not arranged. So what I can do is just copy this macro, go to add it, select all Control A and copy, and then I can paste it on my desktop word file. And auto adjust program at click Enter, and then just base this and save this. Now, I'm closing this and I'm not saving this program to open a fresh sheet where we don't have the data adjusted. As we can see the data is not arranged here. From which step, we have started recording the macro, we have to reach at that step first. Our first step, it was not recorded in the macro when we start to arrange the data is merging. So we need to click first and merge all the cells. Now that the cells are unmerged, we can just go to Developer tab, and I need a button thing to play that macro. First, I need to insert the macro, so I will go in visual basic and then go to insert and module. You can paste this and and close. Now what I need to do is to play this macro, I need a button. So Developer tab, insert. There are two kind of options. Form controls and active x controls. Just remember form controls run with macros and active controls runs with VBA. You can see the same kind of options are there and here. This is the little difference that we have. I will use a command button, this little button right there, and I will just click on this, this kind cursor means that I don't have to write anything, but I need to draw the button. I will just draw this and immediately as you draw, Excel is suggesting to attach a macro with it. I will attach auto ges macro and then click. Then you can also if you want to write the name auto and click anywhere in the empty sheet. As I click this, see the magic, Oh. Now, isn't that amazing? All the data is auto arranged? You don't need to do even a single job. So that's the power of Excel, and that's the power of macros. 29. Quick Trick Formatting: Okay, now, what we are going to do further with this sheet is format it a little bit. So I will just get rid of that. You can just cut it and paste it anywhere like this. Okay? And in the first column, I want it to be like it should be in brown color, and it should be bold, and the size should be ten. In the second row, I needed to get highlighted in light ray, for example. And I also want to have my borders, and I want it around the cells. So thick border just around the outline. So there I have it. You don't want this underline, you can also highlight this and remove underline. Now we have that our format. But that's not all because I need to apply the same formats in all the other rows. So I have a lot of them. So how can I just apply this format in each and every heading? So you need a quickest way of doing a difficult work. So in this case, what I can do is I will select this two rows. And then I will just dip my brush in the paint and then paint it over this heading. After selecting that, you can go in Home tab and click format painter. You can see litter brush with this sign, and you have to start pasting from where you copied. I will start pasting from here, AC, and it will automatically paste with all the formats with borders, and all the things altogether. But if I click on here, there's no brush sign. That means I have to select again. Instead of selecting again and again, what you can do is just select once, and instead of single click, you can double click it. So you can paste up to unlimited times, and you can do the work like this. And you have a lot of them. I know what you are thinking right now. You might have thought that there could be a better solution for that to auto format all the headings. Yes, there is. But you need to concentrate on what I will explain you in the next video because that is conditional formatting, a little bit complex idea of doing that. 30. Conditional formatting Advance: In this video, we are going to see how to make your own conditional formatting formulas. Now, what I want to do is when the status of any of the payments, we have a lot of them, and there is status cleared or uncleared. If the status is cleared, then it should be highlighted in green. And uncleared should be highlighted in red. Now you might think that this is not very difficult because I can just select all of them, go to conditional formatting, highlight sell rules, and equals two. You can write it cleared. Okay? And you can just change the color and click Okay. Now, I don't want that. Rather, I want to highlight the entire row. So I need to get rid of this conditional formatting, so just select the whole column, go to conditional formatting, clear rules from the selected cells. Now, we have already seen that conditional formatting or any word with condition contains if. In conditional formatting, it's pre programmed that if your criteria meets the mentioned criteria, then format it. It's preprogrammed with formatting. When the condition meets, if this is equal to cleared, then it will format. That's how we can apply this formatting. Now, if we write here that equals to this cell equals to cleared, what would be the answer? Because we haven't applied if this thing for a while. The answer will be true because this condition is true. What criteria we have mentioned, this cell is exactly equals to that. So I is used to show us a different answer. If this cell is equal to this cell, then show like that kind of thing. So Excel conditional formatting works on true and false. If the condition is true, it will automatically colored whichever cell has written this answer. If the condition is false, it will not format that cell. Let's test this formula over here. In the first cell, I need to highlight this whole row. If we test the back end formula, the whole row should show true values. I will write equals to this cell equals to cleared and that is true. But if I move it towards left, you can notice that all the values are false. Why? The easiest way of checking the formula is double clicking it. Because now it's checking that that if clearing reference is equals to cleared. No, obviously not. What problem do we have here is that when we move towards left, the cell should not change its place. But if we move downwards because I have one other status, it should change the rows, but not the columns. What we need to do is fixing. I need to fix it a f. No, this is full fixing, I want to free the rows and fix the columns. Like this, F $2 F two means the column is fixed, but the row is free to move. If I drag now, You can notice that all are true. If I move downwards by double clicking it. Sorry, I have the values right there. By dragging it. You can see that you have all the results. This status is cleared, so all the row is showing true. The other cell is uncleared, so all the row is showing false based on the single status. You have to check each and every cell according to the status. It should not change its columns as we move Towards left. Now, this is only the back end formula, and we need to apply the conditional formatting on this data. What we need to do is just select the data and go in conditional formatting. And then I will go in new rule because I want to create my own formula. So I will select this option. Use a formula to determine which cells to format. Means I will tell Excel which cells to format. Now, I know that I have applied this formula. I will just copy this and we apply in this situation. Conditional formatting, new rule, and use a formula which sells to determine paste this. Now, whenever you highlight the whole table, it means that it will not run according to the transaction cells, but according to headings. The reference we have here right now is first cleared cell. Instead, we have to give the heading reference so that all the contents based in that heading, based on the status will get highlighted. I will remove this and write one. F one and equals to cleared, that will do the thing. Now, if I go on format, I want to format it in fill in green and k and k and boom, you have all the rows highlighted. If I change the status cleared, immediately, the row will get highlighted. Now, for uncleared, you need to select again, the whole table and go in conditional formatting, new rule, use a formula with cells to determine, and now that we know formula, we can apply here as well, equals to this cell, because in highlighting the entire row, the reference should always be heading so that it can see the contents by itself. But to see the contents and move between the rows, you need to unfreeze the rows so that it can move downwards and equals to inverted uncleared. Now I can format this in red. Boom, you have the answers. If I change the status uncleared, it will get highlighted. We will apply the same knowledge that we have just learned in our A stator analysis. 31. Applying Advance Formatting Tricks: Now that we have a stator analysis, we can see that if I move in the first column, I need to highlight all the headings itself, not the contents. So what we can do in that situation? Because we have just seen that conditional formatting always works with headings. This is itself headings. I need to color headings also. In this situation, you have to consider a fake heading. I will insert one row above that and just copy the same contents and paste it above. All right. Now that I have selected a fake heading, it will work exactly in the same way as we have discussed before. What we need to do is highlight the whole range up to this point and then control shift and down. Just keep your finger on the down button until and unless it touch the bottom. So now it's on the 148000, row so I will control shift, move one step up, and now I have all the range selected. If you have confusion or maybe you can click this cell accidentally, so all this strange selection will be removed. In that case, I can name this range as my underscore range. Why I put underscore Because naming the ranges doesn't allow the spaces. So hit Enter. And even if I click on any cell and the range is finished, I can just Go on the name box and select my range and you have it selected. Okay. Now, I want to format all the headings. There are two steps. First, it will highlight this cell. Based on this heading, and then it will highlight this row based on these headings. So I will select the whole row. No, I have the name. Okay. And then I will go in conditional formatting and go in new rule. Use a formula of which cells to determine and equals to this cell, and the row will be free and equals two. Now, we have to see any particular criteria that is common in all headings, that comes in all headings. I can see that is AC. It comes in all headings, so it will check according to that criteria and highlight the full row. If you want to highlight the row, you have to select until that point that you want to highlight. So inverted and you have to mention exactly the same word. A, C, column sign, and inverted close, and then format it in Which color do you like? Brown? No, this is dark. Okay. Let's say this color, and. If I see down, you have whole cells selected. Now, I forgot to bold this row. What I can do is just select my range again and go in conditional formatting, manage rules, and then double click, you're back in that form. And then click on format, and you can also go in font and bold it and borders and set the outlines and click and click. Now you have the bolded ones. Okay. Now, if I want to highlight this second role, you have to consider the heading for that. So if this fake heading is considered the heading for these contents, same is the case here. If you want to highlight the other heading, you will consider it as a transaction, and you will consider the cell right above as a heading. So in this case, this will be the heading. So I will highlight again, the whole range until lost, when you touch the bottom, move one step up, and then I can name range two. Now, I want to highlight the range two in a lighter color. Conditional formatting, new rules, use a formula with cells to determine an equals two. Now I will select this cell, but I will freeze the rows and equals to. Now, what is the unique values in all the headings behind this is number number number. Number is a unique value in all the headings. I will write NO and inverted close and then go in formatting, any color you want to select, this one, go to formats, bold and borders, outlines. Okay. Now you have all the headings highlighted without any effort. Now, this is a little bit complex to understand the whole logic behind that. Practice makes the man perfect. You can just practice and practice unless you get this point because this can save a lot of time for you. All the headings. You don't need to highlight it one by one by format painter or something like this. It will be done just in a moment. So that's all. In the next video, we will start our assignment. 32. Formula Analysis for Aging: Now that we have adjusted all the format, let's get started with our assignment. Now, this is age debtor analysis. Whenever customer invoice is due, for example, if we have sold that customer some items on first of January, he is due to pay us on 15th February. But if he doesn't, we have a problem, and we have to make an analysis for that that for how long after the due date customer has an outstanding balance. So for that, we need to make an analysis. Now, I have discussed before that. Now we extracted the report on 26th, June 2013. But the client has a policy not to include the month, which is not completed yet. So we will not include this because this is not completed. In this scenario, we will consider that we are not on this date, but we are currently on this date. Because this month is completed. So we have extracted the report for the whole year, and we will make analysis not by days but by months. So May will be the current month. And then we will mention one month. Past two months, and we can drag it to write. That clearly depends on industries, but most of the business makes the e or analysis for six months. Including the current month, we have the count of six, and beyond that, we have a lot of problems, so that is considered as older balances. We will hit enter format that a little bit. And adjust that. So what we need to do now is automatically categorize in which month according to this state, in which area the aging should lie. The outstanding balance shod show up. If we consider that this invoice was due to be paid on 28 Februar two then, but we have 0.9 $6 outstanding balance. Although it's quite low, but we have to make analysis for all the invoices. So how much time is passed till this date, March, April and May. That means that it is three months. So what I can do is I can just put an equals to sign and then click on this cell and press tab. That's our traditional approach. It is true, but we have a lot of data to go. So that means it will take weeks to prepare this kind of report. Now, the automation thing is according to formula. That means we need to consider that if this date lies within this period, then show the outstanding amount otherwise show nothing. I guess that you have got the formula. Yes, if we consider this kind of formula, that means, if this date is equal to 28th, February, 2013, then the outstanding value should show a pair, otherwise, show nothing. What do you think? Will it make the analysis right? Yes, it will make the analysis, but not correct. Do you think that all the customer due will be exactly on 28th February 2013? No. So there might be 27th February. Some should be due on 20th February. So it means whenever the date deviates, it will never show up there? And since we are calculating according to the whole month, that means all the February, whether it's first February or 28th February should show up within three months. That means we need to extract months from these dates. In order to do that, it's really simple that we want to insert a column, and then put a heading month. Since we want to extract the month from this date, the formula will be equals to month. Tab, and serial number would be this state and enter. As I entered, I was considering it should extract 02. But instead, we have weird kind of date. That's because the format is date right now. We need to convert the format to general. Then double click and send it down. Now, as we double click, we will just see what problem we have. All the months are correct, but here I can see a problem. I don't have any date, but it's showing one. That's a problem for me because whenever I make an analysis for the generar, this totals will show up there. I need to resolve this. Do you think I have to delete all this one by one? No, it will take a lot of time since we have a lot of data. Instead, what I will do is control z, I need to understand why it is extracting one. Excel considers that if the cell is blank, that means it is zero. It contains zero value. If I write zero and the format is the date format, it will convert in zero, 001 1,900. That means when we extract the months from this date, it will give us the answer one. We need to resolve this. What you can tell Excel that, hey, look, in this state, if a cell is blank, then don't extract any value, otherwise, calculate the month. We need to open our first formula and go in the starting of the formula and write if tab is blank. Means if is blank, then show nothing. What is blank? You need to give the value. If this cell is blank, and since we need to close the brackets. This is our logical test. If is blank, this cell is blank, then what to do? Do nothing means show blank. Otherwise, calculate the month. We can hit Enter, since we have not closed the bracket in the end and Excel is suggesting to do that, we will just accept. Then double lack can send it down. You can notice that the problem is resolved. One other way of doing this is instead, if is blank. I can just give a logical test that I know, I can write C if This cell is equal to zero, then show nothing else, calculate the month. Let's see if this is working. Yes, that also works. That means you have unlimited choices to choose the formula from. Now, let's go further. We can see ano problem that we have. It's showing an error. That's because this formula requires the serial number, the date format, and you are giving the value that contains alphabets. So it's showing that you have given the wrong value. So we need to resolve all these errors, not manually, but by our formula. Whenever Excel shows an error, you can choose that well, don't show your error, but we want all the errors to be converted in NL whenever they show up. You understand my point? So open the first column, and this formula should be written just in the start of every formula if you have the problem. If error, that means, if your calculations shows any kind of error in between. So if error has two contents, provide the value, we will just consider all the formula as our value because it will automatically take the answers and would be considered as value. We just have to tell that even if we apply the formula and it shows some kind of an error, then Just start the formula with if error and put the comma in the end and then just provide the value error. What do you want to show up instead of showing this value value? I want to show Nil. Means inverted commas open and inverted commas close. Enter, enter again. Double click and send it down and your issue is resolved. Let's make an analysis 28th February, second month, 31st, March 3 month, 20 31st, May 5 month. That is just about right. We will go on the top again, and now we need a simple formula. That means we will just match that if this month is two and two matches with two then show up the value outstanding amount here, otherwise, show nothing. To do that, I will just consider this as second month. And this is May current month. Five, four, three, two, one. This is the number of the months. Since this will be December, before January 2013, I will put 12. What I need to do now is a simple formula that is equals to if This cell is equals to this cell. Then show the outstanding amount, otherwise, show Nil. Isn't that so simple? That means if two is equal to two, then show the outstanding amount, otherwise show Nil. That's not a problem, not a big deal, simple formula and hit Enter. Now, one other condition is there that if I change the date from 28th fab 2013 to 28th Fab 2012, do you think it should show up here? Now? No. Where should it show up in the older field? But if we hit enter, it is still here. Why? Because we are only matching month with month. It doesn't matter which year it is. It will not take the year in this comparison. That means we need to modify a little bit of our formula. I will just delete this. We need to incorporate that if month matches with month, and if year matches with year, then show the outstanding amount, otherwise show nothing. So I need to insert one more column and put a heading equals to year and formula will be equals to year, and then take the serian umber same like month and hit enter. There you have a 2012. As we drag down, you can notice that we have the same problem. We can resolve this by putting f is blank. With cell, this cell is blank, then what to do? Show nothing. Otherwise, what to do. Calculate the year, enter and enter. Double click and send it down, and now we have another problem that is to resolve the value error. So we will go in the starting and put if error, on which value on all of this value, then show nothing instead of error, hit enter and hit enter and drag down this and we have all the problems resolved. Now, we need to incorporate two logics. That means if month is equal to month and year is equal to year, then extract the value. So, I have a thing in mind that instead of writing manually, I can put a date of the month end, And then extract the month serio number by month and year seri number by year. So what I can do is instead of mentioning five, I can just mention 30th, May 2013, and 30th, April 2013. And since we have a pattern right now, so we can just select both of them and drag it towards our right. You can notice that we have all the sequences accurately picked up. If I don't want to show the date, and I want to show that it show like May 2013, you have to change the formats for that. Just select all the cells and then right click format cells I want to show in dates column, a format, something like this, the short form of the month, and we have the year. It's showing the sample of the first cell that it will show up like May 13. Click, and there you have it. The format is right there. Now I want to extract the month equals to month, and I can simply refer to this date, and it will automatically calculate the month for me, drag it towards right, and then I can calculate the year also by extracting the year from this date and then enter, drag towards right. So we have our months here, year, here, and we also have it here. What I need to do is compare month by month year by year, and if both matches, then the value should show up here. We have two logics, not one. Formula will be equals to now not only if, but we have two logics. What is the formula? And if this is equal to this cell, and year is equal to this year. And since we have the two logics complete, then we have to close the bracket. If both of these conditions matches month matches with month, year matches with year, then show the outstanding balance, otherwise, show nothing and hit enter. Try to correct it. Okay. But it's not showing. What is the problem? Because we have put a test that if it is 28, February 2012, it should not show up here. So that's okay. What we can do now is change it to 13, and then see we can find it here. In the next video, we will see how to actually drag this formula in all the aging so that one formula can automatically calculate the whole calculation and aging thing. So we need absolute and relative references by the way for that. See you in the next video. 33. Absolute Relative Functions and Date values: Now that we have finalized our formula, we need to drag it towards right down, and then bottom. So in order to adjust this correctly, you need to understand the logic of fixed and relative references. That means absolute and relative references. That is the key to apply any formula in. Okay, now, this formula means we are connected to a lot of cells, and as we drag towards right, Each and every cell reference connected will start moving. So we need to decide right now, what should be fix at its point? What should be absolute? What should be relative? What contents should move and when and what contents should not move and when. So if I drag this formula from three months analysis to four months analysis. What do you think? It should still compare the months or now it should compare the references. Obviously, it should compare the years and months. So that means these cells as we drag the formula towards right, this cells not move towards right. That means it should not change the columns. So the columns will be fixed. I will put $1 sign here, and since this is purple, we will just find purple it's E. We will put the dollar sign here as well. As we move this, that formula in all these transactions. Because we have to make transactional analysis, this should move down to calculate, what is the scenario in the other transaction? What is the scenario in the transaction? So the rows should move. So I need to check. Yes, the rows are free. The rows are free means relative, we're good to go with this. Outstanding amount, It is the similar logic here that the columns should be fixed. Means it should not change its place from here to here or here, and the row should move because we have to show the outstanding value of every transaction. Now that we have resolved issue in the rows, we need to see what to do when we drag towards right with the columns. So as we move in the four months, it should compare these values with this reference. That means whenever I move to four months, it should compare that whether it's generar is matching with this generar and 2013 is matching with this 2013 if both matches then show up the value. That means whenever I drag sorry, whenever I drag towards right, the columns so move. But as I drag down the same formula, the rose should not change its place. Otherwise, it will be down like this, so it should not change. What I will do is I will open this formula, and since this is red, the columns should change, but the rows would be static, so I will put $1 sign with the row. Put $1 sign, and then same is the case here. U dollar sign. Outstanding, I have not changed the outstanding, the column should not change here, and the column should not change and the row should change. That means dollar here. And here the cell and here this. I think I've changed the formula, but, you know, it's unapplied again. So anyways, we have our formula applied. When we hit enter, we will see the formula right there, and if I drag it towards left, I have the formula. If I drag towards right, I have the formula. Older is something else. So don't just consider that. And then as we have applied the formula in the whole row, As I drag down, it will be applied in the row beneath it, so I will drag in all the rows altogether, drag drag rack to this point. And you have the analysis ready for you. One thing you need to check that Now, just consider randomly, that is it showing the exact values like I have 699, but it's not showing anywhere because it is 2012. So it should show in older. That's correct. It should not show in this analysis. So now is the older calculation. Since if we consider December, any date within December, first December December 2012 should show up here. A date even one day before December should show up here. That means any date that is below first of December 2012 should show up here. So the formula will be as we will be directly considering dates in the formula, let me tell you that the date we can see is in the date format right now. But if I change the format to general, you can notice that it has a number behind this. That's date. If we want to give a reference to dates, we can write if this date is less than inverted first of December 2012. That means any date behind first of December. So it will not show in this, but even 30, November should show up in older. But as we have seen that if we convert this to general, it has a different date value. So this comparison will not be right until and unless you convert this date in the value also. You can put a formula date value and then you have to write the date text just like this, close the brackets, and then if this matches with this or is less than this, then show the outstanding value, else show Nail. As I hit enter, try to correct it, and we can have the analysis. If we see it randomly, notice that this cell, it's showing the total amount. If we double click on this, and we just try to check this, we have the same problem here. This state is considered as zero, the blank cells as considered as zeros. Since it puts a date that is way older than our current years, so it will definitely show up in older because it's 19, 19th century. That means I need to insert a formula. At the top of the cell, and if I write, if in the starting, is blank. What is blank? If this cell is blank. Bracket close, since we have referred to the blank cell. If this is blank, then end the formula. We don't want to calculate anything, Show NL, that's it. But if it is not blank, then calculate this and hit Enter, close the bracket, try to correct it. Yes. Double quick and s end it down, and then we have removed all of this. If I notice now, 699 is showing here, if I check the dates, you have first of July 2012, so that means it should show up in older. That's how you can make the analysis. One final test of doing this is, if we go to the end, this grand total should match with all the transactions total. That means if these two totals are not matching, So problem is there, that means we are not considering some of our transactions in this aging. It should not be less or it should not be more exactly the same. If I go on the top, select the whole values, and I can see the sum 905, 47.44, 905, 47.44. Our analysis should obviously match with this and it is matching up ex. So I know this was a complex assignment, but there are scenarios where you need to do something like this. Thanks a lot. 34. Live Aging: Now, what we have right now is a simple kind of data. We have some invoices for the creditors that means suppliers analysis. We have some invoices, we have purchased some material, and we are due to pay them on this date. But until now, we have some open balances, which is still not paid. So we have to categorize it not by completed months now, but by days passed. So zero to 30 days, one to 60 days, 61 to 90 days, 91 to 120 days, 121 to 150, and older. Now, in order to make this analysis. This is a little bit different. What it does is, this is a live aging. That means it will see on what date you are right now. And whenever you open the file after three months even, it will show the updated aging report. That means if you open the same report and the balances are still due, all the balances, And before it was zero to 30 days, if you open it to further months, it should show up here if the balances is still due. So it will do that automatically for you as it will be using the system date to analyze on which date you are. It will compare it with the due date and show the date days passed and automatically categorizes it. Okay. Now, to get started, we need to calculate how much days have passed. Since the due date. We need to compare it with the today's date, so the formula will be equals to today. What that does is just don't refer to anything, just hit, enter. What it does, it will show up the exact date on your system. Mine is seventh, May 2016. If I open this again and minus sign, from today's date, I have to less this dates due and change the format to general. So I can notice that I have 70 days passed since this invoice was due. So double click and drag and there you have it. All the analysis. Now, I need to categorize that wherever it falls 61-90 days, it should show up automatically here. But why I have kept this separated zero to 30 days? Why I have not written something like zero to 30 days. That's because we have to check the upper limit and the lower limit by a formula. What will be the formula? Let me give you a hint. I need to check that if the days pass is greater than 61 and less than or equals to 90, then open balances should show up here. Yes, I. But how we will apply this equals to f, this cell is greater than or equals to zero. Since we have two logics, we need to put and behind this. That means days pass should be greater or equals to zero, and logic two is days pass should be less or equals to 30. If both of the conditions satisfies, I need to close the bracket first, then show the open balance, otherwise, show NL or show. Enter and to enter. If I change the date to 29, for example, my formula is showing the correct answers. Now, hit Control Z, and then we will decide the absolute and relative referencing. That means when I drag this formula towards right, what should move? Now in this scenario, the columns should move as it is, calculating the limit zero to 30 days in the next cell, it should automatically change the limit from 31 to, 60 days. The column should move, but the row should not move, it should not come down. So E one, the reference will be row is fixed and column is movable. And e two, same reference, Rro should be fixed, and column should be movable. And this thing is inverse to it. Here, the rows should move, according to every transaction, the days pots should move, but the column should not change its values. All the references which you have in the transactions is row is free, column is fixed. Column is fixed, column is fixed, and column is fixed. Since this is C three, what is C three, this one, if we hit enter, I can see the analysis, and then I drag it towards right and double click to send it down, and you have all the analysis. Now, what to do in the older column. If the date is beyond or greater than 180 days, then it should show the open balance here, otherwise, show nothing. Equals to if the days passed is greater than 180 days, and 180 days will be fully fixed because it so not change in any case. Then show the open balances, otherwise, show NL or show dashes. Sorry. I have to make the correction, inverted come as close and record close and double click to set it down, and there I have 202 days pass. It should show up here. So the total of my open balances is 478312. If I make the analysis after analysis should show the exact value. So I will go on the top. 20 478312. So that's the simplest a debtor analysis that can be calculated according to days, you need to calculate the today's date and minus that date with the due date. So you will get the days passed and then use the days passed to make the analysis that will automatically change. If I change this date from let's say I'm on seventh May 2016, if I change the date in 20, June, and press. And if I press f9f9 to refresh. 35. Introduction to Vlookup: Okay, now, in this video, we are going to see the advanced searching methods. We have a fairly simple kind of invoices that we have. That's just about for every car part. Imagine we have a business that sells the car parts. And we have purchased engine car shocks, filters, and we have some of them in stock. So whenever an item comes, we will just note all the information related to that. Engine, we have the part number for that. If there's any product code, what is the make, and which location it is stored right now, does it have any warranty and ifs, how much and what is the price? We have eight items in stock. So just imagine that we have 800 items. We want to make a searching box. So whenever a customer comes and ask information for any of the product, we have this available right there in our fields. As we enter it here, we will get all the information related to that. For example, if we write tires, we will get the part number, product code, make location warranty, and its price automatically. So to get started, we need to think that first, if we don't have any formula, how will you do it manually? If a customer wants to know that in which location is the tires. So what you will do is just find tires in the list. Okay. Let me find it for you, engine, car shocks, no, filters, no seat covers, L RMs, and tires. Okay, I have the tires. And then you will jump over to the relevant column and tell him that, it's still in the factory, and it has a warranty for two years. So that's exactly what computer does for you. You're looking up for something that is called the lookup value, and how you're searching that first in this car parts list is vertical. So this is called V lookup. Okay. So that's a fairly simple thing. If I write the tires in my searching box, it should show all the formula. So I will apply a formula here equals to look up. It looks for the value in the left most column of the table and then returns a value in the same from a column you specify. So I will hit tab, and then the lookup value will be this. I want to find this Okay, where do you want to find? The table array is bold. So I want to find it in this whole table. Whenever it specifies select the table array, you have to select the whole table. And then, now, the answer is in which column because Excel cannot move automatically as we move, but we have to specify the column. I'm in part number. In the dataset, it is the second column. I will write, I need the answer of two whenever you find tires in the list, and then range lookup. That is approximate and exact match. Approximate match is used for calculation of taxes. For example, you have the income of 25,353, and you have the tax ban that if your income falls in 20 to 30,000, then it is It contains a tax rate of 20%. So in that case, we will use it. If you want to find the exact results, you always have to use the exact match. So in this situation, we will use exact match. We will drag one arrow key downwards, and then select tab. It will automatically show false. So false is for exact match. If we put zero, it also means exact match. So approximate match is one or true. And exact match is zero or false. Both are the same thing. And if we close the bracket and hit enter, there we have the answers. Now, if I change this to brakes, it will automatically show up the part number related to that. Now, we want all the results for all the fields. In order to do that, you need to fix the values. The lookup value is whatever contained in this cell, so this cell always be fixed, press F four, and then as we move and drag towards right, This whole table will start moving its position from here to the right. So this should not move its place. Because always we have to find this data within this range because we don't have any other cells containing the data set. So we will select this table and fix it with a four, and then we can hit er. Do we have to change any other thing? No, I no, then hit enter. Now, what will happen if I grab this formula towards right? What do you think? It will show up all the similar values y? Because we haven't changed the column index number. So we have to open each and every formula. And since we are in product code in the dataset, it has the column three, so we can delete this and change towards three and hit Enter. There we have the answers, and we have to do this similar here and here and here and here. But do we have to do this manually? Or we have another way. As I told you before that, you can make a fixed number related to a relative reference which automatically changes. That means, if I write this numbers over here, this is the second column, this is third column, and we can also drag this column towards right and fill series. We have all the column numbers. Why I've extracted this column numbers. Because rather mentioning this manually, I will just refer to this cell. Okay, just pick the column index number from this cell. So whatever is present in this column, it will automatically take that column and select the answers. So as we move and drag towards, right, it will automatically change to three, four, five, six, seven. So does it requires any fixing? No, because it has to move. So hit enter, and then move towards right, and there you have all the answers. So if I change now to engine, it will automatically show up all the details related to that. Isn't that awesome? So this is the most important formula in Excel. So most of the organizations test your Excel knowledge by asking you of VC. So that's the most important formula in Excel. So that's VOC, one of the amazing formulas in Excel. 36. Using Named Ranges and IF ERROR: Now, if there is nothing written here and I just delete it, I can notice that I have the errors showing. So I don't want to show up this not available. Instead, I want it to be blank. What I need to do? Yes. I will go in the starting of the first formula and write if error On this formula, then show NL instead. Inverted open inverted commas close, hit, enter, and correction, and drag towards right. Now, if I want to make the formula even simpler, and I don't want to fix this and select the range each and every time. What I need to do is name the ranges. You can easily do that. Right now, this cell refers to e, but I will say that, whenever it refers to A 20, it should show up as a name range of look up value. And if you want to insert the spaces, you cannot insert the spaces, but instead, you can just write underscore. Look up value and hit enter. Now, whenever I select this reference, it will show that it has a fixed value. Similar I can do with this, select the whole table and then write it as range and hit enter. Now if I want to apply the formula again, equals to V look up. It is even simpler. Look up value is look up. You have the name range right there. You can just select this and put, and table is the name of table is range. So we have the name ranges with the name range. So it will automatically select all the range for you. And then column index number, refer to this and exact match zero enter. And we can apply if error in the starting. If error, then show nothing. Drag it towards right and you have all the answers. Notice that I didn't fix any of the range or look up value now because if you have already named the range, it cannot move. Now it is considered as absolute references which cannot change its position. You don't need to put $1 sign on this. That's even simpler. 37. Simplifying Even More With data Validation: Until now, we have tried to make our formula as simple as possible. Now, what to do further to make our life even more easier? When I write a lawyer Rims I can get the answers. But if I make a spelling mistake, I will not get any answers and even that I have to write this manually. What if I attach a list referring to this in this cell? Whenever I want to write anything, I can just open the list, select the relevant thing, and then get the answers. You can do this. You have to just select all the cells of the car parts name, and then name the range as equals, sorry, list and hit enter. And then I want to attach this list right there. So you have to just select this cell, go to the data tab, and then select the data validation. And then just select the data validation. So I will go click on this. And right now, it's allowing any value. So you can write anything in there, doesn't matter. It will not show the answers, but it can enter the values. I want to restrict the values to list So anything which is not in the list or has a spelling mistake will show up an error. And also, I want to attach a list so that I can open the list and select the relevant item from that. I don't have to write that manually. So the source is, you can select the reference, but since I have the name, I will just put equals to pick from the name list. And then click Okay. Now, you have a little dropdown arrow here. If I click on this, I can see the whole list. So if you want to write windscreen, boom, and if I want car shocks, you have immediately the right answers for you. That's how you can make your life even more easier. 38. Using Combo Boxes: Now, what to do if I'm super super lazy? I don't even want to write. And don't even want to select from the list. Instead, I just want, whenever I write S E, show me the whole value like Google, autocomplete function. You can do that. What you need to do is remove this list, if you want to remove this, follow that link again, select the cell, go to Data, and then go on data validation, and then change the value back to any value and hit. Okay. Now, you don't have the list. Rather, I would use an advanced function for autocomplete, and you have the list, if you want to choose from the list, you can do that. If you want to autocomplete, you can just write E and you will get Engine instantly. So the option you have is in the developer tab. And if you don't have the developer tab open, check out my last videos. I've shown you right there. If you have the developer tab, go to Insert, and then you have form controls for macros and active X controls for visual basic. So we will go in active x control second option. Combo box. That means this is a combo package of list of autocomplete function all within one shop. So we will just click on this and notice I have this little icon. That means if the cursor is just like this, I don't have to write anything, rather, I have to draw a box by holding the left mouse key. And whenever I feel the size to be okay, I will just release the mouse button, and there you have the box. Now, the design mode is on, that means whenever the design mode is on, you can place it anywhere, you can just configure it, you can go in properties, so you can change this. And whenever you want to use this, just remember to turn it off. Then you can write anything here. But if I open this. I don't have any list showing up right there. Why? Because I haven't even linked this with the relevant list. So I will just turn on this design mode and then click on this, right click, select properties. Now, you don't need to worry if you see a lot of options here. You can and try by yourself. It is just as simple as it doesn't look. So B color, you can change, Batyle, border color. This is a simple kind of options. We will just stick to what we need. We need to attach the list. So the option is list fare range. It is asking which list you want to fill in this combo box. So I want to select the list that I have named already. And since we are in visual basic, we don't need to write equals to sign. So I will just mention list. And close. And if I want to operate this, I need to turn off my design mode and whenever now I want to write wind screen. So as I had W. Oh, my God, you have the answer right there available for you. If I write seat covers, Oh, Unbelievable, it auto completes the function. Okay, now, what to do if I get this? Because I cannot get any answers from this only. Because the formula only works with a cell reference, and this is not a cell, but a manually drawn up box that doesn't have the cell reference. So what to do in this situation? Yes. If we link the answers of this combo box in this cell. Whenever we fill this, this value automatically links and changes with this cell. And then the formula picks the value of this cell, so it will automatically show the calculation. I will show it's fairly easy. Turn on the design mode again. Click on this, and then right click, go to properties, and exactly in the list fare range just above this list fare range, we have the link cell. So we want to make a link of this box where this cell. So the reference is A 20, so I will write A 20, not the equal sign. And then close it, and turn off the design mode. Now if I change it to windscreen, you can notice that the answers immediately change in the fields. If I want to make this box as a part of it, I can just turn on the design mode, move it right there and adjust the size of it. Okay. I guess this is, turn on the design mode, turn off design mode, and then yer rims brakes, tires, and you have the whole values. That's how you can make your life lazy enough to do smart work. 39. The Hlookup Function: All right. Now, if you know the V lookup and how to apply it, believe me that you know e lookup also, because it is just you know, just in the other way of analysis, if we flip the table around and you completely inverse that table so that all this headings becomes the row numbers and all this row information becomes the headings and all the value changes. So if you have a data like this, you have to use the lookup. To understand this, first, we need to flip around the table. Now, you don't need to do that manually and write again. You can just use the power of Excel. How we can do that? I will show you right away. In order to flip the table, you can just select the first cell, hold control shift, and then move right and then down. Select the whole table. Copy this, and I need to place it here, but by flipping it around. So just go right click and go to pay special, select the pay special options and transpose. So what it will do is it will make the rows as columns and columns as rows completely the inverse of it, and there you have it. All the data converted. Now, in this sequence, let me change the formatting Alt H, and since I want to apply the borders, B and A, So I have the borders all side. Arrange your data a little bit more, double click, and there you have it. If I want to make this analysis, similar to this, I have to flip around this thing as well. So Control C based special, based special and transpose this also, there you have it. So if I write the car part name here, all the information should be extracted and show up vertically. So it is exactly the, you know, opposite of V lookup. If you want to find the part number of seat covers, what you need to do is pick the lookup value from here, check in the whole table array and find the seat covers in this list. Now I'm searching for the part number horizontally in this list because I have the horizontal sequence of the list. So this is look up. And when you found that product, you have to just go downward to that product, and then you can see the part number. If I write here seat, covers, and you can also apply the data validation or combo box if you want. We have to extract the part number. Equals two, now the sequence is flipped, so, look up. Look up value will be the same, and we have to fix this. Otherwise, it will move down. So select this and press F four, then put a comma and you can select the table array, you can name the range as well, but for now, I will just select the whole table, and you can fix that also by pressing F four. You can notice that it is exactly the same as V L. And then not the column index number, but when you found the product, you have to mention the row number because now we are moving. If you want to find the answers, we have to move in rows. Found this. You have to move one step downward two step two rows down actually. So the reference number will be part number is in the second row. So two and range lookup will be exact match, and if I hit enter, I will get the product name, adjust this, and just check s105, s105. There, I have it. Now if I want to drag down to calculate all the answers in one go, and you don't have to change this all manually, then what you can do is just Insert one column, and then write the references. This is the first row number, and you can drag it down and go here and fill series. This is the second row number, third, fourth, fifth. So once I'm done with this, I can just double click and select the reference, not mentioning it manually, but taking it from here. As I drag it down, it will automatically change the row index number. So hit enter, and then if you double click, you can see all the answers. Since we have to mention the quantity by ourselves, you can write, how much you want? I need ten pieces. So the total will be price, multiply by quantity, and there you have the answers. If I mention engine, all the data will change. So that you can practice, but it is simple enough to know that if you know the lookup, exactly the same is a lockup, but you have to flip the data around. 40. Lookup for Inverse Data: All right. Now, let me test your knowledge. If you know the Vlookup, just tell me that if I want to search all the part numbers and the data set sequence is same. So I need the data, something like this. Cut here. And here I want the part numbers and other are similar to this, I will just move it down and here I want the car parts name, and here I want to find the values. So here I have the formula, so I will just delete this because I'm just changing the sequence, and since the format is not Sm to this, we can just select both this, which contains the same format. And if we want to apply this format here, You can just go in home format painter, then click on this and there you have it. Okay. I want to search all the information, but not by car parts, but by part number. So it seems like it is up, but it is not why because we have a problem here. We look up only searches for the data when you have the look of value in the left most column. So all the answers should be on the right. And if you want to find the look of value since I have the scenario here, I mentioned the part number and you have to find the car part instead. So it is not on the right side, but it is on the left side of the look up column. So where you are searching data, all the answers should be on the right, not on the left. So that's the problem with V lookup. It doesn't see on the left, doesn't see upward, doesn't see downward, only sees if the data is on the first column or even the second column, and all the answers that you have to find should be on the right of the look column. In this case, we have a different formula. Let's say, if I copy this and write it here, you cannot just find the answers with V look up. Instead, you can just search it with look up. Okay, so look up. Only look up. It is kind of similar like V Lou, but it has a little bit different properties. Look up value will be the same. Now it's saying, select the lookup vector. Vector means exactly the same direction or the column in which the lookup value is, not the whole table, but the exact column which contains the part numbers only. So I have to select. This is the exact vector for the product that I'm looking for. And then you have to select the result vector. Means in which column are your results. So since I'm searching for car parts, I will select the column this. So you have to select the exact references whenever it is mentioned, you need to select the vector. Once I'm done with this, I need to decide what to fix and what not to fix. Look a value will be fixed. No question about that and look vector, we have to find this lookup value all the time in this column only, this should be fixed, and since the results are different for different cells, we have to change this. This should be movable. We will leave it as it is and enter. Now, we have the answers. If we check this, we have allorams, and if we copy this and write it here, we have the tires and copy this, if you have here, have engine, it is just about right. Now, in the other column, you cannot just drag it until and unless it is in the same sequence. What I mean by that is when I move towards right, the resultant vector will move from here and will overlap, means the Luka value and the result vector will be same in the next column. I have to move one further step to find the product code. Only when the sequence of the data is not same, you have to change it manually. If I apply this, I have to change the result vector and drag it towards here. That way you can make it right. We have a problem here. I've just changed the wrong reference. This should be here, and this should be here. Instead, you can also select this reference manually. You can just delete the resultant vector and select this one manually and hit enter. Now we have the same sequence product code. We have product code in the dataset, then we have the make, we want to find the make location warranty price. It will start moving to right, and that's just what we need. We can go with this. It will automatically show the correct answers and move it towards right. You have the whole answers. If you want to find the byproduct code, it will not show sometimes the correct value of y because lookup has a very, very huge problem. That the data you're searching for in the dataset should be arranged in alphabetical order or decreasing sequence. You don't understand what I mean by that. Okay. Let's say that I have to find according to the product code, and rest all the sequence is same. I'll just adjust this, and then this thing. So I want a kind of analysis just like this. Now if I apply equals to look up, Look a value will be this. Product code will be exactly the same vector, so I have to select this one and result in vector, I'm searching for car parts, so I have to select this, and it's already named as List. Just go with this. No, I cannot go with this because I have to change this reference every time. So I need to delete this reference. It should not show the list. So in order to do this, you can go in formulas and name manager. There you can find all the ranges that you have named. So I can just delete this and press okay, close, and then I can select the look up formula again. Look up value will be this. Look up vector will be this. Resultant vector will be this. And since now it has the references, it can move its place. So I have to fix this because always find all the details from this looker value, and you can find product code here only. So the answers will change, so I didn't fix it. And press enter, and there you have the answers. But in the next column, it will show the part number, which is just about right because this resultant vector moves from here to this place. So that's just about right. But in the next column, it will overlap because I want to find make, but it will overlap and come in product code. So our looker value and result vector will be same. So if we drag it, I have to change this manually and select this since I'm in the M column and hit Enter. And then this is the same sequence, so we can move towards right. Now we have engine 540, and all the information just seems to be right. If I change it to car 38, it will show an error. That is the biggest problem that lookup has. It will not show the answers until and unless all the data is sorted according to the product codes. Since we are finding product code, all the dataset should be alphabetically arranged just according to product codes. We need to select the whole data and go to sort and select sort option and sort by We have product code, select A to Z and press. Now you have the answers. So that's a little bit confusing, and it is not used much often, but in some situations, you might have to use this. Anyways, this is L. 41. Introduction To Index Match: Okay, now is the time to see a most amazing formula that doesn't have any kind of hurdles or you can say disadvantages or drawbacks. But first, let me test your knowledge. This data we have is for different vendors? We have planned to start a new business, and we are interested in these five vendors? We have 12 products in our business that we are starting with, and we have asked all of them to submit their quotations, and we are just interested in the lowest bid. We don't care about the quality but the lowest price. So first, if I need to extract the lowest price from this vendor, which price is the lowest you want to find? What is the formula for that? And that's very simple equals to men, and then you have to select the whole range and that hit enter, and then just double click to extract this. When I see the results, I have a problem here. It's not just the problem. When I have 38.57, I can just see the lowest bid, but to identify the vendor, I have to find this thing in here and then move upwards to find the vendor. So that's not very sensible. I need to have the vendor name right there. Whenever the lowest bid price is identified, it should also identify the vendor. Now, the problem here is lookup value is this. Table array might be this, but the answers are upwards. So that's where V look up will not work because V look up only sees the answers towards your right. And it should all be after the La value. So what is after LA value, two words right? Nothing. All are before this. So do you think we can apply look up in this situation? The answer is might be S, but it will be very complicated. So let me just take you to the formula that doesn't have any kind of drawbacks. Now, that's not just one formula, but two formulas. We have the match function. And we have the index function. Now, the match functions, what it does is identify the page numbers, and index, as you've seen in the book, when you open the first page, you have the chapter names, and you have the page numbers. So match identify the page numbers, and index matches that chapter with that page number. So if you open that page number, index means the chapter will open. So To identify the page number, let's say if we want to identify the page number in the sequence of chapters, if you want to identify the position of eight, product eight, it will just identify like this. Match. I want to identify the position of product eight. Within look up array, this and exact match. So which position it will identify, it will say, k, product one is on first page, second page, product two, third page, product three. So product eight will be on eight page. So that's how we can identify the page numbers. Okay. So basically, it identifies the relevant position of the cell that contained the value, which we mentioned as looku value. So if I want to extract the relevant position of this look value in this range, it should give the answer of one. So equals to match. Lou value is this, and the lookup array will be this because I can just find this lowest bit only here. And exact match always and hit. Sorry, exact match, close the brackets and hit enter. It is showing that this lowest bit is in the first position. And since now we have identified the page number, there is no problem in identifying the chapter number to open that. So index has the function equals to index A. It only asks the chapter names. So we want to identify the vendors from the lowest bid. So that is our chapter names. And if I hit comma, It's asking the row number. That means the page number. Page number is this. If I hit enter, boom, it will automatically identify the cross reference. So if I want to write it in one formula, I can just write it here equals to match. First, you have to identify the page number. So Lou value is this, and look up array is this whole reference and exact match. There it identifies the page number, and then we have to link it with index to identify the chapter. So the relevant chapters are here. And since we have B one to F one, array is complete, we have to put a coma sign, and then row number, the page number is automatically identified by Mt. So that means match, do all the engineering and architecture work for you. It will show the calculations, identify the look a values, and, you know, test it where it is standing. Index just matches the position one with position one, one with one, two with two. So it is like by like comparison. You can also say that Matt is the real architect. And index is just a mason that can only connect with the relevant cell number. Okay. So just hit Enter and auto correct. Yes. And there you have the vendor number, vendor name. And then I can just double click and send it down. But before I actually drag this, I need to see what I need to fix. Since I have to analyze all the transctions and the range and lowest bit should change accordingly. But the chapter name should not change because all should show the same results according to the different page numbers or lowest bits, the positions that match identifies. So Headings will be same. It will be fixed, and all the other data will move. If I hit Enter, I can see the result, if I double click, I can see all the other results. So that's how easy it is to apply the index and match. It might be a little bit complex for you at the first place. But if you practice each of the function separately, it will not be a problem anymore. And that's a very important formula because it doesn't have any drawback. It can see upwards, downwards, right, and left. 42. Quiz Assignment Remington Pharma Explanation: Hello, and welcome again. In this video, I'm just leaving you with the test. We have an invoicing system of monton Pharmaceuticals Industries Private Limited, and you need to develop an automatic system that whenever you mentioned the medicine name, all the information should come. And just the client will tell you how much quantity he wants. And according to the price, it should show the amounts, okay? And at the end, you can just extract the total of it. And since we have newly started our pharmacy, you have some discount policies. So for discount policies, you can go here, and discount tables are there 02 1,005%, 1001 to 2,010%, 2001 to 5,000 is 15%, and 5,001 above is percent. So according to the bill, For example, if I have the bill 3,218, it should exactly show what discount will be applicable on this amount. So if we see manually, 3,218 will fall under this band. So you need a formula for that. And then total amount minus discount, you should have a net amount here. Now, the data that you have to extract is from the Data tab. So you have all the information available, and you have the prices, you have the quantity on hand. So what you need to do is apply any formula, the best choice from VLookup or Index match or any other formula that you know to get the best results. The best results means the quickest way of doing this. So I'm leaving you on this since you have understand the assignment. Go through it thoroughly, and then get back to the next video after trying this by yourself. 43. Invoicing System Remington Pharmaceuticals: I think you have tried to apply Index and match. Index and match doesn't have any drawback. But in some situations where you have a lot of data and you have to, you know, move in rows and identify with the different columns, it might be a little bit complex. That's why you might have heard the word V Lookup, that is a most popular formula. Why? Because it is very, very, very easy to use. So in this situation, we have to use V lookup. How I can identify this. Because he lookup has a basic requirement. If that requirement satisfies the criteria, then we can apply that. In order to apply V look up, the Medicine name or the lookup value should be the first column in the data. And then all the data that we need to extract as answers should be on the right of the lookup value. So it is on the right of the searching box. And in the data, we can also see that the medicine names is first, and all the relevant columns is on the right. That means there is no hurdle in applying V lookup. The problem now is to identify the column index numbers. So first, we need to see in what arrangement we want the details to be extracted and in what sequence, do they have that in the data. So in the searching medicine template, we have Medicine name first. In the data, we have it on the first position column one. We need product code secondly, we have product code in the data tab, and when we go back to invoicing expiry date. If we go back, expiry date is on third invoicing. Now we want the status. In the data tab, it's not in the fourth column, but there is these remaining in expiry, and the status is in the other column. Now we have the price. The price in the data is in the last, not on the quantity on hand. It means the seventh column here. And quantity on hand in the template, it's on hand right there, and in the data, I have it just besides the table, that is 123, four, five, six, sixth column index number. So that is a little bit problem. What do you think? So that's not a problem, because it can easily identify what is the column index number. Just what you need to do is insert one row, and just write all the column index number, the position basically that it has in the data. So Medicine name in Data is in the first column, so the column inx number is will be one. Product code second, write two, expiry date is the third, write three, status is fifth, five prices sent seven. But do you think that I need to write all that manually? I guess you never thought that, right? So which formula is the function that identifies the row numbers or any kind of relevant positions of the cell automatically. That is the match function which we have just seen. So instead of applying this, I will just delete this and apply rather. I will change this size to ten so that you can see equals two match. Tab, Lou value is this because I want to identify the, you know, column index number of this cell in the data. So, lookup array, I will move in the data tab, and then I will identify the headings. So on which position is the heading. So I will select all the headings. And if you want to make even simpler, rather I can just give the name to all the headings, and name is as headings. Enter, go back to invoicing, and then how we can apply is match. Look a value is this. And since I have already named the headings as heading, I don't have to go there to select the range. I can just write headings. And mash type will always be exact. Inverted close and T tab. Then I can easily move towards right. Nothing needs to be fixed because look up value should change on everything. Okay, now, we have a problem here, not available. Here the heading name is on hand, and on the data, it's quantity on hand. So if the heading name is not same, it will not show the reference correctly. So we can just copy this and paste it in invoicing tab. It will show six, but we have a formatting problem here. So we want to follow the same format. We will just pick it by format painter and paste it right there. Double click to adjust. Okay, now is the time to apply our formula that is look up equals to look up. Now, the looker value here is this. But as we move down, it should change. But as we move towards right, it should not change. So the columns will be fixed, and the rows will be movable. So I will just put $1 sign here, and the table array will be the whole table in the data tab. So it is better if I hit escape and name this table. Select the whole table, name it as table. And then I don't have to go on this tab, rather I can directly mention the reference here. Equals to Vk up. Look up value is here, column will be fixed, and the table array is whatever name I've already mentioned, that is table. And then the column index number, hit comma, and we have it identified right here and. Okay. Now, the column index numbers should change in columns in columns when I drag towards right, but if I drag it down, it will move it position from here and start coming downwards. So it should not change the rows. It should not come down, but it should go towards right. So column will be free and row will be. Fixed. So I'll put here, and range lookup is always the exact match. So hit enter and there you have all the details, notice that if I move towards right and then double click to drag it down, you have all the results. So expiry date has numbers. What does it mean? It doesn't mean anything. You just need to select this and change this format to short date. Okay. Now we have all the details. So if you want to get the amount, quantity multiplier price, that's fairly simple. And then you can double click to send it down. You will have the results. And since I have this results, 50, for example, here, you have the total 40, and you have the total 43, 32, the total start coming and 15. Okay. Now, the total amount is all equals. You can make the totals 4,498. I will just reduce the quantity to four. And a little bit here, 22,728. Now I want to identify what discount will be applicable on this amount. So if I see it manually, 2728, it is this band, 15% of discount. Remember that we have applied a I then and looping function in the grading system, the mark sheet, if this is less than 40% then fail, else, if this is less than 50% then D, then okay, you know, very long kind of formula. We can also use that formula. But here we will apply not if the n, but V look up because that's really simple. Believe me. You can just apply equals to V look up. L value is this. I want to identify this amount and see what is the discount applicable, in which table, in the discount table. Just highlight the table and then hit And what is the index number? Column index number means in which column is your discount percentage. So I have that in third column and exact match, Enter. No, not available. Because it cannot find 2007 28 exact amount in here. There's nothing like 2728 amount here. So rather, we will make a minor change that, then identify the upper and lower limit automatically by using not the exact match, but the approximate match. True or you can also write true or one for approximate match. Hit enter and boom, it will automatically identify the value for you. If I change this, or if I change this, for example, this, it will automatically change to ten, And then if I want to see the amount of discount applicable, so amount and the discount multiply, should multiply and then show the net amount equals to total amount minus. Discount applicable. That's how you can calculate all the data. If you want to make it even simpler, you can apply the data validation list or the combo box list, whatever is suitable for you. For this, you don't have to delete this, but rather you can hide this identified the row numbers. You can just hide it and there you have it. That's how amazing lookup is. 44. Sum If and IFS: In the last video, we have seen the pharmaceuticals example. Now we have planed to start a new business for a software house. So we have developed five types of software or maybe more, and we are planning to sell initially in UAE. Okay, so we have the data for that. And the data is fairly simple. The sales persons comes to us, and he said, Hey, I made a sales on First January, just record this sales. Okay? What is the information? I sold expert ERP payroll. Okay, in which region you have sold, I've sold in free zone. What's your name? My name is Atul. What's the customer code? The customer code is A A. What was the selling price? Okay. By the way, you can check it from the website, right? So, it's 3,500. And how much units do you sold? Well, I sold 18. Okay? And what's the sale price? That's what you need to calculate. Okay. That's sales price multiply by units. And the cost of goods sold is 30%. Okay? So that's a fairly simple calculation, but I want to see that it's a long period, and we have a lot of records. So that's overcrowded, you know, and I want to make a simple summary report. What I need to find from this data is how much sales in total from each product I have made in all this period. So for that, I need to create a summary here. Let me just start the example, and then you have to complete it because we have already seen this in the last videos. So I will just copy all the products, since it has the duplicate values, I will paste it right here, and now I have to remove the duplicates, go in the tab tab. And what option? Yes. Remove duplicates. Okay. I will select this column heading is the products, and then 633 values found and removed, five unique values remain, and then I need to calculate the total sales. Now, how to calculate the sales? I need to calculate how much I have sold in all of this period, expert ERP pay roll means how much sales I have generated from this. So this is my criteria, and I wants to do a criteria based sum. So that's simple. Just pause the video right here and practice by yourself if you can't do it, or even if you did play the video again to see the whole formula. I'm sure you have paused the video and tried to do it by yourself. If you didn't, shame on you. No, I'm just kidding. The formula is equals to some if the range. Okay, I want to calculate the totals of the product, means how much sales I made. So where is these criteria in the data set? That is my range. So where the products are written in the data is right here, Okay. From that, I need to calculate, Okay, In this dataset, you have a lot of values. So what is your criteria? I want to calculate the total of this. And, what you want to add, I want to add the total sales and hit Enter, and then you can double click. You can also go in Home tab and press the sign to, you know, give it a better look. Now, that's the basics that we have already discussed in sales and bonus report. Now I will insert one more field and the criteria, I will change it a little bit. I now need sales representative, which means, well, expert ERP pay roll total sales in the whole period is this. But I want to know how much Atul sold of this product. Logic accounts ERP, total sales is this, but I want to know how much Smith sold other like Ali Robert and Fat. So I'm just taking the names from here. Okay. Now it has two conditions, how you will pick the data. The problem is we have only one logic. If means you can mention only one criteria, only one range. If I change this to ifs, then you can do whatever you want. But if we remove this, the tooltips suggest an other sequence. In the Sm if, the sum range was the last. In this, it is the first. You better follow the tooltip so you can automatically know when and what to apply. Sm range means what you want to total based on these criterias. I want the total of sales. According to what criteria. According to this criteria, but no, no, no, first, select the range where these products are contained in the dataset. It's right here. From this dataset, you want to according to which criteria, I want to total only this. Similarly for range two, my range is this. Where is this data contained in the dataset? That is my criteria change two. It is right here, and I want the total of sales representative right here, and then No. Criteria change three. That means that is just finish, bracket close, enter, and then double and send it down. Now, travelers ERP Faha doesn't make any sale, but if I click Ali, it has made a sales. Now, that looks so cool when we change this formula or you know, we can get the results whatever we want. But if I want to see, okay, focus BS Ali sale is this. But, Ali performance is not going very good. I want to know right now that how much Ali has sold, regardless of whatever software. So I want to delete it and just give the answer of the total sales of Val. Delete? No, you cannot get the answer. Why? Because you have mentioned that check according to two logics, not one. So if one logic is not there, it will not show any answers. And that's why you can use some ifs in most of the situations, but not all, because there are some situations where it has the drawbacks, and it needs to be resolved. So we have just seen that you can use some if and some ifs formula in most of the situations, but not in the situations like this. It has a certain drawbacks. So this is what you cannot resolve in some ifs. Rather we have to see are unique, different, awesome formula, so stay tuned. 45. Forget filters use DSUM Search Box: Okay, now welcome again. Now, we need to resolve what we have just seen before, and we could have a couple of different problems in future also. So what we want to do is just create a search box. You can just paste it right there and just delete this value. This is my search box. Now I want to get rid of all this kind of problems, and if anything comes in between, it should still show the total values of sales. So now that I've created the search box, I want to get rid of these situations. So whenever I write any kind of criteria, the sales value should change immediately according to that. And if I remove the other criteria, it should also work. So whenever you have such kind of situations to tackle all the way around, you need a database formula that is equal to the sum. What it does, it will take all the database, and according to the criteria, it will sum it. The tooltip suggests that you need to select the database. The database is from here and control sired and down. Notice that I have also included the headings and press, and then you need to select the field. Where's the field? All the database formulas work according to the headings. So just in the field, you want to tell what do you want to calculate? Okay, sales, which heading is that? Just you need to tell this because it can automatically sort out the data for you. So you will select the heading. It will match up the headings right there and calculate the answer for you. Okay, C, Okay, now we have to mention the criteria. And since it works according to headings, I have to include not only this blank cells, but from here. That means headings and the empty fields. Whenever I mention a criteria here, it will check it and it will sort it out in which field it is, and it will go to that field and calculate the relevant answer. Otherwise, that's not our concern, how they manage it. Now, I will just enter and get the value of it. Which value is this? This is the total value when you don't have any criteria. When I mention any of the criteria like data, for example, how much I sold in data? This is the sales and how much Smith sold in data? Immediately, the value would change. How much Smith sold in data and the product is focus PS. How much this product that Smith sold in data, enter, and you can get the answers right away. Okay. Now I know that focus PS sales is quite good, but how much the total sales is? Delete this one. Okay. That's quite good. And how many times the salesperson sold greater than ten units. So you can filter however you want to. So that's a pretty awesome formula. Now that you've learned the D functions, you can also use D count. D Mx, D Min, D average. You need to apply it by yourself. I'm not explaining this because it's quite simple. So just apply it by yourself in the different situations, and if you can't just get it, I have prepared a guide for you how to apply that, and I've also mentioned that in the finished work book. So that's it, and we will see you in the next video. 46. Amazing Advance Filters and Macros: All right. Time to examine your knowledge a little bit more. If I ask you, this is a whole database. That's fine. But I want to see the results of just motor city. How much is the transactions, how much we've sold? Means I don't want here to display all the regions. I want just the results for motor city. What will I do? Yes, you're right. I will just go on this heading and click on this filter and deselect all, select Motor City, and then click Okay. You can see all the transaction that involves only this region. All the other results are hidden. Now, I don't actually want that. What I want is to create a search box. Let me create a search box. I will just copy here and paste it on one other sheet. Right here. And as I paste it, I will just delete this contents, and I will adjust the size a little bit. Let's just extract it like this. Okay. Now, what I want is whenever I mention any of the criteria in this search box, According to that criteria, it should extract all the results from the database containing that transactions and paste it right here. For example, if I write that show me the results for focus PS, it will immediately go in the database, identify the transactions that involves focus PS sales, and paste it right here. As I change the criteria, it should automatically change the results. So that seems amazing, right? So let's just try. This is not a normal filter that we have just seen. This is advanced filters. So what we'll do is just follow me. It's not very complex, but you need to follow what I'm seeing. For less complexity, let's just name the database. Select the whole data, and I've already named it as DB. You can just write it as DB and hit Enter, and then your database will be fixed as a name. Then you will move on to the sheet one, which we have just created, and then after moving on to this sheet, remember this is a step. You have to follow. Whenever you are applying advanced filters, you have to be on the sheet where you want to apply that. So I will go in the Data tab and select advanced And now, there are two actions. Hey, what action do you want to perform? Filter the list in place? That means where the database contains just filter it right there. So that is just like a normal finer. What is the use of advanced filters for that? So I will not use that one. I will use copy my results two and other locations. So it will extract the database containing transactions and paste it right here. So now, just select List range. My list range is the whole database. So I don't have to select it manually as I have already named the database as DB. Now, what is your criteria? Same like D sum, all the headings, and one blank row where you will mention the criteria or even more if you want. So I will just keep it to one and copy two. Okay, If you extract the results, where do you want to copy and paste them? I want to paste it from here and click Okay. As we click, we have the results right here. Okay, I will adjust it little bit. Okay. Now, we have all the results extracted from the database. Why? Because you haven't mentioned any of the criteria, right? So if I change the region from blank cell to data, and I want to ask Excel, Hey, show me the results just for data. I can notice that the results are not changing. If I hit F nine, F nine, it's not working even. What's the problem? The problem is that advanced filter doesn't refresh automatically. What is the solution for that? Just delete the database, clean the whole area, go to home and go to clear and clear all. And then just reapply the filters. That sounds stupid, right? Because if you have to do this over and over again by yourself, then what is the need for using this advanced filters? The reason I told you this that it doesn't refresh is because it's a fact. It doesn't refresh, but we can do this. How? When Excel functionality has a drawback, we can take it to a further level by taking the support of macros and VBA. So what does refresh means? It means reprocessing all the procedure again so that when the sheet refreshes, it show us the updated results. So, that's what we need to do. We will go in the developer tab, and since it doesn't refresh automatically, we have to reprocess all the function again. But we don't want to repeat this each and every time. So we just want to record the macro and ask Excel, Hey, just watch me how I am doing this and repeat this every time. I will not tell you again. So record macro. As we record macro, we will write the name as refresh. And then you can notice it has started the recording. Now it will record whatever you will do in its memory, and it will program itself, you know, So you have to keep in mind the steps that you need to perform before reapplying the filter. You know that if you have given less criterias, you will get more results. And if you have mentioned more criterias to filter, the results will automatically filter, and the remaining will be very small amount. So when I apply filter, you have to keep in mind that you need to erase the old data so that it will not overlap the existing data and the new data. So The first step is erasing the data. Now, we don't have any data right here, but we can imagine if we had the data, you have to erase that. We have to record even this step in its memory. We will click on this cell and just hold control shift, move towards right, and then down. Now, I know the short key, so I'm using it. Alternate for home, E, E for clear and A clear all. So I have just cleared all the records. Now, what next, just reapply the filter. Go to Data. Go to advanced, copy to another location. List range will be my name of the database is TB. Criteria range is whole of this range. You can even delete that and select this again. Copy two means where you want to paste the data. I will just delete this reference and click again and hit. Now you can see all the transactions containing only region data. That's how you can filter. Now that our steps to use advanced filter have been recorded, I will just go in Developer tab and stop recording. Now, I need one little button here to play this macro. So I will go in Developer tab, insert and Fm controls. I've told you before that form controls are used with macros. Active X controls are used with VBA. So I will just click on this. And as I drag and draw the button here, it will automatically redirect the window to assign macro window. So which macro you need to assign with this button. I will assign refresh macro and click Okay. So let me just change the criteria. If I change it to motor city, it will not change now, but if I click this button, you can notice that the results will automatically change. Now, I will just hold the right click in this button, and then I can change this button name. Refresh, and now click anywhere. Okay. Now if you are having any problem with applying this macro, you need to consider that macro records each and every step whenever you are recording it. So I need to include even this functions. Okay, when we start the recording, I will just hold control shift down and just delete this record. Then even you have to record this in your recording that I will start pasting from here, so you have to click on this cell. If you don't, I can show you what will be the problem. Just go in Developer tab, you can also see the program that is running behind that. So I will go in advanced, and then you can see the refresh macro which we have just recorded, you can edit this to see the back end transactions. Now, our first command in the program is range a four select. That simply means that when we started the macro, we clicked on this cell. If we don't have this command, what will be the effect? Let's see. After deleting this command, I will just close this button. If I now try to change the region, for example, data, and click Refresh. This result is fine. But if I filter it with Atul and hit Enter, if I click Refresh, you can notice that there is a little bit of problem. If I again click refresh, now it's working fine. Why? Because we have a little problem here. If I write Smith, and we click refresh, there is a problem. But if the cursor is here, when we start the macro, it will work just fine in the second click. So why not it is just working fine in the first click? That's because the cursor is here. So you have to place the cursor from where you will base the data. If you want to move the cursor automatically to this position whenever we start the macro, you can just go on this macro and edit this and you can test it and you can write range bracket open a four dot select. It will start from selecting this cell, and then it will go on to this procedure. What you can also do is just delete this whole transactions contro write and then down H E A. And then go onto this program. Just I will minimize it a little bit so that I can see at each step what is going on in the background. That's how you test the macros. That is the D bug option. You can use F eight, and it will show us what each piece of command is doing in the background. So I will just press F eight. Okay, let me reset this and play again. If I place the cursor here, and now I want to see whether it comes in A four or not, I will just hit F eight and F eight, range A four dot select. Whenever this command executes, the cursor should automatically go from here to here F eight. This is just working fine. After that, select Data from, you know, control shift right and then control shift down. These are the bst commands. So F eight, control shift right, and control shift down. This is working fine. Selection dot clear. This is the command which Excel pre programs itself. So we used the shortcut home and clear all. The short key is t H E A. So it just cleared the data, and then range A four dot select again. Now it will go on to this point and then apply advanced filters. Now it is just working fine. Okay, this command will stop the debugger, or you can just click, or press F eight again and just close this. That's how you can test the macro commands a little bit. Now, I want to see, show me the results for focus that contains region data and the transactions that is only done by Smith. So just hit refresh, and then you can see the results. But I can notice one thing that if I delete this and change to refresh, you can notice little blink here. Did you notice that? If I delete this, you can refresh. Little blink comes whenever the sheet refreshes. You can even stop that. How. You will go in the developer tab again, and you need to edit the macro. Go to refresh, edit, and then in the starting, I need to mention that hey, whenever the macro plays, don't show me the screen updates of what you are doing in the background. So I will write one little piece of code is application. Dot screen updating equals two false. Application, dot, screen, updating equals two false. This should solve the problem. I will just close this and then if I try to hit data and remove focus, whenever I refresh, immediately, the results will change without the click of an eye. If I change the results again, just to focus views, we can click. There, you have the results. So that is literally amazing. You can also edit the macros a little bit more so that when the macro is processed or our results have already been placed here, I should show the message box, Hey, your data has been extracted successfully. So it should show a message whenever we refresh. So I will go in the macro again and then go on this macro and edit Before the macro ends, it should show a message box. So I will go in the end and write message box, MSG box is the command. You will open the brackets and you can write inverted commas. Congratulations. Your data has been extracted successfully. Okay. So whenever you will click on delete this, refresh. Congratulations, your data has been extracted successfully. Okay, There you have the data. So that's a little bit about the visual basic programming, although it's not the part of the course, but, you know, I'm just very kind. So see you in the next video. 47. Introduction to Pivot tables: Hello, and welcome back. Now, I'm so excited about this video because in this video, all your life problems related to Excel will be just gone. How? Because we are going to see the pivot tables, the most easiest and effective thing in Excel. In this, you don't need to do anything. Just you need to think how you want the report to be extracted with this kind of raw data. For example, you want to ask Excel, Hey, show me the report for let's say in which region, so the regions will be in the vertical form, which salesperson made how much sales. So just you should have a picture in your mind of what you need to do. If I want to explain the pivot tables, just think if I remove Pivot and change it to virtual. What does it mean? That means it is a virtual table that doesn't have its own values, but it will extract the results or data from this database and plot it in the summary report. How you think it should be. For example, if I want to make the report, which we have just discussed, what I will do is just go on the top of the data. Remember, you have to select the headings and select the whole database by holding the control shift key and then move towards right and down. And then go on to insert and insert a Pivot table. Now, it will obviously ask you the range that, hey, just select the table or range for the data that you want to pick in the Pivot table. We have already selected the database. Okay. Now it's asking choose where you want the Pivot table report to be placed. Should it be the new worksheet or should it be the existing worksheet? I want to place my data or summary report on a new worksheet, and then click. And there you have pivot table applied, but it doesn't contain any value. So as I've told you before, that it is just like a virtual table which is empty. It has the column labels, but it's not filled with anything. It has the row labels, but it doesn't have any values, and it has the value options, but even that is empty. So that is just the advanced form of pivot table to see it exactly like a virtual table. You need to convert the advanced style to the classic style. What I'll do is just click anywhere in this Pivot table, right click on it, and go to Pivot table options. And then go in display and click classic Pivot table layout. If I click, Okay, you can just see now it looks like a virtual table, which has the column fields, but it's empty, which has the value fields, but it is empty also, and you have the road fields, which is empty. So it is just asking, Okay, just tell me how you want your report. I want that I need regions here. Salesperson here and values of sales here so that I can know in which region, which salesperson made how much sales. So in the right, I can see all the headings which we had in the database. So what I need to do is just hold it with a left mouse key and start dragging it. You can notice that we have a little brick here, and we need to drop it in the regions, means I want the regions to appear in the column and just drop it right here. Now, just drag and drop. I will just hold sales representative, place it right here. I will just hold the sales amount total and place it right here. And boom, your data is final. Isn't that awesome? Okay, for example, if I've seen, I have one, two, three, four, four regions, and I. One, two, three, four, five, six, six sales representative. So I might think that it is better if the salesperson comes in the column fields, and the regions should come in the row fields. So I want to change the place of this and overall report. So that's not a complex thing. You can notice that in the bottom of it, I have the regions selected in the columns right here like this, and I have the sales representative in the rows like this. So I want to change the position of it. Just grab it right here and drag it right here. And then you have the updated analysis. Isn't that awesome? So that's just the introduction of the pivot tables. If you want to make more than one, you can just copy this and paste it right here, and then you can just click anywhere in the cell and empty the pivot table by unchecking the boxes. Now I have the empty pivotable you can generate and other analysis. For example, in this one, I need which products are sold in which region and how much was the sales. Now, if I want to see that, show me the details of the salesperson, but I want to see which salesperson sold which of these products in which of the regions. So what I need to do is include salesperson here also and just keep the products like this. If I grab the salesperson also in this field, I can see that the report is quite good and extensive. But it is just not about right. Why? Because I want to see the report according to salesperson, not by products. This report is showing that the focus product is sold by these salespersons in this region, and sales amount is this. I want to give the preference to salesperson. So that means Ali has sold this diseases products in this regions and the sales amount is this. So the preference should be salesperson and then should come the products. So if I go in the rows, I can notice that right now, the preference is given to products. What I will do is just drag it and move it up, and you have the report updated. It is simply unbelievable. So you can just practice this and we will see you in the next video. 48. Grouping In Pivot Tables: Now, I hope that you have practiced the pivot tables a little bit. Moving one step further, I need you to take this data and prepare a report for me of the months, how much we have sold in each month according to the regions, and what is the sales amount. Just like this. If I click sheet two, I have the report summary. That's what I prepared. You don't have it in the worksheets. So you have to prepare a report for me just like this, in which region, and in which month, how much we have sold. So just write by yourself, pause the video right here and see what you can do. Okay, now, in order to create a summary report according to months. We have just noticed that in the Pivot table basics, this table, we have only the date fields, not the months. So what I need to do is right click on it because I want the columns to be inserted just behind this, right click on this column and click insert. Now, I will write Month. I want a month here. Oops, I want month here. So there is one formula that you have seen to extract months. What was that? No, not equals to month. Equals to month only shows you the serial number of that month. It will just so if I change the formatting because it's in the date format, I have to change to general. It will just show one, and for February, it will show two. I want the full name January to be written with this formula or any other formula. So the formula will be to extract the text from this date. I need to write equals to text. And the value will be picked from here, and in which format, in which format I need the month. That is just according to the calendar. Like the dates are defined in the calendar. How do you want the month to appear? DDM YY format or DD MMM YYY format. So if you want the whole month, you can just write format text means you have to start from inverted commas, and I want the full month name to appear, so I will write full M M. So it will show the full month hit enter January. If I remove one, it will show Jan. If I remove one more. It will show 01. If I remove one more, it will show only one. So that's how you can extract the months. So I will just double click and send it down. But even now, I am super lazy. So I don't even want to insert a column or, you know, even if I know this formula, I don't want to write it. So I will just delete it. Excel pivot tables are very, very smart to do things on their own. They don't want the support of any formula. So you can just select the database again and then hit alternate and then go insert N V V f pivot tables. The shortcut is t N V enter, and new worksheet, since we have selected the whole database, and click Okay, there you have it. Now, we have understood how to apply the pivot tables in the classic format. In the classic format, you can just hold the heading and place it right here. In the advanced form, you cannot. You can see the little cross sign here, so you cannot place it just like this. If you understand what is columns, what is rows, and what are values, according to the classic table, you can understand the advanced form of it too. So what I need is to place the date in the row fields. So here's my row. I'll just drag and drop it. And in the first time, I can see this all march. I will just click this. And I want the region wise report, so the region should appear in the column fields, and the sales should appear here. So this report is just fine, but it needs a little bit of adjustment because I can see date wise report. What I need is a month wise report. So what you need to do is just right click on it and select group and there you can see that it will automatically pick the starting date from where your transactions started. So my earliest date of the transaction is first of January 2014, and I have the transactions up to first of October 2015. If you don't want the 2015 transactions to appear, you can just change it and write it manually. I want only till 30, December 2014, and then you can group it by months. If you want the date to be automatically picked? You can just check on it, and, I will just hit cancel. You can just go in grove fields and leave it as it is. And then the problem is that the January 2015 will be combined with January 2015, which is not right. So it should show separately according to months and according to years. So I will group it by months and by years both and click. So isn't that amazing? All your results is automatically extracted just within a moment. Okay, let's just say that I want to see, in January 2014, I made this much of total sales. But what is the distribution or transactions behind this? You don't need to do anything. Just double click on this, and you can see all the report that is filtered according to your criteria, and that contains only Gener. Okay, It inserts a new sheet for this. Okay? Just keep this in mind. So I have to go and find my pivot table. Here's that. And now I want, I've sold This much in Gener for Motor City. What are the details? Just double click and you can see that all the transactions will be instantly brought forward for you. Okay, if you don't like the format of the Pivot table, you can just design it. You don't need to put so much efforts to, you know, change the format. You can just go in design. And you can also notice that whenever you click on a pivot table, you have two extra tabs that is analyze and design. I will go in design, and then you can select the format, whatever you like. This seems to be quite good, this one. Okay, and there you have it. So that's about it. We will see you in the next video. 49. Calculated Fields: All right, welcome back. Now, let's just insert another Pivot table. I will just select the whole database Cros right and down and old N V enter. And then you have the Pivot tables. What I want now is for which product, how much is my sales, how much is my cost, and how much profit I've earned. For each product, how much is the sales, how much is its cost and how much is the profit? So what you need to do in this scenario. You need to place sales amount in the values. Where should we put the cost of goods sold in the same column of the values? So we have sum of sales and sum of cost of goods sold. Hey, by the way, you can change this labels whenever you want to. Just click here and you can see write sales amount. Okay. And you can also write total total cost of sales to enter. Okay, now I want the profit. If I go in the main data sheet, I can see that I have all the other fields. I have the sales. I have the cost of good sold. But if I go in the data sheet, I can notice that I have all the other fields. I have the sales amount. I have the cost of good sold, but I don't have the profit. So what you will do? Insert a new field, and then insert the pivot table again. Okay. You can also do this. Let me show that. Profit. Sorry, profit. Equals to this minus this. This is your profit, and you can just double click and send it down. Now, I want all this to appear in the Pivot table. Let's see. This is my Pivot table. If I see here, I cannot see the profit field here. So if I write click anywhere in the Pivot table and just hit refresh, you can notice that it will not get inserted. What you need to do is change the pivot table range. So you have to go in the analyzed tab and then change Data source. And as I can notice that the pivotable basics is selected 1-639, I will change it from I to J. So let's go here. Delete this, J, and then press. Now, I guess it's inserted, yes. So you can also drag it in the profit. Instead of doing this manually, I can try my super lazy formula, and that is not doing anything. Okay? So I will go in pivotable basics, and you can notice that I've removed this profit field. So what I will do is, I can just select the whole database, Alt N V enter, and then I don't have the profit field. What I will do is just place the product right here, and I want the sales to appear, cost of good sold to appear, and the profit field. So how much I've sold, how much how much was the cost, and how much profit I have earned for each of that product. Now if you don't have the field, you can just cross reference within the Pivot table to get a new field. How you can just click on Pivot table. There you will see the new tabs. You can go in field items and sets. And then you can select calculated field. What calculated field does? Okay, what name you want to write in this field. So this is sales, this is cost of goods sold. I want the name to be let's say, gross profit. Okay. And what is the formula for that? Sales, minus cost of goods sold. So I will delete this zero and after equals two, pick the value of sales from all these fields. Notice that all the fields is from our existing data. I will hold this and insert. Get the sales for me and minus cost of good sold insert field. Sales minus cost of good sold, and then click. And there you have the profit. If it is not automatically inserted, you can just drag it right here. So that's how amazing the pivot tables are. The possibilities are unlimited. 50. Pivot Charts: All right, welcome back. Now, when it comes to reporting, you have a lot of options from charts, but the most common are line graph, pie chart, and bar chart. In which situations you have to apply this and whether to apply line graph in the situation or a bar chart, we have to see some of the details. Well, the line graph is used to show the trend analysis, and it is also used for short term or fast fluctuations. For example, the cricket score changes on every ball. And even in the stock markets, the share price opens at a certain amount and then changes after every few seconds. So in that situation, line graph is the most ideal because you cannot draw a bar for each of the small movement. But still, that is not a hard and fast rule, because in most of the situations where your data has too much of information, for example, 12 months, then it's better to, you know, show the sales performance over the year in line graph rather than bar chart, because you have to insert 12 bars to show the performance month wise. That means line graph is mainly used when you have a lot of data to analyze and bar chart is used frequently when you have to make the trend analysis, either it is long term fluctuations or a report that contains less data. So you have to compare, for example, between four regions, which region is performing better in a whole year. That means you will have just four of them or maybe five of them. So in that situation, bar chart would be ideal. What about the Pi chart? Pi chart only shows the percentage of the total contribution of sales of expenses, how much item A contributed in the sales, for example, 11%, and item D has most of the contributions and item D has the same. So that's a little introduction about the charts. Most of you already know what these are. So let's just jump on to our data and apply the chart on our analysis made by Pivot tables. So here I have already prepared for you a report from Pivot tables of the different products, how much we have sold in each of the month, and we have the data for two years. So now we have to apply a chart on it. But first, we have to analyze which one would be better. What do you think? I guess the bar chart would be ideal for this. If I go on to the Analyze tab and just select Pivot Chart, pivot chart is seem like normal charts. So I can just click anywhere in the Pivot table. Go to Analyze tab, click on Pivot Chart. I have a lot of options here. What I've seen in that example in the starting charts explain, that we have only three of them, but we have a lot of them, even in the bar chart. We have two categories bar chart and column chart. So that's not a big deal. Column is just the vertical charts of the bars, and bar chart is just the horizontal one. But what about this clustered coun, stack column. There you have 100% stacked. So you have a lot of different options. Let's just apply clustered column. And there you have your report available. But do you think that is just fine? No, because it's overcrowded. We have a lot of information that is plotted into the graph. So What we want to do is first analyze which of the things that you don't need to insert in one chart. That is two years. Two years should never be represented in one chart if you have to analyze by months. So what I will do is I can just filter this chart and uncheck all and say, I want the report only for 2014. And there you have a better looking chart. But still it is overcrowded. Why? Because we have five products, and it is creating one separate line or bar for each of the products. That means even in one single month, we have five products, you know, 5 bars to analyze from. So we only wanted to see who is the main contributor in, for example, October. So we can see that logic is the main contributor, which has the most of the sales, and traveler sales is quite less. But there is no need for five of them. What I need to do is create 1 bar, and you can divide it in a portions colorwise so that you can see the performance of each area. Don't need to delete this. Just click anywhere in the blank chart and right click, go in chain chart type, and then you can change from clustered column to stack column. And you can see that it has combined all the 5 bars in 1 bar, and even now you can see the performance. There's one other scenario since you can see the movement, the high points, the low points. So if I started a new business with a unique idea. So I know that by the end of the year, I will make a lot of sales because I will get the first mover advantage as my product is very unique in the market. So obviously, there were some hurdles, and in Gener, I've made only 20,000 But by the end of this year, I made $2,000,000,000 because that was an awesome idea. But if we have something like that, we know that any of the chart will not work. Even the bar chart line graph, because it is a huge movement from zero to the top. That means that the jumps or levels defined by Excel automatically will have too much of the gaps or differences. That means all the analysis, this bar will remain just in this area, and you can't even read what analysis it is making. I'll show you the example. If I go on to the basics, If I move on to my sheet and change the value of December to a much greater amount, something like this, one, two, three, four, five, six, seven, and eight figures. Okay? And if I move on to my chart and right click and refresh the data, you can notice that the seals in December for a particular product is much higher and the levels are automatically jumped from huge differences. So when we see the other comparatives, it is approximately nothing in comparison with this. And since it is not a huge values, we cannot even read this analysis. So that means in this situations, you need to change the chart type. At least you can see what is the main contributor in each month sales, which product is the main contributory, even in the percentages. So I will just click anywhere in the blank chart, and I can right click, go in change chart type. And that's why you use 100% stack columns. That means all the levels our performance in each month will be shown according to the percentage of the totals. That is the most common report, which is used by companies in data analysis and final reporting. That was the main part, and in the next video, we will see some other charts like Pie chart line graph and complete our whole completed dashboard. 51. Dashboard Reporting: Hello, and welcome back. If you know about the pivot table basics, and you know how to insert the charts, you can make any kind of analysis and dashboard reporting is just a simple piece of sheet, which includes all the charts, all the pivots to show the company performance or snapshot of the overall company performance. That's it. If you know the charts and pivot tables, you can prepare the dashboards easily. You need to do is make some kind of different analysis like I have made one month wise seals, and then I can insert one other pivot table. A N V enter, and by seals rep, I need seals. And you can apply any of the charts here. Pivot chart. I can see that I can also apply the clustered column, but I guess it's better to use line graph in this situation. If I click and then go in design, you can just change it to any layout, that it looks better, I think. And you can just extract it, reduce the size of it, and then you can see the performance. So you can change the caption also, sales analysis by month, and then you can also make the analysis of this sales representative, insert any kind of pivot table that seems suitable. For example, the pie chart. I guess this is okay. And if you want to change the layout, go to design, and you can select any kind of layout that you need. So I will just select this one, and that is analysis, for example, by sales rep. And this is one chart NV enter. I also want to see that what is the contribution of each product by percentage in the total sales? So when I place the sales in the values field, I just don't want to see the figures rather, I want to see which product has the of the contribution in percentage in the total 100% of the total sales. What I can do in this situation is just right click on it and go to value field settings, and you can also click Show Value As, and you can change it to percentage of grand total. And then click, Okay, there you have all the analysis. So we can see that logic accounts ERP has 29.21% contribution in the total revenue of 100%. So what kind of chart you can apply in percentages, usually we apply the Pi charts. You can change the format. And if you can't see the data labels in this kind of format, you can just right click and add data labels. You can add data labels or data call outs. I like call outs. It is better. So there you have it. So now that we have some of the reports in different sheet tabs, let's compile it in our dashboard. Dashboard simply means that I will write one sheet as dashboard, and you can change the color in black, and then copy and paste all the charts one by one in the dashboard. This is sales by month. This is sales by sales wrap. M in the dashboard. This is another analysis. You can change the sizes of it, make it smaller, and make it bigger. That's how you can make the analysis. Now I want to insert some other techniques in my dashboard report. This is a simple kind of dashboard report that I've just explained you for example. What I need to do is go to insert and use the timeline functions. So to insert the timeline, you must click on this chart and then go on timeline, select the date. And there, I have a long timeline. If I adjust the charts and move it little down, I can place it right here. And as you can see that you have all the months available. For example, I don't want to see the report for whole year, but just for three months. So all these charts should change accordingly. Right. Okay, show me the report for January and not only January, but January, February, March. Just hold the left lee and stretch it to June, for example. Okay. Now, I can notice that this report has immediately changed, but it has no impact on all the other reports. That means it is not connected with this timeline. To connect all the charts with one timeline or Slicer, you can click on this timeline and right click go on to report connections, and then you can connect it with all the other two. Currently, it is only connected with this Pivot table. You can connect with this and this also. And if I change the value now, you can notice that all the charts are changing, if I reduce the size, you can see even better report. So, that's a little introduction about the dashboard reports. Now I want to insert the slicers. But to insert the slicers, you need to click on any of the chart and then go in insert and select the slicers. Now you have a lot of different headings that you have in the database, but I will use only three of them, product, region, and sales rep. You can notice that you have some filters or slicers here that you can use to filter the report even further. You can also change the layout, any color you can select. And then you can just connect this right click on the slicers and report connections. You can select three of them. Sorry. You can select the three of them, and you can connect all the others one by one. Okay, I will just connect this ones also. And then, now that I've connected this slicers with all the pivot tables and the related charts, as I change the region. If I clear this filter, I have the whole report, I want to see all these reports just for region data. If I click here, you can notice that all the results changes. If it doesn't change, that means it doesn't have that criteria. So you can notice that it is changing as I'm changing the regions. Okay? You can also change it by products, or you can also change it by sales rep. Immediately, you can see the exact analysis for a particular type of report. You don't need to create separate reports for each type of analysis, rather you can easily filter by clicking on this one. This is amazing. So that's it. This is dah reporting. I hope you like that. 52. Automated Cheque Printing System (Using Mail Merge): Everyone wants to reduce time taken to perform a task and the chances of errors that they can make. You might have seen in organizations that instead of writing manual checks by hand, which has a lot of chances of errors, and even you cannot review and make the correction before printing that because you are already writing the check. So even if you make little mistake by accident, you cannot change it further. So that's why organizations have created some templates in word, just like this, so that Whenever they want to print the check, they can just type it, put the check in the printer, and then print it, and even before printing, they can review two times three times, and even they can confirm from an other person whether or not to print this amount of check before printing. So that saves their errors and that saves their check cost. But what if I have to finalize the pay role, and at the end of the month, I have to distribute the salaries to employees by checks. So there's a little problem here. Let's see and go in our daily cash book and check printer too? We have a lot of employees, and at the end of the month, their salaries are something like this. I will enable the content, Now, according to this check writer, I have the name of the person for which I will give the check. There I have all the names. I have the amount that I will give to that person. That's fine. What I don't have is the amount in words. What I wanted to do actually is to import all these names and amounts so that even I don't have to write any check manually in word. That means connecting Excel with word to automatically print whole 50 checks or 500 checks or as much as you want. So that will be a great help if I can, you know, use the Excel sheet to print the checks from check printer template from word. But the problem I can see here is I don't have the amount in words. So is there any formula which can extract amount in words for us? The answer is, no, you don't have any kind of formula that can do this thing. But you have the developer tab. So as I told you, when Excel has a limitation or doesn't have any function, you can insert that manually by using either macros or visual basic. So what I will do is I will just insert a module in visual basic. So how I will get the module. I can just Google it also. There are a lot of people who write free of cost modules, and it is all around the Internet. Also, I have already downloaded that module. So what I'll do is I will go in our dist thing, new spell number. I have saved it in word format. What I will do is just simply copy Control A and Control C. Okay. And go on to this set. Right now, I can see that if I write any formula like spell number, Excel is saying you're stupid. There is no formula like that. But I can insert that if I insert a module in the developer tab. So let's go in developer visual basic, and then you just need to insert, go to insert and insert a new module, just paste the copied module right here, and then you can close this. And if I check it now, equals to spell number, wow, I have a formula. So isn't that amazing? Let's just go further. Tab, and it doesn't have any tooltip, so just you have to select this number and it will spell it in words. So that's how you can get 4,000 Tums only. I actually changed their formula a little bit for UA currency. If you want to change their hums to dollars, for example, or Rupees, for example, you can go in visual basic, open that module, and if you're not on that module, you can click Module one, and then just press Control F. Find what? I don't only want to find. I want to replace as well. So the ums and replace it with dollars. And replace all 11 replacements made and Dam replace with dollar. Replace all, two replacements made and fills. That means the short currency change with cents and replace all ten replacements made. If I check the formula now, I can just double click and see the results. $5,000 and dollar, dollars dollars everywhere. If I try to see 356,782.23. That is amazing. $356,782.23. That means you can never make the mistake even you have to write a big amount in words on the check. Now I want all this to appear automatically in the check printer format so that as I give the print command, immediately, all the checks will be printed. Just right away. Even if you have 50 checks, 500 checks, depends on the printer speed actually. After that, you need to save this. As I told you, whenever you go in developer tab and use any of the functions from here, you have to save as not a normal Excel workbook, but as macro enabled workbook. So I have to use this file in the word template. So that's a different situation. In this case, I will not change it to macro enable rather. I will use the macro free workbook. So I will just place it on the desktop for now, save this, and it is saying that you haven't enabled the VV project, so it will convert it to a macro free workbook. Yes. No problem with that. Okay. Then you can even close that. But before closing, you have to note on which sheet tab is your salaries. The sheet tab name is salary given, all right. All right. Now that I have this, I can just delete all the names, the amount in words and amount from this template. For example, I'm giving away the salaries on 31st of January 2016. Okay? 2016. So here, I have to write the name of the person. So that's what I have to take from Excel sheet. So before, you know, applying this thing, I have to connect word with Excel. So in order to do this, you need to go in mailings, and then select recipient. And since you have all the list already defined, the names, the mountain words, but it is in the Excel file. So use an existing list, mailings, select recipient, use an existing list. And then I can go in my location where my Excel file is stored. Select this open, and connect to which one, I need to connect it with salary given sheet, press. Now it is simply connected. Now, what do you want here? The name or employee name? So if I go in mailings, insert Merge field, I can see all the names appear. You have to keep in mind that you must give headings to each of the column, you have to give the headings. Otherwise, you will you cannot identify. So I want name here. Insert much field, I want amount in words here, and I want amount here. And when it is done, you can just, I need to adjust the size also. I guess 14 is fine. This is also 14. Okay. Now, I can go in mailings again and preview the results, and there you have it. All the results are automatically taken from this from your Excel file. What you can do is immediately, go in the starting point, go to file, print, Then start printing it. It will print all the checks one by one. Even if you want to see all the checks together in the template. What you can do is go to finish and merge under mailings, mailings, finish and merge, and edit individual documents. Merge all records is fine. Okay. What you can see is it is divided into all the templates. Then you can simply print and you can even get time to check each if each values are good to go, and you can confirm that there is no problem with any of the situation. We can see a problem here. Sometimes it automatically occurs. I don't know why. But in that situation, just ignore the last check and give the other numbers. For example, if you have 29 checks, give the reference of page one to 28. So file, print, you can change the named the custom print, and you can write the pages. So that's how you can do it. You can even put a You can even put a fake check or, you know, already use check so that your new check will not get destroyed by this, because in this one, it's not showing the amount in words and even the names. So in most of the situations, it works just fine and saves you a lot of time. So I hope you like this amazing functionality that most of the organizations don't use or don't know about. So thank you for watching, and we will see you in the next video. D. 53. Introduction to Hyperlinks: Hello, and welcome back. Now, we have another function called the Hyperlinks. Let's say that Mr. Smith is a lawyer, and he has number of clients in a month that is 27. He charges a nominal fee of $200 with a client, and the subtotal is simply number of clients, multiply by client fees, and he have also put some investments, and he earned some income $2,000. So his total income for the month is 7,627. So he knows Excel, and he has prepared a little profit report. So that means that he have the total income of 7,627, which can easily seen right here. And he has some expenses, 4,950. This kind of expenses where it come from is unknown. If I click on the second sheet tab, the hyperlink data. Okay. Now I can know that what is the distribution of the expenses and where does it come from? The problem is, if we have a common data sheet that have a lot of calculations and say our calculation is placed right here, So in that way, I can never know that what was my expenses. Because when I will come on this sheet, it will show me blank or other variables. What I need to do is connect immediately from this range, whenever I click on this expenses or this figure. So for that, you need to, you know, name this and you can go on this cell and you can write expenses. EXP is just fine, and go to Hyperlinks, and then I can select both of them. Right click on it, Hyperlink. The hyperlink simply can connect with external references. For example, if I click on this word, it can open income or google.com or facebook.com, anything I want, write the address here so that the selected cell will be referenced to the website. You can also create a link with another file. For example, I have this calculation, but in a PDF file stored at another location, for example, D drive, that is simply separate from this Excel file. So that is also external references. You can link onto any of the file. But right now, I want to link this cell with my internal references. That means the reference or area, which is contained within this sheet. So place in this document. Do you want to link it by cell reference cell references means simply the sheet names, from which sheet name do you want to link? Even if I linked it with hyperlink data sheet, that is this. When I will click on this, it will immediately redirect it here. But as I open it, I have the references right below. So in the front, I will not see that calculations. So what I will do is I will go a little downwards, and we have the defined names that is the exact range associated with that expenses. So I have already named it, and then just press. When I click on this, it will immediately go up to this point. You can also link it with external references or anything which you like. Now, the formatting you can see has now changed. If I don't want this purple, this underlines, it doesn't look good. But rather, I think that this format is just fine. You can use this format as format painter, select the whole data, you click format painter in the home tab, and then paste it right here. In that way, No one would know that this is a hyperlink, but as you click, you can just go on here. 54. Hyperlinks With External Sheet Refrences: You can also map up your data in the office, that means that you have a lot of files in your computer, right? But you don't need all of them every day. Every day, you might have some common files. For example, I want to check the bank as I enter in the accounts department. I want to check the last month salaries. I want to check employee loans detail, staff details, check print. I need to print some of the checks every day. So That is the most common functions that just I have written in a list. What you can do now is hyperlink that one. If I click right and select hyperlinks, let's remove the hyperlink, and then apply again, go to hyperlink, and I can relate it with an external reference. Existing file or web page, I can link it, for example, with this file, bank book reconciliation, and then click Okay. Now, the formatting is not really good. I will select this format and apply it over here. Select both of the cells, use format painter, click on this and then click on Hare. Then you have the answers. If I click now, it will immediately open my sheet. So just you have to remember, you don't have to change the positions at which the files in behind are kept. So if you change the name of the file or the location of the file that it is linked to, this hyperlink might not work. But it can save you a lot of time. You just have to open the data mapping file every day and even you have tons of folders after which you can get this file, you can directly click this to get directly in that area. So thank you for watching. 55. Columns Separation and Concatenation: Now I have something amazing for you. You have some list of full names, but for some reason, you need to split that in first name and last name. This full name should be divided in two columns. One is for first names, and one is for last names. Now, you don't want to do that all manually because these are a lot of names. If I show you 24, 24 names, you can imagine what if it is 24,000 names? Obviously, it's not possible to do everything by your hand. You need Excel skills for that. Here it comes. You can just select the whole column. And go to Data tab and select text to columns. Now, what it does is it will split a single column of text into multiple columns. That is exactly what we want. So I will just click on it, and we have two options here. One is delimited and one is fixed with. Now fixed with doesn't have much use. I'll show you why. I'll just select this one and click next. Now you have the preview of the data that is available. What it is saying that just click anywhere within this preview, and it will drop a line between this two names. It's showing that if you go to next and finish, it will be split according to this line. But I can see that this is not just right. Why? Because the length length of the name is not similar in each and every scenario. As you can see that it might cut at cardo and take the DO last few characters of the first name in the next column, which is just wrong. It is not used in much of the scenarios. We will just go back and try de limited functions. Now de limited separates according to a special characters that exist in each and every field. If we say that hey, split the names after the space. That will be perfect. I will hit next, and I will say that I want to separate this full names according to space, and you can see that in the preview, it seems about right. I can just hit next and then finish. Here you go. You can now label it as this is the first names, and this is the last names. Here you go. You can separate it just like this. Now, imagine the inverse scenario. You have received this sheet, and for some reason, you need to combine this first and last name into full name. Now, in that situation, you cannot use text two columns, but because it is used only to split the text, not to combine the text, and I have to separate I have to combine the two cells. I will just write equals two sign, and then I will select this cell, and I need to combine it with other cells. I have to insert and and then click on this cell. The formula is this cell and this cell, I want both and hit enter. You can notice that it combined the the names, but still we need a space between these two names. What I can do is after A two and I need a space inverted, space, inverted commas close, and then I need to join it by P two. I have to write and again, the formula is A two and space, and then I want the last name. So this is just about perfect. Let me enter this. So Sunil Kumar, you can see that. I will just double click and there you go, you can see that all the names have combined. Now, if you want to get rid of this data because you need only full names, you need to remove the formulas from it because if you removed this first and last name columns before this one, before making it independent, it will show some errors because the full names columns is dependent on that values. To make that independent, you need to get rid of this formulas, and you know that. It's really easy. I will just select the whole range, control shift down, and then you can hit Control C, and what is the short key to paste special as values only? This is very easy. Alternate E S V, and hit Enter, and then you can see that. You have all the names without the formulas. Now it is independent, so doesn't matter if I get rid of this. I'll just delete it and there you have it in the original position. That's how you can separate the names and then combine that. 56. How to Extract All File Names from a Specific Folder in Excel using CHAT GPT: In this amazing video, I am going to show you a very quick but very useful Excel trick. That is that let's say you have a folder and you want to extract all of the file names in Excel without using any formula with just a single click. You don't have to know any coding for that because we are going to do something amazing here with the support of chat GPD. For that, we need a developer tab to be enabled in Excel. So to enable that, you can just click on any menu. And on the right hand white space, just click on Customize the ribbon. Now you can see the developer tab will be unchecked like this if you don't have developer tab shown here. So just check that and you have the developer tab here. Now, I'll go to Visual Basic and click on EnserT and click on Module. Now, here I have to write the code, right? But I only know what to do, and I don't exactly know the code, so we will ask Chad Gibt to do it for us. I'll ask Now, I only ask Cab to write a VBA code to extract all file names in Excel from a specified folder with select folder option. Let's just copy this code and paste it here and close. That's it. Now let's test if this works or not. I'll go to macros and here we have extract file names option. If we run that, it will ask you to select the folder. We have our folder right here. Let's click here. It says, file names have been extracted and listed in the worksheet. L. We can see here by default, it places all of them in sheet one. Let's try to do this again. Let me show you again. Let's actually delete this other sheet. We only have sheet one now. I'll go to Macros, run the macro. It will ask you for the folder selection. Let's select the folder, and V are done.