Advanced Excel Interview Questions For Mis Executive

Besant Technologies supports the students by providing Advanced Excel Interview Questions and answers for the job placements and job purposes. Advanced Excel is the leading important course in the present situation because more job openings and the high salary pay for this Advanced Excel and more related jobs. We provide the also Advanced Excel online Training for all students around the world through the Gangboard medium. These are top Advanced Excel Interview Questions and answers, prepared by our institute experienced trainers.

4) How many data formats are available in Excel? Name some of them.

Eleven data formats are available in Microsoft Excel for data Storage. Example:

• Number – Stores data as a number
• Currency – Stores data in the form of currency
• Date – Data is stored as dates
• Percentage – Stores numbers as a percentage
• Text Formats – Stores data as string of texts
• 11) How can you sum up the Rows and Column number quickly in the Excel sheet?

By using SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.

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 shows an example of an approximate match by VLOOKUP:

• 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’.

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:

• 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:

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 :

27) Difference between 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.