Excel test for data analysts

What the test looks like

There is no single standard test. Each employer sets its own, and the format varies:

• A take-home workbook. You get a file with a raw table and a few questions, and send it back by a deadline. Whoever checks it can click any cell and see your formula, not just the answer.
• A timed online test. Questions in the browser with a clock running, often a mix of multiple choice and small tasks in a spreadsheet.
• A live task. You share your screen and work through a small table while someone watches and asks why you chose that formula.

Whatever the format, the data looks like the job: orders, sales, customers or invoices, and questions like how many, how much, which one and what's missing. A formula that keeps working when the data changes beats a number typed in by hand.

How to prepare

• Learn each move on a small table first. Six or twelve rows are enough to see whether a formula is right. Do it until you can write it without looking up the syntax.
• Write formulas, not numbers. A total typed in by hand looks right until the data changes. The challenges here reject typed-in answers for that reason.
• Lock ranges before you fill down. A lookup table without $ slides down with the formula and the bottom rows go wrong. Practice absolute references.
• Clean the data before you look things up. Stray spaces and numbers stored as text are two common reasons a lookup returns #N/A when the value is plainly there. Try the #N/A mistake hunt and the numbers-stored-as-text hunt.
• Check one answer by hand. Filter the table, count the rows, and compare with your formula. It takes a minute and catches a wrong range.
• Practice against the clock. Once each move is familiar, do a few tasks in a row with a timer running.

Other Excel tests

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

FAQ

What Excel skills does a data analyst test cover?

Usually counts and sums with conditions (COUNTIFS, SUMIFS), lookups (XLOOKUP, VLOOKUP or INDEX and MATCH), text cleanup (TRIM, splitting names), error handling (IFERROR) and summarizing a table, often with a pivot table. The exact mix depends on the employer.

How should I prepare for a data analyst Excel test?

Practice each skill on a small table until you can write the formula without looking up the syntax. Then do a few tasks in a row with a timer, and check one answer by hand each time.

Can I use XLOOKUP in an Excel test?

If the test runs in Excel for Microsoft 365, Excel 2021 or later, or Excel for the web, yes. Older versions do not have it and show #NAME?, so know INDEX and MATCH as well.

Do I need pivot tables for a data analyst Excel test?

It is worth knowing them: a pivot table is a quick way to total a table by region, month or product. Also know how to build the same summary with SUMIFS and COUNTIFS, for when the answer has to update with the data.

Is this the test employers use?

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

Is the practice on this page free?

Yes. The two tasks on this page and every challenge linked from it are free, need no sign-up and run in your browser.