Home › Learn Excel › Excel tests › Intermediate Excel test
Twelve practical tasks at the level most office jobs mean by "good Excel skills": lookups, conditional sums, cleaning text, dates and pivot tables. No tips, a score at the end, and a certificate when you pass.
XLOOKUP looks for a value in one column and returns the value from the same row in another column. Find the price of product P-104 in F2SUMIFS adds up with several conditions. A condition can also compare, like ">500" for more than 500. Add up the West sales over 500 in D2COUNTIFS counts the rows that meet all your conditions, one range with one condition at a time. Count the Paid invoices from the North region, in D2IFERROR shows your own text when a formula gives an error. Kiwi isn't in the list, so VLOOKUP alone would show #N/A. Look up the stock of Kiwi in F2 and show "Not found" instead of an errorCONCATENATE joins text from several cells. Add a space between quotes to keep the words apart. Join the first and last name into one full name in C2TRIM removes spaces at the start and end of a text, and turns double spaces in the middle into one. Clean up the extra spaces in the name, in B2DATEDIF counts the time between two dates. The last part sets the unit: "y" for years, "m" for months, "d" for days. Work out how many full years Anna has worked here, in C2The intermediate test is where most employers set the bar. You combine data, summarise it and clean it up, in a real Excel window.
Take it if the job ad asks for "good" or "strong" Excel skills: office managers, sales support, HR, planning and junior analyst roles. Pass with 70% or more and claim a certificate with your name and score.
"Add up the sales of the West region that are over 500." The answer: =SUMIFS(C2:C9,A2:A9,"West",C2:C9,">500"). In SUMIFS the range to add up comes first, then each range with its condition. Mixing up that order is the most common mistake at this level.
Scored 90% or more? Try the advanced Excel test. Want to brush up first? Check the formulas one by one with the Excel formulas test, or go back to the basic Excel test.
A test of the skills most office jobs need beyond the basics: lookups with VLOOKUP or XLOOKUP, SUMIFS and COUNTIFS, IFERROR, text and date functions, and pivot tables.
Both count. Use the one you know best; if your formula gives the right result, it's correct.
About 10 minutes. There's no time limit.
Yes, as often as you like. Your best score counts, and it's the one on your certificate.