Advanced Level Excel Interview Questions

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.

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.

50. What is the purpose of the COUNTIF function?

The COUNTIF function counts the number of cells that meet a single condition.

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 Freshers and experienced professionals. These interview questions are also helpful for individuals who are preparing for roles such as data analysts, business analysts, and accountants. These Excel Interview Questions cover everything about Excel 365, from the basics to advanced formulas, VBA, macros, and more.