THE JOB TESTS · SASFLYNET KFT. · FREE PRACTICE GUIDE

Excel Intermediate Practice: practice, explain, check

Practise lookup logic, conditional summaries and checking spreadsheet data. Formula examples use English names and commas. XLOOKUP requires a supported modern Excel version; functions and separators vary by application.

This original 12-question practice set describes only the sampled questions. It is not a validated proficiency level, aptitude percentile, language certificate or hiring recommendation. All examples and data are fictional.

Your seven-day plan

  1. Days 1–2: Lookups and references

    Create five product codes and prices. Check an exact lookup for a matching code and a missing code; explain the difference between a row reference and a fixed range.

  2. Days 3–4: Conditional summaries

    Build six fictional sales rows. Compare SUMIFS and COUNTIFS, then check a filtered total with SUBTOTAL against the visible source rows.

  3. Days 5–6: Data checks

    Make a small table with a duplicate row, an ordinary extra space and a date. Check how each cleaning action changes the original records before applying it.

  4. Day 7: check independently

    Try one changed example from each topic without the guide. Repeating the same questions can reflect familiarity; use a new example to check transfer.

1. XLOOKUP("P2",A2:A4,B2:B4,"Missing"): codes are P1, P2, P3 and prices are 10, 25, 40. What is returned?

My answer and reasoning:

Worked answer

25

The matching code P2 is in the second lookup row, so XLOOKUP returns the second price, 25.

2. In a supported Excel version, which XLOOKUP match mode is used when match_mode is omitted?

My answer and reasoning:

Worked answer

Exact match

The default match_mode is 0, an exact match. Explicitly handle a missing key.

3. A2 is blank. What does =IFERROR(10/A2,"Check") return?

My answer and reasoning:

Worked answer

Check

Division by a blank cell is treated as division by zero. IFERROR replaces that error with Check; it does not fix the source data.

4. Cell C2 contains =$A2*B$1. Copy it one row down and one column right. What formula appears?

My answer and reasoning:

Worked answer

=$A3*C$1

Column A is fixed; row 2 moves to 3. Column B moves to C; row 1 stays fixed.

5. Regions: North, South, North. Statuses: Paid, Paid, Pending. Sales: 120, 80, 60. What is the North-and-Paid sales total?

My answer and reasoning:

Worked answer

120

Only the first row meets both criteria. SUMIFS applies all listed conditions to aligned ranges.

6. Regions: North, South, North. Statuses: Paid, Paid, Pending. How many rows meet both North and Paid?

My answer and reasoning:

Worked answer

1

Only the first row satisfies both criteria. COUNTIFS counts matching rows rather than summing their sales.

7. Values 10, 20 and 30 are in B2:B4. A filter hides the row containing 20. What does =SUBTOTAL(109,B2:B4) return?

My answer and reasoning:

Worked answer

40

Function number 109 sums visible values and excludes filtered rows. The visible total is 10 + 30 = 40.

8. A PivotTable summarises Revenue as Count rather than Sum. What should you check first?

My answer and reasoning:

Worked answer

Whether Revenue contains text or mixed value types

Check the source types and chosen Value Field Settings. Counting records and summing numeric revenue answer different questions.

9. Remove Duplicates is set to compare every column. Two rows share a customer name but have different order IDs. What normally happens?

My answer and reasoning:

Worked answer

Both rows remain

The full records differ because their order IDs differ. Duplicate detection depends on the columns selected.

10. What does =TRIM(" Blue team ") return for ordinary spaces?

My answer and reasoning:

Worked answer

Blue team

TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. Nonbreaking spaces can require separate handling.

11. A numeric fraction 0.25 is formatted as a percentage. What is displayed without changing its stored value?

My answer and reasoning:

Worked answer

25%

Percentage formatting displays the fraction multiplied by 100 with a percent sign. The stored value remains 0.25.

12. TODAY() is used in a deadline formula. What should a reviewer remember?

My answer and reasoning:

Worked answer

Its value can change when the workbook recalculates on a later date

TODAY returns the current date when calculated. A fixed historical date should be stored separately if the task requires one.

This worksheet sends no entries to us. Print or save it as PDF from your browser. Return to the free test.