Excel 2013 (77-420) Interview Questions
Microsoft offers the Excel 2013 (77-420) exam. This exam is only recommended for candidates who have a basic understanding of Microsoft Excel 2013. These candidates must be capable of creating and editing a workbook with multiple sheets for a variety of purposes and situations. Budgets, team performance charts, sales invoices, and exercise logs are all examples of professional-looking documents. Students, clerical workers, bookkeepers, instructors, and other candidates may be considered.
We have compiled a list of the most frequently asked Microsoft Excel Interview Questions and Answers to assist you in preparing for the MS Excel interview questions and answers that an interviewer may ask you during the interview. We have covered all frequently asked basic Excel interview questions to advanced Excel interview questions with detailed answers to help you pass the job interview in this list of MS Excel interview questions.
The most important Excel interview questions and answers for freshers and experienced candidates to help prepare for the upcoming interview are listed below.
1. What exactly is Microsoft Excel?
Microsoft Excel is a spreadsheet application that allows users to store, organize, calculate, and manipulate data using formulas on a spreadsheet system divided into rows and columns. It also allows you to use an external database to perform analysis, generate reports, and so on, saving you a lot of time.
2. Define Ribbon.
In MS-Excel, the ribbon is the topmost area of the application that contains menu items and toolbars. CTRL+F1 can be used to show/hide the ribbon. The ribbon runs across the top of the application and replaces the toolbars and menus. On the top of the ribbons are various tabs, each with its own set of commands.
3. Explain the Spreadsheet and its Fundamentals.
A spreadsheet is similar to a paper ledger sheet. It is made up of rows and columns, as well as their intersections, which are referred to as cell
4. How many data formats do Excel support? Name a few of them.
Microsoft Excel supports eleven data formats for data storage. Example:
- Number – A number is used to store data.
- Currency – Data is stored in the form of currency.
- Date – Dates are used to store data.
- Percentage – Numbers are stored as a percentage.
- Text Formats – Data is stored as a string of text.
5. Specify the order of operations used in Excel to evaluate formulas.
In Microsoft Excel, the order of operations is the same as in standard mathematics. The term “PEMDAS” or “BEDMAS” refers to it.
- Brackets or Parentheses
- Exponent
- Multiplication
- Division
- Addition
- Subtraction
6. What is the best way to wrap text within a cell?
You must first select the text to be wrapped and then click wrap text from the home tab to wrap the text within a cell.
7. Describe Macro in MS-Excel.
Macros are used to iterate through a set of tasks. Users can program macros to perform customized repetitive functions and instructions. Depending on the user, macros can be written or recorded.
8. What are the two macro languages in Microsoft Excel?
VBA and XLM (Visual Basic Applications). XLM was used in earlier versions of Excel. VBA was introduced in Excel 5 and is now widely used.
9. Is there any way to prevent someone from copying a cell from your worksheet?
Yes, it is conceivable. To prevent others from copying your worksheet, go to the Menu bar >Review > Protect Sheet > Password. You can prevent others from copying your sheet by entering a password.
10. What are charts in Microsoft Excel?
Charts are provided to enable the graphical representation of data in Excel. By selecting an option from the Insert tab’s Chart group, a user can use any chart type, including column, bar, line, pie, scatter, and so on.
11. How can you quickly sum up the number of rows and columns in an Excel sheet?
In an Excel worksheet, you can get the total sum of the rows and columns by using the SUM function.
12. Describe a few useful Excel functions.
The following Excel functions are available for data manipulation:
- Logical Functions IF, AND, FALSE, TRUE Math, and Financial Functions – SQRT, DEGREE, RAND(), GCD Logical Functions IF, AND, FALSE, TRUE
- NOW(), DATEVALUE(), WEEKDAY(NOW()) are date and time functions.
- Pivot tables with Index Match – VLOOKUP and INDEX MATCH
13. What does a red triangle in the upper right corner of a cell mean?
The red triangle indicates that the cell has a comment associated with it. Hover your mouse over it to read the full comment.
14. How do you insert a new Excel worksheet?
Insert a worksheet tab at the bottom of the screen to add a new Excel worksheet.
15. What is the purpose of the NameBox in Microsoft Excel?
By typing the range name or cell address in the name box, you can return to a specific area of the worksheet.
16. Describe pivot tables and their applications.
A pivot table is a tool for quickly summarizing large amounts of data. It automatically sorts, counts, totals, or averages the data in the spreadsheet and displays the results in another spreadsheet. It saves a significant amount of time. Allows us to connect to external data sources in Excel.
17. What are the three report formats available in Excel?
The types of report formats are as follows:
- Compact
- Report
- Tabular
18. How would you provide a dynamic range in the “Data Source” of Pivot Tables?
To provide a dynamic range in the “Data Source” field of Pivot tables, first, create a named range with the offset function and then base the pivot table on the named range created in the first step.
19. Is it possible to create a pivot table from multiple data sources?
If the multiple sources are different worksheets from the same workbook, it is possible to create a Pivot table with data from multiple sources.
20. Which event do you use to determine whether or not the Pivot Table has been modified?
To determine whether or not the pivot table has been modified, we use “PivotTableUpdate” in the worksheet that contains the pivot table.
21. How do you turn off auto-sorting in pivot tables?
To disable automatic sorting in pivot tables, do the following:
Navigate to > More Sort Options > Right-click ‘Pivot tables’ and select ‘Pivot tables’. Select sort menu’ > ‘More Options’ > uncheck ‘Sort automatically.’
22. What is the meaning of Freeze Panes in MS-Excel?
Freeze panes are used to lock any row or column. Even if we scroll the sheet vertically or horizontally, the locked row or column will remain visible on the screen.
23. What could you do to prevent the pivot table from losing column width when it is refreshed?
Format loss in a pivot table can be easily avoided by changing the pivot table options. Turn on “Enable Preserve Formatting” and deactivate “Auto Format” under “Pivot Table Options.”
24. Describe the different types of workbook protection in Excel.
Excel provides three methods for safeguarding a workbook:
- A password is required to open a workbook.
- Sheets are protected when they are added, deleted, hidden, and unhidden.
- Protection against changes in the size or position of windows.
25. In MS-Excel, what is the difference between COUNT, COUNTA, COUNTIF, and COUNTBLANK?
- COUNT is used to count cells that contain numbers, dates, or any other value stored as a number, excluding blanks.
- COUNT or COUNTA All is used to count any cell value that contains numbers, text, logical values, and so on, excluding blanks.
- COUNTBLANK counts the number of blank cells or cells with an empty string.
- COUNTIF and COUNTIFS are functions that count cells that meet a set of criteria.
26. What is Excel’s IF function?
The IF function is used to perform the logic test. It determines whether or not certain conditions are true or false. If the condition is true, the result or output will be the same; if the condition is false, the result or output will be different.
27. Is it possible to create shortcuts to Excel functions?
Yes. The ‘Quick Access Toolbar’ above the home button can be configured to show the most frequently used shortcuts.
28. What is the advantage of using a formula in an Excel sheet?
Calculating the numbers in an Excel sheet not only allows you to give the final’sum up’ of the number, but it also calculates the number replaced by another number or digit automatically. Complex calculations, such as payroll deduction or averaging the student’s results, become simple with an Excel sheet.
29. In an Excel formula, what is the “What If” condition?
The “What If” condition is used in Microsoft Excel formulas to change the data and produce a different result.
For example, if you are purchasing a new car and want to know how much tax will be levied on it, you can use the “What If” function. There are three cells, for example, A4, B4, and C4. The first cell contains the amount, the second cell contains the tax percentage (7.5%), and the final cell contains the exact amount.
30. What does the AND function in Excel do?
The AND function, like the IF function, performs logical functions. The AND function will evaluate at least one mathematical expression in another cell in the spreadsheet to determine whether the output will be true or false. It is possible to see the output of multiple cells in a single cell by using the AND function.
