Ms Excel Interview Questions

Microsoft Excel is commonly known as an electronic spreadsheet application, which facilitates the users to store, organize, edit, and calculates data with the use of formulas. The whole spreadsheet is divided into rows and columns and accordingly, information’s are entered and values are calculated.

Spreadsheets are similar to a paper ledger sheet. It has rows and columns and has their insertion named cells.

The topmost area on the Microsoft Excel that contains menu and other toolbar items are known as ribbon. CTRL+ F1 key is used as shortcuts key to show/hide it from the screen. It runs on the top of the application and acts as a replacement for toolbars and menu.

• Number – values are stored as a number
• Currency – values are stored in the form of currency.
• Date – Date is stored in date format
• Percentage stores number in a percent form
• Text formats stores data as strings of text.

• Parentheses or brackets.
• Exponent.
• Multiplication.
• Division.
• Addition.
• Subtraction.

Select the text that has to be a wrap, after selecting click on wrap text option available on home table Selected text will be a wrap.

In MS- Excel, the macro is used for iterating over a group of the task. It can be either in a written or recorded form. It depends upon the users to create macros for their customized and repetitive function.

Charts are used in order to enable graphical representation of the data in Excel file. It depends upon the user to select the chart type. Like column, bar, line, pie, scatter, etc. It can be used as the users select an option from Insert tab’s Chart group.

Go to Menu bar > click on Review > select Protect Sheet > set a Password.  Hence, by setting a password, sheets can be secured from getting copied.

Following are the useful functions in MS Excel.

• Date And Time Functions – Now(), Datevalue(), Weekday(Now())
• Math And Financial Functions Like; Sqrt, Degree, Rand(), Gcd
• Index Match – Vlookup And Index Match
• Logical Functions  Like; If, And, False, True
• Pivot Tables

In order to add a new Excel worksheet, users can click on insert worksheet tab, which is visible at the bottom of the screen.

In MS-Excel, "Name Box" is used for returning to an area of the worksheet, by means of typing the range name or cell address in the name box particular area can be found easily within a fraction of seconds.

Yes, the Pivot table can use multiple sources of data but only when the multiple sources are different worksheets, from the same workbook.

Click on more Sort Options > Right Click on  ‘Pivot tables’ > Select ‘sort menu’ > select ‘More Options’ > deselect ‘Sort automatically’.
And the automatic sorting will be disabled.

Firstly, there is a need to change the width of one column and drag it towards the boundary on the right side of the column heading till the required width.

Secondly, it can also be done by selecting the Format from the home tab, and in Format, one has to select AUTOFIT COLUMN WIDTH that is under cell section. Directly by clicking on this, the cell size will be formatted as required.

• Compact
• Report
• Tabular

For locking any row or column, freeze panes function is used. The locked row or column will be visible on the screen even after the screen is a scroll.

1. Password protection for opening a workbook
2. Protection for adding, deleting, hiding and unhiding of sheets
3. Protection from changing size or position of windows.

Syntax: SUBSTITUTE (text, old Text, new Text, [instance Number])

Whereas the REPLACE function swaps the part of the text string with another set of text. The Syntax for this is:

Syntax: REPLACE (old Text, start Number, Number Characters, new Text)

COUNT is used to count the cells that contain numbers, dates, etc. (any value stored as a number.)

COUNTA or Count All is such a function that is used to count any cell value containing numbers, text, logical values, etc.

COUNTBLANK is cell or cells with an empty string.

COUNTIF and COUNTIFS count cells matching any particular criteria.

In order to perform various kind of logic tests, IF function is performed in excel. It verifies whether certain given conditions are true or false. If the condition is true, then it will give result accordingly if the condition is false then the result or output will be somewhat different.

Excel sheet is known for its simplicity, while in the process of calculation of the numbers in Excel sheet, along with giving the final 'sum up' of the number it also calculates the number replaced by another number or digit. In an Excel sheet, the complexity of calculation becomes easy to like for example calculation of payroll deduction or the average result of the students in a class, etc.

In simple words, the VLOOKUP function in Microsoft Excel states: =VLOOKUP(what item needs to be looked up, where you want to search for, column number in the range containing the value to return, return an approximate). VLOOKUP can be employed when you need to find things in a table or a range by row. To exemplify, look up the price of an automotive part by part number, or find an employee name based on their ID.