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

The FILTER function is specifically designed to return a range of data that meets a set of criteria. To apply multiple 'AND' conditions, the logical tests for each condition are multiplied together.

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

The LET function assigns names to calculation results, allowing you to store an intermediate calculation and reuse it multiple times within the formula. This improves performance by calculating the result only once and makes the formula easier to read.

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

The correct structure is INDEX(data_range, row_number, column_number). The first MATCH(H1, A2:A10, 0) finds the correct row number based on the vertical list. The second MATCH(I1, B1:F1, 0) finds the correct column number based on the horizontal list.

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

By default, Excel records macros with absolute references (e.g., Range("C5").Select). Enabling 'Use Relative References' records actions based on their offset from the active cell, allowing the macro to be run from any starting point on the worksheet with the same relative effect.

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

The 'Disable all macros with notification' setting is the default and most common security level. It prevents macros from running automatically but provides a message bar, allowing the user to enable content on a case-by-case basis.

Excel Practice Test

Excel Practice Test

Microsoft Excel Advanced Formulas and Macros Questions and Answers