Advertisements

Excel Most Asked Interview Questions with Answers

Excel’s Most Asked Interview Questions with Answers

Excel Most Asked Interview Questions with Answers
Excel Most Ask interview questions
In this article, you are going to learn how to ask interview questions in MS Excel. Here you are going to prepare the 50 most important Excel interview questions with answers. These Excel files are divided into three different sessions: I. General Questions, II. Pivot Tables, and III. Formulas and Functions. Let’s go with General Questions. 
 

I. General Questions


1. Explain MS Excel in brief.
 
Microsoft Excel, as we all know, is a spreadsheet or computer application that allows the storage of data in the form of a table. Excel was developed by Microsoft and can be used on various operating systems, such as Windows, Mac, OS, iOS, Android, Etc. Some of the essential features of Ms. Excel are the availability of graphing tools and built-in functions such as date counting, etc. Excel allows data analysis through tables, charts, filters, Etc. It also provides Visual Basic for applications, flexible workbooks and worksheet operations, and data validation. 
 
2. What do you mean by cells in Ms. Excel? 
 
The area that falls at the intersection of the columns and the rows is known as the cell. The total number of rows that are available in Excel is 10,48,576; the total number of columns is 16,384. So the total number of cells will be 10,48,576 x 16,384. 
 
3. What is a spreadsheet?
 
Spreadsheets are collections of cells that help you manage the data. A single workbook may have more than one worksheet. So as you can see at the bottom of the workbook, you can see all the sheets that are available in the workbook. So by default, they are named Sheet 1, Sheet 2, etc. If you want to change the name of any of these sheets, just double-click on them and give them any name of your choice. 
 
 
4. What exactly is a cell address? 
 
The address of a cell refers to the address that is obtained by a combination of the row number and the column letter. So each cell of an MS Excel sheet will have a unique address. 
 
5. Can you add new cells to an Excel sheet?
 
Yes, you can insert new cells into a sheet. To add a new cell, all you have to do is simply select the cell where you want to insert a new cell, right-click on it go to the insert option, and then select the place where you want to insert a new cell. So in case, you want to shift all these cells towards the right and then insert the cell before this, you have to select the first option in case you want to insert a cell above some lines. All you have to do is choose the second option.
 
6. Can you format MS Excel cells, and if yes, then how?
 
The answer is yes Excel cells can be formatted. To format the cells, you can use the commands present in the font group of the Home tab. So as you can see, the number group allows you to format cells to be of any type, such as currency accounting date percentage Etc. alignment allows text control, alignment, and setting of the direction of the text that’s present in the cell. Font enables various fonts Styles sizes, and colors Etc. borders allow cell borders to be changed, removed, colored, and so on. You can also fill the cells with any desired color. And finally, you can also protect yourself by locking and hiding them. 
 
 
7. Can you add comments to a cell?
 
Yes, comments can be added to yourself. All you have to do is right-click on that. Cell and choose the new comment option. As you can see over there, a dialog box will open wherein you can type any comment of your choice. So I’ll just write down comments over there. Now, just to notify you that there is a comment present in this cell, There’s a purple symbol present in the top-right corner of the cell. So in case you do not want this comment anymore, just click on the three dots that are present in the dialog box, and then delete the thread. So one more important thing that you guys have to note is that these comments will be visible to all those who have access to this Excel sheet. Not just that, but they can also answer your comments. 
 
8. Can you add new rows and columns to Excel?
 
Yes, I can add new rows and columns to an Excel sheet. Select the column right-click over it and then select the insert option. So, you can see the cells will have just shifted towards the right. 
 
 
9. What is ribbon? And where does it appear?
 
The ribbon is a key interface with Excel and it appears at the top of the Excel window. That is nothing but the ribbon tab it consists of file home insert page layout formulas Etc. The ribbon tab, basically allows users to access many of the important commands directly. So in case you want to access any of these commands, all you have to do is just click on them. Not just these, Excel also allows you to customize the ribbon now to customize the ribbon tab. 
 
All you have to do is right-click anywhere on this and then choose the Customize the Ribbon option. So as you can see on the screen, you can choose any option of your choice to be added to the ribbon tab. So, let me just choose another option over here. I’ll say I want to include the developer tab and I’ll click on OK. So as you can see over here a new tab has been added to this ribbon similarly. You can select or unselect any option of your choice to be present in the ribbon tab. 
 
10. How do you freeze panes in Excel?
 
Ms. Excel allows you to freeze panes that will help you to see the headings of rows and columns even if you scroll a long way on the sheet. Now freeze the cells. All you have to do is select the rows and the columns that you intend to freeze then go to the View tab and select the freeze Inception Nowhere you be able to see three options that are freeze panes freeze top row and freeze First Column you can give any option of your choice. So what I’m going to do is just delete this and over here. 
 
I’ll just give some new titles. It’s a name and ID. So now what I’m going to do is freeze this so I’ll go to the View Tab and select the freeze panes option. I’ll freeze the top row. So now even when I scroll down this top row has been frozen and I’ll be able to see it. Even if I scroll a long way down the sheet. So I hope you guys are clear about this. Okay. 
 
11. How do you add a note to a cell? 
 
So to add a note? All you have to do is select the cell right-click on the same and then select the new note option. Now you can type in any note of your choice here. I’ll just type a note and then I’ll hit enter. So as you can see over here a new note has been added to my cell and it is indicated by a red triangle that is present in the top right corner of the cell. Also, one more thing to note. Is the name that is shown over here the default user’s name? Okay. So now in case you want to delete this node, all you have to do is right-click on the same and choose the delete note option. 
 
12. Can you protect workbooks in an Excel sheet? 
 
Yes, workbooks can be protected. Excel provides three options for this password that can be set to open workbooks. You can protect the sheets from being added deleted hidden or unhidden and protect window sizes or positions from being changed.
 
13. How do you apply a single format to all the sheets present in the workbook 
 
Excel allows you to apply the same format to all the sheets of a workbook. So to do that. All you have to do is right-click on any of the sheets that are present in the workbook and then click on Select. Cheats option now after doing this if I apply some format to any of the sheets present in this workbook that format is going to be applied to all the sheets that are present in this workbook. So as you can see, I just have one workbook over here. Let me just add a new workbook. And then I’ll select all sheets. And what I’m going to do over here is go to the Home tab font. I’ll just fill these cells with some color. So now let me move on to the next sheet and see what happens. So as you can see over here, I made some changes to this sheet and it’s also been reflected in the next sheet. So I hope you guys are clear about this. 
 
14. What do you understand by relative cell addresses 
 
whenever you copy formulas in Excel the addresses of the reference cells get modified automatically to match the position where the formula has been copied now, this is done by a system called the relative. Addresses, guys. So what I’m going to do over here is Type in some numbers. 
 
I’ll say four and five here. I’ll say six. and seven eight and nine So what I’m going to do over here is Type in the formula in this cell and then I’m going to copy the same formula to these two cells. So what I’m going to do is type in an equal to sign and then I’ll just give the cell addresses as a 3 plus a 4. And then I’ll hit enter. So as you can see, I’ve got the result for a 3 and a 4 over here now in case I want to copy the formula over here as well. What I’m going to do is simply select the cell and drag it down to the cell that I want to copy the formula to as you can see over here. I have copied the formula and Excel by default has changed the cell reference according to the position. 
 
15. What if you do not want to modify the cell addresses when you have copied the formula what should you do?
 
So in case, you want to copy the formula without modifying the cell addresses you’ll have to make use of absolute cell addresses When you use absolute cell references the row and the column addresses do not get modified and they remain the same. So basically when you have to use absolute referencing, you’ll have to make use of the dollar $ symbol before the column and the row numbers. 
 
So what I’m going to do over here is just change this formula or let me just take a new cell and then I’ll add in all these cells. So what I have to do is insert a dollar symbol before each of these. And then I’ll hit enter. So now what I’m going to do over here is copy this formula to the other cells and see what happens. So as you can see over here, the same result has been copied to this cell which means the formula has not been modified in case I would have used relative cell addresses. Then the formula would have been modified according to its position.
 
16. What will you do if you want to change either the column letter or the row number but not both? 
 
To do this you must make use of the mixed cell referencing here either the row number or the column letter will change but not both of them. So to show this example to you guys. What I’m going to do is get 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 here is Type in an equal to sign and then I’ll type in a dollar three then after that. I’ll type in a plus sign and then be Dollar Tree. So now what I’m going to do is copy this formula and see what happens. So as you can see over here when I copy the formula the column Remains the Same and the value of the Roll number has not changed because I’ve given absolute referencing here. So I hope you guys are clear about this. Okay. 
 
17. Which is can you protect the cells of a sheet from being copied? 
 
So the answer is yes, you can do it by protecting the required cells or the complete sheet. Not to do this. All you have to do is select the cells that you want to protect go to this font group here and then select protection from here check the hidden option and then click on OK. So as you can see there are no changes that are made to my sheet The formulas that are present in the cell are still visible now in case I want to apply the changes that I just made All I have to do is go to the Review Tab and then select the protect sheet option. Excel will ask me to enter some passwords. 
 
So I’ll just give some random password over here. And then click on okay. So as you can see previously when I used to click on these cells, it would show me the formula for both of these Now when I click on either of these cells the formula is hidden when I click on any of the other cells that are not protected the formula is still visible. Okay. So I hope you guys are clear with this now in case you want to unprotect the sheet. All you have to do is Select this unprotects sheet option and then enter the password that you had specified. 
 
18. How do you create named ranges? 
 
To add named ranges. All you have to do is select the area in which you intend to give a name. And then from the ribbon tab, click on formulas From here go to the Define name command, and give any name of your choice. So I’ll just give vegetables as the name of this table or I’ll just say veggies.
 
And then I’ll click on okay. So as you can see or hear the name given to this range is veggies similarly. If you want to create a name for this range as well. All you have to do is Select that go to the Formulas Tab select Define name then give any name of your choice and over here. I’ll just say fruits. And then I’ll click on OK. So as you can see I have created two new ranges that are vegetables and fruits. 
 
19. What do you mean by macros?
 
Excel allows you to automate the tasks that you do regularly by recording them into macros. So a macro is an action or a set of them that you can perform several times. So for example, if you have a record of sales of each item and at the end of the day, you have to calculate The sales profits loss etc.. So if you create a macro it will automatically calculate all these values and then you can use the same for future purposes. 
 
20. How do you create drop-down lists in Excel? 
 
So now in case you want to create a drop-down list, all you have to do is select the data tab that is present in the ribbon. And then from this data tools group over here select the data validation command. Okay from the given options select the list and provide the source. So I’ll just provide the vegetable names as a source and then I’ll click on OK. So as you can see over here, I have a drop-down list that contains the names of all three vegetables that are specified over here. So I hope you guys are clear about this. 
 

II. Pivot tables

21. Explain Pivot tables along with their features.
 
Pivot tables are statistical tables that condense the data of those tables that I have extensive information about. The summary can be based on any field such as sales average. Somes Etc that the pivot table represents simply and intelligently some of the important features of pivot tables. They allow the display of exact data that you have to analyze the provide various angles to view the data. They allow you to focus on important details Comparison of data is very handy and pivot tables can detect different patterns relationships data Trends Etc. They can also create instant data accurate reports and serve as the base for Pivot charts. 
 
22. How do you create pivot tables to create a pivot table? 
 
You will first need to prepare the data in tabular format. Now while preparing this data you will have to keep a few points in mind. The first thing is you have to arrange the data in two rows and columns. The first row should contain a unique heading for each of the columns and the columns should have only one type of data present in them. 
 
The rose must have data for a single recording. There should be no blank rows columns should not be empty and the data for creating a pivot table should be separated from the other data that is present on that sheet. So what I’m going to do over here is a copy of the table that I’ve already created. So as you can see over here, the first row has a unique heading and each column has the same type of data The following rows contain the data for a single recording. So I hope you guys are clear about this.
 
22.1. How you create private. 
 
Abel’s so what I’m going to do over here is select the stable. And then I’ll click on the insert tab from here. I’ll choose the pivot table option. I’ll select the existing worksheet option. I’ll give some random cell as the target place and then I’ll click on OK. So as you can see over here an empty pie with a table has been created. Also, the PivotTable fields pane has opened that contains all the fields that are present in my pie with the table now to create a pivot table. 
 

Your best regards – winsomeismail.com

All you have to do is drag and drop any of these fields present in these four areas over here which are Filters columns rows and values. So what I’m going to do over here is drag and drop some Fields into these four areas. I’ll drop names into the rose. I’ll drop the items into columns that amount to values. And the filters will be based on the city. So as you can see over here a pivot table has been created which contains all the names as rows and all the item names as columns Also, you can see all the amounts that are collected for each item bit by each of these vendors and also the total of each of them. 
 
24. What are pivot charts 
 
Ms. Excel charts are data visualization tools that help you visualize the data in various ways These Charts can be of any type such as bar pie area line donut Etc. So in case, I want to create a pie chart for this pivot table over here, what I have to do is go to the insert tab and then from the charts group. I’ll select any chart of my choice. And then I’ll click on okay. So as you can see a pivot chart has been created for this pivot table. You can also filter out the data according to your choice by selecting any of the data that you want to see selectively. 
 
Let me just select Chicago and I’ll click on OK As you can see over here by pie chart has been modified accordingly. Not just this you can also format this chart by selecting this option and from here, you can select any type of chart that you intend to see so I hope you guys are clear with this. 
 
25. Can you create pivot tables using multiple tables?
 
The answer is yes, you can create pivot tables using one or more based tables, Please note that both tables should be present in the same worksheet. So now to show this to you guys what I’m going to do is insert a new sheet and over here. I just called on behalf of the state data. So what I’ve done over here is separate the table that I’ve created previously into two different tables. So what I’m going to do here is Click all plus d and then I’ll click on pain. From this pivot table and pivot chart wizard. I’ll select the multiple consolidation ranges option and then I’ll click on next. 
 
I’ll choose the option. I will create the page fields over here. I’ll specify the first range to be the order ID as the date name and the item I want in one field. I’ll just name it field one. and I’ll add this so now for the next field what I’m going to do is Select this I’ll add it and I’ll give the name as a field and then I’ll click on next. So Excel will ask you where you want to create it and I’ll say the existing sheet and I’ve selected some cells over here then I’ll click on finish. So as you can see over here a new pivot table has been created by taking the information present in both these tables. 
 
25.1. What happens when you check the different layout update options present in the PivotTable fields window? 
 
So in case you check this option, you will not see Dynamic changes while interchanging the table field. So as you can see by default this option is off or unchecked All the changes will appear only when you click on the update button when you check this box, so I’m going to do over here is the first show you guys what happens when I make changes without checking this box. So just remove the rows and I’ll put it back over here. So as you can see, I’ve just removed the rose and put it back over here and the pie with the table has changed accordingly. 
 
Now, what I’m going to do is put it back. And we’ll check this box and then I’ll do the same. So as you can see previously I removed the rose and I put it back on this list over here. The pivot table changed accordingly. Now only when I hit the update button it is going to change. So I hope you guys are clear about this. Okay.
 
26. Can you create pivot tables using tables from different worksheets?
 
Yes, if both the sheets are from the same workbook. You can create a pivot table from the tables present in different sheets to create five tables from two different. Follow the same steps shown in question 24 and when you’re adding the tables go to the respective sheet and select the tables that you intend to merge. 
 
27. So is it possible to see the details of the results and display them in the pie with a table? 
 
Yes. It is possible to see the details of the results shown by the pivot tables in Excel to see the details of any of the results. All you have to do is double-click on any of them. And you will see that Excel creates a new sheet with all the details about that result. So as you can see over here, I double-clicked on the total of 400 obtained by Brat and you see that Excel created a new sheet giving me all the details regarding this. 
 
28. How our pivot tables are used to filter data?
 
Excel pivot tables allow you to filter data according to your requirements to do. All you have to do is place the field based on which you wish to Do the data and then from this pivot table filter the data according to your choice. So as you can see over here, I’ve placed the Citi Field in the filters area and over here. I can filter out the data for all the cities or choose any city of my choice. Okay. So let me just choose New York and click on OK. 
 
 
29. How do you change the value field to show some of the results other than the sum? 
 
To change the value field to show results other than the some all you have to do is right-click on any of these values present here or from this Pi would feel stable. Click on this drop-down list present over here, then select the value field settings option and from the given list choose any total of your choice. Let me just change from some to count and I’ll click on OK So as you can see over here, the sum of values has been changed to the count of amount. 
 
30. How do you stop automatic sorting in pivot tables? 
 
So as many of us know Excel automatically sorts the data present in pivot tables In case you do not want to do this open up the drop-down list present for the row labels or the column labels click on more sort options, then go to more options and over here and check this box that says sot automatically every time a reporter. It will be updated once you’re done with that. Just click on Okay. So that was the last question present in the section. So now moving on to the third section which is question-based on formulas and functions in Excel. 
 

III. Formulas and Functions.

31. What do you understand by Excel functions?
 
Excel is used to perform specific tasks Excel has many built-in functions that are used to calculate the results of various formulas. Thereby helping in time conservation. Also, these functions make it very easy to execute formulas, which would have been difficult If you go manually write them down.
 
32. What are the various categories of functions available in Excel? 
 
Ms. Excel has been categorized in this list of options that are present over here. So you have categories based on the date and time Financial functions based on mathematics and trigonometry statistical functions lookup and reference functions database text logical information Engineering functions Cube functions compatibility and web functions. 
 
33. Which is what is the operator precedence of formulas in Excel?
 
Formulas in Excel are executed according to the body mass roles Board mass as many of us know stands for brackets order division multiplication addition and subtraction. So that means in every formula brackets are executed first if any of them are presently followed by multiplication division Etc Now to show an example of this, let me get back to Excel. Let me just give some values Here, I’ll say three. Four five are now in this cell. I just type in some expressions. 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 calculated by first adding B1 and C1 and then multiplying the same with a 1. 
 
So basically 5 plus 4 will give me 9 and 3 times 9 is 27 now in case I modify this formula, which is removing the brackets. And now let’s see what happens. So as you can see previously I had a result saying 27 now. The result is 17. So what Excel has done over here is first multiplied A1 and B1 and then the result is added to see one. So basically 3 times 4 is 12 and 12 plus 5 is 17. So I hope you guys are clear about this. 
 
34. Explained the SUM and the SUMIF functions.
 
So now let me just open up the sum function over here to show you guys the syntax of this. So as you can see over here, the sum function takes an N number of values as its parameter and it Returns the sum by adding each of them. So now what I’m going to do is just cancel this and over here. I’ll type in an equal to sign and I’ll use some function. I just specified this range to be all the values to be added using the sum function and now I’ll hit enter. So as you can see over here, the sum function has added all the values that are present between A1 and D1 similar to the sum function is a sum of functions One difference is that this function is used to calculate the sum of values that comply to a given condition now in case I just change this from some to some if I’ll have to specify the range followed by the criteria. 
 
Now. What I’m going to do is give some criteria. I’ll say calculate the sum of values that are less than 15. And then I had Dental so as you can see over here, it has added only those values that are less than 15 and it has discarded 17. 
 
35. What are the different types of count functions available in Excel?
 
Excel provides five types of count functions. That counts a count black count if and count if s so talking about the first function that is the count function this function Returns the total number of cells. That has numbers in the range that are specified to it as a parameter. So now to show y’all how this works. What I’m going to do is type in an equal to sign over here and then I’ll make use of the count function. So as you can see over here, it takes all the values as a parameter. So what I’m going to do is just specify this range and then I’ll hit enter. So as you can see over here, the count function has returned five which means five cells in this range contain numbers. 
 
So now moving on to the next function, which is Count, and if I just change this from count to count it is going to count all the cells that are not empty within this range. So as you can see over here, I had given a very big range that has cells that contain information plus the cells that are empty but the county has returned only the number of cells that contain data in them. So now talking about count if the count of function counts the number of cells that comply with a given condition. So let me just change it from count eight to count if and within this range, I’ll just specify some criteria. I’ll say count the values that are greater than 10. I hit enter so as you can see within this range, two cells contain values that are greater than 10 In contrast to count a is Count blank count is the number of cells that contain information on the other hand count blank will count the number of cells that are blank within a given range. So what I’m going to do over here is change this from count if to count blank And there are just remove this condition from you. Okay. So as you can see, there are seven empty cells present in this range. 
 
So now talking about count if s this is a very special function that allows you to specify a set of conditions to count them. So what I’m going to do is go to the table that I’ve created here. So what I’m going to do over here is Type in an equal to sign. Then I’ll choose to count as a formula and then I’ll have to specify the range for criteria on the range for criteria 2 and so on. So what I’m going to do is just select the names. And the name that I want to select from here is Yvonne. After this, I’ll give the criteria for the range. And I’ll specify the quantity as 24 now. I’ll hit enter. So as you can see over here within the given range, there’s just one cell that has a one and has the quantity of 24 present in it. So, I hope you guys are clear about this. Okay.
 
36. How do you calculate the percentage in Excel
 
Percentages as we all know are the ratios calculated as a fraction of a hundred Mathematically percentage will be equal to the part by the whole multiplied by a hundred. In Excel, percentages can be calculated similarly. 
 
So now to calculate the percentage of marks obtained by each of these students. What I’m going to do is just select the cell that I want to calculate the percentage. I’ll type in the equal to sign and then it is passed by whole so it will be b 2 divided by C2 now hit enter and once I’ve obtained the result, I’ll click on the Home tab, and from here. I’ll click on the percentage symbol similarly. I’ll just copy this formula to the next cell. l So as you can see Yvonne has got 96% and Sergio has caught 78 percent. 
 
37. How to calculate compound interest in Excel?
 
You can make use of the FV function the FV function returns a future value of an investment based on periodic constant investment rates and payments. So let me get back to Excel As you can see over here, I’ve already created the table. The investment amount that I have over here is 500 at the rate of 10% for five years and the compounding periods are 12. So now to show the syntax of this function to you guys. What I’m going to do is just type in an equal to sign followed by f v so as you can see over here Excel provides a description saying this Returns the future value of an investment based on periodic constant payments and constant interest rate. 
 
So now to find the rate, I’ll divide the annual rate by periods, and to find n / I’ll be multiplying the number of years with the periods PMT over here can be any value including zero first. I should type in the rate to calculate the rate. Like I’ve already told y’all I have to divide the annual rate by periods. So that will be 2 divided by B 4. After this, I have to specify the number of periods that will be obtained by multiplying B 3 and b 4 The periodic payments value I’ll specify a zero and then I’ll specify – of B1 as the investment rate. Now. This is because the money has been taken from my end. 
 
So as you can see over here the future value for an investment of 500 at the rate of 10% for five years and the compounding periods being 12 is eight hundred and twenty-two point six five dollars. So the future value for an investment of $500 at the rate of 10% for five years and 12 compounds. Spirits are eight hundred and twenty-two point six five dollars. So I hope you guys are here with this. 
 
38. How do you find averages in Ms. Excel?
 
To find the averages, you can make use of the average function. So as you can see here this function Returns the average or the arithmetic mean of its arguments which can be numbered names eras or references that contain numbers. 
 
So now to find the average obtained by each of these students over here, what I have to do is just select the range. And then I’ll click enter. So as you can see over here, the average for Dave is 83.4. Similarly, if I have to calculate for Ava all I have to do is copy this formula. So I hope you guys are clear about this. 
 
39. What is V lookup in Excel? 
 
The lookup is a function present in Excel used to look up and bring forth data from a given range v stands for vertical and to use this function data should be arranged vertically vlookup is very useful when you have to And some piece of data from a huge amount of data.
 
 
40. How does this lookup function work?
 
the lookup function in Excel takes a lookup value and begins to look for the same in the leftmost column when it finds the first occurrence of the given lookup value. V looks upstarts to move. Right, that is in the row where the value was found. It goes on until the column specified by the user Returns the desired value. This function can be used to match exactly as well as approximate lookup values. However, the default Match is an approximate match now to show y’all the syntax of this function. What I’m going to do is get back to Excel. 
 
So as you can see over here, I’ve already created a table. So now just to show y’all the syntax of this function. What I’m going to do is type in an equal sign. And then I’ll choose the V lookup function. So as you can see over here the lookup function takes in four parameters, which are the lookup value the table array the column index number, and the optional parameter, which is the range lookup The lookup value gives the value to be looked for a table. The index is the table wherein the data should be taken from column number specifies the column from which you want to fetch the value and the range lookup is a Boolean value, which is either true or false true will find the closest match, and faults will find an exact match. 
 
 
41. Explain the exact match with an example.
 
For an exact match. You’ll have to set the range lookup value to false. So let me just take one example of this the value that I want to look over here will be Leo and then I’ll specify the table array. I’ll give the column number since I want to take down the salary of Leo and then I’ll specify faults as the Boolean value. So as you can see over here, I fetch the value of Leo’s salary. So what we look up does over here is it will start to look for Leo Finn this leftmost column present in the stable. So once it traces Leo, it will start moving right in this row and it will go on till that column which is specified as a parameter over here. So I hope you guys are clear about this. 
 
42. Explain the approximate match with an example.
 
For an approximate match. V lookup will fetch values. Well, there are no exact matches of the given Up value now to make vlookup search for an approximate match. You will have to set the Boolean value to True Remember that the table must be sorted in ascending order for vlookup to do an approximate match. So what we look up basically does over here is it starts to look for an approximate match of the given lookup value and then it stops at the value which is the next largest of the given lookup value. Then it starts to move right in the same row and fetches the value that is desired. So what I’m going to do is type in an equal sign and Then select the lookup function. 
 
I’ll specify the lookup value to be 80 and the table array. The column I’ll specify and the range lookup will be true. So as you can see over here, we looked up has fetched be as the result 480. So what we look up is doing over here is it starts looking for 80 in this leftmost column and then it stops at the next largest value and then moves right in the same row. So I hope you guys are clear about this. Okay. 
 
43. Can you use vlookup for multiple tables? 
 
Yes, you can use lookup for multiple tables as well. In case you have to look up values create named ranges for each of the tables and then use the IF function to select between each table based on some of the given conditions to know more about this. You can check out the lookup in Excel video from Eddie Raker. 
 
44. How do you perform a horizontal lookup in Excel?
 
To perform a horizontal lookup you’ll have to make use of the lookup function the H lookup function just like the lookup function takes a lookup value in the table array and instead of the column index number it takes Is the row index number as a parameter and the range lookup value. So now to perform an edge look up what I’m going to do over here is take a new cell. 
 
I’ll just type in Edge Lookup. 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 match or an exact match, I’ll type in false. I’ve just made a mistake in this range over here. So as you can see over here, I have the output as a senior developer. So just like the lookup function, the H lookup function will first do a horizontal search and then it will move downwards. So I hope you guys are clear about this. Okay.
 
45. How will you fetch the current date in Excel?
 
To fetch the current date you can make use of the today function. This function will return the current date in Ms. Excel’s date format. So, let me just choose a new cell and over here. I’ll just be in today So as you can see over here, it doesn’t turn the date in Ms. Excel date format. 
 
The formula for Date with current time ” =NOW( )
Formula for Today Date ” =TODAY( )

 
46. How does the AND function work?
 
The AND function in Excel is used to check whether a given condition or a set of conditions is true or not. In case the conditions are satisfied. This function will return a Boolean true. So as you can see over here, I’ve already created a table now to make use of the function What I’m going to do is type in an equal to sign. And then I’ll just type in and for condition 1 I’m going to check if 230 is greater than 200 and less than 500. 
 
So what I’m going to do over here is select the cell and I’ll see if this is greater than 200 and less than 500. Now let’s hit enter. So as you can see 230 is greater than 200 and less than 500 similarly if I have to calculate the rest of the values. I’ll just copy the formula. So I hope you guys are clear about this. Okay. 
 
47. What is the what-if analysis?
 
What-if analysis is the technique of Performing changes to one or more formulas present in the cell to see how it affects the result of those formulas in the worksheet Excel provides. Three types of what-if analysis tools are scenarios goal seek and data tables scenarios data tables take a set of inputs to check for the potential results Scenarios can work with many variables, but input values can be at the max 32 data tables on the other hand work with just one or two variables, but can accept many distinct values for each of those variables goal seek in contrast to scenarios and data tables takes the outputs and determines the possible. It’s for the same. 
 
48. Can you create shortcuts for the most frequently used formulas? 
 
Yes, you can do it by customizing the quick access toolbar right-click anywhere on this quick access toolbar and then choose to customize the quick access toolbar option from here. You can add any function of your choice to this quick-access toolbar. 
 
 
49. What is the difference between formulas and functions in Excel 
 
Formulas are defined by the user and are used to calculate some results Formulas can either be simple or complex and they can consist of values functions defined names Etc. A function on the other hand is a built-in piece of code that is used to perform some particular action Excel provides a huge number of built-in functions such as the sum function product if some if count Etc, 
 
50. How do you use wild cards with lookup?
 
Wildcards can be used when you’re not sure of the exact lookup value to use wildcards in Excel. You should make use of the star symbol. So for example in this table over here in case I do not know the complete name of Sergio. What I’m going to do is just type an ERG over here. And then I’ll make use of the lookup function. The first thing that I will be specifying over here is the wild card symbol. That is a star. Then I’ll type in an ampersand symbol. 
 
And the reference will be f13 and the Ampersand symbol again and elastic. After this, I’ll specify the table array. The column index number is as usual and then false. 
 
I hope you guys have enjoyed and learned something new in case you have any doubts or queries. Please do let me know in the comment section.
 

Your best regards – winsomeismail.com

Buy Excel Formula Book

Spread The Knowledge

Leave a Comment