Advanced Excel test

What the test looks like

"Advanced" means different things to different employers. Usually it is one of these:

• A harder online test. Multiple choice and spreadsheet tasks that go past SUM and IF: lookups, logic with several conditions, pivot tables, sometimes Power Query or macros.
• A case file. A raw table and open questions ("which region grew most?"), where you choose the method and the marker reads your formulas.
• A modelling or analysis task for analyst roles, built from scratch in a set time.

What separates advanced from intermediate is less the function list than how formulas are built: one formula that copies across a whole table, lookups that don't break when a column moves, and steps that someone else can follow.

How to prepare

• Master the $ in both directions. A formula that fills right and down needs $A2 and B$1. Practise mixed references.
• Know INDEX and MATCH as well as XLOOKUP. The test may run in a version without XLOOKUP, and INDEX/MATCH looks left in any version.
• Build summaries in steps. Totals in one block, changes in the next, the answer at the end. Each step is easy to check.
• Use IFERROR on purpose. Wrap the lookup that can legitimately miss, not the whole formula.
• Learn the tools this site doesn't cover yet. Pivot tables beyond the basics, Power Query and macros are worth an hour each in Excel itself if the job ad mentions them. Start with a first pivot table.
• Practise against the clock. Advanced tasks take longer, so pacing matters more.

Other Excel tests

Excel test for data analysts, Excel test for finance jobs, Excel test for accounting jobs, Excel test for administrative assistants, Basic Excel test, Excel quiz. Or start from what Excel interview tests check, with the formats and the test providers.

FAQ

What is on an advanced Excel test?

Usually nested IF or IFS, AND and OR, INDEX and MATCH or XLOOKUP, mixed references, SUMIFS and COUNTIFS for summaries, and often pivot tables. Some add Power Query, dynamic arrays such as UNIQUE and FILTER, or macros. The exact mix depends on the employer.

Do advanced Excel tests include VBA?

Some do, mostly for roles where automation is part of the job. Many advanced tests stop at formulas and pivot tables. The job ad and the invitation are the best guide.

Should I use XLOOKUP or INDEX MATCH in an advanced test?

XLOOKUP if the test runs in Excel 2021, Excel for Microsoft 365 or Excel for the web. INDEX and MATCH works in every version, so know both.

How do I get from intermediate to advanced Excel?

Get fluent with mixed references, lookups in any direction and logic with several conditions, then practise multi-step summaries on raw data. After that, pivot tables and Power Query.

Is this the test employers use?

No. Hands-On Excel is not connected to any employer or testing company. The tasks here practise the skills these tests are built on.

Is the advanced practice test free?

Yes. The timed test, the two tasks on this page and every linked challenge are free and need no sign-up. Pro adds the task-by-task report after the test.