Advanced Excel MCQs (Multiple-Choice Questions)

1. Which Excel function can look up a value and return a corresponding value from another range without requiring the lookup column to be on the left?

1. VLOOKUP
2. XLOOKUP
3. HLOOKUP
4. LOOKUP

Answer: B) XLOOKUP

Explanation:

XLOOKUP searches a lookup array and returns a corresponding value from a return array. Unlike traditional VLOOKUP, the return array can be positioned to either side of the lookup array.

2. What is the default match mode of XLOOKUP?

1. Approximate match
2. Exact match
3. Wildcard match
4. Next smaller item

Answer: B) Exact match

Explanation:

The default match_mode of XLOOKUP is 0, which specifies an exact match. If no match is found, XLOOKUP returns #N/A unless another result is supplied through its if_not_found argument.

3. Which XLOOKUP argument specifies what should be returned when no match is found?

1. not_found
2. if_not_found
3. error_value
4. default_result

Answer: B) if_not_found

Explanation:

The optional if_not_found argument specifies the value or text XLOOKUP should return when it cannot find a valid match.

4. Which function combination is commonly used to perform a flexible lookup in older Excel versions before XLOOKUP?

1. INDEX and MATCH
2. SUM and COUNT
3. LEFT and RIGHT
4. IF and OR

Answer: A) INDEX and MATCH

Explanation:

INDEX can return a value from a range, while MATCH can determine the position of a lookup value. Combining them provides flexible lookup behavior.

5. Which function returns the position of a value within a range?

1. INDEX
2. MATCH
3. LOOKUP
4. OFFSET

Answer: B) MATCH

Explanation:

The MATCH function searches a range or array and returns the relative position of a matching item.

6. Which function returns a value from a specified row and column position in a range or array?

1. INDEX
2. MATCH
3. CHOOSECOLS
4. LOOKUP

Answer: A) INDEX

Explanation:

The INDEX function returns a value or reference from a specified position within an array or range.

7. Which formula correctly calculates the total sales in B2:B100 where the region in A2:A100 is "North"?

1. =SUMIF(A2:A100,"North",B2:B100)
2. =SUM(A2:A100,"North",B2:B100)
3. =COUNTIF(A2:A100,"North",B2:B100)
4. =TOTALIF(A2:A100,"North",B2:B100)

Answer: A) =SUMIF(A2:A100,"North",B2:B100)

Explanation:

SUMIF adds values in the sum range when the corresponding cells in the criteria range meet the specified condition.

8. When multiple criteria must be applied to calculate a conditional sum, which function is appropriate?

1. SUMIF
2. SUMIFS
3. COUNT
4. SUBTOTAL

Answer: B) SUMIFS

Explanation:

SUMIFS calculates a sum using multiple criteria ranges and criteria.

9. Which function counts cells that satisfy multiple criteria?

1. COUNT
2. COUNTA
3. COUNTIF
4. COUNTIFS

Answer: D) COUNTIFS

Explanation:

COUNTIFS counts cells or rows that satisfy multiple specified criteria.

10. Which function is useful for returning one value when a condition is TRUE and another when it is FALSE?

1. IF
2. CHOOSE
3. SWITCH
4. IFS

Answer: A) IF

Explanation:

The IF function evaluates a logical condition and returns one result when the condition is TRUE and another result when it is FALSE.

11. Which function is designed to test multiple conditions without repeatedly nesting IF functions?

1. IFS
2. IFERROR
3. AND
4. OR

Answer: A) IFS

Explanation:

The IFS function evaluates multiple conditions and returns the result corresponding to the first condition that evaluates to TRUE.

12. Which function returns a specified value when a formula evaluates to an error?

1. IFERROR
2. ERRORIF
3. IFNAERROR
4. HANDLEERROR

Answer: A) IFERROR

Explanation:

IFERROR returns the result of an expression when it does not produce an error and returns an alternative value when the expression results in an error.

13. Which function specifically handles the #N/A error?

1. IFERROR
2. IFNA
3. NAERROR
4. ERRORNA

Answer: B) IFNA

Explanation:

IFNA returns an alternative result when an expression evaluates specifically to the #N/A error.

14. What does the LET function allow you to do in an Excel formula?

1. Create worksheet protection rules
2. Assign names to calculation results or expressions within a formula
3. Create a PivotTable automatically
4. Convert formulas into VBA code

Answer: B) Assign names to calculation results or expressions within a formula

Explanation:

LET allows names to be assigned to intermediate calculations or values within a formula, making complex formulas easier to organize and potentially avoiding repeated calculations.

15. Which Excel function can define reusable custom functions using Excel's formula language?

1. LAMBDA
2. FUNCTION
3. CUSTOMFORMULA
4. DEFINE

Answer: A) LAMBDA

Explanation:

The LAMBDA function allows users to create custom reusable functions using Excel formulas without requiring VBA.

16. Which function returns only the rows that meet specified criteria from an array or range?

1. FILTER
2. SELECT
3. QUERY
4. SUBSET

Answer: A) FILTER

Explanation:

The FILTER function returns an array containing only the rows or columns that meet specified criteria.

17. Which function returns a list of unique values from a range?

1. UNIQUE
2. DISTINCT
3. REMOVE.DUPLICATES
4. ONLY

Answer: A) UNIQUE

Explanation:

The UNIQUE function returns a list of unique values from a range or array.

18. What is the main characteristic of a dynamic array formula?

1. It can return multiple results that spill into neighboring cells
2. It can only return one cell
3. It requires VBA
4. It cannot reference ranges

Answer: A) It can return multiple results that spill into neighboring cells

Explanation:

Dynamic array functions can return multiple values from a single formula. Excel automatically spills the results into adjacent cells when sufficient space is available.

19. What happens if cells required by a dynamic array formula are not available for its spilled results?

1. The formula returns a #SPILL! error
2. The formula automatically deletes the blocking cells
3. The formula converts to a static value
4. The workbook closes

Answer: A) The formula returns a #SPILL! error

Explanation:

A dynamic array formula cannot spill into occupied or otherwise blocked cells. In such a situation, Excel displays the #SPILL! error.

20. Which function can sort an array dynamically within a formula?

1. SORT
2. ORDER
3. ARRANGE
4. RANKARRAY

Answer: A) SORT

Explanation:

The SORT function dynamically sorts the contents of an array or range based on the specified sorting parameters.

21. Which function can sort an array according to the values in another corresponding array?

1. SORTBY
2. SORTWITH
3. ORDERBY
4. RANKBY

Answer: A) SORTBY

Explanation:

SORTBY sorts a range or array based on the values in one or more corresponding ranges or arrays.

22. Which function can generate a sequential array such as 1, 2, 3, 4, 5?

1. SEQUENCE
2. SERIES
3. GENERATE
4. AUTONUMBER

Answer: A) SEQUENCE

Explanation:

The SEQUENCE function generates an array containing sequential numbers according to the specified rows, columns, start value, and step.

23. What is the main purpose of an Excel Table?

1. To provide structured data management with features such as automatic expansion and structured references
2. To convert a workbook into a PDF
3. To replace all formulas with values
4. To create a VBA project automatically

Answer: A) To provide structured data management with features such as automatic expansion and structured references

Explanation:

Excel Tables provide structured data organization, automatic expansion, filtering, calculated columns, and structured references that can make formulas easier to maintain.

24. What is a structured reference in Excel?

1. A reference using Excel Table names and column names
2. A reference that can only contain absolute cell addresses
3. A VBA reference to another workbook
4. A reference to a chart object

Answer: A) A reference using Excel Table names and column names

Explanation:

Structured references allow formulas to refer to Excel Table columns using table and column names instead of traditional cell references.

25. What does a PivotTable primarily provide?

1. A way to summarize and analyze data interactively
2. A method for writing VBA procedures
3. A replacement for all worksheet formulas
4. A method for encrypting a workbook

Answer: A) A way to summarize and analyze data interactively

Explanation:

PivotTables summarize large datasets by organizing fields into areas such as Rows, Columns, Values, and Filters, allowing users to analyze data from different perspectives.

26. Which PivotTable area normally contains fields whose values are aggregated?

1. Rows
2. Columns
3. Values
4. Filters

Answer: C) Values

Explanation:

The Values area contains fields that Excel summarizes using calculations such as Sum, Count, Average, Minimum, or Maximum.

27. Which feature provides clickable visual filtering controls for PivotTables?

1. Slicers
2. Macros
3. Scenarios
4. Solver

Answer: A) Slicers

Explanation:

Slicers provide interactive buttons that allow users to filter PivotTables and other supported data structures visually.

28. What is the purpose of a PivotChart?

1. To visually represent data associated with a PivotTable or PivotChart report
2. To execute VBA code
3. To create worksheet formulas
4. To replace Power Query

Answer: A) To visually represent data associated with a PivotTable or PivotChart report

Explanation:

A PivotChart provides a graphical representation of summarized PivotTable data and can respond to PivotTable filtering and field changes.

29. What is the purpose of grouping dates in a PivotTable?

1. To organize dates into periods such as years, quarters, or months
2. To convert dates into text permanently
3. To delete duplicate dates
4. To protect date cells

Answer: A) To organize dates into periods such as years, quarters, or months

Explanation:

PivotTable date grouping can organize individual dates into useful periods such as years, quarters, months, and days for analysis.

30. What does refreshing a PivotTable generally do?

1. Updates the PivotTable using the current source data
2. Deletes all PivotTable fields
3. Converts the PivotTable to a normal range
4. Removes all filters permanently

Answer: A) Updates the PivotTable using the current source data

Explanation:

Refreshing a PivotTable updates its results based on changes in the underlying data source or data model.

31. Which Excel feature is designed primarily for importing and transforming data before loading it into Excel?

1. Power Query
2. Goal Seek
3. Watch Window
4. Scenario Manager

Answer: A) Power Query

Explanation:

Power Query is designed for connecting to data sources and performing data transformation and preparation operations before loading the resulting data.

32. Which language is used by Power Query for its transformation expressions?

1. DAX
2. M
3. VBA
4. SQL only

Answer: B) M

Explanation:

Power Query uses the M language for data transformation and query expressions.

33. In Power Query, what does an Applied Step represent?

1. A transformation or operation performed in the query
2. A worksheet cell reference
3. A PivotTable calculation
4. A VBA procedure

Answer: A) A transformation or operation performed in the query

Explanation:

Power Query records transformations as Applied Steps. Each step represents an operation applied to the data and can generally be reviewed or modified in the Query Editor.

34. Which Power Query operation is appropriate for combining rows from two tables with compatible columns?

1. Append Queries
2. Merge Cells
3. Transpose Only
4. Group Columns

Answer: A) Append Queries

Explanation:

Append Queries combines rows from multiple tables or queries. It is useful when datasets have similar column structures and need to be stacked vertically.

35. Which Power Query operation combines columns from related tables based on matching values?

1. Append Queries
2. Merge Queries
3. Transpose
4. Fill Down

Answer: B) Merge Queries

Explanation:

Merge Queries combines tables by matching rows based on one or more selected columns, similar to joining tables in relational data operations.

36. What is the main purpose of Power Pivot?

1. To work with data models and perform advanced analysis
2. To create standard text documents
3. To replace the Excel formula engine
4. To edit images

Answer: A) To work with data models and perform advanced analysis

Explanation:

Power Pivot provides capabilities for building data models, creating relationships between tables, and performing calculations using DAX.

37. What does DAX stand for?

1. Data Analysis Expressions
2. Data Access XML
3. Dynamic Analysis Extension
4. Database Analysis Exchange

Answer: A) Data Analysis Expressions

Explanation:

DAX stands for Data Analysis Expressions. It is the formula language used for calculations in Power Pivot and other Microsoft data-modeling technologies.

38. Which DAX function is commonly used to calculate the sum of a column?

1. SUM
2. TOTAL
3. ADD
4. SUMCOLUMN

Answer: A) SUM

Explanation:

The DAX SUM function adds all the numbers in a specified column.

39. Which Excel feature can automatically format cells based on rules or conditions?

1. Conditional Formatting
2. Data Validation
3. Goal Seek
4. Text to Columns

Answer: A) Conditional Formatting

Explanation:

Conditional Formatting applies formatting based on specified conditions, such as values greater than a threshold, duplicate values, or custom formulas.

40. Which Excel feature can create a selectable drop-down list inside a cell?

1. Conditional Formatting
2. Data Validation
3. Scenario Manager
4. Flash Fill

Answer: B) Data Validation

Explanation:

Data Validation can restrict input and can provide a drop-down list of permitted values.

41. What is the purpose of an absolute reference such as $A$1?

1. Both the row and column remain fixed when the formula is copied
2. Only the row remains fixed
3. Only the column remains fixed
4. The reference changes automatically in every direction

Answer: A) Both the row and column remain fixed when the formula is copied

Explanation:

The dollar signs in $A$1 make both the column and row absolute, so the reference remains unchanged when the formula is copied.

42. What does the reference $A1 mean?

1. The column is absolute and the row is relative
2. The row is absolute and the column is relative
3. Both row and column are absolute
4. Both row and column are relative

Answer: A) The column is absolute and the row is relative

Explanation:

In $A1, the dollar sign fixes column A while the row number remains relative when the formula is copied.

43. Which Excel feature allows a meaningful name such as SalesData to refer to a cell or range?

1. Named Range
2. Table Style
3. Cell Alias Format
4. Range Label

Answer: A) Named Range

Explanation:

A named range assigns a meaningful name to a cell, range, constant, or formula, allowing the name to be used in formulas and other Excel features.

44. Which Excel tool is used to determine what input value is needed to reach a specific formula result?

1. Goal Seek
2. Solver only
3. Flash Fill
4. Remove Duplicates

Answer: A) Goal Seek

Explanation:

Goal Seek works backward from a desired result and changes one input cell to determine the value required to achieve that result.

45. Which Excel tool can optimize an objective subject to multiple constraints?

1. Solver
2. Goal Seek
3. AutoSum
4. Flash Fill

Answer: A) Solver

Explanation:

Solver can optimize an objective cell by changing decision variables while respecting specified constraints.

46. Which function extracts a specified number of characters from the beginning of a text string?

1. LEFT
2. RIGHT
3. MID
4. START

Answer: A) LEFT

Explanation:

The LEFT function returns a specified number of characters from the left side of a text string.

47. Which formula extracts five characters starting from the third character of the text in A1?

1. =MID(A1,3,5)
2. =MID(A1,5,3)
3. =LEFT(A1,5)
4. =RIGHT(A1,3)

Answer: A) =MID(A1,3,5)

Explanation:

MID uses the syntax MID(text,start_num,num_chars). Therefore, the formula starts at character 3 and returns 5 characters.

48. Which function returns the number of characters in a text string?

1. COUNT
2. LEN
3. CHARCOUNT
4. TEXTLEN

Answer: B) LEN

Explanation:

The LEN function returns the number of characters in a text string, including spaces.

49. What is the result of the following formula if A1 contains 120 and B1 contains 100?

=IF(A1>B1,"Above Target","Below Target")

1. Above Target
2. Below Target
3. TRUE
4. FALSE

Answer: A) Above Target

Explanation:

The condition A1>B1 evaluates to TRUE because 120 is greater than 100. Therefore, the IF function returns the first text result, "Above Target".

50. A worksheet contains employee IDs in A2:A100 and departments in B2:B100. Which formula returns a dynamic list of unique departments?

1. =UNIQUE(B2:B100)
2. =DISTINCT(B2:B100)
3. =REMOVE.DUPLICATES(B2:B100)
4. =SORT(B2:B100,UNIQUE)

Answer: A) =UNIQUE(B2:B100)

Explanation:

The UNIQUE function returns the distinct values from the supplied range as a dynamic array. If the resulting values change, the spilled output can update automatically.