MS Excel Interview Questions And Answers [2024]
MS Excel allows users to perform calculations and present data systematically. It is a powerful tool used by over 750 million users for analyzing, formatting, organizing, and manipulating data. Almost every field utilizes this software for one purpose or another. Thus, every candidate must be well-versed in the ins and outs of the tools available in MS Excel.
You must prepare for Excel Interview Questions thoroughly to mark success in any job interview. If you have a good knowledge of these questions, it can demonstrate your proficiency in Excel and hence potentially enhance your job prospects. In this blog, we will go through the most asked MS Excel interview questions and answers.
Why is Excel Used Extensively?
There are a lot of reasons that make MS Excel a popular software and the first choice of so many users. The options that it offers are flexible and have simplified functionality that makes understanding and using Excel easier for everyone. It can summarize and analyze large amounts of data in a very short amount of time which saves up effort and time for the user. It is also useful as it can manage data, create budgets, and analyze sales data.
Excel is a powerful and versatile tool that is essential for many businesses and personal uses, it is easy working and flexibility makes it likable for users to use daily.
Basic Excel Interview Questions and Answers
To get a good understanding of the questions that are asked during an interview, go through the Excel interview questions for freshers with their answers below:
Q1. Define Spreadsheets
A grid of cells that is organized in rows and columns that conscious data, functions, and formulas is known as a Spreadsheet. Excel works in spreadsheet format, it stores, analyzes, and manipulates data.
Q2. Define a cell in Excel
The intersection of a row and a column in the spreadsheet is known as a cell. It is a basic unit of a spreadsheet where data, functions, and formulas are stored.
Q3. Define a formula in Excel
An equation that performs calculations and starts with a (=) sign is called a formula. It starts with the (=)sign and is followed by mathematical operations.
Q4. Explain the method to insert a row or column in Excel
To insert a row or a column, right-click on the row or column where they want to insert a new one, then select “Insert”. This will give you a new row or column depending on the requirement.
Q5. How do you format cells in Excel?
The user must select the cell that they want to format, then select “Format Cells” to change the different formatting options like format, font, color, or number.
Q6. What is a function in Excel?
A predefined formula that specifically performs calculations and manipulation of data is known as a function in Excel. It starts with a function name and is followed by arguments enclosed in parentheses.
Q7. Define a pivot table in Excel
It is a powerful tool used for analyzing and summarizing data in a spreadsheet. A pivot table also allows a user to group and aggregate data in different ways.
Q8. Explain the steps to freeze columns and rows in Excel
The user should first select the cell below the row column that they want to freeze, then select the “View” tab and click on “Freeze Panes” and select it to freeze rows and columns in Excel.
Q9. How to sort data in Excel?
The user should first select the cells that they want to sort, then click on “Sort” on the “Data” tab, then select the column that they want to sort by and in sort order which can be ascending or descending.
Q10. Explain how to create a chart in Excel?
The user must select the data that they want to chart and then click on the “Insert” tab, then choose the type of chart that they want to chart their data in from the Chart group.
Q11. What is the difference between Relative cell referencing and Absolute cell referencing?
| Relative Cell Referencing | Absolute Cell Referencing |
|---|---|
| There is a change when copying a formula from one cell to another in Relative Cell Referencing. | There is no change when copying a formula from one cell to another in Absolute Cell Referencing. |
| Relative Cell Referencing is built by default. | Absolute Cell Referencing is not built by default. |
| This does not require a dollar sign in the formula when a change is made in it. | This requires a dollar sign in the formula when a change is made in it. |
Q12. List the order of operations that are used to evaluate formulas in Excel
The order that is used in Excel while evaluating formulas is known as PEDMAS. Here is the meaning of the order:
- Parentheses
- Exponentiation
- Division/Multiplication
- Addition
- Subtraction
You can take up a professional Excel course for a better and in-depth understanding of Excel and its functions.
Intermediate MS Excel Interview Questions and Answers
Now that we have covered basic Excel interview questions. Following below are some intermediate MS Excel Interview questions and answers:
Q13. What is the difference between LOOKUP and VLOOKUP?
| LOOKUP | VLOOKUP |
|---|---|
| This function enables the user to look for data in a row or column. | This function enables the user to look for data in the leftmost column of the table. |
| LOOKUP returns the value in another row or column. | VLOOKUP returns the value in the left to the right way. |
| It is easier to use. | It is not as easy to use as LOOKUP. |
Q14. What is the difference between a Formula and a Function in Excel?
| Formula | Function |
|---|---|
| An equation in Excel is known as a Formula. | The predefined calculations in Excel are known as Functions. |
| The calculation type depends on the user. | The calculation is in-built. |
| It needs to be typed manually. | It is built-in in Excel. |
| It consumes more time. | Calculations can be done faster. |
Q15. Explain Data Validation
The work of Data Validation in Excel is to restrict the type of values that a user can enter into a particular cell or a range of cells. The user should select the “Data Validation” option in Data Tools, then they should select the kind of data validation they want to apply.
Q16. Explain how are nested IF statements used in Excel
The nested IF statements are used in Excel when the user has multiple conditions to meet, the FALSE value in the IF function can be replaced with another IF function if any further test is required.
Q17. Explain how Slicer works in Excel
Slicer is used in Excel when the data needs to be filtered in pivot tables. Here are the ways to use Slicer:
- Click on the Insert tab and select the Slicer option which is present under Filter.
- After that, the user should select the list of fields for which they want to create Slicers.
Q18. Can users create a Pivot table using multiple sources?
Yes, it is possible to create a pivot table using multiple sources or worksheets but there must be a common row in the tables, which will act as the Primary key for the first table and the Foreign key for the second one. By creating a relationship between these tables, a user can then build a pivot table.
Q19. Explain the conditioning formula in Excel and how it is used
A tool in Excel that allows the user to apply formatting to cells based on certain criteria is known as the conditioning formula. To use it, the user must select the cells that they want to apply it to, and after that, they must choose the formatting options that they want to apply.
Q20. Explain macros in Excel
A program that resides within Excel and automates the repetitive tasks that the user wants Excel to perform is known as a Macro. It is used by recording the task, and to record the macro the user must go to the Developer tab and then click on Record Macro or they can access it from the View tab. Once it is recorded, the Macro is set to perform its functions.
Q21. How do you create a column in a pivot table?
The user has to go to the PivotTable Analyze and then select the ‘Fields, Items & Sets’ option, and then click on “Calculate Field” will appear, the user must give a name to the column. Then click “Add” followed by “OK” to create a new column in the pivot table.
Q22. How can you provide Dynamic Range in the Data Source of the Pivot Table?
Dynamic range is used in the Data source of the pivot table to make the pivot table dynamic enough to adjust the new data when it refreshes automatically. The user must create a named table to provide a dynamic range, then go to the Insert tab and select Table, under which they should give a name to the table.
Advanced Excel Interview Questions and Answers
Now that you have covered a significant range of interview questions in MS Excel, let us move on to some advanced Excel interview questions that are usually asked in Excel interviews.
Q23. Explain What-If analysis in Excel
A tool that is powerful enough to perform complex calculations in Excel, experiment with data, and try different scenarios is known as the What-if analysis. The user must go to the “Data” tab and click on “What-if” analysis under forecast, which will enable this function to perform all the complex calculations.
Q24. List the differences between a function and a subroutine
| Function | Subroutine |
| They return the value of the task it is performing. | They do not return the value of the task it is performing. |
| These can be also called a variable. | These can be recalled from anywhere in the program. |
| These are used as formulas in the spreadsheet. | These are not directly used as formulas in the spreadsheet. |
| They can carry out repetitive tasks. | The user needs to insert a value to carry out any task. |
Q25. How is a VBA code debugged?
The user can use the F8 key to debug the VBA code line by line, they can also create a breakpoint to execute the termination wherever they want. The execution is supposed to start from the beginning and every time the user will press the F8 key, it will execute the next line and this will continue until the end of the code.
Q26. How to use COUNTIF in Excel?
This formula in Excel allows the user to count the number of cells in a range that meets certain criteria. For example, a user can use the COUNTIF function to count the number of cells in a range that contains the word “banana”.
Q27. List the difference between a worksheet and a workbook in Excel
A single tab within a workbook containing cells, columns, and rows for organizing and analyzing data is known as a worksheet. Whereas, a file in Excel containing one or more worksheets is known as a workbook.
Q28. Explain VBA
A programming language that is used to automate tasks in Excel is known as VBA or Visual Basic for Applications.
Q29. List some useful loops in VBA
Following are some useful loops in VBA:
- For Next Loop
- Do While Loop
- Do Until Loop
- For Each Next Loop
Q30. Name some text functions
CONCATENATE(): It is used to join several text strings to one string.
PROPER(): It arranges the characters properly.
TEXT(): This text function Converts a value into text formatting
LEFT(): It returns the specified number of characters from the starting character.
Q31. Define charts in Excel
Charts are provided in Excel to enable the graphical presentation of the data. The user can use any chart type, including column, bar, line, pie, or scatter, by clicking “Insert” and then selecting the type of chart as per their requirement.
Q32. List the different ways to run a macro
Following are the ways to run a macro:
- Assigning it to a particular shape.
- Assigning it to a button.
- Running a macro from the ribbon.
- The user can run a macro using a keyboard shortcut key.
Q33. Define the volatile function in Excel
A function that recalculates the formula so the Excel workbook performance slows down is known as Volatile Function. It automatically recalculates the formula whenever any changes are made in the worksheet.
Q34. Define ADO, ODBC, and OLEDB
- ADO: a data access framework that is useful to get the data from the databases is known as ActiveX Data Objects.
- ODBC: It stands for Open Database Connectivity and is used to get the data from the external database.
- OLEDB: It is known as Object Linking and Embedding, Database.
Most Basic Excel Interview Questions
Following are some more Excel interview questions commonly asked during an interview:
- Explain the difference between count, count, and count blank.
- Explain the shortcut that is used to add a filter to a table.
- How do you create a hyperlink in Excel?
- How can you merge multiple cells text strings in a cell?
- Name the report formats available in Excel.
- How would you create a drop-down list in Excel?
- How would you apply advanced filters in Excel?
- How can you use SUMIF() function in Excel?
- How would you find duplicate values in a column?
- How can you remove duplicate values in a range of cells?
- What is the Excel shortcut for spell check?
- How to cancel an entry using a shortcut in Excel?
- Explain the news enhancement in Excel.
- What is the shortcut key to minimize the workbook in Excel?
Conclusion
Excel is a powerful tool used for data management, and it is widely popular in various industries for its versatility. Candidates may be asked about their proficiency along with Excel interview questions and advanced Excel functions depending on the role that they have applied for.
