Advanced Excel Functions & Excel Formulas: Countif, Datedif, If Function,Password Security | Kaustubh Kelkar | Skillshare

Advanced Excel Functions & Excel Formulas: Countif, Datedif, If Function,Password Security

Kaustubh Kelkar, Expert - Microsoft Excel

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
10 Lessons (54m)
    • 1. Advanced Excel Functions and Formulas

      1:34
    • 2. Password to Open Excel File

      2:10
    • 3. How to make only selected cells read only in excel lock and unlock cells in excel protect cells

      3:41
    • 4. How to Calculate Difference between two dates

      12:14
    • 5. How to Make Excel Workbook Read Only

      1:27
    • 6. Vlookup and Duplicate Values

      5:52
    • 7. Vlookup does not work and how to overcome it

      4:38
    • 8. How to Use If Function in Excel

      7:43
    • 9. Couintif & Countifs Function in Excel

      10:35
    • 10. Absolute and Relative Reference Explanined in Excel

      4:25

About This Class

This is Advanced Excel Course, and contains the following useful Formulas, Functions and Explanations

  • Advanced Vlookup, Why Vlookup Fails and How to fix that. Vlookup and Duplicate values

  • Advanced Countif and Countifs functions with detailed examples and explanations

  • Security of Excel Data

  • If function with the basic and advanced/complicated example.

  • Date function in excel - Advanced finding date differences in excel

  • Understanding of Absolute and Relative Reference in Excel with examples

Transcripts

1. Advanced Excel Functions and Formulas: Hi, Franks. I wish your been great As everyone off for snow finance people are most exposed to Microsoft Excel being a finance professional, I got lopped off exposure and experience in Microsoft Excel. So I decided to share my knowledge. I helped creator this course, especially for you, so that you will enjoy improvement in your day to day working life off using Microsoft Excel This course contents quality material on Microsoft Excel. It covers important topics like Security Off Excel file. How to make only selected sells red only in Excel at once we look up function and how to handle it with duplicate values. Why we look of fails and how to our commit if function with basic and advanced example date function in Excel finding difference between boutiques understanding, absolute and related reference in excel with examples. County function in excel with examples. So make yourself believe that you up for this course and ended. Sure XL knowledge. Thank you 2. Password to Open Excel File: could I wish to discuss one Useful Excel function that is password to open the fight. Suppose I have one for equal Book One currently does not have any password. So if I felt I can open directly there Suppose I wish to give the password to open this file. How will I do that? Okay. Same Ligaya Save s Excel workbook. Yet there is option off to its gentle options. There are two options here that is password to open and must work to modify. So I'll give some password here, but for 21 OK, I'll re enter that masseria password here. Okay? No, I'll simply say this book one already exists. We want to replace it. Yes. So I will close this fight. No, I suppose if I want to open this file again, just civil defense. Okay. Now it is asking me for the password. So I just enter the password. Which I sect. Yes. This is how you can ah, give the password to open the excel. Fine. Now suppose if I want to remove the parcel How well I do that, Okay. Simply Gorgon syrahs again. Go The tools. Gentle options. Simply delete despots wit Click. OK, su book one already exists. Do you want to replace it? Yes. I'll close this file. No. If I want to open this file, I can I will be able to open without any possible check this. I hope this video was useful. Do 3. How to make only selected cells read only in excel lock and unlock cells in excel protect cells: Hello French Today we are going to learn culpable. Only some required sales really in excel. So for this I have some basic data so that we can understand this. I have three columns, overs worked, read power and labor cost any labour cost is the multiplication off worse work and read power. Now suppose my requirement is that I want only the column off. Worse work, Toby. Irritable for the user. And these two columns. Toby Non every table. So how does Judas Let's have a look on it. So first I look and told a Then I will go to for much else the year on protection type. I will uncheck this locked button. Okay. Now I will select their cells which are required to be read only control and selection. So I have selected the cells which I required to be read Only then I will again go toe format cells on protection type. I will click on this check box and it will be locked. Then I will click on OK, no, I will go to protect shit. Button off this review tab No more will enter the password. We re enter the password to confirm confirm that. Okay, now only did sales are any table Another set of cells have become real only so. If I can change anything yet like this this this this. And accordingly the result will be changed. But if I try to make any change here, for example, if I Inter try to enter forya Oh, it will display the message that the sailor chart you are trying to change is protected and therefore read only. So this is how it is achieved. So if I want to one protect that again, then I will have to again go toe on protection. I will have to enter the password. Okay. Now I will be able to make changes here. So this is how this is how it is achieved. I hope you have understood this very well. 4. How to Calculate Difference between two dates: French Could I wish to discuss one? Very interesting. Any useful function in excel. And that is to finder difference between two dates. Ah, many times we need to find a difference between two days for many purposes. And, ah, interestingly, in excel there is There are formulas with which facilitate that very quickly and easily. Okay, so let us discuss that in details. Okay? Friends supposed how the stool dates, and I want to find their difference between these. Ah, so how do how can I do that? Okay. First ensure that, uh, you simply click on a right click for my chills and ensure that the four might off date is the date format. So for maturity date and you can select any off these options for date for my similarly, this date also should out or date format. Okay, Now, suppose I want to find the difference between these stories, so I confound. Find that in the border of this. Okay, Okay. Leia, Say is equal to detailed if and simply click on this first comma this, dear on a bracket, complete Frank's cell. Please note this formula very well. Our data if 83 b three D in in water comma. So I will get number off days indifference. What's the problem? Is equal to be jailed, if that's this. Okay, Prince, please know the formula. So it is a data if 83 B three and the into inverted comma. So, in that way I can get the number off days difference between these two dates. Okay? Supporting the similar way I want to find a home in the number off months. There is a difference between these two ribs. I was simply Dealer is equal to the sheriff. A three beat three put a mere Okay, detective 83 comma. Be three m simply enter. So you get the number off months difference in the similarly you can find the number off years difference The date If this comma this comma Okay, French in this way you can get there. Difference between to raise in the form off number off this in the form off number off months in the form off number off years. Yes, but here the problem is that Ah, you cannot get exact proper picture if you find it in number off months. Because ah do you lose the you cannot find the number off days which are less than month. So it is only showing 13 months. Similarly here it shows only one year. But it ignores that days which are in addition toe one year. So there is one more thing in except that facilitates. I'll show that number off additional months in additional year. Okay, so how it goes is equal to did. If this comma this comma if I put here, why month? Why am I Why am okay, So yeah, it will give me the Eddie Snell. Oh, additional months which are in addition to year, so year. In addition to year, there is one month also, Jenny's that kinda great dick. This is much more than when the air even more than one year plus one month. So it is giving me the additional months which contents like it contains one month intern in relation to one year similarly number off days in addition to month. So Z Quito detectives, this comma this comma I'll put here m d. So it will give me additional day You anyone in addition to month also so basically ah, this contents one year, one month and one day in that way we can we can calculate So our friends, if I want to automate this I want to find this in the form off Year, month, 10 days. So how exactly? I rely a calculator debt. Okay, so ah, year There is little bit complicated formula. There is a pick through which or take 10 combination all formulas through which I can actually this Okay, so what I'll do is equal to retaliate. If this comma this I simply put why here. Fair enough. OK, now I want to find additional months in this. Okay, so So please check the formula. Very well. It is little bit complicated formula, but if you pay attention, you can very well grasp night is equal to If this is more than or equal to one then detective, this It's this why I m sufferings. So if this is more than or equal to one, then I will I will consider it as a Why am else detective this This m will complete this bracket. I will complete this bracket office. You can the very well know down the formula and I lent her so it will give me additional one month it now I am to find in days also. Okay, So, friends, this is more a little more complicated but you will understand it. Please pay attention is equal. To If are this is more than or equal to one are this is more than or equate toe went so friends If either off this two is more than or is equal to one then data. If this this yet it will be m d so French. If either off this two conditions are fulfilled then I will consider it as Embry s deterred . If this this how you consider only deep bracket complete, I will complete this bracket off. If also Prince, you cannot know down the formula Very well. See See, it's not much difficult as you are thinking. If both off these are either one or more than one then I will consider it ties MD else I will consider it on. It has only as a d. Please note the formula very well. Okay, I will it click on enter? Yes. So in this way I can get the day difference in the form off year. Someone send its ok friends now. Ah, let's cross check? No, If I'm making it only hurts as 16. Then see France. It is showing one month, one days, right? One month and one day if we days. Ok, if I make it as ah to get off the sim. But 2015 then severance. Here there is the Roman 20 year did showing difference off logic to this. You can get it here also. Okay. Ah, I suppose if I make it exactly one year Ah, let me 28th off December stuff. I will make it 2016. So, France, it is giving me one year less breweries. Three sixties, eight days, 20 year plus pulleys. Because our 2016 is a leap year. So friends in this way you can find a dead difference between two days in the form off year , month or days, or you can either find in the form off days. Also, that is very easy like this. So French. I hope this video was useful to you. And I guess 5. How to Make Excel Workbook Read Only: useful thing in excel, and that is to protect the excel file. Suppose if why I want to make Excel files read only then how, exactly? I should do also build it. Okay, so let me explained it. So go to review Det Simply taken Protect Shoot yet Blue seven. Possibility it Password to UN protect ship. I'll given some past failure. Okay, I get arguing westward. I am re entering the password. Okay? No. If you see if I try to make any changes If I tired toe type something, it will give me the message that the sailor chart you are trying to change is protected. And therefore, really? So in this way you can make the Excel file. Really? No, no. Obviously you will ask how to again and protect the ship. And that is also were easy against Simply goto on protect, shoot and turned the password. Okay, No snow. See yet I can make the genius. So in this way we can make the Excel file. Really? I hope you like this video. And if Yes, 6. Vlookup and Duplicate Values: Thus, one situation that is we look up and duplicate will use many times we face a problem or while applying. We look up if there are duplicate values and I want to address this situation in this, will you So please. Ah, check this data. Suppose I have this doctor which contends simply alphabets and our luggage is that there are numbers. And I suppose if I apply, we look up here. So his acquittal in the cup look a prelude, this zero so and simply real copy this formula. And I played here. No, Si, uh, in my orginal data, there are who is okay. And for first Ed, the there is number against. Second day there is number eight, but in my B look up for every it is showing number. Tony. So this is not ah, quite a logical. So logically you would like to have eight year, but it is. No, you're not getting there. So our hope to handle this situation actually, in excel there is no such formula our function to handle this situation. But there is one trick through which we can a Judas. Okay, so how exactly to do that? Simply follow me. I know one thing inside one column. I didn't set one more column here. Okay? No, simply follow me. Ah, wouldn't know what a little by Little Z Quito Come. Def. This comma This. Break it complete. Simply check this formula. I don't exactly follow my exactly follow me. Okay, Now what? I'll do. I'll copy this and this ticket. Okay? I know what I really A very simple thing is equal to this and this. Okay, again. What? I look so basically over with this county formula. If for every alphabet you did showing how many, uh, alphabets are there, for example, like for for a there is for second A. There is a number two for all other alphabets. There is only one, only only one because there are no duplicates. And if I combined this, I'll get this unique number. So foot forced air will get a one for second day. I will get a no for a little while. Simply I play. We look up here just, uh, for a bit up display. I copy this. Okay. I know what I'll do. I'll simply a play. We look up in this column. Sorry. First I would like to do the same exercise here also, Zik will do Going deaf. Thanks. Simply follow me. See it before Amina. Okay. I'll copy this formula again in the similar way. How got the unique numbers So I will combine them is equal to this. And this General Copy this. No, I will apply. We look up here is acquittal. We look up, you could tell you this and I really clever We look up stability on this This do zero Okay Now will simply copy this and placed it here. No, si for this a how got for they say how got it, which which is what I works, you mean? Yeah A for they say I had to get it and that That's exactly I'm achieving this with this. So I hope you have ah like this video. And if Yes, please. 7. Vlookup does not work and how to overcome it: to discuss when interesting thing about we look up many times it happens that really does not give the expected dessert. And we keep wondering why it is It is not giving the expected reject. So, uh, how do are how to get a proper recite with vehicle formula? So check this data. So basically, I hope they see a doctor and vehicles to a black. So I have a basic data which contains compete and its capital. So if I apply, we look appear. I suppose if I want to get the capital off UK, I will. Simply a play is equal to me. Me. Look up. Look up. Value this table, Larry, if I select a table area in this way Coma column index number two Gino record completely. Okay, so how god they Ah, proper answer ir to this Lynette for UK capital is London. Okay, but if I work, if I copy this copy. So it wasn't so If we can see it one in my basic data. Ah, there is Ah ah, capital off India. We are not getting the expected. He's idea. So what is the reason outfit Simply debility corner. Click on that simply ability conduct so that we will get their results. Okay, so we can see that as we look up. Yxta related reference it tends. Picked the two columns down our basic data and so that it has missed India and it is not giving the expected exact. Okay, so how do we overcome this? So I really we look up in a different manner is equal to P. You look, you could do this again. Coma for table. Larry. What? I will go. I will sleep The whole column column index number two zero. Break it completely. Yes. Now if I copy this Mr. Yes, Exactly. There you are. No, I'm getting the expected Rejected. So in this way Ah, there is always a better way. We can ah play. We look up. Okay. Um in fact, in innovation could just stare One more situation when we don't do not get the expected result from the look of formula Supposed What will happen if there is a space? For example, If there is a space yet, you can see there is any for UK. Similarly, if there is a space here, suppose you're if there is a space, then also, though we will not get the expected is eight in vehicle So s so if there is a space in our basic data or in a what we look up data, then we will not get the proper desert azi expected to look up formula. So how door Come this have very simple, smart way through this I will simply slick this will column we look into left. I will put space here and I will replace and year There is not any space. So I will do replace on when replacement point similar level Who just same thing here control if I'll put one space here, replace new space replacement again. Wonder placement. So no, we can see that the vehicle is giving the properties Lately I hope you have enjoyed this radio and you have under 8. How to Use If Function in Excel: golden the if function off exit To explain this how to put examples when is very simple example Anyone is little bit complicated Example. So we will first go with the simple example. In this simple example, I have the marks. If the marks are more than or equal to 35 the result is bus and if the marks are less then ratify didn't then the result is failed. So now I want to derive the desert off these trees currents who have marks off tight et vagina and 100 respectively. So to get the results, I will use the function. Let's see carefully his acquittal if open bracket this his more than equal to 35. This is the logical test korma then Then again, comma else this combines for else. Bracket complete. So here we can see as the marks are less than 35 the desert is filled. I can simply copy this formula and this ticket so year we get the results off every student you can see the formal argon. Okay, I hope you you have understood this very simple formula. Now we will move a little bit complicated formula off if here we can see Basically Ah, they're slaps for income tax. So if your income is the less than her love less than are up 200 k income tax us is a hell if your income is ah 101 to wonder in taxes off 10%. If your income is off 012 years, then taxes off 20%. And if you're income is about 300 in taxes off 30%. Okay. No, If I want to automate the tax calculation Big day of function off If how will I Buddha? Yet it is so I'll explain this how exactly it is done. So I will use the function is in Quito. If this is more than 300 then this minus 300 and do 0.3 less 30 because up to 300 days of Libya backs off 30 short. So didn't if there will be one more if again if if X is more than no wonder, then if three minus two under in 2.20 less then so there will be one more if function within this this is more than 100 then this minus indicts in 2.1 else it will be zero. So basically there three times we have used. If so, I will have to complete the bracket three times one. And so here it goes. So Texas calculated automatically. So if I change this as a 4 50 the index will be for 75. If I make it as 105 100 in text will be 90. So, for example, we will cross the leaf over. Calculation is correct or wrong. So if I make it a 4 50 Texas, calculated as 75 will cross telling with this example. So for 1st 100 off rupees 4 50 the tax will be zero for next 100 off your income X will be 10%. That will be 10 for next 100 off your income tax will be 20. That is 20%. And for next 1 50 the text will be 30%. So it is 75. So we're calculation with this. If function is correct, I hope you have understood this funk 9. Couintif & Countifs Function in Excel: county function in Excel. Yes, it is very, very useful function in excel. And it is very, very useful for especially finance professionals for number crunching. Okay, so how do exactly use county function in excel? So I will explain this function with some examples so that it will be very useful for you to understand. Okay, so let's start. So first we'll check count their function. What exactly is a constant function? Counted? A function counts all non blank cells in a particular range. Okay, so oh, for your friends, for your rarely reference our creator these formulas here so that you can very well knows down. So I'll for for start with Kulti supposed Ella put the content function is equal to girls. Get B two b seven. Ditto to the seven bracket complete. Okay, So it will count all the non Blanco cells in this particular particular rich. So it is that it comes to six. So it is giving me the count off six. No, count the blank. It will give me the older blank cells in a particular range so is equal to blank bridge. So it will give me zero because there is no any blank cell in this particular range. Suppose if I make this sale blank then it will give me the count of fun. Okay? No. Oh, see this formula were carefully If I want to do the if I want account off all the sills which is having number is equal to or more than so I would give The range is equipped Count gif my range. Then I will put a comma more than is equal to two inverter coma complete bracket complete inter So yeah, you can see there is a three and four So it will give me the count off all the numbers which are more than or equal to suppose if I entered two year then it will give me the count off three. Ok, ok, so moving forward. And now I want a count off particular number. Suppose I want to have a count off cells which contain the number three So I will simply go here is equal gold. If I'll do the range, it's simply saved three break it complete. So there is only 13 in this particular range. So it will give me the count off when gold If If I suppose I want to have a count off cells which content alphabet eight. So is equal to can't if range friends for alphabet. I will have to put that in inverted commas because it is not a number. Break it completely. So there is only one in this particular range, so it will give me the count off. No, this is lit Hilsum A smart way to use a constant function here. Suppose I want o all the cells which contain number three also and number four also. So how exactly I will achieve this. Okay, so I'll go yet is equal to ground beef every inch, three bracket complete. Know what I loo look less again going If that VH four make a complete no, it will give me the some off all the sales which contained number three and number four. Okay, no year our them one small experiment How to combine two functions in Except this is a very good example. Now, if I want to say that if some off gold if three and four is if more than two it should be better. And if it is less than toe or equal to do then it should be only good. So what I do l simply simply lose importing will simply copy this No, While put a function here. If this is equipped with this this more than is acquittal to then should be good Ace, break it complete. Enter civilians. See, In this way we can see it is more than equal to do so. It has given me either result off. Good. If it is Ah I will tell you if it is so. If this number comes down toe to then it will be better supposed if I made this See? So in this reference we can combine it 12 different functions. So I have made a combination off if functional called the function for better results. Okay, No complete count ifs, count ifs is ah in there is a function which allows who took account If criterias are free feed. So check this formula. Check this formula available. Countermoves did to the seven a liberal shows here. Going gifts. Do you truthfully? Seven Your food disease e and a troop issue in Is he so only if two criterias are fulfilled ? Then it will meet. Give me the count supposed If I make this as a G, then it will show me that seal So friends in this way you can use a count ifs function. Okay, now French on this is very basic. These were very basic examples off count if function now I want to tell you how exactly used this country function for advanced 36. So I'll show you how to use a county function even for some little complicated things. No. Suppose how this data on and I want to find out duplicates in this particular data. Widows deleting them. OK, so and how Count ifs can be used for this particular thing. So let me explain. So I lose going doof I'll give the range off D three again The three break it complete. I do one thing that I'll put Dolar saying here. No, it will give me the count off this. No, I'll simply picture this. Ah, off formula. Very well. You can. No, you don't. How exactly it is used. I will simply copy this. And I missed it here. No, as there are no any duplicates here, you can see when everywhere. But if I make suppose this as it. Then it will give me to hear if I add one more year, then it will give me a 34 against the number two. I suppose if I make this as a win, this is 2nd 1 So it will give me number two here. So, friends, in this way, county function can even be used for some complicated and and advanced things. 10. Absolute and Relative Reference Explanined in Excel: today. We're going to understand. What is that related reference? An absolute reference in exit. We will understand this with these three examples. Yeah. What? We will look Well, we try to understand what is the related reference? Suppose my requirement is that I want the multiplication off these two cells. Similarly, these two cells, these two cells and these two sets. So for this I will simply boom multiplication yet this into this. And I will simply copy this, but what I want and yet we can see how God required is X. So this is a relating reference in which you can simply copy the formula and based in the way they were to go on. No. Now we will check. What is that? Our absolute difference? Suppose my requirement is that I want the cell number if I if six any of seven to be multiplied by 25. So if I do this way this into this if I copy this, I noticed it. Then I'm not getting the required dessert. I should get 50 year and I should get 75 year to achieve this. How you doing is equal to this. I'll enter F four year in the this. So yes, I'm defining deck Sell F four has absolute difference. And still, if I has a related reference Now, if I copy and pasted this, I get the required dessert Prince, We will explain one more example yet my requirement is deck. I want these three cells. That is the cells off column K. We multiplied by 25 sells off column L we multiplied by 26 and sells off column name to be multiplied by 27. So how we let you so here I will require the absolute preference root for and relatable reference off all this. So to achieve this are you doing is acquittal. I would go to sell a K for a press f four. Will you press it for one more time? Okay, so here you can see there is a dollar sign against Roe for so yet I'm defining that roof or will have absolute reference into here into this and silk If I will have a related weapons , simply copy this. Copy this bested yet so friends, we can see that I have a chilled require. It is it is a