match the questions and the answers questions

match the questions and the answers questions, match, questions, answers, questions

Hey everyone, welcome back to a whole new sessionfrom Edureka. My name is Wajiha. And in this session, you’ll be preparingfor the 50 most important Excel interviewquestions and answers. So this session will be dividedinto three different sections that is generalquestions, questions relating to pivot tables and then questionsdealing with formulas and functions in Excel so before we begin justmake sure you subscribe to our Channel and hitthe Bell icon to stay updated with all the latestEdureka videos. Also if you’re looking for an online trainingcertification in any of the trending Technologies, check out the link givenin the description box below. So now moving ontowards the first question that is explained Ms. Excelin brief Microsoft Excel as we all know is a spreadsheetor a computer application that allows the storage of data in the form of table Excelwas developed by Microsoft and can be Various operating systems such asWindows Mac OS iOS or Android Etc. Some of the important features of Ms. Excel arethe availability of graphing tools built in functionssuch as some date count if excetera Excel allows data analysis throughtables charts filters Etc. It also provides the Visual Basic forapplications flexible workbooks and worksheets operationsand also allows data validation. So these were a fewfeatures of Ms Excel. So now let’s move onto Towards the next question that is what do you meanby cells in Ms. Excel? So as you can seeon the screen the area that falls as an intersection of the columns and the rowsis known as the cell. The total number of rows that are availablein Excel are 10 lakhs 48,000 576 and the total numberof columns are at 16,000 384. So the total numberof cells will be 10 lakh forty eight thousand five hundredand Seventy-Six cross 16384. So I hope you guys are clear with this so Let’s move ontowards the next question, which is explain. What is a spreadsheetspreadsheets are basically collections of cell that help you manage the dataa single workbook may have more than one worksheet. So as you can see over hereat the bottom of the workbook, you can see all the sheets that are availablein this workbook. So by default,they are named as sheet, 1 sheet 2 Etc in case youwant to change the name of any of these sheetsjust double-click on them and give any nameof your choice. I’ll just say example.

match the questions and the answers questionsI’ll just say example. So now let’s move ontowards the next question what exactly is cell address? So the address of a cellrefers to the address that is obtained by combination of the row numberand the column letter. So each cell of an MS Excel sheetwill have a unique address. So I hope you guysare clear with this. So now moving ontowards the next question of the session, which is can you add new cellsto an Excel sheet. The answer is yes,you can insert new cells into a sheet to add a new cellwhat you have to do. Simply select the cell where you want to inserta new cell and then right click on it go to the insert optionand then select the place where you wantto insert a new cell. So in case you want to shift allthese cells towards the right and then insert the cell before this you haveto select the first option in case you want to inserta cell above some lines. All you have to dois choose the second option. So right now I’m not goingto insert any cells are just cancel this. So now moving ontowards the next question, which is can you formatms-excel cells and if yes then how so the answer is yesMs. Excel cells can be formatted in order to format the cellsyou can use the commands present in the font groupof the Home tab. So what I’m going to dois just open this up here. So as you can see the number group allowsyou to format cells to be of any type such as currency accounting datepercentage Etc alignment allows text control alignment and setting of thedirection of the text that’s present in the cell. Font enables variousfonts Styles sizes colors Etc border allows cell bordersto be changed removed colored and so on you canalso fill the cells with any desired colors. And finally you can alsoprotect yourself from locking and hiding them. So I hope you guysare clear with this. So now let’s move ontowards the next question, which is can you add commentsto a cell yes comments can be added to addcomments to yourself. All you have to do isright click on that. Cell and choosethe new comment option as you can see over herea dialog box will open wherein you can typeany comment of your choice. So I’ll just writedown comment over here. Okay, so as you can see, I’ve added the commentto the cell. Now just a notify that there is a commentpresent in this cell. There’s a purple symbolpresent in the top right corner of the cell.

match the questions and the answers questionsof the cell. So in case you do not wantthis comment anymore just click on the three dots that are presentin the dialog box and then delete the tread. So one more important thingthat you guys have to note is that these comments will bevisible to all those people who have accessto this Excel sheet. Not just that they can alsoanswer to your comments. So now moving ontowards the next question, which is can you add new rowsand columns to an Excel. Cheat yes, you can add new rowsand columns to an Excel sheet while going to do over here isSelect this column right click over it and then selectthe insert option. So as you can see the cellshave just shifted towards the right nowfor a more clear picture. Let me just typein something over here. I’ll say example and then I’ll selectthis column right click on it and say insert. So as you can see a new columnhas been inserted before this column. Similarly. You can also insert a row. So I’ll just writean example of here. Select this row. Go to the insert option. So as you can see a new rowhas been inserted. Okay, so I hope youguys are clear with this. So now let’s move on towhat’s next question, which is what is ribbon. And where does it appearthe ribbon is basically a key interface with Excel and it appears at the topof the Excel Window. So as you all can seeon the screen, this is nothingbut the ribbon tab it consists of file home insert pagelayout formulas Etc. The ribbon tab, basically allows usersto access many of the important commands directly. So in case you want to accessany of these commands, all you have to do isjust click on them. Not just this Excel alsoallows you to customize the ribbon now in orderto customize the ribbon tab. All you have to do isright-click anywhere on this and then choose the customizethe ribbon option. So as you can see on the screen, you can choose any optionof your choice to be added to the ribbon tab. So, let me just chooseanother option over here. I’ll say I want to includethe developer tab and I’ll click on OK. So as you can see over herea new tab has been added to this ribbon similarly. You can selector unselect any option of your choice to be presentin the ribbon tab. So now moving onhow do you freeze panes in Excel Ms. Excel allowsyou to freeze panes that will help youto see headings of rows

match the questions and the answers questions that will help youto see headings of rows

match the questions and the answers questionsthat will help youto see headings of rows and the columns even if you scroll a long way on the sheet now in orderto freeze the cells. All you have to do is selectthe rows and the columns that you intend to freezethen go to the view Tab and select the fries. Inception now here you’llbe able to see three options that is freeze panesfreeze top row and freeze First Column you can giveany option of your choice. So what I’m going to do isjust delete this and over here. I’ll just give some new titles. It’s a name and ID. So now what I’m goingto do is freeze this so I’ll go to the view Taband select freeze panes option. I’ll freeze the top row. So now even when I scroll downthis top row has been frozen and I’ll be able to see it. Even if I scroll a long waydown the sheet. So I hope you guysare clear with this. Okay. So now moving ontowards the next question. How do you add a note to a cell? So in order to add a note? All you have to do is selectthe cell right click on the same and then selectthe new note option. Now you can type in any noteof your choice of here. I’ll just type a noteand then I’ll hit enter. So as you can see over herea new note has been added to my cell and itis indicated by a red triangle that is present in the topright corner of the cell. Also one more thing to note. Is that the name that is shown over here isthe default users name. Okay. So now in case you wantto delete this node, all you have to do isright-click on the same and choose the delete note option. So now moving ontowards the next question, which is can you protectworkbooks in an Excel sheet. Yes workbooks can be protectedExcel provides three options for this passwordscan be set to open workbooks. You can protect the sheetsfrom being added deleted hidden or unhidden and protecting window sizesor positions from being changed. So up next is how do you apply a single formatto all the sheets present in the workbook Excel allowsyou to apply the same format to all the sheets of a workbook. So to do that. All you have to do isright click on any of the sheet that is present in the workbookand then click on the select. Cheats option nowafter doing this if I apply some format to any of the sheet presentin this workbook that format is going to be appliedto all the sheets that are presentin this workbook. So as you can see, I just have oneworkbook over here.

match the questions and the answers questionsI just have oneworkbook over here. Let me just addon a new workbook. And then I’ll select all sheets. And what I’m going to do overhere is go to the Home tab font. I’ll just fill these cellswith some color. So now let me move on towards the next sheetand see what happens. So as you can see over here, I made some changesto this sheet and it’s also been reflectingin the next sheet. So I hope you guysare clear with this. So now let’s move ontowards the next question, which is what do you understand by relative cell addresseswhenever you copy formulas in Excel the addressesof the reference cells get modified automaticallyin order to match the position where the formulahas been copied now, this is done by a systemcalled as the relative. Addresses, so let me get back to excel in orderto show this to you guys. So what I’m going to do overhere is Type in some numbers. I’ll say four and five here. I’ll say six. and seven eight and nine Sowhat I’m going to do over here is Typein the formula in this cell and then I’m goingto copy the same formula to these two cells. So what I’m going to do is typein an equal to sign and then I’ll just give the celladdresses as a 3 plus a 4. And then I’ll hit enter. So as you can see, I’ve got the result for a 3and a 4 over here now in case I want to copythe formula over here as well. What I’m going to do issimply select the cell and drag it down till the cell that I want to copythe formula to so as you can see over here. I have copied the formulaand Excel by default has changed the cell referenceaccording to the position. So what if you do not wantto modify the cell addresses when you have copiedthe formula so in yes, you want to copythe formula without modifying the cell addressesyou’ll have to make use of absolute cell addresses when you use absolutecell references the row and the column addressesdo not get modified and they remain the same. So basically when you haveto use absolute referencing, you’ll have to make useof the dollar symbol before the columnand the row numbers. So what I’m going to do overhere is just change this formula or let me just take a new cell and then I’ll addin all these cells. So what I have to do isinsert a dollar symbol before each of these. And then I’ll hit enter. So now what I’m going to doover here is copy this formula to the other cellsand see what happens. So as you can see over here, the same result hasbeen copied to this cell which means the formulahas not been modified in case I would have usedrelative cell addresses. Then the formulawould have been modified according to its position. So now let’s move ontowards the next question, which is what will you do if you want to changeeither the column letter or the row number but not both. To do this you must make use of the mixed cell referencinghere either the row number or the column letter will changebut not both of them. So to show this exampleto you guys. What I’m going to do isget back to my Excel sheet and over here. Okay. Let me come towards this side. Let me select a new cell. Okay. What I’m going to do over hereis Type in an equal to sign and then I’ll type in a dollarthree then after that. I’ll type in a plus signand then be Dollar Tree. So now what I’m going to dois copy this formula and see what happens. So as you can see over here when I copy the formulathe column Remains the Same and the value of the Rollnumber has not changed because I’ve givenabsolute referencing of here. So I hope you guysare clear with this. Okay. So now let’s move ontowards next question, which is can you protectthe cells of a sheet from being copied. So the answer is yes, you can do it by protectingthe required cells or the complete sheet. Not to do this. All you have to dois select the cells that you want to protect goto this font group of here and then select protection fromhere check the hidden option and then click on OK. So as you can seethere are no changes that are madeto my sheet the formulas that are present in the cell arestill visible now in case I want to apply the changes that I just made allI have to do is go to the review Tab and thenselect the protect sheet option. Excel will ask meto enter some password. So I’ll just givesome random password over here. And then click on okay. So as you can see previously when I used to clickon these cells, it would show me the formulafor both of these now when I click on either of thesecells the formula is hidden when I click on anyof the other cells that are not protectedthe formula is still visible. Okay. So I hope you guys are clearwith this now in case you want to unprotect the sheet. All you have to do is Selectthis unprotect sheet option and then enter the passwordthat you had specified. So now moving ontowards the next question, which is how do youcreate named ranges? In order to add named ranges. All you have to dois select the area which you intend to give a name. And then from the ribbon tab, click on formulas from here goto the Define name command and give any nameof your choice. So I’ll just give vegetablesas the name of this table or I’ll just say veggies. And then I’ll click on okay. So as you can seeor hear the name given to this range is veggies similarly. If you want to create a namefor this range as well. All you have to do is Select that go to the formulasTab and select on Define name then give any nameof your choice and over here. I’ll just say fruits. And then I’ll click on OK. So as you can see Ihave created two new ranges that is vegetables and fruits. So now moving ontowards next question, which is what do you mean by macros Excel allowsyou to automate the tasks that you do regularlyby recording them into macros. So a macro is basicallyan action or a set of them that you can performa number of times. So for example, if you have a recordof sales of each item and at the end of the day, you have to calculateThe sales profits loss Etc. So if you create a macroit will automatically calculate all these values and then you can use the samefor future purposes. So now moving ontowards the next question, which is how do you createdrop-down lists in Excel? So now in case you wantto create a drop-down list, all you have to doselect on the data tab that is present in the ribbon. And then from this datatools group over here select the data validation command. Okay from the given optionsselect list provide the source. So I’ll just providethe vegetable names as a source and then I’ll click on OK. So as you can see over here, I have a drop down listwhich contains the name of all the three vegetablesthat are specified over here. So I hope you guysare clear with this. So now let’s move ontowards the next section which is pie with tablesand pivot charts. So the first questionof this session is explained by wood tables along with their features pie with tables arestatistical tables that condense the data of those tables that Ihave extensive information. The summary can be based onany field such as sales average. Somes Etc that the pivottable represents in a simple and an intelligent manner someof the important features of pivot tables. Are they allow the displayof exact data that you have to analyzethe provide various angles in order to view the data. They allow you to focuson important details comparison of data is very handyand pivot tables can detect different patternsrelationships data Trends Etc. They can also createinstant data accurate reports and serve as the basefor Pivot charts. So now let’s move ontowards the next question, which is how do you createpivot tables in order to create a pivot table? You will first need to preparethe data in the tabular format. Now while preparing this data you will have to keepa few points in mind. The first thing is you haveto arrange the data in two rows and columns. The first row shouldcontain unique heading for each of the columns and the columnsshould have only one type of data present in them. The rose must have a datafor single recording. And there should beno blank rows columns should not be completely empty and the data for creatingpivot table should be separated from the other data that is present on that sheet. So what I’m going to doover here is copy of table that I’ve already created. So as you can see over here, the first row hasa unique heading and each column hasthe same type of data the following rows containthe data for a single recording. So I hope you guysare clear with this. So now let’s move on and seehow you actually create private. Abel’s so what I’m going to doover here is select the stable. And then I’ll clickon the insert tab from here. I’ll choose thepivot table option. I’ll select the existingworksheet option. I’ll give some random cellas the target place and then I’ll click on OK. So as you can see over here and empty pie with tablehas been created. Also, the pivottablefields pane has opened that contains all the fields that are presentin my pie with table now to create a pivot table. All you have to dois drag and drop any of these fields presentin this four areas over here that is Filters columns rowsand the values. So what I’m goingto do over here is drag and drop some Fieldsinto these four areas. I’ll drop names into the rose. I’ll drop the itemsinto columns amount to values. And the filters willbe based on the city. So as you can see over herea pivot table has been created which contains allthe names as rows and all the item namesas the columns also, you can see all the amounts that are collectedfor each item bit by each of these vendors and also the grand totalof each of them. So now moving ontowards the next question, which is what our pivot charts Ms. Excel charts aredata visualization tools that help you visualize the datain various ways these Charts can be of any type such asbar pie area line donut Etc. So in case I wantto create a pie chart for this pivot table over here, what I have to do is goto the insert Tab and then from the charts group. I’ll select any chartof my choice. And then I’ll click on okay. So as you can seea pivot chart has been created for this pivot table. You can also filter out the data according to your choiceby selecting any of the data that you wantto see selectively. Let me just select for Chicagoand I’ll click on OK as you can see overhere by pie chart has been modified accordingly. Not just this you can alsoformat this chart by selecting this option and from here, you can select any type of chart that you intend to see so I hopeyou guys are clear with this. So now let’s move ontowards the next question, which is can you create pivottables using multiple tables. The answer is yes, you can create pivot tablesusing one or more based tables, please note that boththe tables should be present in the same worksheet. So now to show this to you guys or what I’m going to do isinsert a new sheet and over here. I just called behalfof the state data. So what I’ve done overhere is separated the table that I’ve created previouslyinto two different tables. So what I’m going to dohere is Click all plus d and then I’ll click on pain. From this pivot tableand pivot chart wizard. I’ll select multipleconsolidation ranges option and then I’ll clickon next then. I’ll choose the option. I will create the page fieldsand over here. I’ll specify the first rangeto be the order ID is date name and the item I want one field. I’ll just name it as field one. and I’ll add this so nowfor the next field what I’m goingto do is Select this I’ll add it and I’ll givethe name as field to and then I’ll click on next. So Excel will ask you where you want to create itand I’ll say the existing sheet and I’ve selected some cell overhere then I’ll click on finish. So as you can see overhere a new pivot table has been created by takingthe information present in both these tables. So now let’s move ontowards the next question, which is what happens when you check the differlayout update option present in the pivottable fields window. So in case youcheck this option, you will not see Dynamic changes while interchangingthe table field. So as you can seeby default this option is off or unchecked allthe changes will appear only when you clickon the update button when you check this box, so I’m going to do overhere is first show you guys what happens when I make changeswithout checking this box. So just remove the rowsand I’ll put it back over here. So as you can see, I’ve just removed the roseand put it back over here and by pie with tablehas changed accordingly. Now, what I’m goingto do is put it back. And we’ll check this boxand then I’ll do the same. So as you can see previouslywhere I remove the rose and I put it backin this list over here. The pivot tablechanged accordingly. Now only when I hit the update button itis going to change. So I hope you guysare clear with this. Okay. So now let’s move ontowards the next question, which is can you createpivot tables using tables from different worksheets. Yes, if both the sheets arefrom the same workbook. You can create pivot tablefrom the tables present in different sheets to createfive tables from two different. Follow the same steps shownin question 24 and when you’re adding the tables go to the respective sheetand select the tables that you intend to merge. So is it possibleto see the details of the results display itin the pie with table? Yes. It is possible to seethe details of the results shown by the pivot tables in Excel in orderto see the details of any of the results. All you have to do isdouble click on any of them. And you will see that Excel creates a new sheetswith all the details about that result. So as you can see over here, I double-clicked onthe grand total 400 obtained by Brat and you see that Excel created a new sheet giving me allthe details regarding this. So now let’s move on towardsthe next question which is how our pivot tablesused to filter data Excel pivot tables allow you to filter data accordingto your requirements to do this. All you have to dois place the field based on which youwish to Did the data and then from this pivottable filter the data according to your choice? So as you can see over here, I’ve placed the Citi Fieldin the filters area and over here. I can filter out the datafor all the cities or choose any city of my choice. Okay. So let me just chooseNew York and click on OK. So as you can see over here, I have a private table createdonly for the New York city. So now moving ontowards the next question, which is how do you changethe value field to show some of the resultother than the sum? In order to changethe value field to show results other than the some all youhave to do is right click on any of these valuespresent of here or from this Piwould feel stable. Click on this drop-downlist present over here, then select the valuefield settings option and from the given list chooseany total of your choice. Let me just changefrom some to count and I’ll click on OK So as you can see over here, the sum of values has beenchanged to the count of amount. So now moving on how do you stop automaticsorting in pivot tables? So as many of us know Excelautomatically sorts the data present in pivot tables in case you do not want to dothis open up the drop-down list present for the row labels or the column labels clickon more sort options, then go to more optionsand over here and check this box that says sot automaticallyevery time a reporter. It is updatedonce you’re done with that. Just click on okay. So that was the last questionpresent in the section. So now moving ontowards the third section that is questionbased on formulas and functions in Excel. So coming towards the firstquestion under this section. What do you understandby Excel functions functions in Excel are used to performspecific tasks Excel has many built-in functions that are used to calculateresults of various formulas. Thereby helping intime conservation. Also these functions make itvery easy to execute formulas, which would have been If yougo to manually write down, so what are the variouscategories of functions available in Excel? So as you can seeon the screen functions present in Ms. Excelhave been categorized in this list of options that are present over here. So you have categories basedon the date and time Financial functions functionsbased on mathematics and trigonometry statisticalfunctions lookup and reference functions database textlogical information and Engineering functionsCube functions compatibility and web functions. So no talking abouta very important question, which is what isthe operator precedence of formulas in Excel formulas in Excel are executed according to the bodymass roles board mass as many of us knowstands for brackets order division multiplicationaddition and subtraction. So that means in every formulabrackets are executed first, if any of them arepresent followed by multiplication division Etc nowto show an example of this, let me get back to excel. Let me just givensome values of Here, I’ll say three. Four five now in this cell. I just type in some expression. I’ll say a 1 star b 1 plus c 1. And now I’ll hit enter. So as you can see over here, the result has been calculatedby first adding B1 and C1 and then multiplyingthe same with a 1. So basically 5 plus 4 will give me 9 and 3 times 9 is 27 nowin case I modify this formula, which is removing the brackets. And now let’s see what happens. So as you can see previouslyI had a result saying 27 now. The result is 17. So what Excel has done over hereis first multiplied A1 and B1 and then the resultis added to see one. So basically 3 times 4 is 12and 12 plus 5 is 17. So I hope you guysare clear with this. So now let’s move ontowards the next question, which is explained the sumand the summer functions. So now let me just open upthe sum function over here in order to show youguys the syntax of this. So as you can see over here, the sum functiontakes an N number of values as its parameter and it Returns the sumby adding each of them. So now what I’m going to do isjust cancel this and over here. I’ll type in an equal to signand I’ll use some function. I just specified this rangeto be all the values to be added using the sum functionand now I’ll hit enter. So as you can see over here, the sum functionhas added all the values that are present between A1 and D1 similar tothe sum function is a sum of function one difference is that this function is usedto calculate the sum of values that comply to a given conditionnow in case I just change this from some to some if I’ll have to specify the range followedby the criteria. Now. What I’m going to dois given some criteria. I’ll say calculatethe sum of values that are less than 15. And then I had Dentalso as you can see over here, it has added only those values that are less than 15and it has discarded 17. So now moving ontowards the next question which is what arethe different types of count functions available inExcel Excel provides five types of count functions. That is count count a countblack count if and count if s so talkingabout the first function that is the count functionthis function Returns the total number of cells. That have numbers in the range that are specifiedto it as a parameter. So now to show y’allhow this works. What I’m going to do is typein an equal to sign over here and then I’ll make useof the count function. So as you can see over here, it takes all the valuesas a parameter. So what I’m going to dois just specify this range and then I’ll hit enter. So as you can see over here, the count functionhas returned five which means five cellsin this range contain numbers. So now moving ontowards next function, which is Count a if I just change this from count to count a itis going to count all the cells that are not emptywithin this range. So as you can see over here, I had given a very big range that has cells that containinformation plus the cells that are empty but county has returnedonly the number of cells that contain data in them. So now talking about count if the countif functioncounts the number of cells that complyto a given condition. So let me just change itfrom count eight count if and within this range,I’ll just specify some criteria. I’ll say count the valuesthat are greater than 10. And that I hit enter so as you can seewithin this range, there are two cellsthat contain values that are greater than 10in contrast to count a is Count blank count are countedthe number of cells that contain information on the other hand count blankwill count the number of cells that are blankwithin a given range. So what I’m going to do overhere is change this from count if to count blank And there are just removethis condition from you. Okay. So as you can see, there are seven empty cellspresent in this range. So now talking about count if s this is a veryspecial function that allows you to specify a set of conditionsin order to count them. So what I’m going to dois go to the table that I’ve created of here. So what I’m goingto do over here is Type in an equal to sign. Then I’ll chooseto count as formula and then I’ll haveto specify the range for criteria one the rangefor criteria 2 and so on. So what I’m going to dois just select the names. And the name that I wantto select from here is Yvonne. After this, I’ll givecriteria to range. And I’ll specifythe quantity as 24 now. I’ll hit enter. So as you can see over herewithin the given range, there’s just one cell that has a one andhas the quantity of 24 present in it. So, I hope you guysare clear with this. Okay. So now let’s move ontowards the next question which is how do youcalculate the percentage in Excel percentages as we all know are the ratios that are calculated asa fraction of hundred mathematically percentagewill be equal to the part by whole multiplied by hundred. In Excel percentagecan be calculated similarly. So now to calculate the percentage of marks obtainedby each of these students. What I’m going to dois just select the cell that I want tocalculate the percentage. I’ll type in the equal to signand then it is passed by whole so it will be b 2 dividedby C2 now hit enter and once I’ve obtained the result, I’ll click on Home taband from here. I’ll click on thepercentage symbol similarly. I’ll just copy this formulato the next cell. l So as you can seeYvonne has got 96% and Sergio has caughtin 78 percent. So now moving ontowards the next question, which is explain how to calculatecompound interest in Excel to calculatecompound interest in Excel. You can make useof the FV function the FV function returnsa future value of an investment based on periodic constantinvestment rates and payments. So let me get back to excel soas you can see over here, I’ve already created the table. The investment amount that I have over hereis 500 at the rate of 10% for five years andthe compounding periods are 12. So now to show the syntaxof this function to you guys. What I’m going to do isjust type in an equal to sign followed by f v so as you can see over here Excel provides a description sayingthis Returns the future value of an investment basedon periodic constant payments and constant interest rate. So now to find the rate, I’ll divide the annual rateby periods and to find n / I’ll be multiplyingNumber of years with the periods PMT over here can beany value including zero first. I should type in the rateto calculate the rate. Like I’ve already toldy’all I have to divide annual rate by periods. So that will be be2 divided by B 4. after this, I haveto specify number of periods that will be obtained bymultiplying B 3 and b 4 The periodic paymentsvalue I’ll specify a zero and then I’ll specify – of B1 as the investment rate. Now. This is because the moneyhas been taken from my end. So as you can see overhere the future value for an investmentof 500 at the rate of 10% for five years and the compounding periodsbeing 12 is eight hundred and twenty two pointsix five dollars. So the future valuefor an investment of $500 at the rate of 10%for five years and 12 compound. Spirits is eight hundredand twenty two point six five dollars. So I hope you guys arehere with this. So now moving ontowards the next question, which is how do you findaverages in Ms. Excel in order to find the averages you can make useof the average function? So as you can see overhere this function Returns the average or the arithmetic meanof its arguments which can be numbersnames erase or references that contain numbers. So now in order to findthe average obtained by each of these students over here, what I have to do isjust select the range. And then I’ll click enter. So as you can see over here,the average of Dave is 83.4. Similarly if I have to calculate for Ava all I have to dois copy this formula. So I hope you guysare clear with this. So now let’s move on towards thenext very important question, which is what isV lookup in Excel. The vlookup is a functionpresent in Excel used to look up and bring forth datafrom a given range v stands for vertical and to use this function data should be arranged verticallyvlookup is very useful when you have toAnd some piece of data from a huge amount of data. So how does thisvlookup function work the vlookup functionin Excel takes a lookup value and begins to look for the samein the leftmost column when it findsthe first occurrence of the given lookup value. We look up starts to move. Right that is in the rowwhere the value was found. It goes on until the columnspecified by the user and Returns the desired value. This function can be usedto match exact as well as approximate lookup values. However the default Matchis an approximate match now to show y’all the syntaxof this function. What I’m going to dois get back to excel. So as you can see over here,I’ve already created a table. So now just to show y’allthe syntax of this function. What I’m going to do is typein an equal to sign. And then I’ll choosethe vlookup function. So as you can see overhere the vlookup function takes in four parameters, which is the lookupvalue the table array the column index numberand the optional parameter, which is range lookup the lookup value gives the valueto be looked for table. Index is the table where in the datashould be taken from column number specifiesthe column from which you want to fetch the value and the range lookupis a Boolean value, which is either trueor false true will find the closest match and faultswill find an exact match. So now explain exact match with an examplefor an exact match. You’ll have to set the rangelookup value to false. So let me just takeone example of this the value that I want to lookover here will be Leo and then I’ll specifythe table array. I’ll give the column number as for since I want to takedown the salary of Leo and then I’ll specifyfaults as the Boolean value. So as you can see over here,I fetch the value of Leo salary. So what we look up does overhere is it will start to look for Leo Finn this leftmostcolumn present in the stable. So once it traces Leo, it will start moving rightin this row and it will go on till that column which is specified asa parameter over here. So I hope you guysare clear with this. So now explain the approximatematch with an example for an approximate match. We look up will fetch values. Well, there are no exact matchesof the given Up value now in order to make vlookup searchfor an approximate match. You will have to set the Booleanvalue to True remember that the table must be sorted in ascending order for vlookupto do an approximate match. So what we look up basicallydoes over here is it starts to look for an approximate matchof the given lookup value and then it stops at the value which is the next largestof the given lookup value. Then it starts to moveright in the same row and fetches the valuethat is desired. So what I’m going to do is typein an equal to sign and Then selectthe vlookup function. I’ll specify the lookup valueto be 80 and the table array. The column I’ll specify asto and the range lookup will be true. So as you can see over here, we look up has fetchedbe as the result 480. So what we look up is doing overhere is it starts looking for 80 in this leftmost column and then it stopsat the next largest value and then moves rightin the same row. So I hope you guysare clear with this. Okay. So now moving ontowards the next question, which is can you use vlookupfor multiple tables? Yes, you can use vlookupfor multiple tables as well. In case you have to look upvalues create named ranges for each of the table and then use the IF functionto select between each table based on some of the given conditionto know more about this. You can check out the vlookupin Excel video from Eddie raker. So now moving on how do you performa horizontal look up in Excel to performa horizontal look up you’ll have to make use of the hlookup functionthe H look up function just like the vlookup function takesa lookup value the table array and instead of the columnindex number it takes Is the row index numberas a parameter and the range lookup value. So now to performan edge look up what I’m going to do overhere is take a new cell. I’ll just type in Edge Look up. And over here,I’ll specify that. I want to see the designation. And then I’ll specify the range. followed by the row index number and since I want a perfect matchor an exact match, I’ll type in false. I’ve just made a mistakein this range over here. So as you can see over here, I have the outputas senior developer. So just like the vlookupfunction the H look up function will firstdo a horizontal search and then it will move downwards. So I hope you guysare clear with this. Okay. So now let’s move ontowards the next question, which is how will you fetchthe current date in Excel to fetch the currentdate you can make use of the today function. This function will returnthe current date in Ms. Excel date format. So, let me just choosea new cell and over here. I’ll just in today Soas you can see over here, it doesn’t turn the datein Ms. Excel date format. So moving on towardsthe next question, which is how does theand function work the and function in Excelis used to check whether a given condition or a set of conditionsare true or not. In case theconditions are satisfied. This function will returna Boolean true. So as you can see over here, I’ve already created a table nowto make use of the and function what I’m going to do is typein an equal to sign. And then I’ll just typein and for condition 1 I’m going to check if 230 is greater than 200and less than 500. So what I’m going to doover here is select the cell and I’ll see if this is greater than 200and less than 500. Now let’s hit enter. So as you can see 230is greater than 200 and less than 500 similarly if I have to calculatefor the rest of the values. I’ll just copy the formula. So I hope you guysare clear with this. Okay. So now moving ontowards the next question, which is what is the what-if analysis what-ifanalysis is the technique of Performing changes to one or more formulas presentin the cell in order to see how it affects the resultof those formulas in the worksheet Excel provides. Three types ofwhat-if analysis tools that is scenarios goal seekand data tables scenarios and data tables take a setof inputs to check for the potentialresults scenarios can work with many variables, but input values can beat the max 32 data tables on the other hand workwith just one or two variables, but can accept manydistinct values for each of those variables goal seekin contrast to scenarios and data tablestakes the outputs and determines the possible. It’s for the same. So can you create shortcuts formost frequently used formulas? Yes, you can do it by customizing the quick accesstoolbar right-click anywhere on this quick access toolbar and then choose customize quickaccess toolbar option from here. You can add any functionof your choice to this quick access toolbar. So what is the differencebetween formulas and functions in Excel formulasare defined by the user that is used to calculatesome results formulas can either be simple or complex and they can consist of values functionsdefined names Etc. A function on the other handis a built-in piece of code that is used to performsome particular action Excel provides a huge number of built-in functions such asthe sum function product if some if count Etc, so now moving ontowards the last None of the session and that is how do you use wild cards withvlookup wild cards can be used when you’re not sureof the exact lookup value in order to usewildcards in Excel. You should make useof the star symbol. So for example in this table over here in case I do not knowthe complete name of Sergio. What I’m going to do isjust type an ERG over here. And then I’ll make useof the vlookup function. The first thing thatI will be specifying over here is wild card symbol. That is star. And then I’ll typein an ampersand symbol. And the reference will be f13 and Ampersand symbolagain and elastic. After this, I’ll specifythe table array. The column index numberas usual and then false. for an exact match Soas you can see over here, I found the result for Sergioas Junior software engineer. So I hope you guysare clear with this. Okay, this brings usto the end of this session. I hope you guys have enjoyedand learned something new in case you haveany doubts or queries. Please do let me knowin the comment section and I will revertto you at the earliest. Good bye and take care. 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 Edurekachannel to learn more. Happy learning.