Microsoft Excel
Top 40 Microsoft Excel Interview Questions and Answers
1. What Is a Logical Test in Excel?
The IF() function in Excel can be used to create logical tests where an output is produced based on a speci ed scenario. For example, the code =IF(A1>0,”Yes”,”No”) tests whether a number in cell
A1 is greater than 0 or not. If it is, the output will be “Yes” and if it is equal to or less than 0, the output will be “No”. This function can be useful in a variety of situations where conditional outputs are required.
2. When do we get a reference error?
A Reference Error typically occurs in spreadsheet programs like Excel when a formula or function references a cell that doesn’t contain valid data or a valid cell address. This error happens when the referenced cell is blank or contains an error itself, making it impossible for the formula to calculate or return a result.
3. Explain the PivotTable function
- Summarizes and analyzes data from a range into a concise, tabular format.
- Aggregates data based on arithmetic operations.
- Allow altering and sorting of data. Enable deep data analysis.
4. What is Excel used for?
Excel is a spreadsheet program used for data organization, analysis, and visualization. It provides many features such as
- Performing Complex Calculations with the help of built-in functions such as COUNTIF, SUM etc.
- Creating charts
- Creating Graphs for Data Visualisation and more.
5. What is ABS function in Excel?
Excel’s ABS function is used to return the absolute value of a number. The absolute value of a number is its distance from zero, whether the number is positive or negative. The ABS function ensures that the result is always positive or zero.
6. How can you link data between different worksheets?
Use cell references or create formulas that reference cells in other worksheets.
7. How will you stop Excel from automatically converting certain text values to dates?
It is a widespread issue faced by Excel users. However, you can put an ‘=’ before the double quotes to stop Excel from automatically converting certain text values to dates. This step forces the data to be text.
8. How can panes be frozen in Excel?
The user can use MS Excel to freeze panes which would allow the user tond the headings of the columns as well as the rows if he scrolls a long way down the sheet.
The panes in Excel can be frozen in the following ways:
- Selecting the columns and rows which require freezing.
- The user then needs to select the option of freeze panes in the view tab.
- The user will thennd three options to freeze the columns and rows which include “freeze panes”, “freeze top row” and “freeze the rest column”.
9. What Is the Order of Operations in Excel?
The order of operations in Excel is as follows: first, any calculations inside parentheses are performed; second, any exponentiations are completed; third, multiplication and division are carried out from left to right; and finally, addition and subtraction are executed from left-right.
Remembering this order of operations is critical for getting accurate results in Excel. Invalid results can arise if the user does not follow the correct order.
10. What is the SUM function used for?
Excel’s SUM function is used to determine the total or sum of a set of numbers. It enables you to easily add up several values that are contained in a given range or a collection of distinct cells.
11. What is the difference between formulas and functions in Excel?
Formulas are de ned by the user and used to calculate certain results. Formulas are either simple or complex and can consist of values, functions, de ned names, etc.
A function, on the other hand, is a built-in piece of code that is used to perform a specific action. Excel provides a huge number of built-in functions like SUM, PRODUCT, IF, SUMIF, COUNT, etc.
12. How do you transpose data in Excel?
Copy the data
Right-click the destination cell
Select “Transpose” under “Paste Special.“
13. Explain the importance of the MAX and MIN functions in Excel.
MAX returns the highest value
MIN returns the lowest value in a range.
14. What is the shortcut to applying a formula to an entire column in Excel?
You can double-click on the bottom right corner of the cell to apply a formula to an entire column in Excel.
OR
If the cell already has the formula, you can apply it as follows:
Select the cell containing the formula and press
CTRL+SHIFT+DOWN to select the rest of the column
Press CTRL+D
Use CTRL+UP to return up OR
If the formula is in the first cell of a column –
Select the entire column by clicking the column header or any cell in the column Press CTRL+SPACE Fill the consecutive cells using CTRL+D
15. Use conditional formatting to highlight the top 10 selling products in each region steps followed?
Select the sales data range, including the product and region columns.
Click Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
True Box Formula.
Click Format and select the desired formatting for the top 10 selling products. For example, you can choose to highlight them in green.
Click OK twice to close the dialog boxes.
16. What is a Slicer and how does it work?
Slicers are visual filtering tools in Excel that let you filter and work with data in pivot tables and pivot charts. By choosing particular values or categories, it offers a user-friendly approach to slice and dice data, making it simpler to analyze and examine the data. Slicers allow you to easily filter and analyze data in a pivot table or pivot chart without using complicated calculations or filtering tools. Slicers provide you with a simple, visual approach to examine your data, zero in on certain subsets, and discover new patterns by filtering and enhancing the view of your data in real time.
17. Why Would You ‘'Freeze Panes” in MicrosoExcel?
Freezing a pane in a spreadsheet keeps a designated section of data stationary as you move through other information. This feature is o en employed to ensure a particular element remains in place, such as an index or key data. By using freeze panes, users can navigate through a sheet without losing track of that important information, improving overall productivity and organization.
18. Write the formula for the following – Multiply the value in cell A1 by 20, add 10 to the result, and divide it by 4.
You must follow the PEDMAS precedence in Excel when writing such a formula.
Here the answer would look be =((A1*20) + 10)/4
19. Explain the difference between COUNT, COUNTA, COUNTIF, and COUNTIFS functions.
- COUNT counts the number of cells with numbers.
- COUNTA counts non-empty cells.
- COUNTIF counts cells based on a single criterion.
- COUNTIFS does the same with multiple criteria.
20. How can workbooks be protected in Excel?
It is not difficult to protect workbooks in the cell. The user can find three options for doing the following. They are:
The user can set up passwords to open the workbooks.
The user can also protect sheets from being added up or deleted. The sheets can also be prevented from being hidden or unhidden.
The user can protect the size of the window or the position of the windows from being changed.
21. What are the data formats available in Excel?
There are eleven data formats for storing data and six primary formats for saving the workbook.
Standard data formats include:
- Number
- Currency
- Date
- Percentage Text.
- Common data formats for saving a workbook include:
- Excel workbook (.xlsx)
- Excel macro-enabled workbook (.xlsm)
- Excel binary workbook (.xlsb)
- Template (.xltx)
- Template (code) (.xltm) XML data (.xml)
22. How can you de ne ribbon and where to locate it?
The main interface of the user with Excel is known as Ribbon. The following can be found on the top of the Excel window.
The following interface lets the user gain access to numerous crucial commands directly. The following features various tabs like File, View, Insert, Home, and All.
The user is also allowed to configure the ribbon according to his requirements. The user can do the following by clicking the right mouse bu on after selecting the option of “customize the ribbon”.
23. How can you create a histogram in Excel?
Use the “Histogram” tool in the “Data Analysis” toolpack.
24. How can you define Microsoft Excel briefly?
Microsoft Excel is a software or application in the computer in the format of a spreadsheet that lets the user store data by converting it into the form of a table.
The following software was innovated by Microsoft and has now become one of the most necessary software.
The following can be utilised on numerous operating systems like Windows, macOS, IOS, and Android.
The necessary features that MicrosoExcel carries are:
- The following software carries graphics tools
- The following software carries inbuilt functions like SUM, COUNTIF, DATE, and others.
25. List the benefits of using the formula in an Excel sheet
There are numerous benefits of using the formula in Excel, such as
Time-Saving: Formulas can be used to automate calculations. Once a formula is established, you can easily and repeatedly change the input values without manually calculating the new values.
Accuracy: A formula is much more accurate, especially for complex calculations.
Consistency: A formula can be applied to different datasets ensuring that the calculation remains consistent.
Flexibility: The formula can be easily modified, allowing the user to accommodate new data, scenarios, etc.
26. What is the purpose of the INDEX-MATCH function?
It is an alternative to VLOOKUP, which is used to look up values in a table.
27. What needs to be done in a situation where the cell addresses are copied but the user doesn’t need to modify them?
If the user doesn’t wish to modify the cell addresses and still copy the formulas, then he needs to utilize the Absolute Cell Addresses.
When the Absolute Cell References have been used the addresses of rows and columns do not get modified and they remain the same.
28. In what ways can Pivot Tables be created?
Arrange the data in a tabular form before creating a table. There are certain points to keep in mind when he prepares the data.
- Arrange the information in the form of columns and rows.
- Keep in mind that there should be a unique heading in the first row of each column.
- There should be only one type of data in each column.
- The rows must feature data for a single recording only.
- There should be no blank rows in the following table.
- The must be no blank columns in the following table.
The data kept for creating the table needs to be kept aside from the rest of the data present in the Excel sheet.
29. How do you modify the data source for a chart?
To modify a chart’s data source
- Select the chart you want to edit.
- Locate and open the “Chart Data” or “Select Data” option in your charting software.
- Adjust the data range references by selecting and editing them.
- Confirm the changes and the chart will update to reflect the modified data source.
- Ensure that the new data structure aligns with the chart’s requirements for accurate visualization.
30. How do you insert a new row or column in Excel?
Adding a Row
Where you wish to place the new row, choose the row below. Select row 4, for instance, if you wish to add a row between row 3 and row 4.
To access the context menu, perform right-click on the chosen row.
Go to the context menu and select ‘Insert’ or ‘Insert Cells.’ This will add a new row above the currently chosen row.
31. What is conditional formatting in Excel?
Excel’s conditional formatting tool enables you to format cells according to particular requirements or specifications. It gives you a tool to graphically emphasize or highlight data that satisfies predetermined criteria, making it simpler to see patterns, trends, or outliers in your spreadsheet.
32. What is data validation in Excel?
The ability to limit the kind of data that may be entered into a cell or a range of cells in Excel is known as data validation. By specifying particular criteria or standards for data entry, it helps to assure data correctness, consistency, and integrity. You can enforce data rules, avoid mistakes, and enhance data quality in your Excel spreadsheets by using data validation.
33. What is a radar chart?
A radar chart, also called a spider or web chart in Excel, is designed to compare different values for a single item. It consists of axes that emanate from a central point, each representing a specific metric. The axis’s length indicates the value of that metric, and all the axes meet at the centre of the chart. This chart type is useful for displaying multiple data points and comparing them quickly. It provides a clear visual representation of different values and can help in identifying the strengths and weaknesses of the item being compared.
34. Explain the difference between the terms ‘ Filter’ and ‘sort’ in Excel.
Sorting arranges data in a specific order
Filtering displays only the data that meets specific criteria.
35. What Is a Surface Chart in Excel?
A surface chart is a 3D map used to display topological data that requires accurate coordinates. It provides a visual representation of the terrain, highlighting peaks, valleys, and slopes. The chart can be used to facilitate analysis and visualization of the topography of an area. It is especially useful for geology, geography, and earth science professionals who want to study and interpret landforms and identify patterns. The chart’s 3D nature allows for a more precise analysis of data, providing insights that might not be evident from 2D visualizations.
36. What do Relative cell referencing and Absolute cell referencing in MS Excel mean?
There are two types of references –
Relative reference in Excel
Relative references refer to the location of the cell, that is, the row and column where the cell that contains a value or a formula is located. When we take that same cell to move it to another cell, worth the redundancy, this will reference new cells based on their location.
Therefore, we can say that Excel predetermines relative references.
Absolute reference in Excel
The absolute reference represents a specific address. It always refers to the same cell and does not allow them to be modified when copying.
Absolute references are important because they remain, which is very good when working with Excel functions or formulas.
37. How many report formats are available in Excel?
There are three formats:
Compact Form
Outline Form
Tabular Form
38. How do you password-protect a workbook?
Go to “File” > “Info” > “Protect Workbook” > “Encrypt with Password.”
39. How do you create a drop-down list in Excel?
Use the Data Validation feature under the “Data” tab.
40. What Charts are available in Excel?
Excel has many types of charts, including Bar charts display data with horizontal bars while column charts display data with vertical bars. Both are used to compare values across categories. Pie charts, on the other hand, are utilized to demonstrate the distribution of categories in percentage or proportion. Pie charts are most effective when there are only a few categories, and they are easy to read at a glance.
