Introduction.
In the corporate world, MS excel is one of the important tools which is often used by professionals. While preparing for interviews candidates one should know the technical terminology associated with this. Apart from the basic, there are few advanced level functions, which require a special attention. Given below is the list of thirty- five such questions, which covers up the basic and advance learning in MS Excel.
Candidate should try to know the importance and other uses of the application and should never forget to the emphasis on the fact that with passing years the advancement in the learning has taken place in MS Excel and work can be completed easily and effectively. From storing data to calculating various components, this application covers all the functions required.
Stated below is the list of questions, which will help the candidates to prepare for interviews.
Q1. What is Microsoft Excel?
Ans. 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.
Q2. Explain the spreadsheet.
Ans. Spreadsheets are similar to a paper ledger sheet. It has rows and columns and has their insertion named cells.
Q3. What do you mean by ribbon?
Ans. 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.
Q4. How many types of data formats are available in Microsoft Excel for storing the data/ values? Name a few of them.
Ans. 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.
Q5 Specify the order of operations used for evaluating formulas in excel.
Ans. 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.
Q6. How is text wrap done within a cell?
Ans. 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.
Q7. What is meant by a macro in MS- Excel?
Ans. 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.
Q8. What do you mean by charts in MS-Excel?
Ans. 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.
Q9. Is it possible to sum up the Rows and Column number in the Excel sheet? If yes, then how is it possible?
Ans. 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.
Q10. Is it possible that users can prevent copying of the cell from any self-made worksheet?
Ans. 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.
Q11. What are the useful functions in Microsoft Excel?
Ans. 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
Q12. At the time of using MS Excel, a red triangle at the top right of a cell is visible. What does it indicate?
Ans. 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.
Q13. How is the new Excel worksheet added?
Ans. In order to add a new Excel worksheet, users can click on insert worksheet tab, which is visible at the bottom of the screen.
Q14. State the purpose of “Name Box” in MS-Excel?
Ans. 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.
Q15. Can a Pivot table use multiple sources of data?
Ans. Yes, Pivot table can use multiple sources of data but only when the multiple sources are different worksheets, from the same workbook.
Q16. How can users check that the Pivot Table is modified?
Ans. To check the pivot table is modified, the user can use “PivotTableUpdate” in the worksheet containing the pivot table.
Q17. How can you disable the automatic sorting of information in a pivot table?
Ans. 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.
Q18. How can the user change the size of the column?
Ans. 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.
Q19. What are pivot tables and also mention its use?
Ans. 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.
Q20. In how many formats is a report available in Excel?
Ans. There are three main report formats available, which are:
- Compact
- Report
- Tabular
Q21. How is Dynamic range provided in “Data Source” of Pivot Tables?
Ans. 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.
Q22. What is the role of cell reference in the calculation in excel?
Ans. 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.
Q23. What do you mean by Freeze Panes in MS-Excel?
Ans. 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.
Q24. What could be done in order to stop the pivot table from losing the column width while refreshing?
Ans. 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.
Q25. Explain types of workbook protection in Excel.
Ans. Excel provides three ways of protection of workbook:
- Password protection for opening a workbook
- Protection for adding, deleting, hiding and unhiding of sheets
- Protection from changing size or position of windows.
Q26. What is the difference between SUBSTITUTE and REPLACE function in MS-Excel?
Ans. 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)
Q27. Differentiate between COUNT, COUNTA, COUNTIF, and COUNTBLANK in MS-Excel.
Ans. 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.
Q28. What is IF function in Excel?
Ans. 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.
Q29. What is the use of LOOKUP function in Excel?
Ans. 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.
Q30. In order to apply the same formatting to every sheet in a workbook in MS-Excel what needs to be done?
Ans. 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.
Q31. State the shortcut to move the previous worksheet and next sheet
Ans. 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.
Q32. What filter is used, if more than two conditions are used or if analysis of list has to be done where there are more than two conditions?
Ans. Advanced Criteria Filter function is used to analyze the list or if more than two conditions have to be tested.
Q33. How can the users return to any particular area of a worksheet?
Ans. In order to return to a specific area of the worksheet, name box can be used.
Q34. What is the benefit of using the formula in Excel sheet?
Ans. 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.
Q35. What is the role of AND function in Excel?
Ans. 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.
Conclusion.
We have tried to cover most of the questions, which are generally asked in an interview related to the MS Excel. Candidates need to go through these answers in order to understand the functions and various other technical terms. While asking the question, the interviewer tries to know the accuracy and confidence level of the candidate hence answer should be sensible enough. The important thing to note here is that answers having illustration and examples are always appreciable. Good wishes for future endeavors.
