Hi guys, welcome to this interestingsession on MS Excel. So in the session, you'll get a complete overviewof Advanced MS Excel 2016. So we'll start this sessionby understanding custom and conditional formatting and then we'll go throughadvanced functions and formulas. Once you understandthese two topics will go through advanced tools and data analysisand finally end the session with introduction to macros. So today we havea special guest pressure who's going to take this sessionforward so over to you Trisha. Hi, my name is Trisha jagtianiand you're going to do the Advanced modulefor 2016 Excel version. So what's new in 2016 newcharts like waterfall, Pareto, whiskers features, like tell me, smart lookupand enhanced pivot table where you havemulti selection options for slices search functionsat a pivot field list. Many more such functionshave been added to 2016 Excel. So let's start withthe important Formulae of Ms. Excel. The first is the sum functionthen enter the equal to sign followed bythe function name and the open bracket. So, let's see in our example. Let's say I needed total salarypaid to all the employees in the organization. So I have the employee listand their salary detail. So how do I start is, I need this informationin cell J 2. So to get started. I will have to enteran equal to sign in cell J 2 followedby the sum function and then open the parenthesesor the bracket next. We'll enter the range which weneed to total in our case. It is a salary columnwhich is H to H. So I select the h2h columnclose the bracket and press enter it givesme the total salary paid by the organizationto all its employees, which is 1553825 rupees You must have noticedthat I selected the full. Column H and not justthe range 1 to h101. Why did I do that? I did this in orderto make our range dynamic because if in futurethere is any addition to the employee data, the sum functionwill automatically incorporate the new data entered saywe have a new employee. All right. So enter the nameof the employee here, and I enter the employee salary just for our exampleas 50,000 Rupees. Once I enter the employee salaryin the new column. What happens is you will see that the total salary that we had calculated in columnJ2 has automatically changed and it is now addingthat 50,000 Rupees that we have addedto the new column. Let's understand an importantConcept in Excel which you need to remember before I get to the nextFormula for any formula or function to work. We need to ensure that we provide correctinput truth excel in order for it to provide youan output having said that there are only four thingsthat an Excel will understand. Now, what are those four things that Excel understandsthey are it's inbuilt function or formulas which are the sum average min Max with the another one isthe numbers like 1 2 3 21,000 that is showingon the screen third One is the true or falseand the fourth one Is it cell referenceswhen I say cell references, what does thatmean the references that we give to the cellin order to get any output of an answer like this onethis cell references or when I was doingthe sum function. Also, I gave that H to H asmy cell reference other than the above all the data need to beentered in the double quotes So when I say all the data tobe entered in the double quotes if I want to giveany information to excel other than these four things which I have mentionedyour I will have to put it in the double quoteslike this anything that I'm putting otherthan the will function numbers true or falseor cell references. I'll put something like this so we will explore this in detailin the next function, which is the sum F function the summary functionsums the range based on the single condition. Or the criteria. So let's explore the functionwith the example on my sheet in order to startwith this function first. We need to understand the term syntax to understandany function for that matter. We will have to understandwhat is syntax. Every function in Excel has a syntax syntaxplays a pivotal role in using a function assoon as you type equal to sign followedby a function name and open the bracketafter the function name. It will give youa list of arguments which are these arguments or itcan also be called as an input that Excel requires in order for it to give you an output. So if you see when I open the summary functionfor the summer function the inputs requiredour range criteria and the sum range nowtaking the same example as used for the sum function. Let's say we need to findthe total salaries paid to the employeesin the South Region. So if you can see our list,we have this list of employees. Just need to find all the people workingin the South Region. So the column that carriesthe south region is column e that is required bythe sumif function is the range. So let's startwith the Fermi function. The range that I have to enteryour is the criteria range. What does the criteria rangemeans the range where your criteria sitsthat is in column e so, I will select column eto e now again, if you see I am selecting thewhole column because in future if there is a addition to the data additionto the employee list, it will automaticallycatch your as well. First range that I selectedis the employee range. Next one is the criteria. Where is the criteria thatwe are looking for which is the South Region, correct? So I'll enter Southas my criteria. If you have noticedI entered the south in the double quotes looking atthe previous example are looking at the previous datathat I told you it's in Bill formulas functionsor numbers true or false. It's still referencesthe criteria that we have which is the South does. Fulfill any of these fourconditions and hence. I will have to put itin the inverted commas or the coats. Once I do that. The next thing that I have to enteris a some range now, what is the sumrange the first thing that you selected wasthe criteria range because your criteria sitsin that next was the criteria because the criteria is yourSouth third one is a sum range because you're not goingto some the region but you're tryingto get a total salaries paid to the employeesin the South Region. We will have to selectthe sum range as salary which is our H to H column closethe bracket and press enter now, what do we get? Here is the total salariespaid to the employees in the South Region. This is only one answer that I requirefor the whole list. And that's the reason Ihave put it in this column. Let's look at another examplefor the same sum F function. We need a total salary of the employees whose salaryis more than 15,000 rupees. So earlier it was like we were lookingfor total salary of the employeesin the South Region and that's the reasonthe answer was three like thirty six thousand. However, this timewe are looking for the total salaries paid to the employees whose salaryis more than 15,000 rupees. How do I do that by againusing the summit function? Like I said, the summit function actuallylooks at the criteria and gives you the totalof that data. Now if you would have noticedwhen I start with any formula, every formula will give a syntax like I mentioned earlierif I open the summer function. The first one is the rangethe second one is the criteria and the some rage in the range and the criteriathere is no bracket or it's just simplywritten range and the criteria, but if you seethe last requirement or the last argument, it sees some rangewhich is in the square bracket. So you have to rememberone thing the square bracket means it is optionalwhen I say optional. What does that mean that input that you're givingto excel is not required to be given in caseof an optional information. So in this example, you can give range youcan give criteria and if you don't giveus some range Excel will consider the ID rearranged as your some range sothe sum range becomes an optional requirement in our case the first casewe had to give the same rage because we were givingregion as our criteria range and it does not have a numericvalue the some can only be done in a numeric values. Hence. We had to give us some range which was our salarybut in our second example where we need a total salary of the employees earningmore than 15,000 there. I will not give the sum range. So the first requirementwhich is our criteria range, which is the column Hto H because that's where the salary is. Now. What is our requirementas a criteria or requirement is a criteriais more than 15,000 again. It's not only a numberit is not its function. It is not true or falseor it is not a cell reference. So we will have to put itin the double quotes when I put double quotes. I put a morethan sign 15,000 rupees which was the criteriaclose the double quotes. This time, like I said, I'm not going to givethe sum range and Excel will consider criteria range, which is the first rangeby default as the sum range. When I press enter. It will give methe total salaries earned by employees earningsalary more than 15,000 rupees. Hope this clarifies how to use the sumifs functionjust to summarize some f function will give youthe total of any cell reference or cell range where a criteria is met. Okay? Let's see. What is the next Formula or function that wehave on our list, which is the sum function. What is thesumifs function again? Just like the sum function sumifs function isthe function to some cells that meet multiple criterias. So like I saidin the summit function, we only had one criteriawhere and I was looking in the first example, I was looking at allthe total salaries on by the employeesin the South Region. However, in this case, I am lookingat multiple criteria, so We are lookingat the second example here find the total salariespaid to the employees in the sales department inSouth Region learning salaries more than 10,000 rupees. So now if we see your wehave three criterias, let's go back to our document where I can writedown my 3 right areas. What is my three criterias? So let's lookat the criteria one. Let's put a small table so we can just put itdown in that table. So to start with Ihave criteria one criteria to and then the criteria 3, so let's look atwhat the deuce three criterias. The first one that weare looking at now is salaries paid to the employeesin the sales department. So our first criteriabecomes Department as sales. Yeah, the second criteria that we have hereis the South Region. So the second criteriabecomes region as South And the third criteria that I have now your issalaries more than 10,000. So I just putthe greater than sign. Okay, so that becomesare three criteria as I have three criterias submitfunction will not work because somewhere functionis only used to get outputs for single criteria. So we will be using the sumifs functionthe input required for the sumifs functionare the first thing that it requiresis the sum range which in our case is the salaryso why is this salary because we are lookingat the total salary of the employees. So the first some rangebecomes our salary so it will total the salarynext is the criteria range one first criteria isDepartment as sales. And your the question that we are asking for allthe input that they are asking for is criteria range one. So if our criteria is sales asa first one our range will be the place or the cellsor the range of cells where the criteria sets where does our criteria setsthat's in the department. Which is the department, so I will select D2Das my criteria range one. We are going to put thisin the double quotes. Now comes yourcriteria range two. What is the criteria range two? The criteria rangetwo is our region. Where does the Region's it? It is the E column. So I will select e2e asmy criteria range two, then comes the criteria to when I say regionas my criteria range. What is my criteria? Criteria is South so I will have to enter that again in the invertedcommas South these information that you're puttingin the double quotes. They are not case-sensitive. Even if you see sales herewith S capital and all small. You can still put salesin all small letters. Okay going back to our formula once I have entered e2e s outthen comes the criteria range 3. Do we havea criteria range three? Yes, we do. What is the Criterion age threethe Great Range 3 is salary why because a criteria 3 is salarymore than 10,000 rupees. So I will selectour criteria range 3 as salary, What is my criteriathree more than 10,000 rupees. So how will I put that I will have to put that in the double quotesmore than 10,000 close the double quotescloser bracket enter. You will get the total salary as fifty five thousand eighthundred and twenty five rupees. This is the total salarypaid to the employees in the sales department workingfor the South Region. And if they are earning salary more than 10,000 rupeesmoving on to the next one, which is our count function. The count function isthe name suggests provides the count of the cellscontaining numeric value. So going backto my sheet as an example where I have the listof employees data. There is only one ragethat contains numeric value, which is thesalary column, hence. I will perform the countfunction for the H how will I do that to start with Iwill again have to start with an equal to signfollowed by the function name which is in our case count and I will select the column Hclose the parentheses or the bracket, press enter. It will give me an answer 1:01 because that's the totalnumber of cells that contains the numeric value. Next comes our count a function while count functioncounts the cells containing numeric data counta function counts. The number of cellscontaining numbers as well as text or characters. So when I say this usingour example this time, I will use region column tocount the number of employees. Why do I do that inthe first example, we use the salary column because the count function willonly count numbers this time. I'm going to use a region columnin order to identify how many cells or how many employees are workingin the organization. So to At with again. I will do count a function counta function counts numbers as well as text or characters characterscan be any character like double quotes or a spacecan also be considered as a character counta function this time. I'll not take salary. It will count the numbersbut we want to show you how it works withthe alphabets as well. So I go backto our my region column. I close the bracket enter. It shows me hundred andtwo as an answer. Why does it showme hundred and two while the first one showedme a hundred and one the reason being countwill only count the number of cells containingnumbers in this case. Our number of cells that contain numbersis hundred and one. However, if I am lookingat the region column, it also has the headingnamed as region. So that is the text. So if I start with region and go till the end it isthe total number of cells that contains the charactersis hundred and two. So in this case, how do you avoidgetting a wrong answer? It's not a wronganswer it just that in case if Asking you geta total employees working in the organization. You will usethe countif function because you don't haveany numeric value in that case. We will still usethe countif function get the information after that. You know that the headingis also counted as in this function. So we will always get used to putting minus 1at the end of the formula. So for example count a e to e, which is this solemn once youget an answer you just do - 1 this is just a trickto get your correct answer it nothing to do withthe formula formula will count your first heading as well. There is another thing that I would want to showin the county function is because I told you it will countthe numbers as well as text or the character in our example. We also have another data where we have numbers as wellas alphanumeric characters. So in this case, these are alphanumeric datais also considered as a text because it starts with an A. It has alphabets in it. So it is considered as a textand these are all numbers. So just to see whether a count Functionworks with the numbers and characters we will do a count a function again andopen the parentheses this time. We will use the employee codeas our count range, which is a toa close the bracket, press enter again, it will give me a hundred andtwo y again the same reason because it is takingtheir wedding also in the count. So again, we'll justhave to do a minus 1 to get the correct answer. So just remember this why weare going to do the minus 1 is when the count function alsoCounty function also counts the heading and we don't wantthat heading to be counted. We will use minus 1to get your answer moving to the next one, which is the countif functionCounty function counts. The number of cellsin the cell range that meet a particular conditionunlike count function count if can be used to count cellswith dates numbers and text that matches Pacific criteria. So like we learned some function and the summit functionsame is the count and the counter function so the countif functionGowns all the cells which contains numbersand the characters or the text the countiffunction counts numbers characters text everything only if a particular condition is metso going back to our example in our example of employee data. We have to identify number of employees workingin the admin department. So here is the adminDepartment XL needs two inputs for the counter functionas per the syntax. So let's look at that. So how do I startas countif function? These are the two syntaxes that it gives the first syntaxis the range again, like the sum function. The first range isthe criteria range in this case. There is no such thing as countrange like in some IF function. We had some rangeencounter function. We don't have a count range. Why because it can countnumbers text and characters. So the first oneis our count range. What is our conditionhere our condition, is that the Should Be Edwin so my first criteria rangewill be department and what will be my criteria the criteria Will Be Edwin. So I will put Edwin againas I told you earlier, these are not case-sensitive so you can put adminin small letters in double quotesclose the bracket enter the total employees workingin the admin Department are 11. Okay, just to cross verify we goand do a filter identify. How many employees are therein the admin Department? I filter by Edmund I select the whole data now onlythe admin Department if I select discussion me asLevin if I select the whole cell it will give me that headingalso in agreement. Well, so rather than that, I will only select this cellswhich has the data. So it is 11 in number moving onto the next function, which is a countifs function. Like we learnedthe sumifs function. We also have countifs function. How does the countif functionwork as sumifs is used to some arrange that fulfillsmultiple condition countifs. Used to count numberof cell ranges that meets multiple conditions. For example, the questionhere is to identify number of employees working in marketing department earningsalary more than 15,000 rupees. Now, there are two condition that needs to be fulfilledin order to get the count. What are those two conditionsinsert a new table? Because we havea condition here. Okay. So our conditions are whatwhat is our criteria range and the criteria? Let's go backfirst and understand. What is it syntax, so I open it I getthe first index is a criteria range oneand the criteria one. So let's put it that waycriteria 1 and criteria to we have onlytwo criterias this time. We don't have more than that. So what is our first criteria? Our first criteria is that we wantthe employees working in the marketing department. So Department as marketing now, we are puttingyour as marketing. However, when you are enteringthe data in the formula, you will have to be careful. You cannot put marketingas a full spelling because the spelling inour cell or in our database is M. KT G department has marketing and what is our second Conditionsalary more than 15,000 rupees. So I will put it herea salary more than 15,000. So these are two conditionsgoing back to our formula is equal to countifs. Open the bracket. What is the criteria range one? First one isthe department marketing. So I will select Departmentwhich is d 2 D cell, What is my criteria one? It is marketing. Like I said, I cannot enter marketingas mark820 ing because if I entered that Excel will not be ableto find this condition in the department know where is the swelling as mark2 ing So to avoid these kindof issues you will have to check what is given on the database. So in our database it is M KT G. I put that MTG in double quotes. To the next one which is criteria range two in our casecriteria range two is salary, which is the H to H column. So I select H to H commathen I give criteria to which is 15,000 rupees. So I'll have to put that in the double quotesas more than 15,000 close the double quotes closethe parenthesis Enter key, it will give me the total number of employees workingin the marketing department with celery morethan 15,000 rupees is the result is 9 moving onto the next function, which is the averagefunction average function Returns the average of arguments which can be numbersor names arrays or references that contains numbers which means average like countonly gives the average of cells that contains numbers againwith the same example used for the some wewill identify the average of the total salarypaid to the employees in the organization. So the sum was too Actively 3825 after addingthat new salary information. We will also now look at whatis the average salary paid to the employees. So to start with we will start with an equal to sign enterthe average function name open parenthesis enterthe salary data, which is in our case hasH column close the parentheses and press enter. So our total salary or the average salarypaid to the employees in the organization as twenty thousand threehundred thirty four rupees or 35 as it is .90 now, in this case, it will only count the numbers but also there is another function knownas the count a function like account Axl alsohas averaged a function which will give an averageof cells containing numbers as well as textor characters or both. How is the average calculated if no numbers are availableto calculate the average Excel adds the numeric valueof each value together and divides the total numberof values specified and Reach a evaluatestrue as one and false a 0 so when I say true as oneand false is 0 that means if there is any dataavailable in a Cell, it will calculatethat as one however if there is no data availablein a Cell it will calculate that as 0 so it will not add upand it will not divide it. So we havea very good example, which is the employee code data. If I'm just tryingto get an average of this salary information. I will just getthe same answer even if I do it every J. However, if I do every daywith the employee data where I have the text as well as the numbers itwill calculate the numbers which I have with justadd all the numbers plus where I have the text. It will take it as true. Each text will be taken as 1 and if there is any blank spaceit will take that as zero, for example from yourit will take it as 0 so if I do average a sayI'm doing every day in the next column justbelow the average open the parentheses. I select a to a asthe Close the bracket and press enter it will giveme 6345 .87 asthma average now for say example. If I delete one of the data what happens is it changesfrom 6,300 6,400. If you see the differencesix thousand three hundred and six thousand four hundred. Why does this happen is because as soon as Idelete the data from your this informationis being considered as false. And so this becomes 0. So once I become 0 itautomatically picks up as a zero information, it doesn't calculate that and itdoes not divide that number by the number of values. Okay. So what is the totalthat numbers that I have is 1 0 1 so it will divide that by 1 0 1 so that's how the averagea function works. Next is our average IF functionjust like average some if and counter functions averageof returns the average of selected argumentsspecified by a given criteria. So In Sum, if it will some the dataif the conditions are fulfilled encounters, it will count the dataif the conditions are fulfilled. Same an average of itwill average the total data if the condition is fulfilled. So let's go to our examplehere in our example. It says find the average salarypaid to the employees in the South Region. So there is only one condition that I have this time to startwith an average IF function or to understand its index. I'll put average if open the bracket itwill give me three arguments one is the rangesecond one is the criteria and third one isthe average range. If you remember someit functioned is exactly the same as the submit functionexcept in the last argument. It says average range while in somewherefit seed average some so that's the only differencethat you see in the summer range and the average rain. So to start with the first rangeis our criteria range. What is a criteria youremployees in the south region. Where does the region sitin the e2e column select e2e as our criteria range. What is the criteriathat we are looking at South so we will Puts outin the double quotes again, And then the average range. In this case. We are going to average the salary databecause we are looking at the average salary pay to theemployees in the South Region. So I'll select H to Has my average range because I want to average thesalary close the bracket enter. It will give me the averageof the salary of the employees in the South Region. Okay moving on to the next function in our listis the round function. We will illustrate three functions tothe round numbers in Excel. The functions are the roundround up and round down first. We will start withthe round function around function will roundthe number two the number of decimal points specifiedin the formula in our example. We have number s 14.7 261 anduse the round function. I select the number I needto round so I go to equal. Around I select the numberthat I want to round, press the commaif you see the syntax, it says the number and then it's ask methe number of digits. What is this number of digits? It is asking me how many decimal pointdo you want or how many decimal places do youwant after the decimal point? So say for exampleyou are in this case. There are four digitsafter the decimal point. I only want to so I enter toand close the parenthesis enter. It will round up or it will roundthe number which is after the second decimal pointjust like in school we learned when there were morethan two decimal points if it is more than 5we will add one number to the previous digitsame thing the Excel does for here in our case if you see properly if you seeor one one four point, it says 726160726the 6 is more than 5 that one will be added to 2 which becomes one morefour point seven three and the 61 is removedfrom the list. So Answer becomes 114 .73. So this is how we around function worksjust for more examples if I had put round function and this time I selectthis number comma I just do the number of digits say I wantto do number of digits as one and close the bracket enterit will give me seven only why because the second number which is 2 is not either 5or more than five. That's the reasonthe seven is left as 7 K. So this is showing is 114 .7this becomes around function. The next one isthe Roundup function around a function alwaysrounds the number up away from zero from our example. We want round a numberup to one decimal point. We will input the formula in cell C2 is equal to roundup again select the number as a 1 comma I'll just putone close the parentheses and limit now if you see properlyin our first instance when I put only round function is showing me asThird one 14.7 why because the next digitwhich we are talking about was the number two. Now this number two isnot more than five. That's the reason itdidn't add up in seven. However Roundup function what it does is itwill go away from the zero when I say away from the zero, it will not leave it as seven. It will automaticallyadd 1 to this number because there is some digitwhich is after the number so it will automatically add oneinto the previous number and give you one 14.8 next. We will learn as arounddown function around and function always roundsthe number down towards zero. So this is the other way roundfor example round a number down to the nearest integer. Let's see in our exampleagain with the same. Number one 14.7 261 let's rounddown to a 2-0 decimal points. So we say round down openparenthesis select the number that we want to roundout comma this time. We don't want any digits or Numbers afterthe decimal point so I put 0 I press enter I getthe answer as 1 1 4 now if you remember again goingback to our school days in normal situation, we would have done itas 114 .7 to as 115. Okay. However in this casein round down because it is goingtowards the 0 that means it will not add upthis number to the previous one. It will remove allthe decimal points. Just like if you seeanother example also, I will try the round downwith the two decimal points rather former to closethe bracket enter see even if in this casewhat happened was when I did two decimal points because it was morethan five added one. Number two, two and gave meone one four point seven three, but if you seein this case Brown down even with the sametwo decimal points, it is not adding up the six even if it is more than 5it is not adding up one into the previous rigid and it is giving methe same answer as 114172. Yeah. So this is how weare Roundup works. No moving on to the next one which is the concatenatefunction concatenate function joins two texts together. Now when I say jointo text together, that means it will join the textwhich are into different cells. It will join them and getit into one cell. For example, if we have name and the surnameinto different columns say in our example, like we have here name andsurname into different columns. We've can get them in one cellusing the concatenate function. That's the example on my sheet. I have to start with I want to combineboth this name and surname in this cell, which is the cell G by usingthe concatenate function in such a way that the first name in the last name hasa space in the middle. So let's see firsthow do we start with the normal concatenatefunction and get the both name and surname in the same cellis equal to I enter concatenate Open Bracket. The syntax of the concatenatefunction is text 1 comma 2 x 2 comma text. You keep on doingthat the first cell that I will select for the textone is e 2 why do I do E2 is because the first nameis in the E to sell. So I select E2 then I do comma then I have to selectthe second text which is sitting in F2 once I select that closethe bracket enter both this name and the surnamein the same cell now. However, as I said earlier, I need the data in such a way that there has to be a spacebetween name and the surname how do I get that now? So as you are aware, if you go back this timeand delete this part again, I'll start with the functionagain to start with the concatenate Open Bracket. I select the first textwhich is E1 aftercoma. I will not selectdirectly the text to which is the f 2 I will haveto put a space in the middle which will becomemy text to so earlier. The text was your surname cause we want a spacein the middle phase will be considered as textto so I will have to put in the Code why because space isalso a character which Excel doesnot understand Excel only understands it in build functions numbers trueor false or its cell references in our case. They are none of them. So what we'll haveto do is we'll have to put it in the codes. So I go back to my sheet. I enter the space in the courts. Now, this becomes my text to what is my decksthree the last name, so I select last name. I close the bracket, press enter if you seenow there is a space between the text one andthe text to which is the name and the surname nowto copy this data to the rest of the cells. I can use my fill handle. What is the full handleif I keep my cursor on this cell? You'll see a small square hereon the right hand side once I keep my cursoron that small square. There is a cross sign that appears as soon asthat Cora sign appears. I Just need to doubleclick on that data for it to copy itto the rest of the cells. Now, when I double-clickon that data, it will only work on the cells if there is any data on the leftor right of that cell. So if I had enteredthe same formula in some other sensesay for example, you're enter now. If I try to doubleclick on this, it doesn't work. What do I do in that case? I'll have to dragthat formula till the end where I need to stop becauseExcel will only understand if there is any dataon the left-hand side that it has to stop your or ithas to copy the same formula to the rest of the cells Till There is a data in the previous cell or onthe left hand side of the cell. So this is how you can do a concatenate functionto get your information. Now if you would have realizedthe concatenate function is a very big I'll tellyou a small trick how you can use to avoidany spelling mistake when entering a function because if you doany spelling mistake like this is what I did. I start with my formulacomma first of all, it will not give you a syntaxand the second thing once you press on enterit will give you an error. So how to avoid that. There is a verysimple way to do that. I first start with an equalto sign I start entering concatenate which is conc a as soon as I entered conca or even the smallest of them. Even if I try with seeall the functions that starts with cwill appear here, but there are too big so I will at least putconc it will come down to only two formulas. I will select the onethat I want to use this time. Okay double click on that it will automaticallyopen the parentheses for you. Now, you can selectE1 or E2 whichever. The first text isthen your space and the last name sell. This is how you can useany function for that matter. You can also dosome meth with this if I put some it will giveme all the functions that is starting with some so I can use summer function itwill automatically open. Could for me thisis only easier way or a trick to actually ensure that you don't miss outon opening the bracket or you don't makeany spelling mistake when entering a function alsothe another way to do this is if I enter concat second CAit will give me that option. I select the data that I want rather thandouble clicking on it. I will press on Tab it willautomatically open the bracket for me the similar way that wedid in the previous example. So this is how we are concatenate worksthe next function on our list which is the index function theMicrosoft Excel index function returns a value in a table basedon the intersection of rows and the columns so supposingyou have a list of orders with their unit pricelike in our example earlier. This is an order ID unitprice and quantity. We want to usethe index formula. So here we have the unit price and the quantity we want to usethe index formula to look up for the TT of oranges soldhere is example of how to do that in cell where youwant the result. Let's start by typingthe index function. So is equal to indexopen the bracket. The first requirementfor the index function is the array as perthe syntax array is the table where you want to pickup the data from so this is our table. What do I want is the quantitysold for the product oranges. I want to answer as 10. So let's startwith how we can do that the index function 812 D7 that is my table commathis time I need the row number in the oranges, which row number is orangessitting under everybody knows what is row and the columnso this is my rose. So if I'm going back justto give you an example. These are called Rosethese part of the cell where you see one, two, three this and the rowsare always numbered or referred to by1230 the numbers, okay while your vertical Itis called as your column while the horizontalis called a zero. So vertical part, which is your column is referred to by the alphabetsABCDE throw to start with an index functionagain going to F2. I am entering in deck is equalto index open the bracket. I select the arraywhich is my table, which is a one to D7. Now. I have to provide the row from where we need to derivethe result in our case. It is 3 y because we are looking for oranges orangesare sitting in the third row how this is one stroke. This is second row. This is the third row. This is according to your table. So I put 3 as my row number now, I have to type the column number because now I've got this rowI have to derive data from buttocks and wants to knowwhich column do I give data from so I'll have to tell Excel that. I want quantity and quantity sitting in whichcolumn 123 and fourth columns, so I enter for Once I closethe bracket and press enter I will get the result as 10 which is the quantityof oranges sold. This is how we areindex function Works moving on to something similar function, which is called asyour offset function like in the index function. It will tell you which row and column you have to work onon the offset function. It is similarto which row and column. However, we willsee the difference between the index and the roofand the offset function what does area offset functiondo offset function returns a reference to a range that is a given number of rows and columnfrom a given reference. For example, I am givinga reference truer self. This is my reference. So I am telling from a to giveme second row second column in the index function if I go second rowand second column, what will it do? It will look at the second rowdirectly from your because I amselecting this table, which is So this is second row. This is third row which column and I'm lookingfor fourth column. So it will giveme the ten answer. However, if I did itwith the offset function, for example, I say offsetfunction in this film sheet. I cannot select the arraywhich is not the table here. I have to give a referencecell number so say I said cell references a one now my row number that Ihave to select after that which is 1/2. In this case. I have because oranges is sitting in the second rowafter this reference, which is cell A1. So I'll have to say 2 commawhich column I am I looking for I'm looking for the column which is after this referenceso column 123. I sprut 3 closethe bracket enter. So if you see the differencebetween the index function and the set function is that in an index function yougive the table it also select from the first rowand the First Column and it gives you whichever number you have givenaccordingly select that row and that column however in the offset function yougive a reference to one cell that cell is Notcounted in the row and the column is for a 1 if I select and say second rowit is giving me one too. It is not counting thisas one row discounting the next one as one row and then the next rowis the second row here. Also the column the same way as this column isnot selected at all because this isyour reference cell. It will select this asone row the second row and this is the third row. This is the differencebetween the index and the offset function. Now if you would have seenin the offset function, there is alsoanother two more sin taxes that is given there or the inputs that requiresis the height and the width. Where is this useful the height and the width meansthe cell references with the vertical data. This is called as the heightand this is called as your with. Where is this useful? And how is it useful? This will be useful when you are usingan offset function with the any other function like you're using offsetfunction with some so I'll start with is equal to some openthe bracket this time. I want to some some But instead of selectingthe cell references I will ask the offset function to selectthe cell references for me how I willselect offset function. I open the bracket this time. I'm giving the referenceas the month comma and then I'm lookingfor February to make so I'm looking for somefrom February to May for East and West together. So I'll startwith reference as a 2 which is the month this time. I want to give from February. So which row I'm goingto select this one to second row is February. So I'll put twoso it will start from February. However, I want to givethe column number from the next one. I don't want to start because it willnot some this part. It has to somefrom your correct one because after this cellit will look at one you're so I will give 2 comma 1 because2 is in February 4 column. It will lookfor the First Column. So now I want to tell Excelthat please add. Ray March April and May sohow many rows on how much heightshould it have 1 2 3 for the height becomes 4 rows because height willbe this part. So I say one two,three and four, so the hide I'll give us for because I am tryingto add up for months February March April May and that four months arein four different cells. So one two, three four, I will put it as 4 comma Nowwith this my columns. So how many columns they are trying to say Eastand West together. So it is February MarchApril May which is the height which you because they are rose and nowI'm looking at two columns which is true with so I'll sayto as my with close the bracket and close the bracket for some press enter. It will give me an answeras 6218 just to cross verify I will selectFebruary March April, May data see the answerthe summons or is 6218 this way not only some you can do averageyou can do product you can do. Count you can doany number of things with the offset function alongwith the other formulas in it. So this was how youare offset function works. Next is the match functionthe match function searches for a specified itemin a range of cells and then Returns the relativeposition of that item in the range stickingwith the same example as we did for index. Let's use the match to figureout what row oranges is in. So what we did earlier was wewere trying to find the quantity of the oranges youspend this time. I am trying to find which rowdoes the orange products it. So what does match dois match will identify if you give a namein the match function, like for example oranges it will identify which rowit is sitting under how does that work isI will type is equal to and the matchfunction name in the cell where I want the answer thefirst X or the first argument for the match functionis lookup value. What is my lookup value? This is oranges. So I will type orangesin the double quotes, which is my lookup value because I'm lookingfor that comma this time I am looking to find outwhich Euro it is sitting under select all the cellsin the product column including the header, which is this partwhy I'm doing this because I am trying to findwhich 08 dist setting under so it is sitting on a 1-2-3 row, but instead of writingthree match will automatically identify for me which rowit is sitting under correct. So what do I do isI enter be to be this way. It will automaticallylook for it, As soon as I put commathere is an option for less than exact match and greaterthan in match function. We have to look for the exact matchof the lookup value. So if it is oranges, it should only look for orangesand not anything else. So we will selectthe exact match close the bracket enter itwill be mere Answer now. If you go backto your index function, if you see the index function, the first thingthat we gave was 822 D7 because we are telling them that this table lookfor the data in this table. Next one was row number. Why did we look at row number? Because we were lookingat row number where the Orange is sitwhich is 3 so arranges that we were sitting we foundit out by calculating it by one two, three. However, if we use the match function and selectthis be to be number 3 will be automatically identifiedby the match function. So instead of the threenumber year I can use the match functioninside the index function. This will make the indexfunction Dynamic. So even if I moved orangesto somewhere else like if I move it to some other cell, let's say I'm movingthis whole data a little up. So the oranges is nowgoing to the last one which is column 7, but in our case, it will not give you the correctanswer it is still giving you. 10 which is not correct. It should be ideally 35 it because we have givenas particular number. It is going to 3 it is going to for and givingyou that bananas quantity. So to ensure that that doesn't happenwe can use a match function inside the index functionand get the dynamic answer by replacing threewith the match function. What will happen is this time? You will see that it automatically pickedup the oranges data, which is 35 same you can usefor the answer for this time. We are trying to identify. Where is this quantity sit? So what I can do I can doa match again open the bracket. What is it that I'm lookingfor this time? I'm looking forthe name quantity where it is sitting now quantity I know is sittingin the first row. So what I'll do is instead of looking for quantityin the whole database. Where is a lookup array. It is in the first row. So I'll select the first rowcomma exact match. So I will put 0 closethe bracket enter. It gives me thatfor Unser again going back to my index functioninstead of using Fourier. Replace for withmy match function that I have done herecopy the match function without the equal to go backto my index function and then replace for with the match functionplay center this time if I moveoranges somewhere else, it will automatically pick the answer thatwe are looking for. Let me move it to the first onethis time so it becomes dynamic dynamic means even if there is any changein the data, it will automatically giveyou that particular answer so you should see that the orangesare showing as 12 say if I move also my quantity in the middle somewherein the middle like before see it will still giveyou that same answer why because now match is lookingfor the quantity and the quantity is 3and it is not for any more. So that's how you can use index and match togetherin order to get your answer or make it more Dynamic. Okay moving onto the next function, which is our main functionthe Min function. Is a function that Returnsthe smallest numeric value in the range of cells or inthe range of values, like in our example, again, we have the salary informationof all the employees of almost a hundred and one employees. Now, I want to knowthe minimum salary that I am payingto the employee. So to identify that I would just put is equalto men and open the parentheses. I will have to selectthe salary column because I am identifyingthe minimum numeric value of this salary column. So I select that closethe bracket enter it will give me the salarypaid to the employees. Okay. So five thousand nine hundredand fifty Rupees is the minimum salary that I am payingto the employee. So the same goeswith a maximum salary. So earlier we learned how do I get the minimumsalary pay to the employees same I want to know. What is the maximum salarypaid to the employees? So is equal to Mac open the bracket selectthe h2h column y h to H again because it is the salary. That I am trying to understand. What is the maximumsalary paid edge-to-edge close the bracket enter. It gives me the maximum salarypaid to the employees. So this is how we are Minand a Max works. This is a very easy, but it's very useful formulain Excel moving on to the next functionfrom our data. We've already seen the Minand the max function this time. We'll see the trim functiontrim function removes The Unwanted spacesfrom a particular cell. It will only remove spacesin the front and end of the text if any so if you wantto remove this node, its Paces from a text. We will see in our example sayI have some spaces in the front and at the end of some text sayfor example in a my name column. I'm just entering some spacesjust to make it more easier to understand so I'm puttingspaces in the front. I'm putting spaces in the endfor each one of them. There are two spacesin Vienna to space in the front and Sima one space in Sothese are all the spaces that I've enteredsomewhere in pankaj. I'm putting a spacein the middle. So two spaces in the middle. Okay, same goes with adri nowif I want to remove these faces because what happens is when I'm doing a formulaalong with these faces, it will not work. So I have to remove the spaces. So how to start with I'll haveto enter the trim function is equal to trim openthe bracket the first cell because from each one of them. I want to remove the spaces. So I select the first cell which is be to closethe bracket enter. I select this cell enter againwith the same fill handle. I double click on it. It will automatically takethe whole data till the end. If you see properly allthe cells have been removed with these pieces. However, for pankaj onlyone space has been removed. If you see properly theretwo spaces here here there is only one space is so in Bunker's Excel will onlyremove unwanted spaces when I say unwantedspaces in the front. There is no spacerequired at the end. There is no spacerequired in the middle. Also if there is one spaceit is considered as Okay, but if there aremore than one species in the middle Excelwill automatically remove those faces. How do I ensure now that the data without despaces is copied and pasted in these cells. What do I do for thatis I select this data. I copy I go back to my cellwhere I have the original data. I click on this. I right click on the first cell and there is somethingcalled A Space special now, why am I doinga paste special is because if you see there isa formula in this cell if I copy this data and directly pasteit into this cell. It will try to copy that formulaand will give you an error. Okay, so we wantto avoid that error. So what we'll do iswe'll copy this cell. I'll go back to my cell where I want this datato be pasted right click on that cell click on paste special then clickon values and press OK once I do that you will see that there is a formulain these cells. However, there isno formula in these sense. Okay, again, just to ensure that we are at the right pacewill see that earlier. We had put two spaces pankaj only has one space justto remove the space we can do. Now so that'show your dream function works. So moving on now to the nextfunction on our list, which is the Len function. What does an end function doLen function Returns the number of characters in a string. So when I say a numberof characters in a string that means that it is lookingat the number of cells that contain a text. What is the number or whatis the length of the text? For example, if I want to knowin our cell Raymond occur is how many characters so what I'll do is is equalto Len function. I'll enter that andI'll select the cell for which I want to know how many characters are therein that cell to see to it is 11 characters. So it says, how do I calculate that startwith 1 2 3 4 5 6 7 8 9 10 11 now it is giving mea Livin character text. But if I want to puta space the end what happens it becomes12 characters why because the space is alsoconsidered as a So in this case, it becomes a12-character text again, I will use the format painterlike we are saw earlier to get that back to the normal dataon the Normandy format. Okay. This is how your Len functionWorks moving on to the next one. Let's start with the nextfunction which is s QR T. SQ r t function Returnsthe square root of a number. For example, Ihave just one sheet where I have put some numbershere to 20 to 140 46745. I need to find the square rootof these numbers. So I put is equalto S QR T open the bracket. I select the number which I need the square rootfor close the bracket enter. It is fourteen pointeight nine nine six six again, like earlier, you can reducethe decimal points by just clicking onthese decrease decimal option. You can increasethe decimal point. So I just keep on clickingon these options. Okay, so we leave it as it is now againfor the next one or you can just Drag the formulaby using the fill handle and it will give me the squareroot for each of them your I do not want any decimal pointso I can just reduce that. This is how youcan use the squared. This is a financial function, which is more usedin case of finding data for the financial industry. Again, this is a demo whichyou can look at to identify. If you want to just comeback and see this examples next is a very important functioncalled as the IF function. The IF functionis very important because it gives you an optionto get the required data. If a particular condition is metfor example in I have a list of employees going back to my sheet in theIF function treat. I have the listof employees data with their DepartmentInformation Management has decided to give20,000 rupees to the employees in the sales department and others will get0 as a commission. So to identify how to get a commissionfor each of the employees will use the if formula soagainst each employees who are in the sales departmentthe results should show The commission column as 20,000. So for example in the first oneit is Edmonds here. It will show is0 second one into sales. So here it shouldshow as 20,000. Next one is admin again. So again zero marketing again 0we are only looking at 20,000 for the sales departmentsof against each employee. We should be ableto see the exact amount that needs to be paidas a commission. So we will have to usethe IF function to start with the IF function again is equal to if assoon as I open the bracket, the syntax says the first data that is requiredor the first input that is required for the functionis The Logical test logical test meansyour condition in our case. The condition that we have is that we are looking to give20,000 to the employees in the sales department. So what are we lookingat the department? So the first employeeDepartment data is in D2 so I select D2 isequal to what is that we want if they arein the sales department. So I am saying is equal. Sales in the double quotes Sothis becomes my logical test. As soon as I enteredthe logical test now, I have to put a comma next input or the next argumentis value if true. So what we are telling Excel isif T2 is equal to sales. That means d 2 is the departmentfor the first employee if is sales, give me what if it is true20,000 if it is false, that means if the department for the first employeeis not sales give me 0 so I close the bracketafter doing this press enter. It is giving me zerofor the first employee why because it is admin DepartmentRoger Raymond occur is an admin department. Now again, just like earlier we mentioned wecan drag the formula either or using the fill handle. I can double clickon that to get the answer for each one of them. Now the formulais dragged you can see that the formula is rackfrom D1 in I to you. CD 2 is equal to sales while I go down the formulachanges the cell references from D2 to D3. Why because it is now movingdown going to the next Row the three then D4 and D5and D6 accordingly. So it is automatically calculating from the respectivecells for the second employee, which is so mentioned a is Shriin the sales department. It is checking and the suman'sdepartment is in D3. So Excel has automaticallytaken D3 as the cell reference. Next we are checking for calledDeep Sharma cool dip sharma's Department detail isn't Dfor the cell reference is automatically depict as D 4 is equal to sales because it is notsince it is admit. It is automaticallygiving it as a zero. So I hope the IF function isvery very clear. It's a very easy formulabut very important. So again lookingat it the logical test which we have given asd 2 is equal to sales the value of true is because what is it that we need if this logical testor this condition is fulfilled. And the value if false is if the conditionis not fulfilled. What is the value that we want? This is how we areF function works if I'm startingwith the ifs function. I will write downeach logical test here first. The first logical test issalary is less than 5,000. Second logical test issalary is more than 5,000 but less than 10,000. This is my second logical testthird logical test is salary more than 10,000 but lessthan 15,000 fourth logical test is salary more than 15,000 but is less than 20,000 and my fifth and lastlogical test is salary is more than 20,000. These are my conditions. However, when I startwith this condition, I have a total employee listof hundred and one employees out of these hundred and oneemployees if I say, for example, I am just looking at anybody salaryless than 5,000. So in the first condition assoon as I put I get an answer as there are ten employees who are earning salaryless than 10,000. So what happens is outof this hundred and one number that I see I will have to deduct 10 employees or earningless than 5,000 rupees. So the answer for them will beless than 5 K now going to the next one. If I start with the second one, I realize that assoon as any employee that is a 10 employeeis owning less than 5,000. They sit in the first bucket Which is less than 5K bucket 10 employees are automatically removed and we are leftwith 91 employees. These 91 employeesare definitely going to be earning salarymore than 5,000 rupees. Why because theremaining 10 employees that we identifiedare earning less than 5,000. So whatever is left is all earning more than That so weare not required to re-enter an IF function that if they are earningmore than 5,000. We will directly startwith less than 10,000 and this time if it is less than 10,000will put less than 10 K. So if for example,there are 10 more employees who are earning less than 10,000so I would put 10 here and - from this 10 so I minus 10 again. I get how many 81 so81 employees are left. Now out of thecity one employees. Everybody is owningmore than 10,000 or more than 5,000. Why because wehave already identified from hundred and one employees. The 10 employeesare earning less than 5,000. Another 10 employees are earningless than 5,000 that ways. We are left with 81 employees who are earning more than 5,000and more than 10,000. So now going to the next onewe are not going to enter more than 10,000and less than 15,000 because everybody else is warning morethan 2,000 this time. We will select. Listen, 10,000 say I've identified 15 peopleowning less than 15,000. So I enter less than 15,000. This will be written asless than 15 K next again. When I go to the next bucket. So these threebuckets are decided. What are the three buckets are one is less than fivethousand less than 10,000 and less than 15,000. So now out of thatI have identified how many people are leftwith the deducting the 15 number that is 66 employeesare still left out of a hundred and one employees who are now owningmore than 15,000. So we know that these 66 employeesare not earning less than 15,000 are all learningmore than 15,000 because we have already put themin these three buckets. So left is all more than15,000 now more than 15,000. Also, there isone more requirement where we are identifying if there is any employeesearning less than 20,000. So say we have identified25 employees owning less than 20,000. So we'll put it here and our Data will beless than 20K last one. We have identifiedthat out of these 25 employees that we removed. We identified that only41 employees are left who are running for more than 20,000 why weare saying more than 20,000 because we have puteveryone in a bucket where either they are earningless than 5,000 less than 10,000 less than 50,000or less than 20,000. So whatever is left is allordering more than 20,000. So they are 41 of themmore than 20,000 and we will putas more than 20 K. So this is how we are goingto split our data and see how are we goingto get an answer. So looking at this examplewhen I'm doing my f function, I will have to go and enter the datainto the first cell which is againstRoger Raymond occur. Am I didn't I am tryingto identify is Raja learning salary less than 5,000 lessthan 10,000 less than 15,000 or less than Was it or it is more than 20,000. So where is the salary data? It is in the H2 for Raja. So the first thingthat I will see is IF function h 2 isless than 5,000 then give me what less than 5 K. So for the first requirementwas less than 5,000 everything that is less than 5,000has automatically now sitting in this bucket, which is the first bucket nowall the leftover employees have automatically cometo the next data, which is more than 5,000but less than 10,000. So now I will goto the next one again. I'll have to select H2 because I'm now seeingif h 2 is not less than 5,000. Then we have to checkif h 2 is less than 10,000. So I put h 2 less than 10,000 if h 2 is less than 10,000 thenwhat do we want again? If you see The Logical valueof true to it we have to put less than 10 Know the Requirement which is a logical test is if h 2 is less than 15,000. This is my third bucketaccording to the third bucket. If it is true, I'll have to put less than 15 Kin the rebel quotes. Now the fourth logical testthe fourth logical test is if h 2 is less than 20,000which is my fourth bucket. If yes, give meless than 20 K. Now. If you see in this one, there is no such thingas value if false in the ifs function we had valueif true and value of false. So what do we doin such case now, we have identified that this number isnot less than 5,000. It is not less than 10,000. It is not less than 15,000. It is not less than 20,000. What is left is it ismore than 20,000. So what we can do isin that case we can put another one which is h2 is greater than just like we normally doin the last case h 2 is greater than 20,000then give me more than Okay close the bracket. Now when we are closingthe bracket will have to close only once and you get that answer drag itto the rest of the cells and you will get itaccording to your data that you had now. This is 7,000 Whichis less than 10,000. But more than 5,000. This is 12,000 250which is for Vienna. It shows less than 15,000. Why because it isless than 15,000. What more than 10,000so it is between 10,000 and 15,000 likewise. You will see datafor each one of them. Now.This is less than 20,000 because it is 15,000 750which is more than 15,000 but less than 20,000. This is how you useyour IF function to get a desired result. There is also something calledas nested IF function which ifs is replacing that nestedif ifs is available in 2016. But if you have any versionother than 2016 you will have to use the nestedfunction now going back to the PPT and seethe next function which is the end functionand function checks if all logical arguments inside. Functional true andReturns the True Value if they are trueand false value, if one of them is falsefor example and and function like we are going to any sheet and trying to identifyif logical test that we do like enough function. We are giving differentlogical test, right? So if you are givingmore than one logical test if they are true, then it will automaticallygive you an unstressed true or it will give you an answer asfor in our example. I am tryingto identify employees in the sales department workingfor the west region. So to start with I want to identify all the employeeswherever they are working within the sales departmentand for the west region, it should show me an answeras true wherever they are not in the sales departmentand not in the west region. It should show me as fault. So both the conditionshould be made what are the two conditionsone is the department as sales and the second condition isthey are region as West so only if both the Conditionsare met then only the answer should be truefor the result should be true. Otherwise, it shouldshow me as fault. So, how do I do that? I'll put an end function in the cell where Iwant to answer. So I want an answerfor each one of them because I want true everywhere where there is salesand best wherever there is sales and East or north or south. It should be me or falseor if there is admin and any other regionshould give me a false answer. So both the conditions haveto be met department sales and reach invest. So what I am saying is the firstcondition is department sales. So for Roger romanticand I'm trying to find out if department is sale. So I will do d 2is equal to sales. That's one requirement. That's my logical one. Second logical is what I go to E 2 because that'swhere my region sits. E2 is equal to Westthat's my second condition. I close the bracket enter. I get a false answerfor the first one why because Raja isfrom the sales department. No doubt, however the regionIs not and our condition is that if the regionWest and the department is sales only then giveme a true answer now when I drag this formula dunk, it will check if they're everthe region is West and the department is sales. It will give me a true answerif you see for huijin Imgur, you'll see it's she's working in the sales department and forthe region West and hence. We are getting an answeras true same goes with everyone else. So we hardly have few such employees where boththe conditions are fulfilled. Now, we have another functionnamed as the or function. What does the or functiondo just similar to the and function it will lookat one of the conditions being fulfilled likein the end function both the conditionshave to be fulfilled but with the or function only one of the conditionshave to be fulfilled. For example, I want an answeras true if the employees are in the sales department or inthe marketing department. So I'm saying thatIf the department is sales, I should get an answer as true. If the department is marketingI should get an answer is true. Otherwise if itis other than sales or marketing I should getall as false as so, how do I do that so I can only thinkof one function which is the or functionwhich will give me this result. So to start with the function, I will put an equal to signin the first employee data and I'll putor open the bracket. My first logical test isthat partment should be sales. So D2 is equal to sales. This is or so you remember that either it is salesor it is marketing. So first logicaltest is Department, which is d 2 is equal to sales. The second logical test is again d 2 is equal to marketingnow marketing again, if you remember in the earlier functionsalso we were careful about what is the spellingof the marketing so marketing is notlike mark2 ing. There is a short formthat has been used which is M KT Gyou may have times where the full form is. When used in that case you are. Not going to useany abbreviation to put it in the formulas. You have to see exactlywhat is given here that needs to be enteredin your formula as well. Now, I close the bracket. Now wherever there is sales or marketing it willautomatically give me n true answer. So this is how it gives so truebecause this is sales. This is sales again. This is true because it is marketing be nice inthe marketing department again, I can see true forthe sales department same goes with marketing againfor and refund on this because they're trees workingfor the marketing department. That's the and likewise youwill see for the rest of them. This is how we areor function works. Next one is your today functiontoday function is a very easy and very useful function. This function is usedto identify the current date. So to identify the current dateI will enter is equal to today open the bracketclose the bracket and press enter itwill automatically change the format of that cellinto a date format and give me the today's datetoday's date is 2nd of July. I was in an 18 and that's the reason itis giving me this date format. Now.This is today's date because I have entered itin my example today. But if you are trying iton your Excel sheet, it will give you the date. What is there on your system? It is always the system dateby any chance if your system dateis not updated the Excel will also check this state which is on your systemand give you that date here. So you will have to ensure that your system datais also updated. What is a quick access toolbarquick access toolbar provides access to frequently usedcommands in the application. When I say application. It is our MS Excel this sitsat the top left hand corner of the Excel worksheet. Let's explore this on our Excel workbook the sitson this part of your Excel, which is on the topleft hand corner. You will see a small dropdown button here with an arrow which you can click and this is where you will get other options that you can addto the quick access toolbar. So this iswhere you have your ex. Toolbar by default quick accesstoolbar will have undo/redo and save button. So this is your save button, which is already thereon your quick access toolbar. This is your undo button. And this is your redo button. You will havethese three buttons. Why default on thequick access toolbar? Other than that you can addthe rest of the buttons on the quick access toolbarby clicking on this button, which you see the arrow keyand adding whichever you think you're going to usefrequently in Excel. I have added new workbook accessin the quick access toolbar. I have also added an open optionin the quick access toolbar because I keep using these twothings very frequently. So next time if I want to open a new blankExcel treat I will not go to file click on open andwhen a blank worksheet instead, I would just clickon this quick access toolbar and where I can see this icon for a new workbookclick on that. It will automatically opena new workbook for me. Same goes with the another. If you see there isanother open option, which is therein the quick access toolbar because I have already added that to my access toolbarwhen I click on that. It will give me an optionto browse and get the file that I want to openfor working on it. So this is how you can keepadding the additional things that you need fromthe quick access toolbar. I have also addedan email option here when I click it willautomatically attach this file, which I have yourto the email box or the new email and I just have to addthe email address of the person who has to receive this fileand click on send it will automaticallygo to them brother and me going to Outlookopening a new mail then attaching this filethis access toolbar button, which I have added. It will automatically open the email for me samegoes with a swelling. These are all added by justclicking on this button and all these optionsthat you can see here. You just need to click on thatand it will The ad for me. Like I said, I have added openI have added email. I have added spelling sothese three things that are added nearly. So when I click on them, that should be a tickmark next to them. So if it is a tick mark, that means the quick accesstoolbar already has those things on the toolbar so like for example automaticsave is already there because there is a tick marknew is already there because there is a tick mark that you can seeyour open is already there. There's a tick mark on it. If I add something new like I had sort ascendingit will automatically add it to my quick accesstoolbar next time when I want to sort some dataI can just click on this if I want to sortin ascending order. I can just click on this button and itwill automatically sort my data. For example, if I go to the central columnthat I have your click on this sort A to Z, which is the smallestto largest it will automatically sort the data and give me data asper the lowest Value First and the largest valueat the end. Isn't this easyand very very useful. Also, these are the things they're showing on the quickaccess toolbar easily. However, if youhave more commands that you want to add which you cannot findin this list, you can clickon the more command button, which I just showed you like go to this drop-down clickon the more command button. It will even giveyou more commands that you can add to your quickaccess toolbar like create chart conditional formattingcalculate now borders. These all can be added to our quick access toolbar alldepends on your requirement as to how many times youhave to use these commands on a frequent basis. So if you use any commandon a frequent basis, you can add themto the quick access toolbar. If you want to removeany of the ones which are already thereon the quick access toolbar. You can just clickon that again. It will go away you will seethat the save button is not there now to addthat save button again, I will go back and click. And that's a burden again. If I want to remove oneof the sort ascending that we just added I will click on that again that sortascending will go away. So the sorted ascendingdoesn't have a technique on the quick access toolbar howto use a quick access toolbar. We have already seen how you can use the quickaccess toolbar by clicking on different buttons goingback just to explain it again, if you have the newand open Button, which I just showed you how you can click on that justclick on the buttons which are looking for their daywill have different icons for different commands. If you have spellingit will say ABC if you have a new workbook that you want to open itwill have a small square button with a small tent on the side. Then you will havea open folder kind of a icon which will tell you that if you want to open a newfile from your Shredder drive or your computer. You can just click on this. This is how you accessthe quick access toolbar next. Like I said, we are moving ontoour conditional formatting. What is the conditionalformatting conditional formatting is a feature of excelwhich allows you to apply. A format to a cell or a range of cells basedon certain criteria. Now.What does that mean? I can color a certainrange of cells. For example, in this case. I can color maybe only cells that contains marketinglike these ones and I can color them in redor yellow or any other color that I feel is required. I can color themin those colors. So why would I color is it would because sometimes youjust want to know that how many people are therein the marketing department? So I will just color those cells that contains the marketingas a department. So let's go back and seehow does that really work? There are different things that are available under conditional formattingsuch as colors icons data bars to one or more cellscan be added on these when you use conditionalformatting for doing that. You will have to firstcreate a rule. So let's see all conditionalformatting can do conditional formatting can first of all hi Lightthe duplicate values in your spreadsheet. So for example in my spreadsheetwhere I have the employee code, I have the first name last nameDepartment region Branch hire date and salary everythingelse can have duplicate value. Why do I say everything elsecan have duplicate value except employee code because first nametwo people can have the same first name two, people can have the same last name Departmentcan be shared by a lot of employees regioncan also be shared by a lot of employees same goes withBranch hire date can also be while it can be unique lotof employees might have joined on the same date samegoes with salary. So what is the unique value that you can see in this databaseis an employee code every employee will havea unique employee code. Now we need to identify if there is any duplicate valuein the employee code. Why are we doing this? It may have happened that when a Charwas creating this file. They mistakenly He addedthe same employee code for two employees. So we just need to identifyif there is any duplicate if there are then theywill need to correct it. So instead of lookingfor each one of them and finding outwhich one is the duplicate. We will use a conditionalformatting conditional formatting will coloronly those cells which has duplicate values. Let's see, how does that work? I select the wholeemployee code column I go to conditional formatting which is under in the ribbonunder the Home tab under conditional formattingclick on highlight cell rules and then clickon duplicate values. As soon as you clickon duplicate values. There is a new windowthat opens up which has a format cells that contain. There are two optionshere one is duplicate or unique as of now. We are looking at duplicatevalues to be highlighted. So I click on duplicateand then it asks me. Which color do you want to givefor all the duplicate values I can select from These few colorswhich are given here so it is light red fillwith dark red text. It will looksomething like this. Then we have yellow fillwith dark yellow text. Then we have green fillwith dark green text like this. Then we have more optionslike light red fill. So there is no change in a font only the fillwill change to red. Then we have read text. That means there will be no film only the red textthat you can see there. Next is red borderor red border will be added to those cells where thereis a duplicate value. So no other change in that and then there isa custom formatting. What does custom formatting dowhen I click on custom format. It will take me to a new window which is the orformatting window. You can decide what format youwant on the duplicate values, either you wantto make it italic or you want to make it bold and italic or you want themto just strike out the ones which are duplicates and then click on OK itwill strike out the whole values which has a duplicatevalue by also. Don't bold and italicplus strikethrough. I don't thinkI need a strikethrough. I don't think I needa bold and italic. I can use one of the filmsthat I'm more comfortable with. I will use pink colorso that it is not very bright, but it is goodenough to identify which one is a duplicate value. I click on OK, there's colored the onethat I just selected. So what happens is nowand I'm looking at this data you will see all the duplicatesvalues have been colored. So if there are two values that are same likein this case we see B-24 579 which is in A4. It also is the same number sitsin a 8 b 2 4579. So if there are two valueshaving the same data or the two cellshaving the same data, both the cells will be colored. So it will tell youthat these two are duplicates. So this is how you can identify the duplicate values by usingthe conditional formatting. Next is highlight the textthat contains specific text. Let's see. The next one on conditionalformatting highlight cell with the values greater thanhow do you work on this? We have already seen that in conditional formattingwherever there is a condition that is met it willgive you the color that you have chosen for it. So in this example as well, we will put in a conditionalformatting a number that we are looking for. If you have any number any cell that fulfills the conditionof the number greater than that, it will automaticallycolor that cell. So in that case, I want to know allthe employees awning salary more than 10,000 rupees by go onto conditional formatting. I click on highlight cell rules. There is an option named greater than I click on greaterthan your I have to enter the number that if it is greaterthan that number it needs to be highlighted. So I have sayten thousand rupees because I am saying any employee or earning salarymore than 10,000. They are salary column or their salary cellshould be highlighted in Red. So I enter 10,000 in this car. Or in this stableand then I enter the color that I want the cellto be highlighted in. I want it in red color. So I'll selectthe red color click on. Ok. Now anybody whose salaryis more than 10,000 rupees. It will automatically giveme this color on that. You can use different formats. Like I said other than onlythe color in this case if I want to change say any suburi salary ismore than 10,000 rupees. I want to changetheir format to currency and currency should beyour Indian currency say I go to currency which is under numbers are usethe currency currency will be the symbol your Rupee symboland then I click on OK. So wherever the salary ismore than 10,000 rupees. It will give me that rupeecurrency with the decimal point because it is showing asa formatted as a currency to see this now. It is also sortedin that particular order. So it is showingyou the exact amount after 10,000 rupees. It is all colored in redwith the format of Given to it. Next is these numbersthat are less than so if I want to knowany employees awning salary less than 10,000should be colored in green and also they arenumber format should now be in a currency formatwith the rupee sign. So I go to highlightcell rules click on less than value this time. It has to be 10,000 again, but my color has to changeand the color is green. Dark green text. Like I said, you can always usea custom format to change your formatting Zone this time. We want to changethe formatting to currency and the current sheshould be rupees. If you want to change thecurrency to any other currency like your dollar sign that you need youcan use one of these now dollar sign can also befor New Zealand US Canada, whichever you think youare wanting to change the currency for in this case. We are going to leaveit as rupees. So I will justselect the currency. And then rupees click on OKand it also depends on if you don't want havedecimal point on that. You can always removethe decimal point by clicking on these things zero asmany decimal points. You want to just keep onincreasing the number here. The number of decimal pointsthat you need. I don't want any decimal point. So I have put it as 0 here. Click on OK, it will change the formatto the currency format. Plus it will giveme a green color because I have requested to put a green colorfor all the M cells that has value less than 10,000. This is how you can usea conditional formatting. Now next one that we will see isthe between value. In this case. We have already colored the more than salary 10,000and less than 10,000 salaries. However, if you wantto First remove any of the conditionalformatting on the sheet, you can select the cells where you want to removethe conditional formatting from so I'll clickon conditional formatting then I will clickon clear rules there. There is an option to clearrules from the selected cells or clear rulefrom the entire sheet. So if I click on clear rulefrom entire tree, it will remove the rules which are addedto the duplicate values. Also, like the employee code where we had the rulefor duplicate values. So I just want to removethe conditional formatting in a salary column. So I'll click on clear rulefrom the selected cells that will remove the rule thereand the conditional formatting which we had addedearlier will be removed. Now. What I want to do is I wantto add up between value. That means if the salary isbetween 10,000 and 20,000. I need those cellsto be colored in yellow now, I'll have to select againthe salary column click on conditional formattinghighlight cell rules and then click on between once I click on between thenI'll have to give this time two different numbersby two different numbers because we are looking foremployees or earning salaries between 10,000 and 20,000. So the first valuewill be 10,000. The second value will be 20,000. So between these numbers if they see any cells They haveto color them in yellow color. So I will click on. Hello. I click. Ok. Now the rule is added to these cells wherever theyfind the salary more than 10,000 but less than 20,000 it willautomatically color those cells. So you will see that it is only coloredtill where they found the salary as 19150. After that when it was 20,000125 did not color that value because it is more than 20,000. So now a new rule is when addedon the conditional formatting. So not only these that we were lookingat all the numbers as of now we can also do a conditionalformatting as per the text say in the first part. I also told you that if Iwant to color the department where I can see marketingand I want to color them in green so I can againgo to highlight cell rules and then click on text that contains marketing so I can say m KT G whereverthere is empty tgy am saying MTG and not marketing because the Your is M K TG and not the full spellingis mark820 ing. So you will again have to be like in the firstmodule we learned when we were doinga formula for some if we have to write M K TGand not mark820 ing. Same goes withthe conditional formatting. We will have to putM KT G as your requirement because then Excelwill look for em, ktg in those cells. Once it finds the MTG what does X and supposed to doit has to color them in green. So I select the green fill with dark green textyou can again select like I said from the custom formattingwhichever color you want. But in this case weare looking for green. So I select green clickon OK it will color the cells which contains MKT G as the text and give me a green coloron those cells other things that you can use under the text function is equalto text that contains and duplicate values. So duplicate values like we sawthe first time it can be used with any cells which has numbersor text or character. So wherever it findsthe Same character or the same textinto different cells. It will color both the cells. So this is how we areconditional formatting works. There is also other optionsin conditional formatting which are known as the data barsand the color sets. How does this data bar? And the color sets workdata bars color scales and icon sets setour conditional formats that create visualeffects in your data these conditional formats makeit easier to compare the values of a range of cellsat the same time. If you see the data bars, which has different colorsthe gradient fill and the solid fill will see how does this data bar workin the first place? So just like the graphs where we have those columncharts same thing. You will seein the data bars your how do we do that is if I want to say give a colors or a data bar tothe salary column where in the highest salaryshould be compared with a smaller ones as well. Let's see. How does that work? So I selectmy salary column I go to conditional formatting click. On data bars and then I clickthe gradient fill for now. If I click on the gradient fillit is giving me a small bar next to each of the values. If you see the bars next to eachof the values you will see that the baris changing its size. So let's sort it in such a way so that we can alsosee the difference between how is itactually giving that bar? So I sought esper salaryin a descending order from largest to smallest now, you will seethat the 49,000 is the salary which is given to sheetal is the highest salarygiven to the employees in the organization nowall the other salary amount which is given there is comparedto this 49,000 amount and then the data bar size is changing accordingto the 49,000 amount. So 49,000 is highest which is close to 50,00024,500 is half of 49,000. And that's the reasonyou will see that the data baris now become half if you go. Went further down like goto say 10,000 or something. You will see because then thousand iseven half of 24,000 data bar has even become smaller, which is it has gonehalf of 24,000 amount. So the data bar, which is there that gives youa graphical representation of where your data sets as compared to the highestvalue in your data. So next is the color scale on conditional formattingcolor scheme can help you understand data distribution and variation such asinvestment returns over time cells are shaded withgradation of two or three colors that correspond to minimummidpoint and maximum thresholds. So, let's see. How does this colorscale work first? I will select this color scheme. Let me removeany colors or rules which are already thereon the selected cell now go back click on color scale selectthe range of the cells the table or the whole sheet that you want to applythe conditional formatting to on the Home tab tab. Click on conditionalformatting point to the color. Then click on thecolor scale format that you want by default for three color scales Excelcalculates 50th percentile. I'll tell youhow does that work? As soon as I clickon this color? Scale, you will see that it has given the greencolor to the highest value then it goes down and it gives different colorsdepending on their value. So there is this anotheror becomes a little orange and then it gets red. So if I want to see how is this workingis I go to check in the manager rule box. I will check how many colors are there in this there arethree colors it soon as I click on the ones which Ihave were just made the rules for I can see thereare three colors one is the lowest value. I have red color thenfor the highest value. I have given green color forthe midpoint in our what is the midpoint midpointwill be the 50th percentile. We can also changethe percentile two numbers say, I don't want to keep a % L. I want to keep somethinglike a number say 24,000 because ha For 49,000is ready 4,500 rather. So let's keep 24,500 as amidpoint and run the percentile. I can change it to number if I want to change itto percentage also that also I can do but as of nowwe will change it to number say 24,500. So if it is the highest valueit will be made the same which is 49,000 the lowest valuewill remain the same as read it can change the colorif you want to I'm leaving it as it is. The midpoint will bea number say 24,500. Now when I click on OKand then click on apply and OK it will automaticallygive me the lowest value as read the highest value willdefinitely be the green color which is 0 and the midpointwhich we entered as 24,500 should be yellow. So, let's see. Where is our 24,500. This is a 24,500. So this is all yellow color. So depending on which color yougive it will automatically hold those colors and the restof all the other cells. Our colored proportionatelywe've also seen how we can customize those cells if I want to click on theseand then change the color scale. I can click on more rules. I can always changethe color scale to to color scale. So instead of threecolor scale now, it will be only two color scalewhich is red and yellow and so the lowest valuewill be read the highest value will be yellow. And the rest of the cellswill be colored proportionately this time you will see that the colors I have reallychanged you'll see more of red because you are number that we have isthe lowest value is red. So the proportionate colors aregiven to the rest of the cells. So this is how we arecolor scale works. Next we are goingto see is the icon sets. How does their iconset work again? If I click on if I select the whole cellwhere I want this icon set I click on the icon setoption select the one that I wanted a directional oneor if I want the shapes like we have the signalshapes we can use. Those two color and the colors will be givenin the similar fashion as we saw in the color scale. So if I am selectingthe directional it will tell me which is the highest which is the lowestand which is the midpoint. So for example, you will see 49,000 isthe highest and so it is showing in the green color and it is showing the arrowupwards then the midpoint, of course like we all knowis 24,500 and hence. It is showing me ason the right hand side when it is highest value. It shows the arrow is upwhen it is the midpoint. The arrow goes on the rightand when it is a lower point if the arrow goes down if you again see the icon setyou will see this value. Yo, if you don'twant those colors, you can also usethe other options which are without the colors. They are all Gray colored so they will not giveyou any color on that. So I just go back I changeany conditional formatting you will have to clickon manage rules. The one that you're goingto change you will click on that particular iconand then select the icon style that You want to change to nowwe want to change it to green and click on ok. Now they are all gree itwill only change the direction of the text but everythingelse will remain greatest time. So this is how we areconditional formatting with icon sets will work. Let's go backto our slides and see so we have already seen that how does your conditionalformatting with data bars conditional formattingwith icon sets work? You can also use formulas. For example, we haveoption to use formula in order to identify if that condition is met onlythen we need a color let's say I want to First removethe conditional formatting which is there on these sheets. So I will just clickon anywhere on the database click on conditionalformatting clear rules. And then there isan option to clear rules from the entire sheet. I click on thatand it will remove all the conditional formattingfrom that sheet. Now. What are we trying to do iswe are trying to identify or get the color on the cells which meets a certain Condition with the formula say I wantto identify in the employee code how many are numbers so if you will seethat employee codes are either alphanumeric codes or there are few ofthe employee codes are numbers. I only want to know the oneswhich are numbered so I will select the a column because they areemployee code sets go to conditional formatting then I will click Bond new rule assoon as I click on new rule. There is an last option that says use a formula todetermine which cells to format under the formula tab where I have to enterthe formula there. I will enter is equalto this time. I am looking for all the cellsthat contains numbers. So I'm going to enter is equalto is number open the bracket and then select the cells which can containnumbers on to see how do you highlightcells with the formula? So only if the condition is what the formulais fulfilled then the cells will be So we'll see that in the employee code column you have numbers as wellas the alphanumeric data. So the employeeswho have joined 5 years back. They are employee codes where only numericdata but anybody who's joined three years backwhich are more recent employees. Their employee codesare alphanumeric. So I just want to identifywhoever's employee code is a numeric data and I want that to be highlightedin blue color. So what I'll do isI'll first select the cell where I want the color which isthe a column in our case because I'm lookingfor the employee code click on conditional formatting goto new rules by new rules because this time weare looking for to use the formula to determinewhich cell to format. So I'll click on usea formula to determine which cells to format thenunder format values where this formula is true. I will enter the formulais equal to is number open the bracket because Ihave selected the whole cell. I'll have to startwith the first cell which is a 1 so Iwill Is equal to is number A 1 so I am saying that if a 1 is a numberthen give me this color. If A1 is not a number thendon't give me the color. So I'll just click a blue color. I click on OK whereverthere are numbers. It will automatically giveme that particular color. So I will be able to identify which are the employeeswhich have joined five years prior or theyare more tenured employees. This is haveyour formula data Works. Let's move on to our PPT. And how does is Formula work? So there aredifferent is formulas that you can use in Excel. One of them is z is numbers. What does it do isit will only identify if it is a number it will saythat it is true. If it is not a numberit will say false. For example, we just learned that if it is a number itwill automatically color that in blue. There is also an optionsomething called as is odd. That means if it isan odd number then it will highlight themin particular color that you mention if it is not an odd numberit will not highlight them. So See even the salary columnif we have any odd number, so I go to news rules. I select the salary columnI go to new rule. Then I click on usea formula to determine which cell to format thenin the format values where this formula is trueI click on is equal to is or open the bracketnow this time again, I have selected fromthe first cell in that column. So I'll have to selectthe first cell which has H1 close the bracket and now I will decideon which color I wanted say. I want the yellow color. So I select the yellow colorfrom the custom format and click on OK itwill color all the ones which is odd numbersinto yellow color. The rest of themwill be left as it is. So these are is functions that you can use in orderto get your data colored. You can also use any cellwhich is more than 75,000 like we use more than data or greaterthen data information. You're in theconditional formatting. You can also usethe formula under new rule where you can put c 3is greater than 75. Maybe I want to know. Employee that is earningmore than 10,000 rupees. I want them tobe colored in green. So what I'll do is usethe formula to determine which cell to formatunder the format values where this formula is true. I will take H 1 again because I'm selectingthe whole cell is equal to H 1 is greaterthan 10,000 same thing. However, we are usingthe formula to determine that now this time I'm going tocolor in blue so I click on OK. So wherever there is in salarymore than 10,000 rupees. It will automaticallycolor in blue anything that is less than 10,000. It will leavethe previous formatting that we had same thing. You can use the less than sign. Also wherever youneed to another one that you can see here is youcan also try the formulas like where you see a as formatlike in this example, if you see the studentsdata is given here under the marks in mathematics. There is a score that is givenin the greatest given according to the scoresin the grade column. You'll see ABCD depending on their marks that theyhave been given a grade now. We want to know anybodywho is coder a grade in that case under the Formulato determine the cell to format. We will put b 2is equal to a by b 2 because we are starting witha be to column we are starting from the First Column if the data startsfrom the first cell, which is b 1 then we will haveto give b 1 as the data but we are startingwith the second cell where that iswhere it is starting. That's the reason wewill give the second cell. How can we logicallyuse functions such as and function and or functionsin conditional formatting. So, let's see how we are goingto do that we can use and and the or functionsin the conditional formatting for us to understand how to use and function wewill have to first decide which are the conditions that we have say in our exampleon my spreadsheet. I have to color allthese employee code cells where the department is sales and the region is not so we havetwo conditions one condition is Department as sales and the second conditionis region s North So these are the two conditions that we have forour conditional formatting. So both the conditions haveto be fulfilled in order to for the conditionalformatting to work. There are three things that weare going to learn here one is how to identify how to color or different cell depending on the conditionsdo different cells. So, for example, I want to color employee codedepending on the conditions fulfilled in column D and column e so to start with Iwill select a to a because I want the colorin this column. So I select way to a I goto conditional formatting I click on new rulesunder new rules. I'll go to use a formula to determine which cellto format under format values where this formula is true. I will enter is equalto this time. I have to condition so I have to useand function open the bracket after the and function Iwill select the first cell in the database, which is d 1. I'll have to type d 1and not select D1. Why I will tell youif I select D1 that dollar sign comes and then not applyto the rest of the cell and so you will not getthe correct answer. So it's betterto always type d 1. So the first conditionis D1 is equal to sales. We will always put salesin the double quotes. Second condition is e 1 is equal to North so I put inthe double quotes again North no case sensitivityin this I close the bracket. So my condition is hasto be fulfilled like if this department is sales and the region is not I wantthe color as say green. So I select the green color in the customformatting I click on OK and then I click on OKagain wherever there is a sales departmentand the North Region it will give me a colorin the employee code sell for that corresponding dataas a green color. So wherever you find sales arenot the employee code cells will be colored same goeswith an or condition if I want to color. This time I want to color the department column whereverI find sales or marketing, so I will select the department. I'll go to conditionalformatting I click on new rules again useformula to determine which cells to formatunder format rule values where this formula is true Iclick on is equal to and start with D or function. Why do I do an or function because I am looking for sales or marketing eitherof these conditions are fulfilled thencolor it as yellow maybe so I say d 1 is equal to salesin the codes or D1 again, right even again because I am lookingin the same cell if it is either sales or marketing marketingwill be M KT G close the quotes close the bracket. Now my condition hasto be fulfilled as either sales or marketing color. It has yellow so Iselect the yellow color and then I click on OKthen I click on OK it will automaticallycolor these. Sales, and the marketingone says yellow. So this is how you can useand an or function in the conditionalformatting as well. So let's start withthe logical functions for now. There are four logical functionsin Excel and or not and Zoar. Let's see, how does the not ends or functions workin Excel the not function Returns the oppositeof the given logical value, that means any value that I give it will returnthe opposite of that. So let's see using the example that we have on our sheetwhere I have the student's name and their scores or their marks. They have scoredin the subject maths. Now, let's say we haveto identify any student that has failed in maths. They're passing marksis 35 here. We can use not function. So how do we start us is equal to not open the bracket enterthe cell reference where the Marxist sofor the marks is in the cell reference be true. So what we have to identify that He too is not more than 35if it is 35, then it has to listen. The river is fail if it is more than 35then it is passed. But what I'm trying to sayis I want to understand who all I have failed in maths. So what I'll do is not morethan 35 I close the bracket once I do that I'll get whereverthere is not more than 35 now get our result is true. So for example a drag it here wherever I can see truethose are the ones which are failed result. So this one G is code 32 and that's the reasonit is showing true for E. It is showing true because they have only scored 30 that means it has failedand again for see it is 28 and because they have scoredless than 35 they have faith. So wherever you can see true,they have all failed. So similarly if I wanted to addthis along with the IF function, how can I do itwith the IF function so I can do if startingwith the logical test? What is my logical test that whoever is not scoredmore than 35 marks has failed. So what I am going to dois B 2 is greater than 35 that means who hasnot scored more than that if I should be fail, so I'll put fail asthe result for Value if true and value if false will be in case ifthat is not the case it is fast. So if I am using itwith the function, this is how it would look movingon to the next function which is as or function. There's our function checkswhether one and only one condition is met so if there are two conditions onlyone of the conditions is met if no conditions are met that means conditiona condition B. Both are not met or more than onecondition is met so our function returns false. That means if I put a conditionsay condition a condition B, if condition A and B, both are met then those or function will giveyou a false answer if none of them are met neitheris a neither be then also, it will give a false only oneof the condition has to be met a or b then it will giveyou a true answer. So let's see how we can do that. I want to buy a new computer and I have two Endsof the computers that I have to consider the size of the hardware and the ramI select the computer if the hard drive hasat least 500 MB s. So the first condition is high drive 500 MB iswhat is the second condition? The second condition isif the ram is greater than 6 GB it has to be greaterthan 6 GB and not 6gb. Just remember this is 500or above for Ram. It is more than 60 B. It cannot be 6 GB. So how do I do sore? So I go to the doorI go to the cell where I need this information. So I need to identify whichof these computers I can select. So the first thing that I'll do is sort oflogical test hard drive which is an f 2 is greaterthan or is equal to 500. Why do I say this? Because I need to identifyif it is 500 or more. Then I can buy that orif this condition is not fulfilled isthe other condition fulfilled. What is our the condition if she too which isour Ram is greater than 6 and there is no equalto in this why no equal to V coz Three say that it is equal to then itwill also take the result when the ram is 6 but we don't want six ithas to be more than 6. So I press enterwherever I see true. That means these arethe computers I can buy because one of theconditions are fulfilled. This is how we aresore function work. You can use them with the IF function similar youcan use the nor donators or in a not function. You can also usethe other neurological functions like and and or withthe IF function now see how to do an IF function. Let's see an example on my sheet sayI have a data here. I have to calculatethe bonus of the employees or Commission of the employeesin the sales department or the commissionof the employees. Now our mission of the employees is decided bythe management management says that we are only payingcommission to the employees in the sales department asCommission of 10,000 rupees. So only to the sales department. Employees. So how do I write I can thinkof an IF function? I have to select B to because my departmentfor each employee sits in the B column. So b 2 is for the firstemployees only I have to check if P2 is equal to sales. If it is sales, then I have to givea ten thousand amount value if true if value if false I will put 0 because there is no conditiongiven for the others assume that if only sales department is getting 10,000 othersare getting nothing so I can leave it as 0so wherever their sales it will automatically throwa 10,000 amount in the cell. So this is how we are IF function Works moving onto using the if with an and or an or functionsimilar to how we use the not function with then if we can use the IF functionwith an ad or a or so to start with we are lookingat our sheet on my sheet. You can see that I havethese name of the students the scores in mathand the score in science. So what I'll do is I needto know the students who? Bost how do I knowif they're past if the student hasscored you can see it here the reconditioned if the student has scored 35or more marks in each subject, their final resultwill be passed or the result will be fake. So if they have cord 35 or moremarks in each of the subjects, that means boththe subjects they are pass. Otherwise, they are failed. So what I have to do is Iand user and function y and because I need to ensure that they have scored 35 in math and they have scored35 in science. So I start with an IFfunction IF function because I have a True Result that I have to giveand a false result that I have to give H is if this is true give mepause or give me fail so I can only do that in IF function nowwith the logical test when I had one logical test, I put it as departmentis equal to sales, but now I have two logical test. So I'll have to use aand function inside if what ismy first logical test. I have to check if the math score for studenta is the more than 35 and the science code for student a is more than 35 so Potentand function then start with n b 2 is more than or equalto 35 and second logical test that c 2 is morethan or equal to 35. So both of them once it is Wilfredonce I complete that end, which is The Logical test. So I have to close the bracketbecause the end is complete once I complete that you will seethat we are coming back to the IF function. I have to put a comma to get to the next elementin this context. What is the nextargument value if true? What should be the value in caseif this is true, it should be passed. So I put bars and the invertedcomma value Falls is fail in the inverted commas. Now close the bracket enter. Now when I drag this formula down it will give me the resultfor each one of them. So if you see for studentssee they had scored 28 in match Which isless than 35 marks while they have scoredmore than 35 marks in science. However, we are looking at both of them to bemore than 35 and a hence. It is giving the failedsame goes with d and e g moving onto the next one. What is the questionthat And see your is if the student has passedthe certification test, which is 80 or higher. So we have a certification test for the studentsand they are saying that if they havepassed the certification in the passing marksis 80 or higher or has passed the preppies, which is your nextrequirement prep test which is the passing marksis 70 or higher. They can registerfor the next class. So what is their question that if they have either scored 80or more in certification or they have scored 70or more and prep course, they can go forthe next registration or they can registerfor the next class. So here again, I have the condition where Ihave to give a true value and a false values and hence. I'm going to use the IF functionto start with an F function and then it is the there aretwo different things that I have to lookat the certification and the prep course, both of them will startwith an or why an or because I have to seeif this is 80 or more or if this is 70 or more. So I start with the first onewhich is certification if certification is greater than It is equal to 80 or the next one is prep courseis greater than or is equal to 70 close the bracket. Now you logical test is completegive me value if true. What if this is truethey can register so I can click I will enterregister in the true value or cannot registerin the false value or I can say yes or norather than saying if you see the headingit says yes or no so I can just putyes or no here. So I'll put yes comma no, it's an easier way to just putit but depending on what it is you're looking for youthe free text you can use that close thebracket enter now, you can drag the formuladown and depending on whether the student has scoredmore than 80 or not. So now if you seethe children D, it has neither scored 80or more in certification nor has it scored 70 or more in the prep tail course and those does itis showing a know if you see student J. It has not scoredmore than 80 in certification nor has it scored more than 70in the prep course, and that's the reason again. They have data showing know. You look at one more example. If you see this one studente-day have not scored more than 80 in certification. However, they have scoredmore than 70 in the prep course. That's the reason the result is. Yes. So this is how you can usethe if and not together or other if and and if not together nowmoving on to the next one, which is the if error functionso the iferror function when working in Excel, sometimes we will experienceerrors these errors can be hash and a hash valuehash reference Etc. You must have definitelyseen all these errors when you're workingin Excel in this tutorial. We will look at how to use if error functionin Excel to deal with this errors constructively. Let's see an exampleon my sheet in this scenario. What is happening is we havea data set consisting of columns of numeratorand a column of denominator. Then we have a column that contains the resultof dividing the numerator by the denominator. There are some zerosin the denominator like this this this so because of this itwill cause a div error. That is the hash div / 0 - error for these rows in the result column wecould go of step further and make Excel place a 0 in those rows to replacethe error for the sake of a cleaner look but it should not interfere with the calculationof all other rows to do this. We will use theif error function. So how do we do that? So I will start by typingand function if you see that I have already putthe result here. So instead of doing that I will first do an iferrorfunction in a another cell and I'll show youhow does that work? Now?What am I trying to do is if you see the syntaxof the if error function that says value value that means which valueare you looking at whether there is a error. So I'm thinking lookingat c 2 comma if value if error if it is an error, what should the value B in caseif there's an error? I need a 0 so thenI Loser bracket enter now if I drag the formula, you will see whereverthere was a div error. It will automaticallygive me a zero however, now it is giving me a differentin a different cell if I want to do itin the same cell. How do I do that? Then wherever my formula is after the equalto the formula start. So after equal to I'm goingto put iferror open the bracket inside the bracket where the value is Iwill put the formula for which I am lookingfor the error in this case. It is a 2 divided by B to numerator divided bydenominator common value if true I'll have to putzero there was one thing that I have mentioned here. I have put a inverted comma Idon't need to put inverted comma in case of numbers so here when I put a 0 and dragthe formula you will see that wherever therewas a div error. It has automatically givenme a zero and result why because now I have gota formula for xindi pay special your we have so you will seethat the result I can see is 0 for each one of them whereverthere was a div error. So moving on to the next one, which is the if any errorif any error is one of returns a value user specifiesin the expression, whether the function encountersthe na error, otherwise, the function Returns, the cell value is similarto the iferror function. So one of the shortcomingsof federal function is that it acts asa catch-all errors. So even if it is a div error n a value reference itcatches everything this means that it covers several errors and the consequencesof this error is that if error isn't really helpfulin helping you distinguish between the differenttypes of error and sometimes may hidea particular error that you would like to fix if an IF functionis used to trap and handle only any errorsthat may arise in the formula, especially those that performlook up using match. We look up a trick of Exeter than a functionwill only handle any errors which means other errorsmay be generated by the formula will still display so in we look upwhen do you In error when the data isnot available in the sheet, so you'll get a errorin that case and if you are getting thinnerlike a any error, so if you're gettingthat kind of an error, then you will haveto definitely use a function which will tell youthat this data is not available. If you're going to be there if error that means there isa referencing error. That means the data is therebut we don't even know if the data is there or not, but the referencing errormeans we have not given a right referenceto that the cell reference and that's the reasonI'm getting an error. So to ensure that I don't hidethe reference error. I only highlight the any errorI am going to use the if any function how do I use the if any functionsimilar to the iferror function I start in the anothercolumn you will see that in the currentshould receive their I've done a vlookupand found the result from this table. We will do we look upahead so we'll know how does the vlookup functionalso work for as of now with the field wake up function. I have been able to derive datafor some but the rest of them where I can seethe na that means that this data is not available. The table, soif I am going to say to excel that if the answer is n a what answer isan a this one is any then keep me up blank data or giveme don't data available again. Like I said, it's a free textbut you have to put it in the inverted commas. Once you have that you'll be ableto get that results on though rate available. Now, I can just dragthe formula down. So for each one of them whereverthe data is not available it will give me the result isno data available. Now, how do I copythe same result or the same formulain the vlookup function similar to if error I'm goingto do after the equal to if n a open the bracketvlookup function I have done in the values comma y value if any what do I need? No data available closethe codes close the bracket enter whereverthere is no data available. It will automatically give methe result has no data available the rest of them whereverthe data is available. If an error will give methe correct answer only if there is an error itwill click for that error and replace it witha no data available now, let's move Wantto the nested function? So how does the nesteda function work? Nested-if is notinbuilt function. In fact, it isnot a function at all. When you have multipleconditions to meet the false value in if is beingreplaced by another IF function to make a further test, for example, let's lookat minus 2 the function. So when I'm startingwith the IF function any function will have a valueof true and value false so value if true ice put a commahere as soon as the value false starts I startwith the next IF function. This is what it means. So moving on to our listsupposing you have a list of employees in column A which we already have hereand you have the department in column B and the bonus amount we payto their each employees decided based on the conditiongiven in this table. So to the sales departmentare giving a bonus of 10,000 for the Edmond bearagree to give a bonus of 5,000 for marketing 8,000 RND 4000. And for others. It is 3000. So looking at the nestedfunction I have to do say the bonus. That I have to identifyis in column e how do I identify bonus? So first and foremostI have to do is if I look at this I startwith the IF function because I know that the IF function Workssomething similar to this one. So I'm going to sewfirst type only the formula. So I start with an Ffunction is equal to if why I'm using a face because I know thatwhen I'm using an IF function, the IF function has the trueand the false value here. I have to see if the sales is truethen give me 10,000 if it is not true then haveto look for other things. So the first and foremost that I'm going to dois I need to identify if this cell contains sales if it contains Hillsthen I'll give 10,000. So let me put itin a different bucket in the first placeif it is sales, I have to give 10,000. So this is my firstbucket say bucket one. I'm putting a bucket also here so I know whatI am talking about. This is my bucket 1 contains sales buckettube contains admin, which I am going to give5000 bucket 3 contains marketing the marketing. I have to remember that as well. A little different fromwhat we normally write. So 8,000 then pocketfor contains RND for R&D that bonuses 4,000 bucket5 contains others. Now what is others the ones which are not fallingin these lists are all others. So looking at this data youshould be able to identify. How are we goingto put it in the list that we have here? So this is my list to startwith any function. I have to startwith an equal to sign. But in this case Ihave to again start with is equal to F function IF function The Logical test25 first logical test is that I have to look if the department is equalto sales department for who I will look for departmentfor Raja Raymond occur. And there are other Raymondoccurs Department result is sitting in be too soI select b 2 is equal to what am I looking for sales? So b 2 is equal to 0 so either I canselect this sales. I can select this cell which says salesor I And put it in the double quotes asseeds either way it will work. So as of now, I am going to selectsales from your gamma. So I say that if V2 is equal to sales then giveme the value of true. What should it give meif the result is sales. It should give me 10,000. So again, I can either put10,000 s1000 or I can select this H to a z columnor the cell reference because the 10,000is there in the cell, but remember if you seeremove this cell reference or if you edit anythingin the cell reference, the result will alsochange here now moving on to the next one so value if true I know the valueof true is 10,000 but what if the value if false I know if it is sales Ihave to give 10,000 but what if it is not sales thenI am telling Excel that please lookfor the other condition. What is our the conditionwhether V2 is equal to Edmund? That is my second conditionthat I have to look for. So b 2 is equal to adminso I select the admin, What if it is admingive me 5,000. So it is true. What if it is false againI start with The formula is a B2 is equal to marketing. Now. I have to rememberI cannot take G to because marketing spelling your is different fromthe marketing selling your so what I'll have to dois I'll have to put it in the inverted commasmarketing comma this data which I am typing in the doublequotes is not case sensitive. So you can put it asM KT G in all capital doesn't matter now again 8,000we can take a cell reference because there is no swellingthing here comma again I start with an if y if because this time I haveto look for be 2 again B2 is equal to RN D comma if it is an R&D giveme four thousand or if it is not R&D. Just remember now again, putting a if will not work because if I putif you're I will have to say which otherdepartments are available. So what are the Department'sleft after Edmund sales and marketing and R&D. There are personalthere is director. There is financed. This is all the CCD is there so all these departmentsare left out. So are we going to put Each oneof them under saying if this given three thousandso not for all of them. Am I going to put three thousandevery time so how do I ensure that in one go? I am able to I don't have to give allthe conditions together instead. I can give 3,000in the end saying that if none of the conditionsare fulfilled then value if false will be 3,000. So why am I getting this error? Because I had to put bracketsfor each one of them. So one bracketthat I put you will see that it is the bracket isfor a first IF function second bracket. I put it is for the secondIF function third record. I put for the third functionand the fourth racket. I put for the fourth a function. Now, when I press enter itwill go ahead and will see that wherever there is data, which is closer to the we are looking for itwill guns we've done so far if you see on the result here, the result is showing a 3000 but if you seeour department it is admin so it should ideallyshow a result as 5,000. Why is it giving an error? What I have done iswhen I'm looking at the list. I have given the cell. References when I'm givinga cell reference. I have to ensure that it isexact spelling should match. It is not case-sensitivethe but dispelling has to match. So what has gone wrong in this if I go and checkhere I will see that the Edmond is without any space at alllooks all fine here also, it says admin but if I coulddouble click inside, I'll see that the adminas a space at the end. So when I remove the space,it gives me the correct result. However, that's the reasonI always suggest that you should rather thanusing this cell references. It is better to usethe names there. So this was one way of doing it, but after your cellreference is done, you cannot drag it directly. You will have to actually see which cell referencesneeds to be freezed. Like you'll have to freeze g2y because G2 willalways remain Jutsu. So let's do one thing rather than makingthis more complicated. Let's do another cell and try and do it with the namesin the double quotes. So first we have done itwith the references now, we will do itin the double quotes So, how do I do if Open Bracket logical test now we are doing itfor the second one. So The Logical testis B 3 is equal to Sales even easier to drivethe sales then give me 10,000 or give me value false again. I'll have to start with if whatis the second one B 3 is equal to Edmund this time. If it is admin, I have to get 5,000now in the false. We will put theif condition again if p 3 is equal to marketing again marketing we will putit as M KT G comma value if true what do we need? If it is the value of true manythe result has 8,000. So I'll put 8,000 you, what is the next oneif again B 3 is equal to R ND then giveme four thousand. The last one that I have we didwere there was three thousand. So I've not givenany cell references this time. I will immediately just takenthe result in the middle commas. You will seethat the difference now there you are getting all zero zerosor some other numbers here. You can get a better result wherever there is admit itwill give you 5,000 marketing. It will give you 8,000. Let's copy the resulteven in the first one so that the formulais copied now, so it is always advisableto put it with a hand so that It becomes more constants. So you don't have to keepfreezing those cells which are the cell references. This is how you dothe nested function. Let's move on to the next one. The next on our listis the text function. What are those takes functionsstart with the first function that we can see isthe lower function. Let's move onto our list. The lower function will convertyour text into a lower case. Like any text that you have in a Cell you can use this lower functionto convert it into a lower text in example is equal to lower open the bracketthen I select the first name. I close the bracket. You will see that the Rajahhas changed the case true or lowercase earlier. It was our was Capitalall the others will lower but this time itis all lowered similarly when you're doing an uppercasethe same function will apply where you can use the upper and all the characters or allthe alphabets in the text. It will convertinto a proper case. You will see that I have beenable to convert the first name into all uppercase similarly. The third one which we have on the list isa proper case proper case just like lower and properfunction proper Works in a similar fashion. The difference is that proper function convertsthe first letter in to uppercase while changing the restof the letters into floor case. So, how do we do this CRVor have the same cases? So let's usethe uppercase letters that we have youso is equal to proper and then select the one in the upper column enteryou get the first character or the first alphabet is upper and the restaurantwill be lower. This is how we are upper lower and the properbomb functions work. The next function that will haveon the text function list is the left function leftfunction extracts a given number of characters from the left sideof this Supply text or string. So if I say a is equal to left and select the text from whichI need to extract the data and then comma I can givethe number of characters that I need to extractfrom the left side of this text. So I select say three once I select three it will giveThe first three characters from the left. This is how your leftfunction works now similar to the left function. We have the right function. So a is equal to right. I select the same name. I give three characters from the right itwill give me a ja why because it is takingit from the right. This is how we areright function will work moving on to the next one. What is the next functionthat we have on our list? We have mid function in the listthe mid function Works in a similar fashion asthe left in the right. It will not extract datafrom the left or right it will extract characters or datafrom the middle of the text where you are required to provide a certain positionand the given number of characters fromthe starting position. So for example is equalto Mid then we select the text say this timeI'm selecting the last name because it's a bigger character. I have to tellwhere I need to start. So it will not decide on its ownfrom we're in the middle. You will have to give a data wetas to where they need to start. So I need to start sayfrom the third character so I can give threein the start. Number and now the number of characters fromthe third character. So when I say from the thirdcharacter number of characters, it's ymo say three characters. I want from the third character. So I will get the answerfrom the middle. It is third character is RA Y and Y MO is the three charactersafter the third character. This is how your midfunctions work moving on to. The next one isyour Len function Len function provides the lengthof the characters in the cell, for example to identifythe number of characters in the first name. I will enter is equal to length and then I will selectthe first name column and close the bracket enter. I'll get the number of characters inthe first name column. This is how we areLen function work. If you drag all these formulas, you will get the datafor each one of them moving on to the next functionthe exact function X the Axl exact function compares to textin the different cells and returns true if they are same and falseif not exact is case sensitive. Let's see. How does that work? Say, I have the data like appleand apple I have apple and I have small letter Apple. I have apple all capitaland then I have apple all small. So it's like different cases that I have it in onlyif both the characters are the same it will giveme a True Result. So I just put exact openthe bracket text 1 comma x 2 I close the bracket enter. It will give me true because both areat the same case and the apple apple boththe names are the same in both the cell and even the cases arethe same same goes with a few if I go and try the exactfunction in this one A2 and B2 close the Enter giveme a false result, why false resultbecause in a to the data that I've entered the character that is enteredin a proper order like we havethat proper functions. So it is like first character or the first letter iscapital rest are all small. But in the B2,we have all small letters same if I try and copy the formulato the third one. It will give me a falsebecause all capital in the A3 while all small in B 3. So this is the exact functionWorks in this way moving on to the next one that we have on our list isz RN between function. What is an RNbetween function are in between truncation Returnsthe random number between the specified values. So it will just giveyou a random number between the values. For example, I give 20 and I give hundred and I use ourand between function. I have to give two numbersone is the bottom and the top number so bottomwill be the lowest number while the top number will bethe highest number between this it will give me a number. Every time I refresh the sheet like I go and calculateread it will give me different numbers every timebetween 20 and 100. So it is a random number that will pick and give you thisis our viewer are in between functions work moving on to the next one isthe named ranges in Excel. You can create namethat refers to cell or cells on the worksheetspecific values or formulas after you define the Excel namesyou can use those names and formula instead ofusing a constant value of the cell reference. Let's see how you can namea range or a sell easily and how you to use them in a formulas to makeyour life simpler. Let's see our vlookup formulathat we have on our list. I can name the tableor a database which in our case iseven to eaten I can go to the name box. Not necessary. I can only eat selectthis whole table. I can select evenone cell for that matter. I can select only one cell and give the name herein the name box. This is called as a name boxso I can go there enter the name as Matt which is name given. Inside the data Ican click enter once I do that this cell name becomes Matt if I want to give the nameto a particular range like this full range. I can say Student's nameso I can just put the students. I cannot givea space in the name so I can put students and then named afteran underscore and pressing enter what happens is itwill changes the name of those range of cellsto Student's name. This is how I have giventhe name to this range. Now, how do I modify anyof the named ranges that I have given already? If you go to formulasin the ribbon under formulas, you will see somethingcalled as name manager. If you clickon the name manager, you will see allthe list of data or all the list of ranges where the names havethe Rangers have been named. For example, Ihave named this range which you can see nowI'm looking for the named range that I have just done which is students namedIke the Click on that and then change or modify the named rangeby clicking on referred to I can change the name. Also if I have to by clickingon that name column that I can see on the wizard once I click on that nameand then give a name there. I will be ableto change the name of that particular range. Then the next one that I can see on the list isrefers to this part is the range which I want to give the nameto so I have selected that range and I can give ita name to that range. So this is how Ihave can modify it if I have to youcan also delete it. If you don't wantthat train name click on delete click OK same thing. We had givenone more name here, which is the mat. I can also select that deleteit and it is gone. This is how you can modify or delete or addany named ranges to a cell or a column moving onto the next function that we have on our list are the lookup functionsunder lookup function. The first function that we are going to seeis the vlookup vlookup is the most well-known. Excel function vlookupis an inbuilt function that is designedto work with data that is organizedinto columns like this that we have hereinto the columns for a specified value. The function finds the valuein one column of the data and Returns the correspondingvalue from another column three conditions to be filter for we look up to work one. There should be a common data which can be lookedupon to derive the corresponding data example, if you see your I havethe student name mentioned your and I need to know their marks that they have scored in the subject mathsin another range that I have your I can seethat I have the student's name and they are marksin different subjects that I have the studio. Now, what I need to dois I need to derive the marks of the studentmentioned in cell H1. So the first condition that has needs to be fulfilledto do a vlookup function is that There should bea common data which can be looked upon toderive the corresponding value. So what is that? We can look upon the name nameis the common data. Yo Bobby 0 Bob is you so that's the commondata set is there what is the second condition? The common data is identifiedshould be unique in itself. So it Bob should not betwice in the data if it is twice in the table, then it will only pick upthe data for the first bomb and the second Bob will beignored by vlookup function. So we have to ensurethat it is not duplicate. It is always unique data that you need to deriveshould always be on the right sideof the lookup value in the database from where you want to derivethe corresponding value. So it should always beon the right hand side. That means the datathat you need to derive. This is the data that youneed to derive right on that. This part should always beon the right hand side of the data thatyou're looking for. So you looking for Bob and your max maxis on the right. So this is alsocondition is fulfilled. So how do we start to dothe vlookup function to get our result? I do an equal to V lookup open. Racket I have to look for Bob. So I select mom in which tableI have to select this table, which column index number. So this is the firstcolumn index number because I'm looking for the Bobin this column and the math. I have to get the answerfrom the second column, which is marks in math. So 2 comma 0 closethe bracket enter I get the result as 88 now, even if I change the name herefrom Bob to some Maria I'll still get a the result as44 because there is a Formula already entered you. This is how we are vlookupfunction Works moving on to the next one isyour H lookup function similar to vlookupfunction H. Look up is a horizontal lookup so we look up is a vertical lookup White Edgewill come is horizontal look up the Syntax for hlookupis similar to vlookup. However at look up with lookingfor Value horizontally the third argument and Hlook up is row index number. So with another example where I have a similar datawith the names of the students shown in the first row, they are marks are shown inthe second third and fourth row. I have to fetchthe marks and English for the student's namementioned column a nine again. The similar way I have to do like we're doing we lookup why would do we look up because we have to lookfor data from the column. But this time youhave to look data in the horizontalmanner like this. So what I will dois is equal to H. Look up finds T comma in whatthe database comma this time. I am Steve what I have to lookfor Reza marks in English. So 1 2 3 and 4. So 4 comma 0 now when I change the nameof the student, it should still giveme the correct marks. This is how we are hlookup worksimilar to the vlookup moving on to the next one. What do we have on our listis the formula auditing under formula auditing the errors first runanother formula auditing is it traded settingsor dependents tracing presidents and dependents helpeduser to identify. The reference cells usedin the formula or function? What is the differencebetween proceedings and dependents presidents arecells are arranged. That affect the active cells value dependentsare safe in syringes that is affected by active cell. So how does that work? So to see what arethe sensor referenced in the active cell formula click the trace precedentsWorton to see which other cellcontains a reference to the active cell clickon the trays dependence buttons. Let's see how that works. I'm trying to identifywhich cell is this coming from this data or this result is comingfrom which are this link to so I click on Trace precedents that means whereis it coming from? So it is sayingthat this whole table and from this cell if I have to say that dependence on the so I have just seenthe Precedence now, I want to First removethe arrows from this because I can see the remove so I can just click on remove arrows and allthe rows will be removed. So now I could not seethe president's now and to see if there is any dependenton this cell like a 9 so I click on Trace dependence and it is telling methat this cell is dependent on Your cell A9. So this is a differencebetween that dependence and the president's now moving on to the next one isthe error checking. What is an errorchecking function. This is Again part of the formula auditing errorchecking helps the user to identify which cellhas the error occurred. This can be very useful when you have a big list of dataand you have entered formulas in different parts of the sheetto get the desired results. So, for example, let's move onto our sheet where we had the errorswe had at the know if error function I removethe air from your I will remove if error so that the errorstays in this list. Now I know that the editor is therein this list you div div. These are all the errorsthat I can see on the list now. It is very much clear because I can seethis is a small data. But if it was a big dataand I want to identify where the error comes for I can clickon error checking and it will tell mewhere the errors it. So Aaron in cell C3, that is only one errorthat I can see now I can click on next to get onto the next error which I can see cell C7next I can see cell C9. Now.I can either edit in the formula barto change the error so that it doesn't give you theerror or show calculation type. When you clickon the calculation. It will tell youwhere the error happens by clicking on evaluate. Once you click on evaluateit will give you where the errorhas actually happened. This is how Wonder check errorworks next on our list that we have isthe show formulas your I can see only the resultof the formula only when I go on to the cell, I will be able to seein the formula bar the formulas that I have entered to seethe Amla in the whole sheet. I can just click on show formula and all the formulaswill be displayed automatically on the list. So you not see the resultbut you will see the formula that I have enteredon that sheet. This is how your shapeflow formula works next to column allows youto move the text from one column to another effectivelysplitting text entries into the two separate spacesto separate the content of One XL send intwo separate columns. You can use thatextra column wizard. Let's look at your sheet where I have an example examplein my sheet has full name of the employee is givenin one of the columns. I want to split theminto two different cells. So how do I split them? So I want to split them in sucha way that plus seems raised in a but to these first name moves to the B columnto perform this action. I have to use the text to columnfirst thing I need to do is to identify the separatorin the data in our case. We can see that the first and the last name is separatedby a comma So our separator is a comma however in Excel terms this separatoris called as delimiter. So how to perform the textto column 2 separate this list using the still limited. I will select the whole dataor the whole column where I need to separatethis list from and then I'll go to data under data and seat X2 column click on thatat X2 column wizard will open up under the textto columns wizard. I'll see somethingcalled is delimited which is our separator. I have to enter the separatorin the next window. I will remove the tab, which is a default valueand just pick the comma as by separator and thenclick on finish. As soon as I dothat the first time in the further name will moveinto two different cells. So you saw that one way to dothat is was to use a delimiter because we had a delimiter or a separatorin our data now, however, if you do not have a separatorin your data or rather, you know that you have a data that can be splitwith a fixed width now where we have a fixed width we can use another set of Formation on the textto columns wizard which is the fixed with informationyou so like for example, we have this datesmentioned earlier. I want to separate the Year from the date like the firstdate month and the year, so I want to separatethe month and date and one column in one cell and the year should moveto the next cell values fixed within the next window. I will see the ruler on the top. I'll can move the rulerusing my left click and selecting a ruleron the page. Once I do that now, I only need the date or the ERto be in the next column. So I separate between or puta ruler between the ear and the previous informationand click on finish. It will automatically movethe year to the next column. This is how your textto columns wizard Works. Let's move on to our nextfunction on our list that we have isthe data validation. What is data validationdata validation is a feature in Excel used to control what a user can enter into the cell and simply displaya message to the user telling what is Out datavalidation is a feature that allows you to performnumber of actions like make list of entries that restrictsthe values in these cell like I'll havethis list of names. So when I am entering any othername other than this list, I will get an error createa prompt message explain the kind of dataallowed in the cell. We will see how we can do that. So we are creating a messageor a kind of a comment which tells see user that this kind of datais required to create a message that appearswhen incorrect data is entered. So as soon as you enteredan incorrect data, it will give youa popup message saying that this is an indirect data, please enter a correct one check for incorrect entrieswere using the auditing toolbar so we can do that as well seta range of numeric values that can be enteredin the cell and determine if an entry is validbased on the calculations in another cell. So let's see how the datavalidation Feature work. So I have thisregistration form here, which has the firstheading I can see the registration form numberthen is the name of the student who have registered for it, then we can see the code. That the imprudent wish to enroll then youhave the gender and if they are graduate yes or no. Now I have a list of courses that I have mentionedin one of the cells here or one of the columnhere for courses that the college offers one isa post graduation diploma master of business managementdiploma in business management Bachelor'sof Business Administration. So student can only selectout of these four courses. They cannot select any other course otherthan this for courses. So what I'll do is insteadof telling them again, and again, please don't selector don't enter any other and give allowing themto enter a free text. I would use a datavalidation technique where I go to data and click on data validationon the settings. I can put list as my dataand under Source, I will select the information which I have in this listas soon as I click on OK you will see that a drop down listis created in this cell. Now the student who is enteringthe information here. We'll enter somethinglike this their name and then the courses that they wish And rollthey cannot put anything like they cannot evenput a short form know they will haveto select from the list that they can see orif they don't put it that way. They will get an errornow gender can be male female or anything that they want to enterif they are graduate. Yes or no. So this is a kind of information that they can enterinto the form however, now we have coursethat they wish to enroll. We just went to data validationwhere we put the list and then enter these swordsfrom one of the columns where we had alreadyentered the source now, we also wantthe students to know that what they needto enter before we do that there is another cell here. So this was only donefor this cell this which is cell C2 if I want to copy this data validationin the rest of the cell so I can just copy that and go to select themthe place where I want this to be copied and pastedit control C and control V. So it will paste the datavalidation will be pasted in all the sense. Next is if you seethe registration form number where I can see is Iwant the students only who have registration form. But still hundred and oneto be able to fill this form. This form is to befilled by students who have the registration number until 101 if they haveregistration number 102, they should not be ableto enter the formula. So, how do I do that? I will first select allthe cells where I'm sure that they are going to enter theregistration form number now, I go again to the data validationunder data validation this time. I'll not select list. When do I select list is when I have the sourcewith me this time. I don't have a source. They can enter any numberfrom one to hundred and one any number they cannot selectanything below one and anything beyond 101. So what I'll do is I'll givethem a number as between. So first I'll selectwhole number allow whole number between why between because I don't want them to putanything zero or below zero. So what I'll do is betweenminimum should be one maximum that they can entershould be 1 0 1 So as soon as I enterthe minimum and the maximum data and click on ok, now if I enter any databelow 1 I will get an error or if I put data Beyond 101. I'll again get an error. Now. How do I ensurethat the students know that what numbersthat they have to put if they put to that is okayif the 42101 that is also okay. So I have to tell studentsthat please enter the data only if you have a registration formnumber till hundred and one so what I can do in againin the data validation window, there is something called as in the settings youenter whole number then between and the numbersnow there is something called as input message inthe data validation window here. You can input a message for the user who's goingto use this form. So here in our caseit is the students. So I will enterregistration form or registration number till 101. So here I am saying theregistration number 12 on 01. I can also put an input message. This was the title that I'm saying or I can justput a registration number. And then I would sayinput message as please. Enter numbers between one 201know forms will be accepted if your registration numberis not between the prescribed or number is beyond 101. So this is how youcan make the changes if you realize that I have madea spelling mistakes. I can go back put inthe input message remove click. Ok.Now this is for telling them that as soon as Igo to the cell, it will automatically give me that message as soon as I goto any of the cells here. However, I'm not selectedthe whole form. So what I can do is I can goback and enter the message here, or I can just copy the datafrom your till the end. So what will happen is now? As soon as I goto any cell in this column, it will automatically giveme that input message here. Next is I can do isI know that by now that I can only enter 101 I have already in puta message for them. Another thing that I can do onit a validation is I can decide what should be the error messagethat should occur. So if I put anything like 1 0 2 this isthe kind of error message that you get Microsoft Excelthis value doesn't match with the data validationrestriction defined for this celldoes not very clear. It is a default message that comes for any errorin the data validation so we can enter a messagefor the user so that they'd know that what error they have madethere is one option you can see in the style you can keepit as strong because you are stopping the user to enterthe data in the title. You can say error and then error messagesor you can say warning or not wanting youcan say something error. And then in the error messageyou can say please enter data between 12101. Okay. This is how you can do it. So what happens is nowwhen Go on to this and enter 102 Cor the error messages change, please enter the numberbetween 12101 you can customize the error message. Also on the datavalidation screen. This is how you can createa data validation and try and make it asper your convenience. Let's move on to the nextone or in this case. We can do one more datavalidation that I have here. We need to ensure that no entry that we are seeing yourfor the registration number if anybody is going to put theregistration number your they should not be able to put any duplicate registrationnumbers or rather. Let me go to a new sheet. Let's say I havea employee code to be entered. They are named Department. I want to tell the employees that please enteryour employee code your name and the departmentand the sheet. However, I need to ensure that no employee entersa duplicate value in the employee grade column because every employee shouldhave a unique employee code. They may not do it purposely but it may happenthat there by mistake. They would entera wrong employee code. That could be a duplicationto the employee code. That is somebodyhas already entered So to avoid that I can use the datavalidation feature again, I'll select the column where I wanted applicationto be avoided click on data validation underdata validation in the settings. I will use a formula what formula depending on whatis my requirement. I can always use a formula so to identifyany duplicate in a Cell. I can use a countif function. So when I use a counterfunction, I identify how many times a particular datais coming in that range. So what I do is I can selectthis range comma my criteria is meta so I want to understand how many times the metacomes into this picture see there are two methods so it will give mea result as 2 so I will use this counter methodto my advantage how so I will collect the counter these employee codecolumn go to data validation on a data validation. I will select customin the custom. I'm going to put the formulais equal to countif and to bracket. What do I need now? I need to identify if these range containsa Duplicate it. So I select a to a commaa the same way. So what I am goingto say is why now, I know that countif countsthe number of times it occurs. Now, I want to ensurethat it doesn't go beyond one. The data should only occur once so what I'll dois is less than two. I can see or I can say is lessthan or equal to 1 so it can be one time but it cannot be more than 1 so what I can see it hasto be less than 2 so that means what will happen isor less than or equal to 1 so 1 less thanor equal to 1 that means if this answer to thisis 0 which is okay. That means the data is not occurred asyet if this is equal to 1 that means one time that employee code has beenentered that should be fine. But if it is more one time,then it should be me an error. So I click on ok. Now when I enter the data goto data validation under data validationin the Customs. I'm going to put a formulais equal to countif we all know how to use the count. If so select all column comma because we are startingfrom the first cell so I will have to givethe first cell has also as the one D cell is lessthan or equal to 1 once I do this I click on. Ok. Now if I enter the informationtwice it really me an error so you can see that I have already entered 12345 is an employee codefor one of the employees. If I put again one two,three, four five. It will give me an error. Now, you know that you can customizethis error message. Also. This is how your customdata validation works and you can add morethan this formulas which we just learneddepending on what is your requirement movingon to the next one on our list is the what if tools so what if analysis this sitsunder the data tab in the Excel ribbon your so there are three options under what if analysisone is goal seek. Another one is data tables and a third oneis scenario managers. So how does thesethree functions work? So let's start first start with the gold seek function goalseek is an inbuilt Excel tool that allows you to see how one data item isin a formula. Impacts another youmight look at this as a cause-and-effect scenarios. It's useful to answerwhat if type question because you can adjustone sell entry to see the result a tool is youoften use in finance sales and forecasting scenarios, but there are other uses. Let's look at a simple example, the current model containsthe following information number of units 500which is being sold. The retail price of the unitis $25 Spelling's discount that we are giving on this or we have already givento the customers 10% and the revenue we haveto calculate so however, I calculate Revenue by deducting or removing a new saleprices retail price, which is 25 minus 10% of $25. And then I have multipliedit with 500 units. So the total revenue it comesto $11,250 Now using goal seek function. I need to identify how many units have to be soldto reach a 20,000 Revenue goal. So our goal is what $20,000I want to reach this goal by how many so my sale priceremained the same my discount Remains the Same. I want to understand that. How can I achieve a 20,000 goal how many units I'llhave to sell to do that SN I go to goal seek. There are three values that I will have to enterone is a set cell that means which cell I need toset to 20,000 the revenue cell because they're I havethe formula which value that means what value I needin this sell 20,000 by changing what which sell anyto change or which data? I need to change. I need to changethe number of units. So I'll select the numberof unit cell I click on OK so it will calculateautomatically and gives me the number of units that needs to be celebratedsymmetry 889 units. So this is how you can useyour goals it to your advantage. Now. Let's see another examplefor the goal seek that I have on my list. So I have datafor water 1/4 2/4 3/4 for the gross profit or rather. I would say the revenue thathas been generated out of it. Out of Revenue. This is the percentage ofprofit rest is all cost at mr. Tiwari other calls that is there or this arethe percentage of profit that we have learnedout of this gross sales. The net profit that we learned isthe percentage of this Crossing. So every time out ofthis one lakh dollars, I have taken 10 percent as a netprofit out of one like 50,000. I got 15 percent asretrofit same 50,000. I got 12 percentat net profit in the quota for we do not know. What is the grosssale the quarter for has not yet occurred. So we are not sure what it is going to bebut similar to this three. This one is alsogoing to be percentage of the gross sale percentagewill be 15 percent this time we have decidedour goal is to get 15% So what I did is I'll haveto enter a formula in such a way that I get 15% of this value. How could I do that is equal to this multiplied by this this thiswill be a my percentage value or this will be the profit valuefrom the grocery as of now. I have entered a netprofit or the total. It for the year that I haveuntil now is 38,500. How did I get that by adding? All the four quartersfourth quarter value? I still don't have what I have added allthe four quarters and got the answer is 38 500Now using goal seek. I have to find out what is the fourthquarter revenue must be to reach the goalof 50,000 profit. So my conditioner Ihave to get this value which I can see that is a it for themby Wonder to 50,000. So my cell will be 50,000. So I go to goal seek my set cellwill be e 13 which is correct. What should I set itto 50 thousand and by changing what I need to change the grosssales this cannot be changed. I have to changethe gross sales and depending on the gross sale. The percentage ofprofit has resided. Here we go. This is how your goalseek functions work. The next we will see nowis the data tables instead of entering the formulas and variables individuallyto compare results. We can set up a data table with 102 variables data tablesare one of the excels what-if analysis features. Let's see. How does it rate up tables work? I have the list. Of sale price is similar datathat we had seen earlier. We have the sale priceat Seventeen fifty dollars. Then I have the quantity that I have soldwith this price, but there is also a discounton this price that is available, which is 10% I've givena discount and the cost price that I haveon my product is 1500. Now I have to understand that. What is the questionis find the total revenue in the discountpercent is change. So as of now my total revenue would be I haveto first identify. What is my total revenuewith this data. So how will I dothat is 1750 is my sale price. I can multiplythis word pretty 5 but if you see there isa 10 percent discount. So what I do is I'll have to - my sale price from the discountwhich is c 2 into 10 percent. We all rememberthe bottom of theory. So we will have to usethe brackets appropriately X. My number of quantityafter ten percent discount. This is the revenue thatI am going to on now. I want to identify if Ichange my discount percentage. What will be my Revenue Whatyou did is going to affect so instead of going and doingit for each one of them. I can just selectand getting the percentage of discount for each oneof them getting an answer. I would rather go to the what- if analysis and doa data table function. When I click on data tables. The first thingthat it asks me is whether you want a rowinput cell or column input cell. What is the difference you'rethe data that we are looking at is the column itis coming in the column so we will have to selectthe column input cell what is changing our discountthis percentage is changing as for the discount so I'll haveto select the discount. However, before we get into thisI will have to select my data where I need the result. If I don't select the data,let's see what happens. It just doesn't understandwhy I need to paste it. So I'll have to first selectthe data go to what-if analysis. I Duke data tablesunder data tables. I'll have to selectcolumn input cell because I'm getting the answersin the column and 10% because my percentage of discount is changingdon't select the percentage from here alwaysselect the percentage from your base data. Click.OK see if you'll see that this is a car mountof prevent you that I'm goingto earn similarly. We have another example here where I have the list of datawhere I have the pale price. I have quantity discountcost price your I have to find the total profitof the discount amount and a sale price changes as given in the table. So what I'm sayingis if my percentage of discount changes from10% to 15% 15 to 20 20 to 25 and more along with that if I also change my pricelike the product price, how am I going to work on that or how am Igoing to get a result as what is the profitthat I'm going to earn? So what I did wasin the first place? I have to gain puta formula years to identify. What is my profitwith the current state first time identifyingmy new sale price this minus this into 10% this this so this is the first how we get the sale pricefrom this I'll have to - 1500 which is the cost pricewould per product once I - this if to putappropriate brackets because it is very important that we know wherethe brackets are to be entered or other than putting fifteen - fifteen hundred helpselect the cell close the bracket into 25. This is a profit that I on in caseI have this data or the sale priceis cost price now. I'm trying to understandthat if I change my discount percentageand I increase my price. So what happens is when you goto a department store, you see a big discounts givenby those departmental stores like 30% 40% 50% but do theycover up their losses? They cannot give you discount of 40% Without changingthe price of the product because if they do that, they will not be ableto cover the losses on the cover the expensesthat they have. So what they do is they increasethe price of the product and then give you 50%discount 60 percent discount. So how do they identify by usingthis data tables method? So I have selected Ihave entered the profit I have selectedwhere I need this data. I click on data tables. Now this time I haveRose as well as input. What are my row input cell rowinput cell is my selling price and my column input cellas my discount the or you get the result ofif I increase my price to 2000 and a discount of 10% Thisis what my amount that I get. This is how you can usea data table to your advantage in order to get the resultinto the next one which is ascenario manager here. I have an example or other. Let's see actually what does a scenario manager do your scenario managercreates different groups of values or scenariosand switches between them. So that means you havea group of I was like if you have decided on something like I need a 10 percentof the interest, but if you want to see that how much profityou will learn if you get 20% of interest that he was an empressthat kind of thing when you want youcan still go in keep checking how you can do that. So let's go aheadand check in our data where there we haveso in my sheet, you will see that I havea information where I say that assume you have a bookstore and have hundred bucksin the store. You sell a certain percentageof the highest price of $50 and the certain percentagefor the lower price of $20. So you've decided maybesome percentage you will sell for a higher price of $60 the remaining you'll sell it for $20now to start doing this what I'll have to do isI'll have to first list down. The number of booksthat I have which I have entered your alreadyto the number of books. Then that person is soldfor the higher price which is and six seventy percent. I can also put 60%if I want to now the next thing that I have to do is I'll haveto put a formula in my list. We're in I'm sayingthat number of books that I have sold at a higherprice and number of books that I have soldat a lower price. And what is the unit priceof which is soldered so I have started putting itin the list where I say that the number of booksthat is been sold at a higher price issixty number of books that are sold at a lower price is 40 why because I'm sayingthat 60% of the books. I have so little higher price. So rather than putting 60 her Iwould calculate the amount 60. So how do I do that by entering a formulaa hundred is a total book multiplied by the number ofpercentage of the higher price, which is 60% So that comes to 60 the remaining will beat the lower price. So what I do is is equalto the hundred which is e total boxwhere it is in the cell A4 - the highest price books, which is thisso it comes to 40 now. What is the higher price value which is $50 and the $20which I have mentioned. You know, what is the totalprofit that I am running out of by doing this sellingthis at the higher price in the lower price, so I'll have to putthat in the formula again. So I start with 60 booksat $50 close the bracket plus 40 books at $20close the bracket again, and this is my total profit that I'm learningby selling in this way. Now. What if I decidethat I understand that now I'm selling the 60%of the books at highest price. But later on I decided if I increase the percentage of the books that I sellit at a higher price to 70% or 80% or 90%How is it going to look like or what is the profit that I am going to learn? What I'll do is I can use isscenario manager to do that. I go to sort of analysisand click on scenario manager. There is an optionto add the scenario so I can clickon that add option. Once I click on ADD. It will ask mefor a scenario name. So let's put the scenario nameas high as truce 60% we are leaving it is 60% for now. What are we goingto change that means? What is that is goingto change our percentage of the books soldat a higher price, which is in B 4. Okay, so I click on ok nowonce I click on. Enter a value for eachof the changing cell that means what will be the valuethat is going to change which is in the before so before has a percentagewhich is 60% 60% in the sense. The value is 0.6 0.6 because so if you do 60 dividedby hundred it would be 0.6. That's the reason I'm puttinga value is 0.6 right click on. Okay. And then this iswhere I come to it 60% but we also alreadyhave the data for 60% Let's put the datafor 70% So I put highest 70% and then againthe same data and the before I click on OK this time. I'll have to put 0.7. Click on OK again. I add one more why because I have more percentagesthat I am looking at now 80% We click on OK it is 0.8. I have three scenarios. Let me add one more whichwill be highest 90% And 0.9. Now that I have come here. What I can do is now I can checkfor each one of them. There is a formulain your as soon as I select which one I want to check if I've said 70% what will be the answeror what will be the that I get for that I can just select the 70% dataclick on show you will see that the data is now changed and highest priceof the book sold at high prices 70 and the totalprofit is now 4,100 similarly. If I do to 90 and click on show it will show methe 90% highest price value. This is how your data will workin case of scenario managers. If I do a summary click OKit will open a new worksheet and in the worksheet, you will see the current valuewhich is there on the steps L, which you can see it is now 90% And so it is showing that but it will also showyou the other percentages that we had decided. It is 60 70 80 90. You can actuallyclick on summary and you can get this datain a tabular format where you can see the datain the format that you want. So you can now decide if it is 80% or 90%that you want to sell their books at this is how weare scenario managers work. Let's move on and seeto the next one. We have alreadyseen the gold seek and the data table saw isthe duplicate removals work on an Excel sheet. If you have a duplicate valuein any of your cells, for example, let's goto one of the values that I can see here. There is a way toremove the duplicates and that is one wayto highlight the duplicate. We have already seenthe conditional formatting how to highlight duplicates. We select the wholecell highlight cell rules and then clickon duplicate values. All the duplicate valueswill be selected here. Now. This was only tohighlight the replicative. I want to remove the duplicate. I can click on data under datain the data tools group. I can see remove duplicates. I can clickon remove duplicates. It will select the whole tablethat we have on the list. You can either selectthe whole table or deselect and select only the ones which you want to removeduplicates from if you select the whole table, it will check if both the data isthe same for example in the first scenario, which is a 2 As meta ends A7 has meta asper this column this both are duplicate. However, if you seethe data corresponding to it, it is radhika. Second one has the Hara. So if you select bothof them together, like say I goto remove duplicates. I select both the cellscolumn B and employee full name, which was these two lists. It will check if both the data is same that means metal Medicaand this is also met America only then it will deletethose duplicates if they are. Not same it will not delete. So if you want to deleteduplicates depending on the data in column Awhich is employed full name, then you will onlyselect employee full name and click on OK, it will remove the duplicatesand it will tell you that all the duplicateshave been removed. It will remove the duplicatesfrom the next corresponding value as well. This is how you removeduplicates work in the Excel. Now, there are some demos that you can gothrough to understand or practice datasanitization through Excel. What is data sanitization youare trying to send to me. Good look better and even workon it in such a way that your data looks better and you are able to getthe desired results. So the first thing that we are going to seeis the data sanitization is a sorting any data that you have in the listcan be sorted as per your requirement. So for example, if you ever goto a departmental store, every department hasdifferent kinds of products like the grocery department will have all the groceryitems vegitables pulses and but out of thatalso the pulses will be in one section vegetables will be in one section truthwill be another section. Similarly Excel alsodoes the same thing when you do the sort it will divide itself depending onwhere it should sit together. So one group will come together. For example, if I click on sort for this dataand then I sort as for the employeefull name it will look at where each one of them sit. So you will see that a is will come togetherbees will come together C's and D's will cometogether accordingly. They Resorted to seea better example that we have on our listis I have the list of the employees nameswith the our department and they are region that is been given in the list. I have this list herewhere I have employee code. First name last name Departmentif I want to sort the data as per the region I can goto data click on sort and then I can selectfrom the list. Which data I want to sort itthrough so sort by I say region and it was at that meansyour alphabetical order. So I click on OK itwill sort as per the region. So all these two will be together all the northwill be together. Then your vest then your South. This is how your data willbe sorted as per the request. Next is in the listis the filter. How does the filter work? So you will see the data you must have seenthis a lot of time that in the data youhave this filter option. When you clickon that filter option on filter be appliedto the or data. There is a kind of a dropdown from which you can select the datathat you're looking for. So for example, if I'm looking To get only the data for the particularDepartment say I'm looking to filter the data isper department sales so I can go to departmentand then click on sales or type sales in the box, which is given to me this way. You will be ableto filter the data. Now if I want the data for sales and finance I can againtype the data in the finance and click on ADDcurrent selection to the filter and click. OK this way. I will also get the datafor fails and parents together. Other than that, there are other things that you can do with the filteris one is called something called as a custom filtercustom Filter Works. When you click on anyof the data dropdowns that you can seeunder the drop-down. We will see something called asa text filter under text folder. You have the customfilter custom filter will work when you havethe data something like if you want to have datasimilar to saying greater than equal to less thanand those kind of information which is more usefulin case of the numbers. So let's go to the salary column and trythe number filter greater than say I want to knowthe salary employees. I want to Employee names asfor the salary information. So for example, I need to know all the employeesor filter only employees earning salary more than 10,000. So I select is greaterthan and I want salary who's less than 20,000in the less than column. I would enter 20,000. So I have selected two differentcells one was the greater than because I'm lookingfor more than 10,000 and the next one isis less than 20,000. So I think it should bemore than 10,000 but it should beless than 20,000. I click on OK onlythe salary column that contains morethan 10,000 value and less than 20,000 will be displayed in this listor will be filtered accordingly. This is how your datacan be filtered. Let's move on to the nextone reptak straps the text which extends beyond the cellor the boundary. For example, I had an exampleon my sheet like this one. So I have a scenario managerShi T. Oh, we're in I have entered some datain one of the cells. If I enter some datain this cell, you can see that the datain this cell is hidden. Why because the cellvalue is going. Beyond the length of the cellor the width of the cell. So how do you ensure that you can see this dataclearly you can go to home and under home you'll seesomething called as the under the alignment group. You will see somethingcalled as wrap text. When as soon as you do webtext, it automatically wraps itself in that same cells makesthe row a little bigger so that you can see the wholedata in that same cell moving to the next one isyour merge cell. What is what cell looking at the same example yourI have wrap the text but you can see that the datahas become little bigger. Now this question is applicableto this whole data. If I don't want to show that in one cell I can makeit into two or three different cells together. So that becomes this rowwill be little smaller and you will be able to seeit together so I can merge these three cells together. Now what happens isthe data is still visible with a smaller row heightand you have merge the cells. So that means you have madethree cells into one. This is how you canLarger cell again. The demo is been given. What is the next one as you're insertinga comment in the cell? So for example, like we sawin the data validation how you can insert a commentin the data validation. You can normally inserta comment in the cell. So yeah, I want to say thatthis is the unit price of a book so I can just clickon insert comment. As soon as I dothat I will get this pop-up box where I can sayunit price offer book and I press anywhere outside. My soon as I go backto that cell just Mouse over that cell it will give me the cell data if I wantto just edit that comment. I can click on edit comment and then make the changes asper the requirement. If I now want to deletethe comment I can right click and delete the comment. This is how your commentswork moving on to the next one insert shapesdepending on your requirement. You sometimes will needto insert the shapes on your Excel sheet. There are different typesof shapes that you can use your under the insert commandunder the illustrations group. You will see something calledas shapes under shapes. You have this Arrow crosssquare boxes round circle, even the block arrows. All this information can be usedthe call-outs are there which you can usefor your advantage so I can just usethis kind of a shape that you are requiring and then make the changes asper in the shapes. You can add comments youcan add details into that. So for example, if I'm addinga call-out I want to say that this is my unit price. I put a call out. I can change the call-outDirection like this enter a comment in there. How would I commentor a comment by right-clicking and edit text? This is the price of one unit. See this is how youcan see the call-out. So instead of puttingthat command you can also use a call out a new youcan do a lot of things with the shapes moving onto the next one is the insert text boxsimilar to the shapes. You can also inserta text box there. You will not get any colorsand all it is just the text box. That means youcan add a text box and enter your data asper the data that you need. So I put a text box here I enter the data it will Allthe grid lines at the back and you will be able to seethe text box on your sheet. Now, we will move onto see the advanced filter. How does the advanced filterwork Advanced filter is very important scenario. If it is just like the similar to our custom filter Advancedfilter also has its benefits and it can work with the morethan two or three conditions that you have on the cellwhere I have my example. I have the employee codedetails the first name last name Department regionis being given to me with the salary information. First thing that I have to dowith the advanced filter is to create a criteria range. That means I have to createit correct area table when I created a creditable Ihave to see what is my criteria. My first criteria is department. So I copy the department heading you're my secondcriteria is the branch. So I select the branchinformation you filtering with the advanced filter. So I have to create the tablebut always creating the table. I have to copy the headingof that particular criterias once I have selectedThe Heading now I have to enter what is the criteria. Just ensure thatthe spelling is correct. Otherwise, it will giveyou an incorrect answer. Now I go to data under the filter sortand filter option. There is something calledas advanced under Advanced. I can select twooptions first option as the filter the list in place. That means when you filterit the filter will happen in this table itself. Another option is to copyto another location. First thing I'll do is copyto another location once I do that then itis asking me for the Lestrange that means which dataneeds to be filtered. My data needs to be filtered is a 1/2 H 101 whichis already selected. Now. Next one I have to enteris my criteria range. I told you that I have to create a tablewhy I have to create a table because I have to enterthis criteria range you so to enter the right arrange and select the criteria tablethat I have created. Now, I have to tell Excel because I said that I wantto copy to another location. I'll have to tellExcel that aware. I need this datato be presented. So I will select the cell where I want this dataand click on OK all the data which is or all the rows which fulfills thesetwo criteria will be. A studio similarly. I have another example herewhere I have the list. What is my condition? I have to see firstAdvanced will display the list of employeesand marketing department. So what is my first thing that Ihave to do the department? So again, I'll copy thedepartment and put it in this. What is the requirementwhat is marketing department? So I will say m KT G. That is what do I haveto enter your next I have is the South Region. So my region ismy second criteria. So I select region and the criteria is whatsouth then my third criteria that I can see is I haveto see their earnings are salary between fifteento twenty thousand and twenty five to ten thousand. So first thing that I have to enter isa salary I'll copy that salary because it is easier to copy it if there isany spelling mistake, it will not giveyou that answer now when I copy and paste itthe salary the first information that I have to putis more than 15,000 which is very clearbut we have another request here more than 15,000 but itshould be less than 20,000 just like we did itin our Advanced filter we have to put both but I cannot put itin the same cell. So I have to put another cellwhere I say salary again when in that cellI'll say less than 20,000. Similarly. I have another requestwhere it says that this we more than 5,000but less than 10,000 all the other conditionsremaining the same. So again, I'll say marketing. I'll say south and thisis my other two condition. Now, I can go backto Advanced filter. I select those Phil range copyto another location select the criteria range asthis range and then where I want to paste it, I'll paste it in another cell which is say this setand click on ok.now everything that is fulfilling. This condition willbe pasted you. This is how we areAdvanced Filter Works in Excel. We are going to startwith the statistical functions. The first oneis average function. What is the averagefunction do it's an inbuilt function in Excel that is categorized with statistical functionaverage does exactly what it says and works similar to some it will returnthe average value of the given seriesof numbers in Excel. Let's say we need to identifythe average salary paid to the employeesin the organization. Whereas our salary datain the H column. So what I'll do is I'll enterthe average function followed by the Open Bracket in the cell. We need the result in our case. It is J2. So I start with an equalto I enter average open the bracket now, I'll have to select the range of sin for whichI need the average and in our case itis the salary column which is H to Hmy close the bracket and press enter I get theaverage salary as 15,000 538. This is the average amountpaid to each of the employees in the organization. The next function in our list is the medianfunction median function is again a statistical function which Returns the medianof a given number what is median itis a middle number in the set of numbers. That means if I have10 12 and 15, so there are three numbers that I have I haveto Identify the middle number from this so is equal to I'll use the median functionhere just to identify. What is the middle number andthen select the range of cells that are has numbers closethe bracket enter it will give me the middle numberwhich is 12 in our case. It is not the highestit is not the lowest but the middlevalue the same goes if I want to knowwhat is the middle value in the salary column. I'll use the median functionin cell L2 is equal to median open the bracketselect our salary column which has h2h close the bracket and enter 15 thousand sevenfifty is the middle value of the salary column. Next we're going to seeis the mode function the Excel mode functionReturns the most frequently occurring numbers in the numeric data set this function onlyworks with numbers. It will identifyamount or the number that occurs maximum timein the range of cells. So for example, here we have the basic salarywhere I can see the highest number is 49 thousandand then there are few. Employees orderingthe same salary, but I want to identify which salary is been earnedmaximum time by the employees so I can use the mode function as equalto mode open the bracket. It is only mode selectmode open the bracket. And then select h2h column because I want to knowfrom the salary column close. The bracket 17,500 is the amount that occurs maximum timein this cell ranges moving on to the next one isthe standard deviation function. What is the standard deviation? It's a formula is to identify the standarddeviation of the set of numbers the standarddeviation function calculates the standard deviation in this sample set of datastandard deviation is a measure of how much variance there is in a set of numberscompared to the average of the numbers standarddeviation dot s function is meant to estimatestandard division in a sample. If the data representsan entire population use the standard deviationdot P function. So we will look at our standarddeviation sheet your I have a sample data. So sample of few studentshave been picked up. Whoo. I've scored a particular number or a particular marksin the different subjects. So we have taken a sample and we have not pickedup all the student data. So if we are using it for a sample only we will usethe standard deviation which is standard deviation dots it estimate standard deviation based on the sampleignores logical value and text in the sample. So it has to be only numbers. Okay.So it's standard deviation dot s when you have a sample datawhere you just want to identify how much variance is there as compared to the average you will use threestandard deviation rods s but if you have a fullpopulation that means if you have all the marksof all the students in the school or the college, you will have to usestandard deviation dot P, which is alsotheir your calculate certain division weighton the entire population given as an argument. But your we are usingthe standard deviation for a sample data. So I select that and then for which Ineed a standard deviation. I'll just go and selectthat particular range. I close the bracket. I press enter it is givingme four point eight nine, which is a standarddeviation value for that scores. So this is how your standarddeviation works next function that we are goingto see in our list when we are going through. Our function list isour large function. What does a large functiondo large function Returns the nth largest valuefrom the sample, for example, if I go back to my data where I have the salary dataor the employee salary list, I have to identify. What is the highest salary that I'm paying I can do itby using the sort and function but it can be that as of now. I have sorted this data, but later on this personas Aishwarya Trivedi has moved to a differentdepartment director sheetal. Desai has left and Russia Trivedi has movedinto being the in the board of directors list. Not only Asha wehave also identified. Fried butter to moveinto the board of director. So now we haveto board of directors but both their salaries will bea little different say I will change the board of director salaryto 51,000 forwarder and or a schwa Trivedi. It is about 60,000. So this is the salaries that we have decidedfor the daughter of director, which are twoboard of directors. Now in this case if I have to seethe first Value First find out what was the largest valuein these salary section without changing the two values that I had now so is equalto large open the bracket. I'll select the arraywhich is our salary and K when I see what does the K meank mean I have to tell which highest number that means if I want to knowthe first highest value or the first largest value from the list I can putthe number as 1 but if I don't want to knowthe first largest, I want to know the secondlargest salary paid to the employee I will Is that were to closethe bracket enter now? If you see the our largest valueis actually 49,000. However, the answerthat you get is 24,500. Why because we are lookingat the second largest value in the range of cells. If I am lookingat fifth largest value, I will put it as 5 and press enter it will giveme the fifth largest value in the set of cells which is twenty two thousandseven hundred and fifty like in our example where I mentioned that ashram very is now movingto their board of directors. Let's not remove weresheetal decipher. Now, she still therein the board of directors. We also move are three veryto the board of directors list and this time the boardof directors salary that I'm moving to a 60,000. So when I say the boardof directors salary 60,000 when I move thissalary now and say that which is the first largestsalary the first graduate salary will be 60,000. The second largest salary willbe 49,000 instead of 24,500. Five because now there iseven one more larger salary the first larger salaryhas become ashes salary which is 60,000 earlier. It was 49,000 wasthe largest so this way if you keep on changinganybody else's salary say I make it to 65,000 justtrue get a gist of how it works. If you will see that earlier. It was forty ninethousand words of largest. Now the 60,000 has becomethe largest salary or the second largest salarysame goes with a small I am trying to identify. What is the smallest salary that I am payingor the lowest salary that I'm payingto the employee now again, I have to give the K valueNucky value again. Like I said, it will be the valueof which smallest number that you want. Is it the first smallest that you want to know isthe second smallest that you want to knowwhat the law is that you want to know isthat the third that you want to do. So according to whatis your requirement? You can put the number thereas see if I put 2 so it will give methe second lowest salary paid to the employeesand The nation which is 7,000 if I make the lowest salaryas even more lower. So let's see. What is our lowest salary. It is 5950. Let's make one of them to 5500what will happen is now if I go back and seethe second lowest salary value it is now 5950 because the firsthas now changed to 5,500. So depending onhow your value is, which is your lowest salary itwill automatically calculate and give you that number. So moving onto the next one, which is our Coral. What is coral or it is the correlation you can see wecan use the correlation function in Excel to find the correlation coefficientbetween the two variables. I'm sure we have all learnedcoefficient correlations in our College days when we were doingmaths in the college if you had optedfor Madison College, you would have donethe correlation coefficient. However, if Not done it,which is not a big deal. The Excel is alreadydoing it for you correlation coefficient formulaare used to find out how strong our relationship isbetween the two data like you're in our casethe relation between the H and the relation between the glucose levelof the person is it that the age increases on thegross level increases or is it that the HD creases in the glucose leveldecreases or is it that the age increasesand glucose level decreases and vice versa? So depending we aretrying to understand what is the relationship formulaalways returns a value between minus 1 and 1. What is the relation ship of minus 1 and 1 why is italways giving you in - on a man? And how is itby forget it or split if your relationship is oneor close to 1 rather? I would say 021 itwould indicate a strong positive relationship a correlationcoefficient of 1 means that for every positive increasein one variable. There is a positive increase of a fixed proportionin the other for example, true sizes go up in perfect correlationwith the foot length. So when your foot lengthincreases your shoe sizes increases, so it'sa proper correlation. That is when you'reone go relation. It is exactly how much your foot sizes increasesyour shoe size increases if the correlationcoefficient is minus 1, it indicates a strongnegative relationship or correlation coefficientof minus one means that for every positiveincrease in one variable that is a negative decrees of the fixed proportionin the other. For example, the amount of gas in the tank decreasesin perfect correlation with the speed every timethe amount of gas in the tank decreasesof your car. So when you running the car if you're increasingthe speed your gas keeps on decreasing why becauseyou're increasing the speed so The gas is getting usedor your fuel is getting used and that's the reasonthe fuel is decreasing but your speed is increasing. So there is an oppositereaction fuel decreases, but your speed increases that's have your coefficientcorrelation ship works if in case of minus 1 if the result is 0 itindicates no relationship at all zero meansthat for every increase. There isn't a positiveor negative degrees the to just aren't related anythingthat is 0 no relation at all one where there isa positive relation that means with the increase theother part will also increase while with minus 1. It is the opposite side with one increasethe other one will decrease that's the three differencesthat you will see. Of course, you will not getthe result as 1 minus 1 or 0 it will be in betweenso we will try and understand if it is between 0 to 1then it is a positive 1 if it is between minus 1 to 0, then it is negative 1and if it is 0 then it is no relation at all. So You've an example in a sheet where I have the age consideredas the x-value age of the person and then I have the glucoselevel of that person as considered as why weare just trying to identify. What is the relationshipbetween the human age and the glucose levelin their body. So if you see the human agethe X level it says 43 the glucose level goes to99212 glucose level shows a 65, but does it make senseby just looking at it? Because we can't identify. What is the relationship isit minus 1 is 1 or is it 0 so to identify that I can use a functioncalled correlation function which is coralin Excel in the cell where I need this result. So is equal to Coralopen the bracket and then I will select the different arrays for whichI need to identify the relation. So first relation that I have to identifyfor is the age so I will see. At age against glucose level. So the age is sittingin B2 to be 7. So I'll select that as the first arraythe second array is C2 to C7. So I select that secondary which is e 2 2 Z 7 and closethe bracket, press enter. It is giving me a value is 0.53 with I remove the moredecimal places that I have. So our answer is zero point5 3 which is a positive result. That means there isa close relationship between the ageand the glucose level when it is positive. It means that the increase in age does increasethe glucose levels in the body. If this was a negative point, then it would havebeen the other way around that increase in age. It reduces the glucoselevel in the body. So this is how yourcorrelation function Works. Let's see another examplethat we have on our sheet, which is a correlation function. We have the priceof the stock a that changes or the They changein the price of stock a and then we also haveS&P 500 weekly change or the percentagechange of S&P 500. The S&P 500 isa stock market index that tracks the stocksof 500 large gap us companies. It represents the stock marketperformance by reporting the risk and returnsof the biggest companies. So what are we doing hereis we are identifying if there is any relationshipbetween the SNP change and the stock a change. So if there is a relationshipthey will be able to take a proper decision in the future when they are makingsome Investments. So to start with I am goingto find out the correlation. So I will do is equal to Coral open the bracketnow we have to set of data which is true with arrays. So I'll select the firstarray as this one, which is C5 true c24 commathe second are a will be D Phi 2 D 24 Closethe bracket and press enter you will seethat it is very close to 1 so it is 0.89 or rather 0.9. This means that every timethere is a 1% increase in the S&P 500. Then there is one percentincrease in the stock a prices same similarly if there is a decreasein the S&P 500 the accordingly the stock pricesof a will decrease by 1 percent. That's how theexact correlation is while in the previous example,there is 0.5 correlation. That means when thereis a one-year increase in the H point five percent ofincrease in the glucose level, so that's how the correlationis identified using the coral function in Ms. Excel. Next. We are going to seethe charts in Excel what our charts when you want to graphicallyrepresent some It is very difficultto interpret Excel workbooks that contains a lotof data charts allow you to illustrateyour workbook data graphically, which makes it easy to visualize comparisons entrance Excel hasseveral different types of charts allowingyou to choose one that best fits your data inorder to use charts effectively. You will need to understandhow different charts are used. So we will startwith the column charts. So you'll see allthese different types of charts. So to start we will go backto our example treat where I have the sheetfor column chart that means there is a data that is already available where I have the datafor the region along or the other the sales datafor each region, which is splitinto two years, which is 2016 and 2017. This data is in millions. So you can see that the Mumbai has done a salesof 65 million in 2016 while it has done the saleof 70 million in I was in 17 same goes with London. It has done 55 millionin 2016 while in 2017. They have done 65 million USA. You can see 45 millionin 52 million while if you seeknock poor you will see that the sales have reduced from 2016 to 70 yardhas a smaller data. So you are able to even identifywhere it is going wrong. And how is it related? How is the comparison done? So instead of doing that if you want to sendit to your management, you can just select this dataand create a chart that will show exact comparison on your screen and itwill represent it graphically. So, how do I do that? I'm going to goto insert in 2016. There is a new optionthat has come up which is called asa recommended chart that means wheneveryou select any data and then click on insert. It will give you an optionas recommended chart where the Excel will recommendyou the types of chart that will look goodwith these kind of data or rather. It will properlyIn this data, which you are looking at. So I select the data that I want the graphfor and then I go to recommended chartswhen I go to recommended charts, you will seethat there is a first shot that it is recommending isthe column chart. This is your column chart but as we are now seeingthe column chart would start with or each type of chart. We will start withthe column chart for now and then we'll come backto the recommended charts. So you'll see the firstone the column chart is your even in the iconfor in 2016 first 2D chart that you can see isthe column chart I click on that and if the data is coming next oneanother when is the column chart really useful the column chartis useful in such kind of datas where there isa comparison required so column charts usevertical bars to represent data, they can work with manydifferent types of data, but they are most frequentlyused for comparing information like in our case 2016 and 17. So I have gotthis data as column. And it is comparingbetween the 2016 and 17 now lookingat this chart. It looks very nice. However, you will seethere is something missing as in when I'm lookingat the data for Mumbai. If I don't see this table,I will not be able to identify. What does the real saledone by Mumbai. I'll have to Mouseover it to get the number when I Mouse over it. It says series 2016 .1 by value 65 givesyou all the data. However, if I am justoutside this data, I will not be able to seethe information on in one go and just shows which one isthe smaller value in Gujarat. I can see that in 2017. They have done less sail as compared to 2016but I need the exact number. So for exact number what I can do isone way of doing that is I can go and look at it by doing a mouseover it another way to do that is to just see it Ican add the data labels. How do I add a data label? The different ways to adda data label one way to do. That is first selectwhere you want to add the data label say I wantto add the data label for 2016 and 17 both the bars so I cannot do it together. So what I can do is Ican select one at a time by using the left click and then right clickon that same data and then clickon add data labels. Once I do that it will addthose numbers on that data. Secondly, I can do itfor 2017 also by selecting it. However, there isanother way of doing it. If I now want todelete the data even so I'll just select and clickon delete from my keyboard and it will work now I donot have the data labels here. I want to add it using thisplus sign Sony available in 2016 when I select the chart. There is an option asa plus sign I click on + + I click on data labels. It will automatically adddata labels for both the bars. I don't have to selecteach of the bars. This is how we datalabels are added now if you can seethere is a axis Y, which is given Which has some numbers whyhad those numbers are there because earlier wedid not have data labels. And that's the reason wecould see with this numbers where our basis it's sojust above 60 somewhere between 60 and 70. So I assume that it is 65. So that is how itwas showing however now that I have the data labels, I will not need this informationon right not need this excess on the left hand side. So, how do I remove that excessfrom the left hand side that you can see that there are again one wayof doing that is going to the design mode which is after you collect the chartgo to design mode and then add under add chart element. I will see X'swhich is the x axis that we used to call another oneis the primary vertical axis vertical axis is the vertical which is on the left hand side that we were just talkingabout this one. This is your primary verticaland this is primary horizontal. So I have want to removethe primary vertical as of now, both are Lightedthat means both are there. I want to removethe primary vertical so I select that it just goes away. That's one wayof doing it easier way or the quickest way to do that is just selectthe excess be very careful that only the excessiveselected you will see that for smallball kind of a thing that comes on the four cornersof the Texas and I select that I can press delete deallfrom my keyboard and it will go away third thing that I can see on this chartis the chart title. I have to add a title to this chart is alreadythere on the list. I just have to add the title. I select the data there and addthe title as sales comparison or sales for two years and inthe bracket and put in millions so that they don't get confused at why is it showing isonly 50 40 or whatever? So this is how I can showmy data one more thing that I would recommenddoing is removing. This grid lines. The grid lineswould be more useful when you don't havethe data labels because then it gives Youwhere exactly your data sets with Y axis you'll be ableto see the number but when I don't have I don't needthis grid lines at all. So I'll just selectthe grid lines and then click on delete on my keyboard. That's one way another way to do that is just goto that plus sign and press the grid line gridlines are there by default now, if you see the data, it looks even more cleaner or itlooks even more presentable because there isno grid lines at the back. It's all clean. This is how youcan tweak your data as per your requirement. This is how yourcolumn chart works, but you can do the same thingwith the other charts as well. There are other quick things that you can dowith the chart is why also changing the chart type if you want toor changing the data that you have selected soyou have selected this data in order to get this chart. However, I wantto change the data so I can click on that designthe design you will see under data select data. Once I click on the select datait will give me an option here. To change the data rangefor the chart so I can click on this data range to changethe chart very important part that you will see your iswhich row or column so what happens is as of now my y-axis onlycontain the numbers and the x-axis containthe Mumbai London and everything and the data which is the colorsor the bars which are there that is 2016 and 17, which is the e or is showingin two different bars. Now when I clickon this switch row and column what will happenis all the region which is there will becomeyour color of the bar or different bars while your 2016 and 17will come on the x-axis. Let's see how that happens by just clicking onswitch rows and columns once I do that and clickon OK you will see that 2016 data is showingunder the x axis and all the bars are showing now with the Region's thisalso can be done. However, in this case it isa little difficult to identify. If I had the ordo the comparison between the two years you can docomparison between in 2016. What is the sales that you have done foreach region that kind of a comparison canbe done to identify if you see Gujarat has donethe lowest sale in 2016 while again in 2017 Hyderabadhas done the lowest sell now. So this kind of comparisoncan also be done but depending on what kind of a comparisonyou're looking at. You can always clickon switch row and column you can also use this buttonto switch row and column. Otherwise, you can clickon select data and then use this information. This is how your chartswill work in Excel and how you can add new datainto our data labels how you can format your datausing the different data sets under design moving onto the next chart. Our next chart in the list is the line chart line chartsare idle for showing trends like you have in Our listI have the number of days and the temperature that is changing as perthe number of days on day one. So we are trying to saymaybe in December. So in USA we have this days if you are trying to seethat on day one of December if I start with day one, what was the temperatureand how is it going up or is it going down sothat kind of information if I'm trying to identify I can use the line chart because the line chartwill give me a kind of a trend on different days. So how do I create a line chartwhen we have the data where we have to show the trendin a particular timeframe that time we are going to showit as in this line chart, like for example in our sheet. We have the pace from dayone to D6 identifying. What is the temperaturelike in these days? So we have a trend like in this first date is 43the next day's 53 third date is 50 then 438 again goes upby fifty Seven fifty. Going up 59 and then 60 again. It is 67 video showing a trendof the data in that case. I'm going to use the line chart. So I'll use the line chartby selecting the data. It is giving me the lineand it's showing you how it is going up. So it starts with the day one. It is lower than goes higherand then keeps on going higher higher higher owningthe day three dips a little down but it keeps ongoing after that. This is how yourline chart works. So whenever you havethis kind of a data where you have to showa trend we are going to use the line chart. What is the next one? The next one isa pie chart pie chart make it easy to compare proportioned. Each value is shownas a slice of pie. So it's easy to seewhich value makes up for the percentage of the whole. So for example, I have this book typewhich are the book types that is being soldby the bookstore which is the classic mystery romance sciencefiction and spiritual. The revenue that is generated out of these booksare also listed here for classic It Is $18,500by mistreat is 78,000 970 So on and so forth. I'm trying to identify. What is the total revenue that I have generatedwhich book type is giving me the maximum Revenue while which book type is going been giving methe lowest Revenue to do that. I can use a pie chart because it's going to giveme the part of a pie like your you will see that the orange part is actuallythe mystery part mystery is being sold maximumtime part of the time and the revenue is alsogenerated now similarly like we did itfor the column chart. You can also use the data tablesin the pie chart as well. How do we use the data tablesand pie chart by again using that to plus signon the right hand side or just right clicking and selecting the adddata labels as of now, we'll use the plus signand click on the data labels. Once I do that the data labelswill be added on the list. Now in this case. The numbers are been added. However, I also wantto add the chart title. The chart title is also alreadymentioned as the revenues so we can leave it is as it is. If you need to change that you can always doubleclick inside and start changing the name of the chart as Or the chart titlein the data labels that we have added. You will see that onlythe numbers have been added. I have to checkwhich color belongs to which in order to see where does the highest number land saved onlyfive different lengths. So that is fine. So if there areabout 10 to 12 list and you want to seewhich one belongs to which it is a little difficult to identify that so what I can do is Ican add the chart labels on each of them like a data labels. I can also add the labelson each one of them to identify. How does that look sohow can I do that by going to the data labelsin the add chart elements and then click on more datalabels option as soon as I do that a new window or a new level has opened upon the right hand side. I can select the category name. What will happen is as soon as Iselect the category name. It automatically gives methe category name in the list. Now the category name youcan see is separated by a comma and it is comingnext to each other if you want to change. Separator you can use any of these as a separatorI can use newline. So what will happen is thenumber will go in the new line. So it looks a little betteragain like the earlier one where we remove the xaxis in this case. Now, we have added the nameson the chart itself so I can remove this legendsfrom the bottom. So it looks a little cleaner now I can even makethis a little bigger because we have enough spaceto make it bigger as I have removed the legendsfrom the bottom. So this is how we are pie chartwill look moving on to the next one. The next one is a bar chart bar chart works justlike the column chart but they use a horizontal barsinstead of the vertical bars. Where is it useful? But it is useful in the data where you have the bigger namesof the headings. Like in this case. Yes, the inbound marketingdemonstration at remonstrate Roi if I insert the normal column chart like this nowyou're the data is smaller. So it is just giving youin that same line. However, if youhave a bigger data, or even it will look betterif you have Bar charts which is shown inthe horizontal level like this because you can clearly read the headings hereon the left hand side. And your bar chart goes on the right againremoving the gridlines adding the data labelswill remain the same moving on to the next one. That is a Surface chart. What does the surface chart and how is it usefulsurface charts are useful when you want to findthe optimum combination between two sets of datalike in our list, we have the marketing finance and effort and theyare which field is used or which departmentis used to get these details like recruitment how much you have to recruitin these three departments, which is the financialMarket in the effort how much contribution is ofthe environment in the financial Marketing in the effort how much I sets are therein the financial Market in another effortand the building how much is thereand the expenses that has been formed partof these three departments. So if I want to knowthese so I'm trying to combine the recruitment part. I'm not Trying toidentify the combination of marketing finance and effort how much recruitmenthas been done in the combination of all three in this kindof a situation. I'll be using the surface chart. So, how do I usethe surface chart? Where is this located again? If I go to the Mandate chartalso I will be able to see the surface chart on the recommended chartsunder recommended charts. There is a gapcalled all charts. I can select that go to surface your I'll getthat option of 3D surface. I click on OK you will be ableto see the surface chart your so it gives you the effortmarketing and finance. So you will see that the combinationof recruitment your which is this part is showingyour total recruitment for all the three departmentssame goes with an environment how much environment is affecteddue to finance Marketing in effort asset that is been used bythese three departments and so on and so forth. So how we are addingthe values to this. So this is how it shows youcan again remove the grid lines if you want from this list, so that looks a little better and it also gives a numberas 0 to Which is this part because the 200 only is affected when I say 0 to 200is the effort is 159. That's the reasonto showing as 0 to 200. The second one ismarketing 200 to 400 which is 345 in case of Finance. So that's a bit little bigger. It is showing your orange one. The next one isactually your gray which is 400 to 600 which is goodreally even bigger. So this is how your datais identified by using the surface chart movingon to the next one. So we have learnedhow the charts work in Excel and how you can make a little bit changes in orderto identify the best chart that fits into it. You can also use the change chart type optionto change a particular chart. So if you think that a surface chart isnot fitting into this you can select the chart and click on chartand then use one of these recommendedcharts from the list. This is how we learnthe charts today. Let's move on to the next onewhich is the pivot tables. What is the pivot tableand how Is it useful in your day-to-day lifemany people have the idea that building a pivottable is complicated and time consuming but it's simply not truecompared to the time it would take you to buildan equivalent report manually pivot tablesare incredibly fast. If you have wellorganized Source data, you can create a pivot tablein less than a minute. So, let's seehow you can do that. First of all, I have a sheet with allthe data information. They're like in our sheet. I have the listof employee code their names. So Department region branchand the salary information. I am going to createa pivot table in order to show the different departments salary how much salary are we payingtwo different departments? However, before I do that,I'll have to show you how to create a pivot tableto create a pivot table. I first we'll go to the database for which Ineed to create the pivot table. So I go to the databaseI click on insert after I click on insert. There is an option called pivottable I click on pivot table. New window will pop upin the pivot table wizard. I have to select the table or the range for which the pivottable is to be created. So I select rangea 1/2 H 101 the next one that I have to selectas either new sheet or the existing tree new sheet. That means when I clicknew sheet and click on OK, it will automaticallycreate a new sheet in the existing workbook. And if the pivot table willbe placed in that new sheet. However, if I clickon existing worksheet, I have to give a new location that means the sheetsthat I already have on the workbook from that sheet, which cell do I needthe private to be placed, hence. I will click on the locationand select the location that I need the pivot tableon I can select from any of the existing sheetthat I have on the workbook. However this time I'm goingto use a new worksheet and click on OK. This will create a newExcel sheet for me just before the Excel sheet thatI would working on currently. So it is now creatingthat Excel sheet. Once the Excel sheetis created on that sheet. You will see the pivot tablewizard is already available or a blank space where it will show youthe pivot table number and on the right hand side. You can already seethe pivottable fields that are shown underthe pivottable fields all the headings, which were there in the databaseare have already occurred. You like employee code. First name last name Departmentregion Branch hide it in the best basic salary. So everything is alreadyshowing your now before we startwith the pivot table. We need to understand thisfor fields that are there on the pivot table. So every fieldhas its important. So let's startwith the values area, which was this one. The values area is the large rectangular area belowto the right of the column and the row headingthe values area calculates and counts data. That means anything that you enter your itwill give you the calculation of that value. For example, if I pick salarywhich I drag and put it on the values column. It will automaticallygive me the All of the salad you can seeit says some of basic salary that means the total salary that has been paidyou can change this to any other field. For example, if I click on this and click on value fieldsetting I can then give other calculations right count average Max Minstandard deviation anything that I need in this field, I can selectand give that option. I don't want to someof the salary. I just need to know the countcan click on counter. It will give methe count of that cells which has the salaries. So I click on somefor now the data field that you drag and dropyour are typically those that you want to measure Fieldssuch as sum of Revenue count of units or averageof price row area, which is this part placing of data field in the row areadisplays the unique value from that field down the rows on the left sideof the pivot table that means because I said that I want to do itor the department when I drag it to the Rose. It will pick up allthe unique value. So even if adminis coming 10 times in our database like admincomes a lot of times here, it will only show Oncein the pivot table same goes with CCD to same goeswith the director. What does that mean? It will group allthe admin together. Then it will some the salary of Edmund the next cellwhere I have done the values and it will give methe answer you're so it will giving methe salary for only for admin then forCCD then for Director of Finance marketing Personnelat Wendy and sales. This is how yourdatabase will look if you put the datainto the rows area. Next is the column areaa column area is composed of headings that stretchacross the top of the column which is this so if you want to givea heading other than this if you want things like regionto be shown in the column if I put column what will happen is now it will show me the East-Westnot doubt you'll be split as per the east west northsouth in the column and it is kind of heading that is givenon the top like for East and Even this is a total salary. This is how itis giving you the split. Next one is the filter column or the filter air filter isan optional set of one or more drop-down list at the topof the pivot table here. The filter area containsthe region filled the pivot table is set to showall the region like this if I put it on the top here, it is actually an optional area, but it will only show methe region on the top then it will not show in the column. The filter area allows youto easily apply filters to the pivot table. For example, if I leave my regionhere and try to put a branch in the filters column. I can select which branchI need the data for for example, if I need only for umbrella, I can click on ambala and clickOK it will only give me data for ambala same way. If I select the rest of other data it will giveme the less for other if I want more than one data. I'll just clickon select multiple items at is can select to 3 at time 2 and then it will befiltered whisper the branch. Give me the rest of the data. This is how your pivottable actually works. Like I said, you can use these value fieldsettings to get the other calculations to be donewith this value field setting. This is how your dataor the pivot table works. Now. This was a simple partof the pivot table. If you ever want to sorton the pivot table. Let me first remove the regionto make it look easier. So do understandingwill be more easier. I'm reviewing the branchfrom the filters as well. I'm just leaving the department and the someof the basic salary. Now, what I'm trying to dois I'm going to filter as per the salary. So I want to understandwhich department I am giving the maximum salary. I select the salary column or Ijust click Mark keep my cursor in the salary column I goto data and the same way as we do the sort. I'll just click on sort there. Once I do that it will give me a wizardwhere I have to select whether you have to sort asper smallest to largest or largest to smallest Iwill do is largest to smallest and I can see that in the sales department. I am giving the maximum salarywhich is 3 lakh Forty Five. 175 so this is how your body worksin the pivot table. So there are other thingsthat you can also use in the pivot table which arereally really important that we are going to see here. There is somethingcalled as a slicer and pivot table. What how what is Slicerin pivot table? And how does that work? When you go to analyzedata there is an option called as insert slicer. If you are anywhereoutside the pivot, you're not keepingyour cursor in the pivot. You will not be able to seethe analyze button to go to the analyze button. You have to clickon the pivot table and then click on analyze once you click on analyzethat would be an option called as insert slicer nowinsert Slicer in the sense. It's a kindof filtering of data. So the same way similarlylike I did it for putting the branch on the topor the region on the top where I could filterthe data I can use the slicer to filter this data. So if I click on slicer and select which fieldI want Filter it with like if I want to filter itwith the hire date, I can selectand hide it and click. Ok. Now it will give me a slicer or the list of higher dateswhich I can select from. So if I select onlythis hire date it will automatically give me datafor only the employees who have been hiredon this particular day. So this is how weOtis Eliezer Works. However, now you can seethat in the slicer, okay, I can only select one dataor one date at a time. But if I wantto select more dates than there is this somethingcalled as multi select which is on the top righthand corner with this is only available in 2016 version. So when I click on multi-select, it will give me optionsto select multiple dates at a time in the slicer. So it is not necessary that you have toonly do the dates. You can also addsome more information other than just the datesclick on analyze I go to slicer again and now Ican select anything else that I want nowdepartment is already. Be there I can selectmaybe Branch this time. So it will giveme another slicer so you can seethe another slicer which is their year. So there are two slicesnow so not necessary that you can you haveto only work with one slicer. You can work with two slicesor multiple slices. I select Calcuttaand the are chilling and it gives me data for the replica in Darjeelingfor these two dates only if I select one more dateit will give me data for Calcutta Darjeelingfor these three dates. So the slicers can bemultiple slicers on the list and it will give you informationas per your requirement how to create a chartfrom the pivot table and it also looks the sameas a normal chart. So when I click on the database where I want to createa pivot table from and I then click on the pivot chartby going to analyze button and then pivot chart it will automaticallycreate the chart that I want to the similarly as we get the column chartin this case so I click ok Once I do that, it will automatically createyour chart in the chart. You can similarto the normal chart. You can give the name as a charttitle that we are looking at add the data labelsby clicking on this plus sign and click on a dataor just add data labels. Once you do that the data labelswill be added all these fields which you can see someof the basic salary Department if you don't wantto show it on the table because it isa pivot tables chart. It is giving youthese information. Also if you want to just hidethat you can right click and click on hide allfeel buttons from chart. It will automaticallyhighlight those fees. Now. If I add any additionalinformation like in this one, you can only see that the sum of the basic salaryis given there is nothing of the row column. So what I can do isI can also add data in the row once I do that it will automaticallyupdate the pivot chart as well and give me the information asper the column also now again, like we did it in a previous one where He had usedor change the chart type by selecting the data andswitching the row two column. You can use a similarfunction here to switch from row two column. So here the data will showin a different way altogether. So as you update your pivot table your pivotchart also keeps updating and all the same functionalities that is availablein the normal chart is also availablein your pivot chart. The only difference is that it is linkedto your pivot chart. The other data is linkedto your database. That is the only differencebut pivot chart when you are looking atthat it is very very useful because your pivot chartson updating as per the new data that is updated their also, you are only giving the information or you're onlyrequesting the information that you want in the chartrather than everything coming into the chart and then you selectingwhich parts you want to display. So that's how your pivot tableand the pivot charts work if they are really really usefulin your Today reporting that you do for your company's and it will reallyhelp you to do that. Now. We have also learnedhow to do the pivot table and how to use the slicersin the pivot table. There is another important partof the slicer is that you can linkto Pivot tables to the slicer. For example, your Ihave one pivot table which is slitting the department and giving you the totalsalaries as for each department. Now, I'm creatinga new pivot table from the same database. So I just copy and paste that will also giveme another pivot table but I do not wantDepartment this time. I want to know the salaryis given to the employees in different region. So this is split asper the region the slugs are that I can add here isby clicking any pivot table. I can select and thenclick on analyze and then click on insert sizeit the slicer will be as for the region or the dateor the last name which ever you wantto as of now. I have deselectingthe branch and click on. Okay, once I click on Branch it will give methe options of the branch with which I can select and analyze the data but what I need now is I wantto link this pivot table, which is a pivot tableto to pivot table 1 that means when I select ambala, you will see that only the can pivot table has givenme the data for ambala, which is a North Regionthe first pivot table stays as it is because this slicer isonly link to pivot table to I want to link this slicerto both the pivot tables. How can I do that? I will have to clickon the slicer right click on that then clickon report connections. Once I clickon report connection. It will give me the list of pivot tables availablein your workbook. Now, we know that the in their feet onewe have both the pivot table 310 is not our treat. So both the pivot tablesare in treat one. So I'll select boththe pivot tables from sheet 1 and clickon OK They do that now when I selectBangalore one branch, it will automaticallygive me data for that one branch just like that coaching itwith me giving me data for that one branch samegoes with ambala or a liquid and if I select more than one itwill give me all the the one selected and you me datafor that two or three branches that I had selected. So basically what I'm trying to say is you can connect twoor three pivot tables 21 slicer so that we'll be able to get the filters with one slicerfor both the pivot table. So this is how your pivot table isor the slicers are useful in case of trying to getthe filters for your data. The next topic that we are going to see today isthe data analysis using Excel. So how to do a dataanalysis using Excel. So there's something calledas the analysis toolpak in Excel through which youcan do the data analysis. The analysis toolpak isan Excel add-in program that provides data analysistools for financial statistical and Engineering data. Analysis to loadthe data analysis. Toolpak. You have to executesome steps in Excel. So how do you do that? Let's go to our Excelon the file tab. I click on the options. Once I click on the options under the add-ins selectthe analysis toolpak hand. Click on OK. So when I go to add insunder the analysis toolpak that you see here under the name where you see add-ins selectthe analysis toolpak and click on go buttonor click on OK button. Once I click on OK button what happens it will openthe new data for you or the new window for you clickon analysis toolpak and click on go. Once I click on go. It will give you that optionof analysis toolpak analysis. Toolpak. We be a euro currency tools and the solver add-in Iwill add the analysis toolpak and click on OK once I do that now Iwill be able to see that under data. There is somethingcalled as data analysis add it to my Ribbon, so you can now click on the data analysis to createyour different analysis that you want to dofrom the list that you have. Now. Let's see how we can createdata analysis using the Excel data that we have. So what do we have hereon our Excel sheet? I have a list of data. Like I have the quantity salthave the quantity sold the price of the productand the advertising. Now this example teaches you how to performa regression analysis in Excel and how to interpretthe summary output. So now in the below data wecan see here on the list. The book questionis is there a relation between the quantitysold and the price in the advertisement. So what is it? Is there a relationbetween this and this and the output is so this is the price and thisis a kind of advertisement that we are doingor this is an amount that we are spendingon advertisement is the quantity sold increasingor decreasing or any relation that we are able to see so can we predict the quantity soldif we know the price and Rising so if I know onlythese two things can I predict what will be my quantity sold that is to do such kind of analysis we can usethis data analysis toolpak. So I go on to the data tabin an analysis grip and click on data analysisnow select the regression from this list and click on ok. Now there is a range that supplies some basicregression statistic. So how do you do this? Now? There are two things that wehave to give the input y range and the input X range. This will always beby default blank and we have to givethe ranges to the Excel. So how do you give the rangeand how do you decide what should be the range? So the first thingthat we will give to excel as the Y range select the Y range a 1/2 a a thisis the predictor variable also called asthe dependent variable. So this is something that we have to predict it isdependent on these two things. We do not knowthe quantity sold. We have trying to understandwhat will be the quantity sold if we have the priceand the advertising so your dependent variablewill be your y range. Choose your a 2238 second onethat you have is the X range. These are theexplanatory variables. These are also called asthe independent variables these columns must beadjacent to each other. So whichever you're selectingso I am selecting the 8 these two should beadjacent to each other. Now. I had to ensure whyI'm taking these two things because these aremy independent variable that means this true. I know and this is dependent on the quantity soldis dependent on this truth. That's the reason the quantity comes old is calledas a dependent variable while the price in the advertisement is calledas the independent variable now, we have to checkon the labels and click on the output range boxand select the where do we need the output? So where do I needthe output to be shown? So I click on the output rangebox and then I click on wherever I need the output sayI need the output in 11. So I select that thencheck on residuals. The residuals willnot be checked. You will have to checkthe residuals and click on OK once I click on OK the answer. Be displayed in thesummary output box here. What does that say? So if you see the results here, you can see that there isa range that supplied some basic regression statisticsincluding the R square. So what is it doing? It's giving you the basicregression statistics regression statistics means we havea trying to find the regression and that's the reason that is what is givingyou the standard error. The number of observationsbelow that information the regression tools suppliesanalysis of variance, which is Anovaincluding information about the degrees of freedom. This is called a degreesof freedom sum of squares mean Square value the F value and the significanceof f now we need the Anova information regressiontools supplies the information about the regression linecalculated from the data notice are calculated from the data. So it says thatincluding the coefficient and read error that same thingthat we're looking at the standard error here, then you have the T stabbed and the probability valuefor the intercept As well as the same informationfor the independent value. So this was for the dependent. This is for the independentvalue that you can see then which is the numberof at Excel also plots out some of the regression data usingthe simple scatter chart. So you can also addthe scatter chart at you want toaccording to Dr. Detail that you need from this. So this is how yourregression analysis work. So this is moreof the statistical analysis or the financial analysis that you are planning to do when you are to the financialdepartment using Excel. What is VB full formof the VBA is Visual Basic for applications. It is a programming language ofExcel and other Office programs. It is powerful and sophisticatedbuilt-in programming language that allows you to writeyour own functions are commands in an Excel spreadsheetthis custom functions and commands help youto ease your tasks and that's why using VBA you can create your own automatedfunctions macros is something which is again related to VBA. So macros are small programsthat records the keystrokes as you perform a task. Set this will be savedin the Visual Basic module which is a type of program file. So when you run the macro, it will repeat the keystrokesthat are saved in the module and thus repeat your actions. This is why they aregreat to automate repetitive tasks in Excel. So let's start with to seehow does a macro work. So I have the sheet with mewhere I have some data which is entered on oneof the sheets treat is called as a sales data on that data. I am going to do some formattingon the sheet and we will record that so that weare able to work on that to start with recording. We need to have a developer tab in your ribbonfor the first time. If you're doing VBA, you will not be able to seethe developer tab here. So how we a developer tab. You can go to theribbon under ribbon. You can right clickon that and you will see something called as thecustomize the ribbon once you click on customizethe ribbon you will see on a new Wizard Excel optionswizard on the right hand side. You will see under customizethe ribbon main tabs under main tabs. You will see all the tabs that are there underthe ribbon in that. You will see somethingcalled as developer and the developeryou will see the box which is unchecked. If you don't have the developerthat means it is unchecked. You have to check that boxand click on OK the developer tab will be addedto your Excel sheet. Once you have done that now, you can see thatunder developer tab. You have a number of options that you can dowith the developer tab One is using the recording a macroto start with in this one. What we are going to learn ishow to record a macro. So that means that when yourecord a macro it converts or it will save these macrosor the keystrokes that you do like if I'm going to this cellI'm going to do sell I'm changing the formatof cell C4. All these will be recorded bythe macro in the Visual Basic for applications. So that is the programming so it will convert itinto a programming and save it under the module forVisual Basic for applications. And then when you re run it it will automaticallyrun the whole program or hold macro and do the repair. Of things that you've beenyou have recorded. So let's see. What is the PBA window first. So to go to the VBA windowthe easier way to do that is go to your developer tab. Click on Visual Basicand this is how your window lookson the left hand side. The first one is 0v bea project window. That means that whateverworkbook or whatever worksheet. You're working on your workbook. It will be by forget itesper first workbook. So if you'll see that your workbook nameis mentioned here. We have booked one as the namethen under the book one name. You will see all the worksheetsthat are listed in there. So the worksheets are sheet 1 sheet 2 sheet3 the sheet one name that is it is sales data sheet to the name itself issheet to that's how that has been shownhere the next to it below that you will seethe properties window. The properties window is Againpart of the Visual Basic window, which also helpsto change the formatting of a little bit on the VBA. These are Two windows that you can seein the Visual Basic to start with we are going to goback to our sheet. Now if you are in this window, how can you go backto your sheet? Which is your Excel tree? Okay, of course, you can togglebetween your sheets by using the All Tab Key. However, you don't knowhow to do that or you can see that you can click onthis Microsoft Excel view icon that you can see hereon the left hand side. You can see the Excel Window that you can see youcan just click on that. It will take you backto the Excel sheet that you were workingon on the Excel sheet for which the VBA is open. Once you go back to your sheet. Now, we are going to startwith the recording again before recording weneed to do one thing is that if you record a macroyou also need to ensure that you are assigningit to a button so we will see that also how you can assign a macroto a button to start with the first we will dothe recording of a macro when you're recordingthe recording is done in two different ways. One is the absolute referencing and another one isthe relative referencing now when we were walking on Excel, we all know how The relativeand absolute referencing work absolute referencing isthe referencing wherein if as per the keystrokewhen I'm recording a macro if I go to cell C4 every time in the absolute referencingrecording it will every time take you to cell C4 while if I do a relativereferencing it will see where I was sittingearlier and accordingly. It will go to that particularcell and pick that cell from the list will explore thiswhile we are recording it. So it will become more clearer. Let's start with the recordingon our sheet sheet. You can see this datato start with this. I will go to the developer clickon record macro this time. I am going to do an absolutereferencing recording. So I will just not clickon use relative reference. I'll leave itas it is by default. It will record isan absolute reference under the macro name. I'll start workinabsolute just ensure that you do not givea space in the middle when you're giving a name. So in this case. We are putting only absolutenext one is your shortcut key to create a custom keyboardshortcut to use in conjunction with the control key to runthe macro enter the desired key into the box next to control +in the record macro wizard while doing this make sure you do not overwritethe existing keyboard shortcuts. Like you cannot enter see your because it is a bad ideabecause controls plus C is in command for copyingyou cannot override that if you do not rememberall the shortcuts for Excel you better not putanything next to control and just click on OK once I click on. Ok. Now the recording has startednow Wherever I Go, whichever cell I go to thatwill get recorded in the macros. So I start by goingto sell d7i color that as yellow thenI go to c9i make that bold and underlinedthen I go to sell which is column f 2 If I right click and I delete now Icome back to cell C9. Once I've done this Iwill now stop recording. Once I click on stop recording. The recording has stoppednow I can go and see what is recordedunder my BB a window. How do I go to be a window by can alt F eleven I clickon all development. You will notice that there is a new windowthat is open up which is the modulesI click on modules as you have module 1 is addedto the list in the module. You will see on the righthand side a new macro is added which is called sub absolute because we had given the nameto the macro is absolute and then the recordings shows as range D7 dot selectand then it says that it needs to be changed thecolor to Yellow then rain scene. I dot select then itmakes it bold then it makes it underlinecolumn F to F select and delete and now last oneit says is range C 9. Select. That means whatever I have donewill exactly show it your now, what I'll do is Letme copy this data and paste it on this list. What I have done iswhatever I had formatted. Now. I am getting the original databack to the sheet to see how does our macro run. But before I runthe macro again, I will first adda button to the macro so that it will be easier for me to run the macro and not keep on going backto the street and run it what I do is I will go to my developer tabunder developer tab. I click on insert under insert. You will seeActiveX your control. I can add a commandbutton from that, which is the first one. I select the command buttona command button is added there. I can change the command buttonnamed by going onto this and making it as run absolute your Ican add a space Also. That should not be a problem. So I click onabsolute run absolute. I'm trying to editthe Can change the name bike right clicking on thatand going to properties? To give a name to that button Igo to command button object and click on edit. I enter run absolute. Once I've done that I've given the nameto that particular tab after I give the nameto that tab or to that button now I can assigna macro to this button. How do I assign a macro? I select that right click on itand then click on view code. Once I view that codeI can add the module that I have hereto that button have copied and pasted it turn to the buttonunder the private sub and ends up betweenthat I'll have to write that's one way of doing it. But that is in case if you want to addthe command button now, there is also something calledas the another button which is the button form control. If you do the form control, it automatically givesyou an option to add the macro button. So for example, if I go to insert Iclick on Forum button, I select where I need that button it willautomatically ask you whether you want to Assign a macro under assignmacro button window. I will just select which macroI need to assign it to and then I click on OKonce I click on OK then I can changethe name to new absolute. I can change the nameto anything your I have selected the control button in this one. I had selected the normal buttonnormal button is better because you are able to add the assign the macro imagewould lie to that. Once I do this now. I will be able to runthe macro by just clicking on this see it wentto D7 it went to dc9 and in the end. It also went to see nine lastit had to go to the C9 button. So that's how theabsolute referencing work. Now if I want to I'mjust deleting this because we don'tneed this anymore. All right, let's see that later. So what I can do isI'm going to now create another button witha relative referencing data. So I'll select this Igo back to the sheet. I go to any other place otherthan say, I want to I'm not in the cell I goto any other cell like you. Sitting in the cell and then run the macro it willstill go back to that cell C9. It will color the same sense and then go backto that sense E9 now. I want to record a macroin the relative referencing. So what I do is I recorda new macro click on record macro same instead of microbial just putas relative this time. I click on OK and then I'll clickon use relative referencing before I do that. I'll have to copyand paste this. Have this data or now. I'm going to record asa relative reference. So I click on record macromy do relative my click on OK. And then I'll have to selectuse relative referencing because this time I'm due to therelative reference same thing. I'll go back to cell D7. I color it as yellow I goto c9i make it bold. I make it underline I goto cell column F to F. I click on delete and then go back to cell C9now go to developer tab and stop recording this time. It was a relative reference. So I'll go backto my VBA window. I'll see where therecording is saved under relative so I can seethat active cell offset. If you see that it is offset and those data itis given there now. Let's start looking at how doesthe relative reference in work? So I'll copy this data and seeif how is it giving me the data as I will first haveto create a new button how I could do that by goingto developer insert. I create a new button this time. It is relative. So I will give the nameas relative relative. Okay, give the name as a relative so we knowwhich one to run now when I'm running the cell. I'll go back to sell this one. Click on seriesrun the exact thing because I have selectedthe same cell as I had selectedwhen I was running the macro now if I'm going anywhere elseam copying this data again. Now I'm not running the Mac OS but what was alreadythere this time. I'm going to run the macroin such a way staying somewhere else like any other cellsee what happened. So it did the same thing. But because I had selecteda different solid win to this cell it made this cell as yellow and deletedanother cell completely which was not selected. So it would justcheck with cell. I'm sitting underand accordingly. It will be relatively lookfor that particular cell which needs to be coloredand which needs to be deleted. That's how the relative and the absolute referencingwork in two different ways. That's about your macrohow you record it. Have you give a button tothat macro or assign a macro to that button? And then how do you recorda macro with a relative and absolute referencing now, let's look at how to debuga code sometimes what happens is what is first of all debuggingthat we need to know now, we've already learnedhow the VBA functions work. How do you record? Micro, how does the VBA look when you record a macrohowever as with the case with any code eventhe macro code can have defects and macro may not run as you expected this requiresexamination of the code to find the defectsand correct them. The term that is usedfor this activity in software developmentis debugging VBA editor allows you to pausethe execution of the code and perform any requireddebug task following some of the debugging tasks that you can do which oneis stepping through the code. How do you stepthrough the code? I click on this. If you code and your we are in the code another wayis also macros, whichever macro thatyou were looking at. You looking at relative. You can click on relativeand then click on step into it will take youto that particular. The first line willbe turned as yellow. So you will know that this is the macro thatwe are talking about now. When I am on this macro, I want to see which oneis running properly or which lines are running properly and which linesare not running properly. So what I can do is I can makethis window a little smaller so that I can seehow the macro is running and how is it showingon the Excel tree? So I'm keeping this on the side to side makingthis a little bigger making the properties windowa little smaller. Once I have that now Ican also see my Excel sheet. I can also seemy we be a screen. So I start by the first one. The first one is alreadybeen immediately if I click on this button the whole macrowill run together, but I want to seeeach line by line. How does the macro run so that if there An erroranywhere I will know where the error is. So I start with this. It's already yellow. I click on f8 to startwith running the macro. As soon as I will click on f8, you will see the yellowwhich was highlighted first this part now itis moved to this part. That means that this lineis going to be executed now, so I click on f8this line is executed. That means it is selectedthat line now I go to f8 again, you will see that it is copying that data is selectedthe data color the data see it is colored now itis going to the next one. You will see that now it is active cell dotoffset range a one dot select. So it is selectedactive cell dot offset. That means it is goingto rows down and - one column behind once it has donethat now I'm doing f8. So whenever I'm doing f8, it is automatically keepon executing that data. So if till the time dry run fatill the time it goes till the end it will keep ongiving you that yellow part now, this is a lot. Thing that it is executedby soon as I do f8. It has talked to you you can seethat now it is already done. This is how you can debugand macro in the Excel now. I have a data with me where you can actuallydebug the macro. What I have done is I havethe list of the employees who have giventhe test would sell. The Excel test is givenin the esper the 10 exercises. I want to create a new sheet, which is a summary sheetwhere I can put the marks that they have scored in eachof this Excel test for that. I need to earn use summary sheetwhere I can see for exercise 1. I need something likethis exercise one then exercise to the next size 3 and so on and so forth and then I shouldbe able to enter the marks you whatever marks that they haveon 10 5 depending on whatever the marks is. This is how it should show so I have written a macrowhich we will see how does this run on this sheet. So I have writtena macro on this. I'm going to first closethe previous sheet. That you don't haveto go back to this. I come here on the macro. You can see that I have written somethingyour I'll start by pressing f8. As soon as I press f8. The first part of the Excelwill be executed which is the sub sheet name. As soon as I do another f8. It will go to the next lineThis is executed. As soon as I pressed sheet dot a DOT name is equalto summary a new sheet is added and the name of that streetis summary now I click on it. It's going to take meto arrange a 1 then if you see it says range a 10lakh forty eight thousand five hundred Seventy-Six dot select. So it's going to the last cell which is 10 lakh forty thousandfive hundred Seventy-Six. Now the next one if I execute it will seethat if the first cell is empty if it is empty, then it will select itand then it will go to the next so it is running eachand every cell so you will seethat it will keeps on running and giving me the numberthat I'm looking for. It will run until allthe sheet names are copied and pasted on this cells. Your V and so your recordor your macro is now run. You can debug a macro if there was an error it willautomatically pop up and say there is an error here. Okay.So for example anything that you enter your andif there was an error, it will automatically poppedwhen tell you okay. There is an error here. For example, I havea summary sheet. I'll just put a namewhich is the wrong name so that I knowthat this is a wrong one. So let me delete and Iwill start our all over again. I go back here and startwith a fate you will see that a new summary sheet is executed now it is givingme an error it says because when I'm doing this, let me start all over again. I'm going back. I'm pressing f8. Okay this one it's alreadycreated a new sheet your when I'm executingthis part of the code. It will give me an error because there isno sheet named sheet 1 now if I click on debug itwill take me to the place where there is a error,even when you're running it. If for exampleif you don't really realize that you have made that mistake,you're not running. By if you just clickon this it will automatically give you an error sayingruntime error once 004. How do you debug click on debug? It will take you to that linewhere the error is now, in this case, we have already created thatsummary sheet is already there. And that's the reason itis giving you you add that item. So let me start all over again. I click on stop now. I run again your itis giving me an error where the error is isn't thissheet this part of this code. That's how you can debuga code to understand where the errors it's so you will know that wherethe actual errors happening. You don't need to go backand forth every time to understand wherethe real error is happening. Now, you know how to workthat is called as a debugging in your Excel VBA functionmoving on now, there is somethingcalled as breakpoint. That's one partof the debugging thing. How do you do a breakpoint isby choosing that data and then So for breakpointbreakpoint in the sense, it will stopat a particular execution. So for example, if I want to givea break point here say I want to sell to stop executing or the VBA to stop executingthe code after this point, which is summary sheetdot select or range A10 48576 dot select after this. This part shouldnot be executed. So how what can I do iseither I can click on that and click outside. It will give me a red mark, or I can just go to that partand press F9 go to that particular line, which you don't wantto be executed. That means that Excel will stop till he heard it will selectthe data layer and then it will not executed next one. How do you check now? I can just click on runit will stop here. You'll see that ithas dropped your why because I have givena break point because I want to seeif the VBA code is running fine. Tell you I want to knowwhere does the issue starts? So I give a break point earlier if I think that the issuedoesn't start your may be here so I can give a break. They'd also by justpressing the F9 or by clicking on that part. Again. That's how the breakpoints Ihave been added to the macro now if I run the whole thing, it will automatically runwithout the break point because I removethat Rick find from that list. This is how your vba'swork in Excel, which is a veryvery useful tool. But of course youare learning the macros, which is the recordingof a macro and how you can use to your advantage. Thank you for thegreat session pressure. I hope you guysfound it informative. So guys. If you have any queriesrelated to the session, please feel free to comment in the comment sectionbelow until then. Thank you and have a great day. I hope you have enjoyedlistening to this video. Please be kind enough to like it and you can comment anyof your doubts and queries and we will reply them at the earliest do look outfor more videos in our playlist And subscribe to Edureka channel to learn more. Happy learning.

## Friday, 12 June 2020

# Microsoft Excel Full Course | Excel Syllabus | Excel Formulas and Functions

**About Now Tech**

Soratemplates is a blogger resources site is a provider of high quality blogger template with premium looking layout and robust design. The main mission of templatesyard is to provide the best quality blogger templates.

Subscribe to:
Post Comments (Atom)

Whether you're just beginning your studies, or are well in to your journey to earning a Project Management Professional (PMP)® Certification, it's natural to feel a little insecure or intimidated by the seemingly endless PMP® ITTO that are diagrammed and discussed for each Project Management process. This one topic is probably responsible for more questions and anxiety for PMP® candidates than any other exam-related topic. I have also read an article which is same as this article ITTO Spreadsheet

ReplyDelete