Microsoft Excel, a popular spreadsheet program from Microsoft is extremely important for businesses, analysts, and professionals in many industries. It helps manage, analyze, and show data, making Excel skills very valuable making it favoured by top companies like JPMorgan Chase, KPMG, Deloitte, and PwC for its simplicity and powerful features.
In this article, we will look at the 50+ Excel Questions And Answers perfect for both beginners and experienced professionals. These interview questions are also helpful for individuals who are preparing for roles such as data analysts, business analysts, and accountants. The article on Interview Questions has been updated to cover everything about Excel 365, from the basics to advanced formulas, VBA, macros, and more.
Excel Interview Questions for Freshers
1. 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 buil-in functions such as COUNTIF, SUM etc.
- Creating charts
- Creating Graphs for Data Visualisation and more.
2. Explain the difference between a workbook and a worksheet.
- A workbook is a spreadsheet program file that you create in Excel. A workbook contains one or more worksheets.
- A worksheet consists of cells in which you can enter and calculate data. The cells are organized into columns and rows.
3. How do you navigate between worksheets in a workbook?
Use sheet tabs at the bottom of the Excel window.
MS Excel
4. How do you reference a cell in a formula?
Use the cell address in the formula (for example, A1).
5. Explain the difference between relative and absolute cell references?
When a formula is copied, relative references change, while absolute references remain constant.
For example, $A$1 is an absolute cell reference as it will not change if we move from one cell to another. However, relative cell reference is by default used by Excel and the cell reference will change if we move from one cell to another.
6. What is the order of operations in Excel formulas?
The order of operations in Excel formulas is
- Parentheses
- Exponents
- Multiplication and Division (left to right)
- Addition and Subtraction (left to right).
7. How do you create a chart in Excel?
Select data you want to include, go to the “Insert” tab, and choose a chart type.
MS Excel Toolbar
8. What is conditional formatting, and how do you apply it?
It’s formatting applied based on specified conditions. To apply conditional formatting, select a category, go to “Home” > “Conditional Formatting.”
MS Excel Toolbar
9. Explain the VLOOKUP function.
VLOOKUP is a function in Excel that searches for a value in the first column of a range and returns a value from the second column in the same row.
10. What is a cell in Excel?
A cell is the intersection of a column and a row, identified by a unique address (for example, A1).
11. How do you freeze panes in Excel?
Go to the “View” tab, select “Freeze Panes,” and choose an option.
12. What is the CONCATENATE function used for?
The CONCATENATE function combines two or more text strings into one string.
13.How can you remove duplicates in Excel?
Select the range, go to “Data” > “Remove Duplicates.”
14. What is the difference between CONCATENATE and CONCAT functions?
CONCAT is a new function that replaces CONCATENATE, providing additional features.
15. How do you protect a worksheet or workbook with a password?
Right-click on the sheet tab or workbook tab, choose “Protect Sheet” or “Protect Workbook,” and set a password.
16. Explain the PivotTable function.
Some of the functions of PivotTable are:
- Summarizes and analyzes data from a range into a concise, tabular format.
- Aggregates data based on arithmetic operations.
- Allows filtering and sorting of data.
- Enable deep data analysis.
17. 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.
18. How do you transfer data in Excel?
Copy the data, right-click the destination cell, select “Transpose” under “Paste Special.“
19. Explain the HLOOKUP function.
The HLOOKUP function is used to search for a value in the first row of a range and returns a value in the same column from another row.
20. What is the Paste Special feature used for?
It allows you to choose specific formatting options when you paste data.
21. How do you find and replace data in Excel?
Press Ctrl + H to open the Find and Replace dialog box.
22. What is the IF function, and how is it used?
It performs a logical test and returns one value if true and another if false.
23. How do you create a drop-down list in Excel?
Use the Data Validation feature under the “Data” tab.
24. 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.
25. How can you round a number to a specified number of decimal places in Excel?
Use the ROUND function, for example, =ROUND(A1,2) rounds the values in A1 to 2 decimal places.
Excel Interview Questions for Experienced
26. What is the purpose of the CONCAT function?
Concatenates a category or multiple ranges.
27. Explain the difference between a relative and an absolute reference in a formula.
|
Relative Reference
|
Absolute Reference
|
- A relative reference changes when the formula is copied.
|
- An absolute reference remains fixed.
|
- It adjusts with the movement of Rows and Columns.
|
- It does not adjust with the movement of Rows and Columns
|
- Useful for applying same formula across multiple cells.
|
- Useful for referencing a specific cell that should not change.
|
- If the original formula in A1 is “
=B1+C1" and is copied to A2, it gets adjusted to “=B2+C2"
|
- If the original formula in A1 is “
=$B$1+$C$1" and copied to A2, it remains as “=$B$1+$C$1".
|
28. How do you use the IFERROR function?
It returns a custom result if a formula generates an error; otherwise, it returns the result of the formula.
29. What is the difference between a line chart and a scatter plot?
- A line chart connects data points with lines
- A scatter plot displays individual data points.
30. How do you use the SUMIF and SUMIFS functions?
SUMIF adds values based on a single criterion. SUMIFS does the same with multiple criteria.
31. What is the purpose of the TRIM function?
Removes extra spaces from text, leaving only single spaces between words.
32. How do you create a named range in Excel?
Select the range, go to the “Formulas” tab, and click “Define Name.”
33. Explain the purpose of the VBA (Visual Basic for Applications) in Excel.
The main purpose of VBA is that it allows automation of tasks and the creation of custom functions using the Visual Basic programming language.
34. How do you password-protect a workbook?
Go to “File” > “Info” > “Protect Workbook” > “Encrypt with Password.”
35. What is the difference between CONCATENATE and TEXTJOIN functions?
TEXTJOIN is a more versatile function that can join text using a specified delimiter and ignore empty cells.
36. How do you use the COUNTBLANK function?
It counts the number of blank cells in a range.
37. Explain the terms ‘workbook’
A workbook is the complete Excel file containing multiple worksheets.
38. How do you create a macro in Excel?
Go to the “View” tab, click “Macros,” select “Record Macro,” perform actions and stop recording.
39. What is the purpose of the PMT function in Excel?
The PMT function calculates the payment for a loan based on a constant interest rate and periodic payments.
40. How do you create a data table in Excel?
Use the “What-If Analysis” tool in the “Forecast” Groupunder the “Data” tab.
41. Explain the significance of the ROUND function in Excel.
The ROUND Function rounds a number to a specified number of digits.
42. What is the purpose of the NETWORKDAYS function?
It calculates the number of whole workdays between two dates, excluding weekends and optionally specified holidays.
43. How can you link data between different worksheets?
Use cell references or create formulas that reference cells in other worksheets.
44. Explain the difference between the terms ‘filter’ and ‘sort’ in Excel.
- Sorting arranges data in a specified order
- Filtering displays only the data that meets specific criteria.
45. How do you use the AVERAGEIF and AVERAGEIFS functions?
- AVERAGEIF calculates the average based on a single condition.
- AVERAGEIFS does the same with multiple criteria.
46. What is the purpose of the SUBTOTAL function?
It calculates a subtotal in a list or database, ignoring other subtotals.
47. How do you convert text to columns in Excel?
Use the “Text to Columns” feature under the “Data” tab.
48. Explain the importance of the MAX and MIN functions in Excel.
- MAX returns the highest value
- MIN returns the lowest value in a range.
49. How can you create a histogram in Excel?
Use the “Histogram” tool in the “Data Analysis” toolpack.
MS Excel Toolbar
50. What is the purpose of the COUNTIF function?
The COUNTIF function counts the number of cells that meet a single condition.
Conclusion
Excel is a helpful tool for managing and analyzing data, whether you are a beginner or an expert. Beginners focus on the basics like cells and formulas, while intermediates can explore advanced functions and charts. Advanced users can also get involved in programming and complex data analysis.
No matter your skill level, being good at Excel is valuable for many jobs. These Excel questions serve as a guide to help you learn and demonstrate your Excel skills in the interview. The more you practice and learn, the better you will become at using Excel for a variety of tasks ranging from simple to complex. So, keep learning and using Excel, and you’ll be an expert in no time!
Excel Interview Questions with Answers – FAQs
What are the Five Basic Excel Functions?
The five basic Excel Functions are:
- The VLookup Function.
- The Concatenate Function.
- Text to Columns.
- Remove Duplicates.
- Pivot Tables.
What are the 5 Basic Excel Formulas?
The 5 Basic Excel Formulas are:
- =SUM(C2:C5)
- =MIN(E2:E5)
- =MAX(E2:E5)
- =AVERAGE(C2:C5)
- =COUNT(E2:E5)
How do I prepare for an Excel Interview?
Prior to the interview, engage in practicing various Excel tasks.
Get ready to respond to common interview inquiries.
Review and update your resume along with other application documents.
What is Excel basic questions?
Some of the basic Excel questions are:
- What is Microsoft Excel?
- What are the basic components of Excel?
- How do you enter data into Excel?
- What is a formula in Excel?
- How can you save an Excel document?
What is formula bar in Excel?
A formula bar is that toolbar in Excel that is situated just beneath the Ribbon and above the Spreadsheet Grid.
Please Login to comment...