Excel for job interviews: what gets tested and how to prepare

You have an interview coming up and the job ad says "strong Excel skills". Maybe the recruiter has already sent a link to an online test. This guide is about that moment: what employers actually test, the formulas and skills that come up again and again, how a timer changes the way you should work, and a two-week plan to get ready.

It is written for people applying to office, admin, finance, sales operations and junior analyst roles, which is where Excel tests are most common. If you are going for a modeling role at a bank, everything here still applies, and you will need more on top.

Why employers test Excel at all

A résumé can say anything. "Advanced Excel" on a résumé means something different to every person who writes it, and hiring managers have all hired someone who turned out to mean "I can make a column wider". A short test is the cheapest way to find out before the first day.

Employers are not looking for tricks. They want to know three things:

1. Can you get a right answer from data? A total for one region, the number of orders above a limit, the price of a product from a list.
2. Can you do it without breaking the sheet? Formulas that still work when they are filled down, ranges that cover all the rows, no numbers typed over formulas.
3. Can you do it at a normal working speed? Nobody expects you to be the fastest person in the building, but a task that takes a colleague one minute should not take you twenty.

Keep those three in mind. Almost every question in every Excel test checks one of them.

What the tests look like

There is no single "Excel test". Employers use a handful of test platforms, and recruitment agencies often have their own. The formats fall into three groups, and many tests mix them.

Knowledge questions

Multiple choice: "Which function returns the number of non-empty cells?" or "What does the $ in $A$1 do?". Some show a screenshot of a sheet and ask what a formula would return. These test whether you know the vocabulary. They are quick to answer if you do, and impossible to work out if you don't.

Simulation questions

You get a spreadsheet in the browser, a task such as "In C12, calculate the total sales for the North region", and you type the formula. The test checks the result, and sometimes how you got it. A typed number that happens to be right often fails, because the test changes the data behind the scenes or checks that the cell contains a formula. This is the format PracticeExcel's exercises use, and the one that most often catches people out, because it is the closest to real work.

Task workbooks

You download a workbook, or open one in the browser, and work through a list of tasks: clean up a column of names, build a summary table, add a lookup, make a chart. There is usually a longer time limit, 30 to 60 minutes, and the result is marked afterwards. These are common at accounting firms and in finance teams.

If you know which platform your employer uses, practice in that format. Our test prep pages cover the common ones, including the Indeed Excel assessment, the Kenexa Prove It test and SHL. We are not affiliated with any of them; the pages describe the formats in general terms and give you timed tests of the same kind.

The skills that come up again and again

Across platforms, the same skills appear in roughly the same order of difficulty. Use this list as a checklist. If you can do everything under "basic" and "intermediate" without looking anything up, you will pass most tests for most office roles.

Basic

• SUM, AVERAGE, MIN, MAX and COUNT on a range, typed as =SUM(B2:B10) rather than =B2+B3+B4. SUM is the first thing almost every test asks.
• Cell references and what happens to them when you copy a formula. A formula in C2 that says =A2*B2 becomes =A3*B3 in C3. That is relative referencing, and it is what makes filling down work.
• Sorting and filtering a table by one or two columns, without sorting one column on its own and scrambling the rows.
• Basic formatting: number formats, percentages, dates, column widths, freezing the top row.
• Simple arithmetic with the right order of operations: =B2*(1+C2) is not the same as =B2*1+C2.

Intermediate

• IF, alone and with AND and OR: "Yes if sales are at least 1,000 and the rating is at least 4.5".
• SUMIF, COUNTIF and AVERAGEIF: a total, a count or an average for one group.
• SUMIFS and COUNTIFS: the same with two or more conditions, including "between two numbers".
• One lookup function: VLOOKUP, XLOOKUP or INDEX with MATCH. Lookups are the single most common intermediate question.
• Absolute references with $: a formula that always points at a tax rate in E1, however far you fill it.
• Text functions: TRIM, LEFT, RIGHT, joining text with &.

Advanced

• INDEX and MATCH, including looking up to the left.
• Nested IF or IFS with three or more outcomes, such as grade bands.
• IFERROR around a lookup, so a missing value shows "Unknown" rather than #N/A.
• Pivot tables: building one, grouping by a field, showing sums and counts. Our pivot table guide explains what they compute.
• Date functions: EOMONTH, EDATE, NETWORKDAYS, DATEDIF.
• Conditional formatting and data validation (drop-down lists).

Most tests for non-specialist roles stop at the intermediate level, with one or two advanced questions to separate strong candidates from good ones.

Five tasks you should be able to do in two minutes

Here are five tasks that, in one form or another, appear in nearly every simulation test. For each, you should be able to read the task, write the formula and check the result in under two minutes. If one of them takes you longer, that is where to start practicing.

1. A total for one category

"What are the total sales for the North region?" This is SUMIF: the range to check, the condition, and the range to add up.

The order of the arguments trips people up under pressure. SUMIF is check this range, for this, add up that: =SUMIF(A2:A6,"North",B2:B6). SUMIFS, with several conditions, puts the sum range first instead. Learn both orders, because tests love to ask for one right after the other. Practice with SUMIF with a text criterion and then SUMIFS with two conditions.

A frequent variation puts the condition in a cell rather than in the formula, so you write =SUMIF(A2:A6,D2,B2:B6). That is better practice anyway, because the person reading your sheet can change D2 and see the new total.

2. Look up a value from a list

"Find the price of the product in D1." Any lookup function will do, unless the task names one.

With XLOOKUP you give the value to find, the column to search and the column to return. With VLOOKUP you give the value, the whole table, the column number to return and FALSE for an exact match: =VLOOKUP(D1,A2:B5,2,FALSE). Forgetting the FALSE is the classic VLOOKUP mistake. Without it, VLOOKUP assumes the list is sorted and may return a wrong price without any error at all. Our guide to VLOOKUP, XLOOKUP and INDEX MATCH goes through the differences in detail.

3. A yes or no answer from two conditions

"Mark the employee for a bonus if sales are at least 1,000 and the customer rating is at least 4.5."

The trick is to put the conditions inside AND, and AND inside IF, rather than trying to write B2>=1000 AND C2>=4.5 as you would say it. Read the task twice and look for the words that decide the comparison: "at least" is >=, "more than" is >, "up to" is <=. Getting one of those wrong is the most common reason a correct-looking IF fails. Practice it in two conditions with AND.

4. Count values between two numbers

"How many applicants are aged 18 to 30?" COUNTIFS lets you use the same range twice, with one condition each.

Notice that both ends are included here: 18 and 30 both count. If the task said "over 18 and under 30", you would write ">18" and "<30". The COUNTIFS between two numbers exercise drills exactly this.

5. A formula that survives being filled down

"Calculate the price including VAT for every item, using the rate in E1." This is really a test of absolute references.

If you write =B2*(1+E1) and fill it down, the next row reads =B3*(1+E2), and E2 is empty, so every row after the first gives the net price. Add the dollar signs, either by typing them or by pressing F4 straight after you click E1, and the reference stays put. Testers check this on purpose, often by asking you to fill the formula to the rest of the column and grading every row. The cross-table exercise takes it a step further with mixed references like $E3 and F$2.

How the timer changes things

Knowing the formulas is half of it. The other half is working well against a clock, which is a different skill from working well at your desk.

Read the whole task before you type. Tests often hide a detail in the last sentence: "round to two decimals", "return 0 if there is no match", "use a cell reference for the condition". A formula that ignores that detail is marked wrong even if the rest is perfect.

Skip and come back. Most tests score every question the same. A hard INDEX MATCH question is worth exactly as much as an easy SUM, so do not spend six minutes on it while three easy questions wait. If the test lets you move between questions, do a first pass of everything you can answer quickly, then go back.

Check the result, not just the formula. Before you move on, look at the number. Does a total for one region look smaller than the grand total? Is an average between the smallest and largest value? Is the count a whole number? Ten seconds of checking catches most range and condition errors.

Use the keyboard. Every trip to the mouse costs a few seconds, and in a 25-minute test with 20 questions those seconds add up. The next section lists the shortcuts worth knowing.

Do not fight the simulator. Browser-based simulators sometimes do not support every function or shortcut that your own Excel does. If something does not work, try the plain version (VLOOKUP instead of XLOOKUP, typing a range instead of selecting it) rather than assuming you are wrong.

Keyboard shortcuts that save time in a test

You do not need hundreds of shortcuts. These are the ones that pay off in a timed test, and all of them work in our grid so you can drill them:

• F2 to edit the active cell without retyping it, and Esc to cancel.
• Ctrl+Arrow to jump to the edge of the data, and Ctrl+Shift+Arrow to select to the edge. These replace slow dragging over long columns.
• Ctrl+D to fill down the formula from the cell above, and Ctrl+R to fill right.
• F4 while editing to toggle absolute references on the reference under the cursor.
• Alt+= (Cmd+Shift+T on a Mac) for AutoSum.
• Ctrl+Z to undo, which you will need more than you think.

The shortcut speed-run puts these against the clock, which is good practice for the feeling of a timed test.

Mistakes that cost points

These come from looking at a lot of graded answers. Each one is easy to avoid once you know to look for it.

• Typing the answer instead of a formula. If the task says "calculate", the cell must contain a formula. Many tests change the data and recalculate, and a typed number then fails.
• Ranges that miss a row. =SUM(B2:B9) when the data goes to row 10. Select ranges with Ctrl+Shift+Down instead of guessing where the data ends.
• Mixed-up argument order. SUMIF has the sum range last, SUMIFS has it first. VLOOKUP counts columns from the left edge of the table, not from column A.
• Text that looks like a match but is not. "North " with a trailing space is not "North". If a lookup or SUMIF returns 0 or #N/A for a value you can see, suspect spaces and use TRIM.
• Missing quotes. Text and conditions with operators go in quotes: "North", ">=1000". Cell references do not: D2, not "D2".
• Rounding too early. Round once, at the end, with ROUND, unless the task says to round each step. Formatting a cell to show two decimals is not the same as rounding the value.
• Hard-coding a value that is in a cell. If the VAT rate is in E1, use E1. Typing 0.25 into the formula works today and fails the moment the test changes E1.

A two-week preparation plan

Two weeks of half an hour a day is enough for most people to go from "I use Excel sometimes" to passing an intermediate test comfortably. If you have less time, compress it, but keep the order.

Days 1 to 3: the basics, fast. Do the SUM, AVERAGE and COUNT topics until you can do each exercise without hints. Learn the six shortcuts above. The goal here is speed, not new knowledge.

Days 4 to 6: conditions. IF, AND and OR, then COUNTIF and SUMIF. Pay attention to the operators in quotes and to the argument order.

Days 7 to 9: lookups. Learn VLOOKUP properly, including the FALSE at the end, then XLOOKUP and INDEX and MATCH. Wrap one of them in IFERROR.

Days 10 and 11: several conditions and summary tables. SUMIFS, COUNTIFS and the summary tables topic, which is also the best practice for absolute and mixed references.

Day 12: a full mock test. Take the Excel test for job interviews under real conditions: timer on, no help, phone away. Note every question you missed or guessed.

Day 13: fix the gaps. Go back to the lessons behind the questions you missed. The test result page links each missed question to the exercise that teaches it.

Day 14: a second mock test, then rest. Take a different timed test, ideally in the format your employer uses. Then stop. Cramming the night before does more harm than good.

When Excel comes up in the interview itself

Some employers skip the online test and ask about Excel face to face, or do both. Expect questions like these, and prepare a short, concrete answer for each:

• "Tell me about a spreadsheet you built or improved." Pick one real example. Say what the problem was, what you built (a summary table, a lookup that replaced manual copying, a monthly report), and what it saved. Numbers help: "it cut the monthly report from a day to an hour".
• "How would you find duplicates in a list?" Conditional formatting for duplicate values, COUNTIF greater than 1 in a helper column, or Remove Duplicates if you want them gone.
• "What is the difference between VLOOKUP and XLOOKUP?" XLOOKUP can look left, defaults to an exact match and has a built-in not-found value. VLOOKUP needs the key in the first column and a column number.
• "How do you check that your numbers are right?" Totals that tie back to the source, a second method for the same figure (a SUMIF total against a pivot table), and spot checks of a few rows by hand.
• "What is a pivot table for?" Summarizing a long table by one or two fields, such as sales by region and month, without writing formulas.

Honesty works better than bluffing. "I haven't used Power Query, but I've done the same clean-up with TRIM and text functions, and I pick up tools quickly" is a good answer. Claiming a skill and then failing the follow-up question is not.

Summary

Excel tests check whether you can get right answers from data, without breaking the sheet, at a normal working speed. The same skills come up across platforms: SUM and friends, IF with AND and OR, SUMIF and COUNTIF with one or more conditions, one lookup function, and absolute references. Under a timer, read the whole task, skip what is slow, check every result and keep your hands on the keyboard.

Then practice, because reading about formulas is not the same as typing them with a clock running. The exercises and tests below are in the order this guide uses them, and every answer is graded the moment you press Enter.