Excel Skills for Finance Jobs: The Functions and Habits Employers Test
The Excel skills finance jobs require fall into five groups: lookups (XLOOKUP, INDEX-MATCH), conditional maths (SUMIFS, COUNTIFS), financial functions (NPV, XNPV, IRR, XIRR, PMT), analysis tools (pivot tables, data tables, scenarios) and model hygiene (clear structure, no hard-codes, error checks). Speed with keyboard shortcuts matters in timed modelling tests.
Key takeaways
- Master about 20 functions deeply rather than 200 superficially.
- Data tables and scenarios are what turn a model into a decision tool.
- Clean structure is what employers notice first.
- Practise timed tasks. Interview tests are about speed and accuracy.
The essential Excel functions for finance
| Category | Functions | Typical use |
|---|---|---|
| Lookup | XLOOKUP, INDEX + MATCH | Pull values from data tables |
| Conditional sums | SUMIFS, COUNTIFS, AVERAGEIFS | Totals by region, product or period |
| Logic | IF, IFS, AND, OR, IFERROR | Scenario switches and error handling |
| Financial | NPV, XNPV, IRR, XIRR, PMT, FV | Valuation, returns, loan payments |
| Dates | EOMONTH, EDATE, YEARFRAC | Monthly timelines, accruals |
| Text | TEXTJOIN, LEFT/RIGHT/MID, TRIM | Cleaning imported data |
| Rounding & checks | ROUND, ABS, MIN/MAX | Tolerances and balance checks |
| Dynamic arrays | FILTER, SORT, UNIQUE, LET | Modern, cleaner analysis |
NPV vs XNPV, IRR vs XIRR
- NPV / IRR assume equal periods between cash flows.
- XNPV / XIRR use actual dates, which makes them more accurate for real investments with irregular timing.
- Excel’s NPV assumes the first cash flow is one period in the future, so add any time-zero cash flow separately. This is a classic interview trap.
Analysis tools
- Pivot tables: summarise thousands of transactions in seconds.
- Data tables (What-If Analysis): build sensitivity grids, such as valuation vs WACC and growth, as used in DCF valuation.
- Scenario switches: a single cell (1 = base, 2 = upside, 3 = downside) driving assumptions through CHOOSE or INDEX.
- Goal Seek: find the input that hits a target, such as the break-even price.
- Power Query: repeatable import and cleaning of monthly data.
Model hygiene: what reviewers notice first
- Inputs separated from calculations
- Consistent colour coding (for example blue inputs, black formulas)
- No hard-coded numbers inside formulas
- One formula per row, copied across consistently
- Error and balance checks visible
- Clear labels, units and timelines
These conventions are applied in the three-statement model guide.
Shortcuts that save time in tests (Windows)
- Ctrl + Arrow: jump to the edge of data
- Ctrl + Shift + Arrow: select to the edge
- F4: toggle absolute and relative references
- Alt + = : AutoSum
- Ctrl + 1: format cells
- F2: edit cell; Ctrl + [ to trace precedents
A 4-week Excel plan for finance
- Week 1: lookups, SUMIFS, IF logic and absolute references, practised on a sales dataset.
- Week 2: financial functions and date timelines; build a loan schedule with PMT.
- Week 3: pivot tables, data tables and scenarios.
- Week 4: a timed mini-model: a 5-year forecast with balance check and sensitivity table in under 90 minutes.
For the modelling context, see what is financial modelling?. For data-analyst Excel use, see Python vs Excel for data analysis.
Practise with feedback
Tutopiya is planning a live Financial Modelling & Valuation cohort with timed Excel modelling exercises marked by a practising finance professional. It isn’t running yet. Register interest in Excel and modelling training. Nothing is charged.
Sources
- Microsoft Support: XLOOKUP
- Microsoft Support: XNPV
- Microsoft Support: data tables
Frequently asked questions
What Excel skills do finance jobs require? +
Lookups (XLOOKUP or INDEX-MATCH), conditional aggregation (SUMIFS, COUNTIFS), logical functions (IF, IFERROR), financial functions (NPV, XNPV, IRR, XIRR, PMT), pivot tables, data tables for sensitivity, date functions, and clean model structure. Keyboard speed is a bonus.
Is VLOOKUP still used in finance? +
It still appears in older workbooks, but XLOOKUP (in current Excel versions) or INDEX-MATCH are preferred because they are more flexible and less error-prone.
How are Excel skills tested in finance interviews? +
Common formats include a timed modelling test (for example building a simple three-statement model or a DCF), a data-analysis task with pivot tables and lookups, or live questions about which function you'd use and why.
Should finance professionals learn Python as well? +
It's increasingly useful for automation and large datasets, especially in FP&A and analytics-heavy teams. But Excel remains the primary tool, so master it first.
Founding cohorts · nothing charged
Want to learn this live, with a mentor?
Tutopiya is planning small, mentor-led online cohorts in English test prep, data & AI, business & finance and digital careers. Tell us which one you want. The programmes with the most interest open first, and the people on the waitlist hear about them first.
See the planned programmesWritten by
Tutopiya Careers Desk
Professional courses & career skills research · Tutopiya
The Tutopiya team researching which professional and certificate programmes to build next. We have taught live online since 2015. These guides answer the questions our waitlist asks most, with sources linked so every claim can be checked.
Related Articles
ACCA Qualification Structure Explained: All 13 Exams, Levels and Requirements
The ACCA qualification structure explained: all 13 exams across Applied Knowledge, Applied Skills and Strategic Professional, plus ethics and PER.
ACCA vs CIMA vs CPA: Which Accounting Qualification Is Right for You?
ACCA vs CIMA vs CPA compared on focus, recognition by country, exams and experience requirements, so you can choose the right accounting route.
Agile vs Waterfall: Differences, Pros and Cons, and When to Use Each
Agile vs waterfall explained: how each approach plans and delivers work, pros and cons, when each fits, and how hybrid project management combines them.