Top 51 excel interview questions And Answers 2024

1. What is a cell address in Excel? 

2. What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?

3. How do you freeze panes in Excel?

4. How can you restrict someone from copying a cell from your worksheet?

5. How is a Formula different from a Function in Excel?

6. Mention the order of operations used in Excel while evaluating formulas.

7. How will you write the formula for the following? – Multiply the value in cell A1 by 10, add the result by 5, and divide it by 2.

8. What is the difference between count, counta, and countblank?

9. What is the shortcut to add a filter to a table?

10. How do you create a hyperlink in Excel?

11. How can we merge multiple cells text strings in a cell?

12. How can you split a column into 2 or more columns?

13. What is the use of VLOOKUP and how do we use it?

14. How is VLOOKUP different from the LOOKUP function?

15. How many report formats are available in Excel?

16. How does the IF() function in Excel work?

17. How do we use the SUMIF() function in Excel?

18. Using the COVID data, find the number of days in which the number of deaths in Italy has been greater than 200.

19. What is a Pivot Table?

20. Create a drop-down list in Excel.

21. How do we apply advanced filters in Excel?

22. Using the below-given sales data, highlight those cells where total sales > $5000.

23. Using the given table, explain how the index-match function works in Excel.

24. How do you find duplicate values in a column?

25. How can you remove duplicate values in a range of cells?

26. What are the wildcards available in Excel?

27. What is Data Validation? Illustrate with an example.

28. Given below is a student table. Write a function to add pass/fail to the results column based on the following criteria.

29. Calculate your age in years from the current date.

30. How are nested IF statements used in Excel?

31. From the below table, find the descriptive statistics of the columns using the Data Analysis ToolPak in Excel.’

32. Using the Coronavirus dataset, create a pivot table to find the total cases in each country belonging to their respective continents.

32. Using the Coronavirus dataset, create a pivot table to find the total cases in each country belonging to their respective continents.

33. How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?

34. Is it possible to create a Pivot Table using multiple sources of data?

35. Create a pivot table to find the top three countries from each continent based on the total case using covid data.

36. How do you create a column in a pivot table?

37. How does a Slicer work in Excel?

38. Use the coronavirus dataset to find the percentage contribution of each country and continent to the total cases?

39. How do you create a pivot chart in Excel?

40. What are macros in Excel? Create a macro to automate a task.

41. What is the What-If Analysis in Excel?

42. What is the difference between a function and a subroutine in VBA?

43. What is the difference between ThisWorkbook and ActiveWorkbook in VBA?

44. How will you pass arguments to VBA Function?

45. How do you find the last row and column in VBA?

46. How do we check whether a file exists or not in a specified location?

47. Explain how to debug a VBA code?

48. Write a VBA function to calculate the area of a rectangle.

49. Write a VBA function to check if a number is a prime number or not.

50. Write a VBA code to create a bar chart with the given data.

51. How can you fetch the current date in excel?

Excel Questions And Answers 2024

1. What is a cell address in Excel?

Ans.A cell address in Excel is a unique identifier for a cell, formed by combining the column letter and the row number. For example, “A1” refers to the cell in the first column and first row.

2.What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?

Ans.Relative cell referencing adjusts the cell address based on its relative position when copied to another location. Absolute cell referencing, on the other hand, keeps the cell address fixed when copied, using a dollar sign ($) before the column letter and/or row number.

3.How do you freeze panes in Excel?

Ans.To freeze panes in Excel, go to the View tab, select “Freeze Panes,” and choose either “Freeze Panes” to freeze rows and columns above and to the left of the selected cell, or “Freeze Top Row” to freeze only the top row.

4.How can you restrict someone from copying a cell from your worksheet?

Ans. You can protect a worksheet and prevent cell copying by going to the Review tab, selecting “Protect Sheet,” and specifying a password. Then, choose the actions users are allowed or not allowed to perform, including copying cells.

5.How is a Formula different from a Function in Excel?

Ans. A formula in Excel is a mathematical expression that performs calculations, while a function is a predefined formula that performs a specific operation. Functions are built-in and can simplify complex calculations.

6.Mention the order of operations used in Excel while evaluating formulas.

Ans.Excel follows the order of operations (PEMDAS): Parentheses, Exponents, Multiplication and Division (from left to right), and Addition and Subtraction (from left to right).

7.How will you write the formula for the following? – Multiply the value in cell A1 by 10, add the result by 5, and divide it by 2.

Ans.The formula would be: =((A1*10)+5)/2

8.What is the difference between count, counta, and countblank?

Ans.COUNT counts the number of cells that contain numbers. COUNTA counts the number of cells that are not empty. COUNTBLANK counts the number of empty cells.

9.What is the shortcut to add a filter to a table?

Ans.The shortcut to add a filter to a table is Ctrl + Shift + L.

10.How do you create a hyperlink in Excel?

Ans.Select the cell where you want the hyperlink, right-click, choose “Hyperlink,” and enter the link address. Alternatively, use the shortcut Ctrl + K.

12.How can we merge multiple cells text strings in a cell?

Ans.Use the CONCATENATE function or the & operator. For example, =A1 & B1 merges the text in cells A1 and B1.

13.How can you split a column into 2 or more columns?

Ans.Use the “Text to Columns” feature under the Data tab. Choose the delimiter that separates the text into columns.

14.What is the use of VLOOKUP and how do we use it?

Ans.VLOOKUP is used to search for a value in a table and return a corresponding value in the same row. The syntax is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

15.How is VLOOKUP different from the LOOKUP function?

Ans.VLOOKUP is used to find a value in a table and return a corresponding value, while LOOKUP searches for a value in a range and returns the corresponding value in the same position.

16.How many report formats are available in Excel?

Excel offers various report formats, including

• Compact Form
• Outline Form
• Tabular Form

17.How does the IF() function in Excel work?

• The IF function in Excel allows you to perform a logical test and return one value if the test is true and another if false. The syntax is: =IF(logical_test, value_if_true, value_if_false).