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

Home › Learn Excel › Excel tests › Intermediate Excel test

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.

  1. Price of a Latte. VLOOKUP searches the first column of a table and returns a value from the same row.
    You can type the value you look for straight into the formula, in quotes. Find the price of a Latte in F2
  2. Price by product code. 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 F2
  3. West sales over 500. SUMIFS adds up with several conditions. A condition can also compare, like ">500" for more than 500. Add up the West sales over 500 in D2
  4. Paid in the North. COUNTIFS 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 D2
  5. Missing product. IFERROR 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 error
  6. Full name. CONCATENATE 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 C2
  7. Extra spaces. TRIM 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 B2
  8. Years of service. DATEDIF 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 C2
  9. Pivot table.
  10. Remove duplicates.
  11. VLOOKUP.
  12. Pivot tables.

← Excel tests

What's in the intermediate Excel test

The intermediate test is where most employers set the bar. You combine data, summarise it and clean it up, in a real Excel window.

Who is the intermediate test for?

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.

Example question

"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.

After the test

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.

Frequently asked questions

What is an intermediate 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.

Is VLOOKUP or XLOOKUP better for the test?

Both count. Use the one you know best; if your formula gives the right result, it's correct.

How long does the intermediate Excel test take?

About 10 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.