Microsoft 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.

  There are eleven data formats available in Microsoft Excel. Few of them are;

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

The order of operations in Microsoft Excel is a standard format. It is basically defined by the terms PEMDAS or BEDMAS

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

Yes, it is possible; users can sum up the rows and column number by using SUM function. The total sum of the rows and columns, in an Excel worksheet, can be calculated by this function.

Yes, it is possible.  And it is done especially when data in the worksheets are not to be disclosed. To protect the sheet from copying, users need to follow these simple steps

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 the MS Excel, the red triangle indicates, a comment, which is been associated with the cell.  If the users hover the mouse over it, the full comment will be visible.

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, Pivot table can use multiple sources of data but only when the multiple sources are different worksheets, from the same workbook.

To check the pivot table is modified, the user can use “PivotTableUpdate” in the worksheet containing the pivot table.

For disabling the automatic sorting of data in pivot tables:

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.

 In order to resize the column, users can use any of the methods

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.

It is a table or kind of tool and it allows a quick summarization of data in large quantity. It performs functions like sorting, counting, total or average of the data that are saved in the spreadsheet and it displays the result in another spreadsheet. It saves a lot of time. It is one of the most helpful too and also allows to link external data sources to Excel sheet.

There are three main report formats available, which are:

• Compact
• Report
• Tabular

when there is a need to provide a dynamic range in Data Source of Pivot tables, the users need to create a named range using the offset function available and then base the pivot table using a named range that was built in the first step.

In order to avoid writing of repetitive data for long calculation, cell reference plays a great role. When any formula is written, for any function, there is a need to direct Excel the specific location of that data. This location is referred to as, cell reference. So, as and when a new data or value is added to the cell, the cell will automatically calculate according to the reference given.

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.

Just changing the pivot table options can stop format loss in a pivot table. In the “Pivot Table Options” select the “Enable Preserve Formatting” and just disable the function named “Auto Format” option.

Excel provides three ways of protection of workbook:

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.

The SUBSTITUTE function basically substitutes one or more instances of previous text with the new text. The syntax for this is:

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.

In Microsoft Excel, the LOOKUP function is used when there is a need to return a value from a range or from any other array.

 In order to apply the same formatting to every sheet in a workbook Click right ‘Worksheet tab’,> Choose ‘Select All Sheets’. Any kind of formatting done here will be applied to the whole workbook. In order to apply to a particular group of sheets, only that sheet has to be selected that needs formatting.

In order to move to the previous worksheet, the keys Ctrl + PgUp have to be used whereas to move to the next sheet Ctrl + PgDown key is to be used.

Advanced Criteria Filter function is used to analyze the list or if more than two conditions have to be tested.

In order to return to a specific area of the worksheet, name box can be used.  

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

AND function also performs the logical function like IF function. To know whether the stated output is true or false. The AND function also evaluates the expression located in another cell in the spreadsheet.