What assessments test
Most practical Excel tests cover the same ground: lookups (XLOOKUP or INDEX/MATCH), pivot tables with a non-default summarisation, a conditional aggregate like SUMIFS, text parsing, date arithmetic, and cleaning a messy sheet.
At analyst level, expect Power Query and possibly the Data Model. At senior level, expect to explain design choices, not just produce a number.
Drills worth doing
Time yourself on these until they are automatic: build a pivot showing % of parent row total; write a SUMIFS with three criteria including a date range; use FILTER and UNIQUE to build a dynamic list; unpivot a wide table in Power Query; write a formula-based conditional formatting rule that highlights whole rows.
Also practise cleaning: trim spaces, split a full name, fix dates stored as text, remove duplicates. Messy-data tasks appear constantly because they mirror real work.
Talking about it
Interviewers ask what you have built. Have one concrete example ready, and describe the problem, your approach, and the outcome — not the feature list.
Knowing when not to use Excel is a strong signal. Saying "that dataset was too large and too shared for a workbook, so we moved it to a database and reported on it" reads as more senior than listing functions.