There are given top frequently asked MS Excel interview questions and answers that has been asked in many companies. Let's see the list of top Excel interview questions.
1) What is Microsoft Excel?
Microsoft Excel is an electronic worksheet or spreadsheet application which is used for organizing, storing, and manipulating and analyzing data. It is developed by Microsoft.
2) What are cells?
The area where data is stored is known as cell.
3) Does each cell have unique address?
Yes, each cell has a unique address depends on the row and column value of the cell.
4) How can you add cells, rows or columns in Excel?
If you want to add a cell, row or column in Excel, right click the cell you want to add to and after that select insert from the cell menu. The insert menu makes you able to add a cell, a column or a row and to shift the cells affected by the additional cell right or down.
5) How would you format a cell? What are the options?
A cell can be formatted by using the format cells options. There are 6 format cells options:
Number
Alignment
Font
Border
Fill
Protection
6) What is the use of comment? How to add comments to a cell?
Comments are used for a lot of reasons:
Comments are used to clarify the purpose of the cells.
Comments are used to clarify a formula used in the cell.
Comments are used to leave notes for others users about a cell.
To add a comment: Right click the cell and choose insert comment from the cell menu. Type your comment.
7) What does the red triangle indicate at the top right hand corner of the cell?
The red triangle at the top right hand corner of a cell indicates that there is a comment linked to the particular cell. If you put your cursor on it, it will show the comment.
8) How would you add comments to a cell?
To add a comment to a cell, you right click the cell and choose insert comment from the cell menu. Type your comment in the comment area provided. A red triangle at the top right hand corner of a cell indicates that there is a comment linked to that particular cell. To remove a comment from a cell, right lick the cell and then select delete comment from the cell menu.
9) What are charts in MS Excel?
Charts are used to enable graphical representation of the data in Excel. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab?s Chart group.
10) What is Freeze Panes in MS-Excel?
Freeze Panes are used to lock any row or column. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.
11) Which are the different workbook protection types in Excel?
There are three ways to protect a workbook in Excel:
Password protection for opening a workbook
Protection for adding, deleting, hiding and unhiding sheets
Protection from changing size or position of windows.
12) What is the difference among COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel?
COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.
COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.
COUNTBLANK count blank cells or cells with an empty string.
COUNTIF and COUNTIFS count cells matching a certain criteria.
13) What is Ribbon?
The ribbon specifies an area which runs along the top of the application and contains menu items and toolbars available in Excel. The ribbon has various tabs that contain groups of commands for use in the application.
14) Is it possible to hide or show the ribbon?
You can hide or show (minimize or maximize) the ribbon by pressing CNTRL F1.
15) How to prevent someone from copying the cell from your worksheet?
If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet > Password.
By entering password you can prevent your worksheet from getting copied.
16) How to sum up the rows and column number quickly in the Excel sheet?
The SUM function is used to get the total sum of the rows and columns, in an excel worksheet.
17) How can you resize the column?
There are two ways to resize a column:
To change the width of one column, drag the boundary on the right side of the column until you find your desirable width.
Select the Format from the home tab, and in Format, select the AUTOFIT COLUMN WIDTH under cell section. Click on this to change the cell size.
18) What are the several report formats in Excel?
There are three report formats in Excel:
Compact
Report
Tabular
19) Is it possible to make Pivot table using multiple sources of data?
If the multiple sources are different worksheets from the same workbook, then you can use these multiple sources of data to make Pivot table.
20) How can you check whether the Pivot table is modified or not?
To check whether the Pivot table is modified or not, you should use the "PivotTableUpdate" in worksheet containing the pivot table.
21) What does the IF function in Excel?
IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.
22) What filter should we use, if you want more than two conditions or if you want to analyze the list using database function?
You should use "Advanced Criteria Filter" to analyze the list or test more than two conditions.
23) What are the advantages of using formula in Excel sheet?
Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the number replaced by another number or digit. It is used to make complex calculations easy.
24) What is the order of sequence of operating mathematical operation in Excel?
The order of sequence is written as BEDMAS:
Brackets
Exponents
Division
Multiplication
Addition
Subtraction
25) What is the use of LOOK UP function in MS Excel?
The LOOK UP function is used to return a value from an array.
26) What is a Macro in Excel? How to create an Excel Macro?
Excel Macro is the set of instructions that is recorded by users for repetition purposes. It is created by the users for repetitive instructions and functions they perform on a regular basis.
How would you reduce the file size? What is the easiest way to reduce the file size?
You can use the following steps to reduce the file size:
Find the last cell that contains data in the sheet. Delete all rows and columns after this cell.
To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard.
Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows.
To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard.
Columns will get selected till the last row.
Press Ctrl+- on the keyboard to delete the blank columns.
27) How many rows and columns are there in Microsoft Excel 2003 and later versions?
You can see the number of columns, rows, cells for Microsoft Excel version 2003 and later versions in the following table:
Excel Versions Rows Columns Total Cells
MS Excel 2003 65536 256 16777216
MS Excel 2007 1048576 16384 17179869184
MS Excel 2010 1048576 16384 17179869184
MS Excel 2013 1048576 16384 17179869184
28) What is the syntax of Vlookup?
Vlookup Syntax:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
29) Is it possible to make pivot table using multiple sources of data?
Yes. It is possible by using data modeling technique.
Q1) Explain MS Excel in brief.
Microsoft Excel is a spreadsheet or a 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, macOS, IOS and Android.
Some of the important features of MS Excel are:
Availability of Graphing tools
Built-in functions such as SUM, DATE, COUNTIF, etc
Allows data analysis through tables, charts, filters, etc
The availability of Visual Basic for Application (VBA)
Flexible workbook and worksheet operations
Allows easy data validation
Q2) What do you mean by cells in an Excel sheet?
The area which falls at the intersection of a column and a row where the information is to be inserted is known as a cell. There are a total of 1,048,576 x 16,384 cells present in a single excel sheet.
Q3) Explain what is a spreadsheet?
Spreadsheets are a collection of cells that help you manage the data. A single workbook may have more than one worksheet. You can see all the sheets at the bottom of the window, along with the names that you have given them. Take a look at the image below:
Worksheet-Excel Interview Questions-Edureka
Q4) What do you mean by cell address?
The cell address of an Excel sheet refers to the address that is obtained by the combination of the Row number and the Column alphabet. Each cell of an MS Excel sheet will have a distinct cell address.
Q5) Can you add cells?
Yes, you can insert new cells into a sheet. To add a new cell, simply select the cell where you want to insert it and then select the Insert option. you will see the following window:
Add cells down in excel
Select the desired option and then click on OK.
Q6) Can you format MS Excel cells? If yes, then how?
Yes, MS Excel cells can be formatted. In order to format these cells, you can use the commands present in the Font group of the Home tab. When you open the Font window, you will see the following options:
Name
Description
Number
Allows formatting cells to be of any type such as currency, accounting, date, percentage, etc
Alignment
Allows text control, alignment and setting its direction
Font
Enables various fonts, styles, sizes, colors, etc
Border
Allows cell borders to be changed, removed, colored, etc
Fill
Enables you to choose different colors and styles to fill up the cell
Protection
Allows you to lock or hide cells
Q7) Can you add comments to a cell?
Yes, comments can be added. To add comments to a cell, select the cell, right-click on it and then select the New Comment option. These comments will be visible to all those people who have access to the Excel sheet.
Q8) Can you add new rows and columns to an Excel sheet?
Yes, you can add rows and columns to an Excel sheet. To add new rows and columns select the place where you intend to add them and right-click on it. Then select the Insert option from where you can choose to select an entire row or column.
Q9) What is Ribbon and where does it appear?
The Ribbon is basically your key interface with Excel and it appears at the top of the Excel window. It allows users to access many of the most important commands directly. It consists of many tabs such as File, Home, View, Insert, etc. You can also customize the ribbon to suit your preferences. To customize the Ribbon, right-click on it and select the “Customize the Ribbon” option. You will see the following window:
ribbon customization-Excel int Ques-Edureka
You can select or unselect any option of your choice from here.
Q10) How do you freeze panes in Excel?
MS Excel allows you to freeze panes that will help you see the headings of the rows and the columns even if scroll down a long way on the sheet. To Freeze Panes in Excel, follow the given steps:
First, select the Rows and Columns you wish to freeze
Then, select Freeze Pane present in the View tab
Here, you will see the following three options to selectively freeze the rows and columns as shown in the image below:
Freeze panes Excel-Edureka
Q11) How do you add a Note to a cell?
To add a Note, select the cell and right-click on the same. then select the New Note option and type in any note that you wish to. In case you want to delete the Note, follow the same procedure and select the Delete Note option. Notes are indicated by a red triangle at the top-right corner of the cell.
Q12) Can you protect workbooks in Excel?
Yes, workbooks can be protected. Excel provides three options for this:
Passwords can be set to open Workbooks
You can protect sheets from being added, deleted, hidden or unhidden
Protecting window sizes or positions from being changed
Q13) How do you apply a single format to all the sheets present in a workbook?
To apply the same format to all the sheets of a workbook, follow the given steps:
Right-click on any sheet present in that workbook
Then, click on the Select All Sheets option
Format any of the sheets and you will see that the format has been applied to all the other sheets as well
Q14) What do you understand by Relative Cell Addresses?
Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in order to match the position where the formula is copied. This is done by a system that is called Relative Cell Addresses.
EXAMPLE:
Take a look at the image below where I have written the formula in C9 and copying the same formula to C10. As you can see, C10 shows the sum of A10 and B10, unlike A9 and B9.
Relative cell addresses Excel-Edureka
Q15) In case you don’t want to modify the cell addresses when they are copied, what should you do?
If you do not want Excel to change the addresses when you copy formulas, you must make use of Absolute Cell Addresses. When you use Absolute Cell References, the row and the column addresses do not get modified and remain the same.
Example:
For absolute referencing, you will need to use the $ sign before column and row number. Take a look at the example shown in the given image:
Absolute address-Excel Interview Questions -Edureka
Q16) 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 Mixed Cell Addresses where either the row or column is relative while the other is absolute.
EXAMPLE:
Take a look at the image below where the columns hold relative referencing while the rows are absolute. Therefore, the values that are to be added in C9 are 5 and 5 since the column letter is the same as in the original formula and hence the result.
Mixeed cell address-Excel Interview Questions -Edureka
Q17) Can you protect cells of a sheet from being copied?
Yes, you can do it by protecting the required cells or the complete sheet. In order to do this, follow the given steps:
Select the cells that you want to protect
Open up the Font window from the Home tab
From the Protection pane, select Protection and then check the Hidden box
Click on Review tab present in the Ribbon, and then select Protect sheet option (Excel will not hide the required cells unless you do this)
Specify a password (This will help you in unprotecting the sheet later)
Q18) How do you create Named Ranges?
To create named ranges, follow the given steps:
Select the area to which you intend to give a name
From Ribbon, select Formulas
Click on Define Name from Defined Names group
Give any name of your choice
Q19) What are macros?
Excel allows you to automate the tasks you do regularly by recording them into macros. So, a macro is an action or a set of them that you can perform n number of times. For example, if you have to record the sales of each item at the end of the day, you can create a macro that will automatically calculate the sales, profits, loss, etc and use the same for the future instead of manually calculating it every day.
Q20) How do you create dropdown lists in Excel?
To create dropdown lists, follow the given steps:
Click on Data tab present in the ribbon
Then, from the Data Tools group, click on Data Validation
Navigate to Settings>Allow>List
Select the source list array
Pivot Tables and Pivot charts:
Q21) Explain Pivot tables along with their features?
Pivot Tables are statistical tables that condense data of those tables that have extensive information. The summary can be based on any field such as sales, averages, sums, etc that the pivot table represents in a simple and intelligent manner.
Features:
Some of the features of Excel Pivot Tables are as follows:
Allow the display of exact data you have to analyze
Provide various angles to view the data
Allow you to focus on important details
Comparison of data is very handy
Pivot tables can detect different patterns, relationships, data trends, etc
They can create instant data
Accurate reports
Serve the base for Pivot charts
Q22) How do you create Pivot Tables?
In order to create a Pivot table, you will first need to prepare the data in a tabular format. Keep the following points in mind while preparing the data:
Arrange the data into rows and columns
The first row should contain unique heading for each of the columns
The columns should have only one type of data
Rows must have data for a single recording only
No blank rows
Columns should not be completely blank
The data for creating Pivot table should be separate from other data present in the sheet
For example, let’s create a Pivot chart for the table shown in the image below:
PivotTable-Edureka
To create a Pivot table, select the table and click on the Insert tab. then select Pivot Table command and you will see the following window:
Create PT-Excel Interview Questions-Edureka
Specify where you intend to create the table and then click on OK. Once this is done you will see that an empty pivot table has been created. Also, PivotTables Fields pane will open that will help you configure the Pivot table. Take a look at the image below where I have created a Pivot Table:
adding fields-Edureka
Q23) What are Pivot charts in MS Excel?
MS Excel charts are data visualization tools that help you visualize data in various ways. These charts can be of any type such as Bar, Pie, Area, Line, Doughnut, etc. For example, take a look at the Pivot table in the image below:
Pivot table-Excel Interview Questions-Edureka
Now, if you wish to create a pivot chart for this table, select any cell from the table and then from the Insert tab, choose the Pivot Chart option. You will see the following options:
charts-Excel Interview Questions-Edureka
Course Curriculum
Microsoft BI Certification Training
Instructor-led SessionsReal-life Case StudiesAssignmentsLifetime Access
Choose any chart of your preference and click on OK. You can also format these charts respectively.
Q24) Can you create Pivot tables using multiple tables?
Yes, you can create Pivot tables using more than one base table. To do this, follow the given steps:
Press Alt+D and then press P to open up the PivotTable WizardPivotTable wizard-Edureka
Then select Multiple consolidation ranges option and click on Next and you will see another dialog box as shown below:Pivot chart wizard2-Edureka
Select I will create the page fields option and click on Next
In the next window, you will need to add all the required ranges as shown below:Adding ranges-Edurek
Once that is done, click on Next
Specify the region where you want to create the table and then click on Finishfinish multiple tables for pivot tables-Edureka
You will see the pivot table has been created by merging both the tables as shown in the image below:pt from two tables-Edureka
Q25) What happens when you check the Defer Layout Update option present in the PivotTable Fields window?
In case you check this option, you will not see dynamic changes while interchanging the table fields. By default, this option is off or unchecked. All the changes will appear only after you click on the Update button when you check this box.
Q26) Can you create a pivot table using tables from different worksheets?
If both the sheets are from the same workbook, you can create a pivot table for tables from different sheets as well. To create a pivot table from two different sheets, follow the same steps as shown in Q24 and when you specify the tables, go to the respective sheet and select the tables you intend to merge.
Q27) Is it possible to see the details of the results displayed in a pivot table?
Yes, it is possible to see the details of the results shown by the pivot tables in Excel. In order to see the details for any result, double-click on the value and you will see that a new sheet has been created with a new table having details about the factors that have led to that particular result. For example, if I double-click on one of the city values for Brad in the pivot table shown in Q15, I will see the following table that displays the details regarding the same:
Brad PivotTable details-Edureka'
Q28) How are PivotTables used to filter data?
Excel PivotTables allow you to filter data according to your requirements. To do this, place the field based on which you wish to filter out the data. Then from the pivot table, open the dropdown list present for the field you have placed in the Filter area and select the section of your choice. For example, in the table shown in Q22, if you wish to filter the data for different cities, you can do it easily as shown below:
Filter PivotTable-Edureka
As you can see, I have filtered the data for Chicago.
Q29) How do you change the value field to show some other result other than the Sum?
In order to change the value field to show results other than the Sum, right-click on the Sum of Amount values and then click on Value Field Settings. Here is the dialog box that you will see:
Change value field-Edureka
From here, you can select any value of your choice and then click on OK.
Q30) How to stop automatic sorting in PivotTables?
Excel automatically sorts the data present in the Pivot Tables. In case you do not want Excel to do this, open the dropdown menu fro the Row Labels or the Column Labels, and then click on More Sort Options. You will see the Sort dialog box opening. Click on More Options and unselect the Sort automatically option.
Formulas and Functions:
Q31) What do you understand by Excel functions?
Functions, in Excel, are used to perform specific tasks. Excel has many built-in functions that are used to calculate results of various formulas thereby helping in time conservation. Also, these functions make it very easy to execute formulas which would have been difficult to manually write down.
Q32) What are the various categories of functions available in Excel?
Functions in Excel are categorized as follows:
Catagory
Important Formulas
Date & Time
DAY, DATE, MONTH, etc
Financial
ACCINTM, DOLLARDE, ACCINT, etc
Math & Trig
SUM, SUMIF, PRODUCT, SIN, COS, etc
Statistical
AVERAGE, COUNT, COUNTIF, MAX, MIN, etc
Lookup & Reference
COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE, etc
Database
DAVERAGE, DCOUNT, DMIN, DMAX, etc
Text
BAHTTEXT, DOLLAR, LOWER, UPPER, etc
Logical
AND, OR, NOT, IF, TRUE, FALSE, etc
Information
INFO, ERROR.TYPE, TYPE, ISERROR, etc
Engineering
COMPLEX, CONVERT, DELTA, OCT2BIN, etc
Cube
CUBESET, CUBENUMBER, CUBEVALUE, etc
Compatibility
PERCENTILE, RANK, VAR, MODE, etc
Web
ENCODEURL, FILTERXML, WEBSERVICE
Q33) What is the operator precedence of formulas in Excel?
Formulas in Excel are executed according to the BODMAS rules. BODMAS, as many of us know, stands for Brackets Order Division Multiplication Addition and Subtraction. That means, in every formula, brackets are executed first (if they are present) followed by multiplication, division, etc. An example of the same is shown in the image below:
Operator precedence Excel-Edureka
As you can see, the output is 27 i.e obtained by first adding 4+5 and then multiplying it by 3. In case you do not specify the brackets, you will get the result by first multiplying 3×4 and then adding 5 to it i.e 12+5 resulting in 17.
Q34) Explain SUM and SUMIF functions.
SUM: The SUM function is used to calculate the sum of all the values that are specified as a parameter to it. The syntax of this function is as follows:
SUM(number1, number2, …)
EXAMPLE:
SUM Excel-Edureka
As you can see in the image, the SUM function is calculating the total price for all the vegetables.
SUMIF: This function is used to calculate the sum of values that comply with a given condition.
SYNTAX:
SUMIF(range, criteria, [sum_range])
where,
range specifies the range of cells to be evaluated
criteria provides the condition to be met
sum_range is optional and provides the actual cells to be summed up
EXAMPLE:
SUMIF-Edureka
As you can see, the SUMIF function is calculating the sum of goals scored only by Dybala.
Q35) What are the different types of COUNT functions available in Excel?
Excel provides five types of COUNT functions i.e COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS.
The COUNT returns the total number of cells that have numbers in the range that is specified to it as a parameter.
SYNTAX:
COUNT(value1, value2, …)
EXAMPLE:
Count excel formulas-edureka
In the above image, you can see that the COUNT function is used to calculate the number of cells having numerical data in them.
COUNTA: Counts the number of cells in a given range that are not empty.
SYNTAX:
COUNT(value1, [value2], …)
EXAMPLE:
Counta-Edureka
The above image shows the functionality of the COUNTA function that returns the number of cells that are not between A4 and B10.
COUNTIF: This function counts the number of cells that comply to a given condition.
SYNTAX:
COUNTIF(range, criteria)
EXAMPLE:
Take a look at the image below, where the COUNTIF function is used to calculate the number of cells that have the name, Dybala.
Countif-excel interview-Edureka
COUNTBLANK: Counts all the blank cells in a given range.
SYNTAX:
COUNTBLANK(range)
BI and Visualization Training
EXAMPLE:
Countblank-Edureka
COUNTIFS: This is a special function that allows you to specify a set of conditions in order to count them.
SYNTAX:
COUNTIFS(criteria_range1,range1,[criteria_range2, criteria2], …)
EXAMPLE:
COUNTIFS-Edureka
Q36) How do you calculate the percentage in Excel?
Percentages, as we all know, are ratios that are calculated as a fraction of 100. Mathematically, the percentage can be defined as follows:
Percentage = (Part/ Whole) x 100
In Excel, the percentage can be calculated in a similar manner. Take a look at the image below where the percentage has been calculated for the values present in A1 and A2.
Percentage Excel Formulas-Edureka
Here are the steps followed in order to obtain the result:
Select the cell destination cell to display the percentage
Then, type a “=” sign
Type in A1/ A2 then hit the Enter key
Click on Home tab, select % symbol from the numbers group
Q37) Explain how to calculate compound interest in Excel?
To calculate compound interest in Excel, you can use the FV function. FV returns the future value of an investment based on the periodic, constant interest rate and payments.
SYNTAX:
FV(rate, nper, pmt, pv, type)
To find the rate, the number of periods are used to divide the annual rate (annual rate/ periods). nper is obtained by multiplying the no. of years (term) with the periods (term * periods). Periodic payment (pmt) can be any value (including zero).
EXAMPLE:
compund interest excel-edureka
The investment amount is $500, rate is 10% for 5 years. There are no periodic payments hence the value for pmt is 0. -B1 means that $500 has been taken from you. Therefore, the FV for this is $822.65.
Q38) How do you find averages in MS Excel?
Average can be calculated using the AVERAGE function.
SYNTAX:
AVERAGE(number1, number2, …)
EXAMPLE:
Average Excel-Edureka
To calculate the average marks scored by Dave and Ava, I have used the AVERAGE function.
Q39) What is VLOOKUP in Excel?
VLOOKUP is a function present in Excel used to lookup and bring forth data from a given range. V in VLOOKUP stands for Vertical and to use this function, data should be arranged vertically. VLOOKUP is very useful when you have to find some piece of data from a huge amount of data.
Q40) How does the VLOOKUP function work?
The VLOOKUP function, in Excel, 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, VLOOKUP starts to move right i.e in the row where the value was found. It goes on until the column number specified by the user and returns the desired value. This function is used to match exact and approximate lookup values. However, the default match is an approximate match.
Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
here,
lookup_value gives the value to be looked out for
table_index is the range from where the data is to be taken
col_index_num specifies the column from which you want to fetch the value
range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)
Q41) Explain the exact match with an example.
For an exact match, set the range_lookup value as FALSE.
EXAMPLE:
In case you want to look for the designation of an employee, follow the given steps:
Select the destination cell and type “=”
Use VLOOKUP
Specify the lookup_value (Here, it is the ID) along with the other parameters
Set range_lookup value to FALSE
The function will be: =VLOOKUP(104, A1: D8, 3, FALSE)
exact match-VLOOKUP in Excel-Edureka
As you can see, VLOOKUP has returned the designation of the employee having 104 as his ID.
Q42) Explain the approximate match with an example.
For an approximate match, VLOOKUP will fetch values when there are no exact matches of the given loopup_value. For an approximate match, set the range_lookup value to TRUE. Remember that the table must be sorted in ascending order for VLOOKUP to do an approximate match. So here, VLOOKUP basically starts to look for an approximate match of the given lookup value and the, stops at a value that is next largest of the given lookup value. It then moves into that row to return the value from the column that has been specified.
The following image shows an example of an approximate match by VLOOKUP:
Approximate match-VLOOKUP in Excel_edureka
Follow the same steps specified for exact match
For the range_lookup value, use TRUE
The function will be: =VLOOKUP(55, A12: C15, 3, TRUE)
The lookup value is 55 and the next largest of the lookup value present in the first column is 40. Hence, the output is ‘Second Class’.
Q43) Can you use VLOOKUP for multiple tables?
Yes, you can use VLOOKUP for multiple tables as well. In case you have two lookup tables, create named ranges for each table, and then use the IF function to select between each table based on some given condition. Click here to know more about this.
Q44) How do you perform a horizontal lookup in Excel?
To perform a horizontal lookup, you will have to make use of the HLOOKUP function.
SYNTAX:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
here,
lookup_value gives the value to be looked out for
table_index is the range from where the data is to be taken
row_index_num specifies the row from which you want to fetch the value
range_lookup is a logical value i.e TRUE or FALSE (TRUE will find the closest match; FALSE checks for exact match)
EXAMPLE:
hlookup-Edureka
Q45) How will you fetch the current date in Excel?
You can make use of the TODAY function. This function will return the current date in the MS Excel date format.
SYNTAX:
TODAY()
EXAMPLE:
today-edureka
Q46) How does the AND function work?
The AND function in Excel is used to whether a given condition or a set of conditions is TRUE or not. In case all the conditions are satisfied, this function will return a boolean TRUE.
SYNTAX:
Course Curriculum
Microsoft BI Certification Training
Weekday / Weekend Batches
AND(logical1, [logical2], …)
where,
logical1, logical2, … are conditions from 1-255 that you want to check
EXAMPLE:
AND-Excel Interview Questions-Edureka
Q47) What is the What If Analysis?
What If Analysis is the technique of performing changes to one or more formulas present in the cells in order to see how it affects the result of those formulas in the worksheet. Excel provides three types of What If Analysis tools:
Scenarios
Goal Seek
Data Tables
Scenarios and 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 inputs for the same.
Q48) Can you create shortcuts for most frequently used formulas?
Yes, you can do it by customizing the Quick Access Toolbar. To customize it, right-click anywhere on the Quick Access Toolbar and select the Customize Quick Access Toolbar option. You will see the following window:
quick access toolbar customization-Excel Interview Questions-Edureka
From here, select Formulas and then choose any formula that you wish to create a shortcut for.
Q49) What is the difference between formulas and functions in Excel?
Formulas are that are defined by the user that is used to calculate some results. Formulas 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 SUM, PRODUCT, IF, SUMIF, COUNT, etc.
Q50) How do you use wildcards with VLOOKUP?
Wildcards can be used when you are not sure of the exact lookup value. In order to use wildcards in Excel, you should make use of the “*” symbol.
For example, in the table that you see in the image below, if you enter “erg” and then use wildcards with it, VLOOKUP will fetch the output that corresponds to “Sergio”.
wilcrads Excel-Edureka
This brings us to the end of this article on Excel Interview Questions. I hope you are clear with all that has been shared with you. Make sure you practice as much as possible and revert your experience.
Got a question for us? Please mention it in the comments section of this “Excel Interview Questions” blog and we will get back to you as soon as possible.
To get in-depth knowledge on any trending technologies along with its various applications, you can enroll for live Edureka MS Excel Online training with 24/7 support and lifetime access.