Excel interview questions for data analysts: 18 questions by level

=SUM(B2:B5) adds the numeric values in the Amount column and returns the total expense. =AVERAGE(B2:B5) would give the mean instead of the total, which is a common mix-up in basic Excel tasks. =COUNT(B2:B5) counts numeric cells, and =SUM(A2:A5) tries to add text month names, so neither produces the requested total.

A filter on the Region column hides nonmatching rows and leaves only East visible, which is the required action. Sorting changes row order but still shows every row. Conditional formatting only changes appearance, not visibility, and freezing the top row keeps headers visible while scrolling but does not filter data. This question tests the practical difference between filtering and other common worksheet actions.

Sorting column B from smallest to largest orders the sales values numerically, which is what the report needs. Sorting column A alphabetically would rearrange reps, not sales amounts. Filtering blanks is unrelated, and Find searches for text or numbers without changing order. The key concept is choosing the correct column and sort direction for numeric data.

=XLOOKUP("P200",A2:A4,B2:B4) searches the ProductID column for an exact match and returns the corresponding value from the Price column. The first wrong option uses the price range as the lookup array, so it is looking in the wrong column. The second wrong option reverses lookup and return arrays, and the third returns the ID column instead of the price. XLOOKUP needs the lookup array and return array to be aligned row by row.

If the source rows were loaded twice, the pivot table is correctly summing duplicated data, so the first fix is to remove duplicates in the source table. Changing the value field to Count would replace totals with record counts, which answers a different question. Sorting or moving fields changes layout, not the underlying double-counting problem. The issue here is duplicate source records, not the pivot layout itself.

To show total sales by department, put Department in Rows and Sales in Values summarized by Sum. Putting Sales in Rows is not a useful category layout, and Count of Department would count records instead of adding sales amounts. Using Department as Columns can also work in some reports, but the question asks for the standard layout for totals by department, which is Rows plus Sum. The value field aggregation is the key concept.

=AVERAGE(C2:C5) calculates the mean of the four order values, which is exactly what the task asks for. SUM would give the total of all orders, not the average. COUNT returns the number of numeric entries, and MIN returns only the smallest value. This question checks the basic AVERAGE function on a clearly shown data range.

SUMIFS is correct because it adds values from the Amount column only where the Status column equals Paid. The common mistake is using SUMIF with the sum range and criteria range reversed or misapplied; that would not sum invoice amounts by status. COUNTIFS is also tempting, but it counts matching rows instead of adding the monetary values. A plain SUM ignores the Paid filter and returns all invoices, including Open ones.

A pivot table summarizes numeric fields by Sum by default, so if you need average deal size, the Value Field Settings must be changed from Sum to Average. Moving the field to Rows changes the layout, not the calculation, and filtering by quarter only removes data without changing the aggregation type. Report layout options control presentation, not whether Excel sums or averages the values. This is a common pivot-table adjustment in analyst tests.

AVERAGEIF is the right function when one condition determines which numeric values to average. The first option averages only salaries where Department equals Sales. The plain AVERAGE formula includes every salary, not just Sales. SUMIF gives the total salary, which is a different result, and COUNTIF returns how many Sales employees there are rather than the average salary.

XLOOKUP is correct because it searches the ItemCode column for the code in F2 and returns the matching value from the Stock column, with "Not found" as the if_not_found argument. The second option searches the stock numbers instead of the item codes, so it is looking in the wrong column. VLOOKUP could work for an exact match, but it does not include the requested fallback text as written. The last option returns item names, not stock counts, so it gives the wrong result type.

This question tests a concatenated lookup key. Joining Product and Month in both the lookup value and lookup array allows XLOOKUP to match the unique pair and return the corresponding Amount. The single-field XLOOKUP choices fail because they can only match one column and would return the first Pad or the first Feb row, which is not the same record. Reversing the arrays would try to find the concatenated key in the Amount column, which cannot work here.

TRIM is the correct function for removing extra spaces at the beginning and end of text, and it also collapses repeated internal spaces to single spaces. That makes it ideal for cleaning pasted codes before lookup. CLEAN removes nonprinting characters, which is a different problem and would not reliably fix the visible spaces. LEFT simply truncates characters, and SUBSTITUTE with a space-removal pattern would delete all spaces, which can change valid text rather than just cleaning it.

FILTER is the correct dynamic-array function because it returns all rep names whose DealValue exceeds 500 and spills the results automatically. UNIQUE would remove duplicates but would not apply the numeric condition. SORT would reorder the names without filtering by deal size, and XLOOKUP would return only the first matching rep instead of the full list of qualifying reps.

Show Values As % of Grand Total is the correct pivot-table calculation because it converts each region’s sales into its proportion of the overall total. Sort Largest to Smallest changes the order, not the measure. Count would replace sales amounts with row counts, which is a different metric. Difference From calculates a delta against a base item, not a percentage share of the grand total.

INDEX with two MATCH functions is the standard way to return the value at a row/column intersection when both the row label and column header vary. The first MATCH finds the rep row and the second MATCH finds the month column. XLOOKUP alone returns only one dimension, not a two-way intersection, while using A2:C4 shifts the column numbering and would return the wrong cell. Adding MATCH results is not a lookup at all. This tests nested lookup logic.

The correct formula tests the Status cell in C2 and returns 3% of the Amount in B2 only when the invoice is Paid. The most tempting wrong option uses the same IF structure but checks B2 against "Paid" and multiplies C2, which fails because B2 is numeric and C2 is text. Another distractor returns the full amount instead of commission. The blank-string version changes the required zero result into an empty cell, which is not the same output.

AND is required because both conditions must be true: the region must be North and revenue must meet the threshold. The correct formula checks B2 and C2, then returns the bonus from D2. OR is the tempting wrong choice because it would pay when only one condition is met, which changes the business rule. Using D2 in the test is also wrong because Bonus is the result, not the criterion. The strict greater-than symbol would exclude exactly 10000.