Home › Learn Excel › Excel tests › 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.
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 F2FILTER returns every row that meets a condition. The answer spills into the cells below by itself. List everyone who works in Sales, starting in D2UNIQUE removes the duplicates from a list. The answer spills into the cells below by itself. List every region once, starting in C2SUMPRODUCT multiplies the ranges row by row and then adds everything up, in one go. Work out the total order value (quantity times price) in D2INDIRECT 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 E2IFS 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 50NETWORKDAYS 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 C2TEXTBEFORE returns everything before a character you choose. TEXTAFTER returns what comes after it. Take the part of the email address before the @, in B2The advanced test goes beyond single functions. Most tasks need you to combine two or three of them, in a real Excel window.
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.
"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.
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.
A test of combined formulas and modern functions: INDEX MATCH, lookups with several conditions, FILTER and UNIQUE, SUMPRODUCT, INDIRECT and nested logic.
No. The test runs in your browser, and functions like FILTER, UNIQUE and XLOOKUP work there even if your own Excel is older.
About 12 minutes. There's no time limit.
Yes, as often as you like. Your best score counts, and it's the one on your certificate.