If you're preparing for an entry-level analyst, reporting, or admin role, you don't need to know every Excel function that exists. You need to be fluent in a short list that shows up again and again — in job descriptions, screening tests, and interview questions.
Here are the five worth mastering first, and why each one matters.
1. XLOOKUP (or VLOOKUP, if that's what your workplace uses)
This is the formula interviewers ask about most, because it tests whether you can pull information from one table into another — the single most common task in reporting work. XLOOKUP is the modern version and easier to use correctly, but many workplaces still run older Excel versions, so it's worth being comfortable with VLOOKUP too.
2. SUMIFS
Any time you need a total that depends on more than one condition — total sales for a region in a specific month, for example — SUMIFS is the formula. It's a strong signal to an interviewer that you can build conditional summaries, not just static totals.
3. COUNTIFS
The counting equivalent of SUMIFS. Useful for questions like "how many orders were late in Q1" — a very typical reporting task that comes up constantly once you're on the job.
4. Nested IF (or IFS)
Conditional logic is everywhere in real spreadsheets: flagging overdue invoices, categorizing performance tiers, labeling data quality issues. Being able to write — and explain — a nested IF statement shows you can think in conditional logic, not just static formulas.
5. PivotTables (technically a feature, not a formula, but always comes up)
Almost every interview question about "how would you summarize this data" is really asking whether you know PivotTables. If you can build one confidently and explain your choice of rows, columns, and values, you're already ahead of most entry-level candidates.
How to actually prepare
- Don't just memorize syntax — practice on a messy, realistic dataset, not a clean textbook example.
- Be ready to explain your formula choice out loud, not just produce the right answer.
- If you're new to all five, work through them in the order above — each one builds on ideas from the last.
All five of these are covered hands-on in our Excel Fundamentals for Work course, with practice on a real messy dataset rather than a toy example.