Microsoft Excel Advanced Formulas and Macros Questions and Answers

Question 1

An analyst needs to create a dynamic, spillable list of all sales records from a table named 'SalesData' where the '[Region]' is "East" AND the '[Revenue]' is greater than 10000.
Which of the following formulas is the most appropriate for this task?

Accepted Answer

=FILTER(SalesData, (SalesData[Region]="East") * (SalesData[Revenue]>10000))

Answer

=SUMIFS(SalesData[Revenue], SalesData[Region], "East", SalesData[Revenue], ">10000")

Answer

=IF(AND(SalesData[Region]="East", SalesData[Revenue]>10000), SalesData, "No Results")

Answer

=VLOOKUP("East", SalesData, 3, FALSE)

Question 2

A user is writing a complex formula that references the same calculated result multiple times, making the formula long and inefficient. Which function allows the user to assign a name to a calculation result and reuse that name within the formula to improve readability and performance?

Accepted Answer

LET

Answer

LAMBDA

Answer

IFERROR

Answer

SUBSTITUTE

Question 3

To perform a two-way lookup, finding a value at the intersection of a specific row and column in a data grid (B2:F10), what is the correct syntax using INDEX and MATCH? Assume the row lookup value is in H1 (searching A2:A10) and the column lookup value is in I1 (searching B1:F1).

Accepted Answer

=INDEX(B2:F10, MATCH(H1, A2:A10, 0), MATCH(I1, B1:F1, 0))

Answer

=INDEX(B2:F10, MATCH(H1, B1:F1, 0), MATCH(I1, A2:A10, 0))

Answer

=VLOOKUP(H1, A1:F10, MATCH(I1, B1:F1, 0), FALSE)

Answer

=HLOOKUP(I1, A1:F10, MATCH(H1, A2:A10, 0), FALSE)

Question 4

A developer is recording a macro that needs to perform a series of formatting steps starting from any active cell. To ensure the macro's actions are recorded based on their position relative to the starting cell (e.g., move one cell right), which setting must be enabled before performing the actions?

Accepted Answer

Use Relative References

Answer

Enable All Macros in Trust Center

Answer

The Personal Macro Workbook

Answer

Stop Recording

Question 5

A company policy requires that all macros in workbooks must be disabled by default, but users should be notified with a security warning and given the option to enable them if the workbook is from a trusted source. Which Macro Security setting in the Trust Center achieves this balance?

Accepted Answer

Disable all macros with notification

Answer

Enable all macros (not recommended)

Answer

Disable all macros without notification

Answer

Disable all macros except digitally signed macros

Question 6

In the formula `=XLOOKUP(A1, B:B, C:C, "Not Found", 0, -1)`, what is the purpose of the final argument, `-1`?

Accepted Answer

It instructs the function to perform a search from the last item to the first.

Answer

It forces the formula to find the next smallest item if an exact match is not found.

Answer

It indicates that the lookup array is sorted in descending order for a binary search.

Answer

It specifies that a wildcard match should be performed.

An analyst needs to create a dynamic, spillable list of all sales records from a table named 'SalesData' where the '[Region]' is "East" AND the '[Revenue]' is greater than 10000.Which of the following formulas is the most appropriate for this task?

An analyst needs to create a dynamic, spillable list of all sales records from a table named 'SalesData' where the '[Region]' is "East" AND the '[Revenue]' is greater than 10000.
Which of the following formulas is the most appropriate for this task?