Learn Excel
EN
Sets how numbers, dates and formulas look, so everything works just like your own Excel.

Home › Learn Excel › Excel tests › Advanced Excel test

Advanced Excel test

Twelve practical tasks at the level analyst and finance roles ask for: flexible lookups, dynamic arrays and formulas that combine several functions. No tips, a score at the end, and a certificate when you pass.

  1. Dina's department. MATCH finds the row of a value, and INDEX returns what's in that row of another column. Together they look up anything. Find the department of Dina in F2
  2. Everyone in Sales. FILTER returns every row that meets a condition. The answer spills into the cells below by itself. List everyone who works in Sales, starting in D2
  3. Every region once. UNIQUE removes the duplicates from a list. The answer spills into the cells below by itself. List every region once, starting in C2
  4. Order value. SUMPRODUCT multiplies the ranges row by row and then adds everything up, in one go. Work out the total order value (quantity times price) in D2
  5. Cell from an address. INDIRECT turns text like B4 into a real cell reference. Change the text and the formula follows. Show the value of the cell whose address is in D2, in E2
  6. A grade. IFS checks conditions in order and returns the value of the first one that's true. No nesting needed. Give the score in B2 a grade in C2: A from 90, B from 70, C from 50
  7. Working days. NETWORKDAYS counts the weekdays between two dates, the first and last day included. Weekends don't count. Count the working days between the two dates, in C2
  8. Email username. TEXTBEFORE returns everything before a character you choose. TEXTAFTER returns what comes after it. Take the part of the email address before the @, in B2
  9. Ben's sales in the South. VLOOKUP looks for one value. For two conditions, column A joins them into one key, like Ben-South.
    In the formula you build the same key with &. Find Ben's sales in the South region in H2
  10. Conditional formatting.
  11. Spill.
  12. Performance.

← Excel tests

What's in the advanced Excel test

The advanced test goes beyond single functions. Most tasks need you to combine two or three of them, in a real Excel window.

Who is the advanced test for?

Take it if you're applying for a data analyst, financial analyst, controller or consultant role, or any job that asks for "advanced Excel". Pass with 70% or more and claim a certificate with your name and score.

Example question

"Return the department of an employee, when the departments are in column A and the names in column B." VLOOKUP can't look to the left, so the answer is =INDEX(A2:A9,MATCH(E2,B2:B9,0)) or =XLOOKUP(E2,B2:B9,A2:A9). Knowing why VLOOKUP fails here is exactly what an advanced test checks.

After the test

Scored 90% or more? You're at the level most analyst roles ask for: add your certificate to LinkedIn. Missed a few? Practise the formulas one by one, or read the advanced Excel interview questions.

Frequently asked questions

What is an advanced Excel test?

A test of combined formulas and modern functions: INDEX MATCH, lookups with several conditions, FILTER and UNIQUE, SUMPRODUCT, INDIRECT and nested logic.

Do I need Microsoft 365 for the advanced test?

No. The test runs in your browser, and functions like FILTER, UNIQUE and XLOOKUP work there even if your own Excel is older.

How long does the advanced Excel test take?

About 12 minutes. There's no time limit.

Can I retake the test?

Yes, as often as you like. Your best score counts, and it's the one on your certificate.