Microsoft Excel Interview Questions and Answers - 3

21. Explain the different data formats used in Excel.

The different data formats seen in Excel are:

i.) Numbers: Numbers are the most common type of data format and can be formatted with a customized number of decimal places, and can appear with or without commas separating a thousand digits. You can perform various functions such as subtraction, addition, multiplication, division or include them in formula and function that accept numerical inputs.

ii.) Dates: Excel can display dates in a lot of ways. The classic US style of date is MM/DD/YYYY format. Dates can be added or subtracted or even manipulated using a date based function.

iii.) Percentages: Number can also be changed into a percentage by multiplying the given number by 100 and by adding a percentage sign in the end.

iv.) Strings: Text stored in Excel in a format is called string. Strings can be letters, numbers, punctuation, and can be manipulated using MID and RIGHT the text-manipulation functions.

22. What is the shortcut if you want to repeat the last action you performed and you want the Autosum?

As you are using MS Excel you must be aware of a few useful shortcut keys to perform a certain action. You can expect these types of question where the interviewer would like to know how efficient you are and if you know the various shortcuts.

The shortcut to repeat the last action is Ctrl Y and for Autosum it is Alt =

23. What is the syntax of the VLOOKUP function?

The syntax of VLOOKUP function is
=VLOOKUP (lookup_value ,table_array , col_index_num, [range_lookup])
VLOOKUP’s argument can be described as follows:

i.) Lookup_value: defines a value to look up within an external table of data.

ii.) Table_array: defines the table of data in which to look up the lookup_value.

iii.) Col_index_num tells the function how many columns into the table_array to look for a value to return.

iv.) Range_lookup :is a TRUE/FALSE which tells the function to look for an exact match, or for an approximate match, to the specified lookup_value.

24. What is the use of the IF function in Excel?

The IF function in Excel is used to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if it is false then the result or the output will be different.

25. What is Macro in Excel?

A set of instructions that are recorded by the users for a repetitive purpose is known as Macro. Macro is created by the users who perform a certain task repetitively on a regular basis.

26. What is the simplest way to reduce the file size?

To reduce the file size you need to follow the following steps:

i.) You will have to delete all rows and columns after the last cell that contains data in the sheet.

ii.) To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard. The rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows.

iii.) To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard. The columns will get selected till the last row.Press Ctrl+- on the keyboard to delete the blank columns.

27. What is the use of comment in Excel?

A comment is used to clarify the purpose of the cell and the formula used in the cell. Comments are also used to leave notes for others to know about the cell.

28. In Microsoft Excel 2013, what is the shortcut to put filter on data?

The shortcut key to put filter on data is Ctrl+Shift+L

29. What is a dashboard?

If you want to present important information through graphical representation you will have to make use of a dashboard. The dashboard helps to present the huge data in a single computer screen so that it is seen on a single screen. While preparing the dashboard ensure that you do not add many colors as it will cause a distraction and focus should be given to important data.

30. What are the steps to select the objects in the sheet?

To select all the objects follow these steps:

i.) Press F5 to open the Go to special dialog box.
ii.) Click on the option Special > Click on object > Click Ok.
iii.) All the objects will get selected.

31. What is COUNT and COUNTA in MS Excel?

COUNT is used to count cells containing numbers, dates, etc. It is used to count any value stored in the form of numbers excluding blanks. COUNTA refers to count all. It is used to count any cell value containing numbers, texts, logical values, etc excluding blanks.