Preparing for an Excel skills test
Many employers and training courses check Excel with a short timed test: a few tasks in a workbook, each needing a formula, a table or a shortcut. The tests differ in detail, but they check the same core skills. This guide lists those skills and matches each one to a lesson here.
Use it as a checklist. For each area, do the lesson first, then practise until you can do the task without looking anything up.
1. Summing and counting by condition
Expect tasks such as total sales for one region, or count the orders above a threshold. These use SUMIF, SUMIFS, COUNTIF and COUNTIFS. Practise writing the criteria range and the criteria value in the right order.
2. Lookups
A test will often ask you to pull a value from another table. Know VLOOKUP with exact match, and be able to read XLOOKUP and HLOOKUP when you see them. Always check the match type and the table's first column or row.
3. Logic and errors
Nested tests with IF, combined conditions with AND and OR, and a clean way to hide errors with IFERROR. Newer tests may also use SWITCH for a list of options and XOR for an either-or rule.
4. Summaries and tables
Be ready to explain what a pivot table does and to build a summary of totals per group. Know when a formula is the better choice, and how to check a pivot total with a formula.
5. Keeping data clean
Drop-down lists and validation rules keep entries consistent. Tests may ask which rule fits a case, so learn the difference between a list rule and a whole-number rule.
6. Formatting and navigation
Speed matters in a timed test. Learn the keys for bold, italic and underline, for copying a format, for jumping to the edge of a range, and for opening the ribbon's key tips.
Common mistakes
Reading a question once and starting to type. Tests often hide a detail, such as a range that starts in row 5 or a value in the wrong column.
Fix: Read the whole task, then check the cell addresses before you write the formula.
Typing numbers into formulas. It works for one test but fails when the data changes, and graders may mark it wrong.
Fix: Refer to cells, and use absolute references with $ when a formula is copied.
Spending too long on one task. A stuck formula costs the time of several easier tasks.
Fix: Skip a task you cannot solve after a few minutes, finish the rest, then return to it.
Frequently asked questions
How long should I practise before a test?
Work through the lessons in the order listed, then repeat the ones you got wrong. A few short sessions each week beat one long session.
Which functions matter most?
SUMIFS, COUNTIFS, XLOOKUP or VLOOKUP, IF and IFERROR cover most of what tests ask. Learn them until you can write them from memory.
Related functions
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- IFIF checks a condition and returns one value if it's true and another if it's false.
- IFERRORIFERROR returns a value you choose when a formula produces an error such as #N/A or #DIV/0!, and the normal result otherwise.