Not the skills everyone lists on their CV. The ones that come up in interviews, get tested in assessments, and separate candidates who know Excel from those who just think they do.
Most people write "proficient in Microsoft Excel" on their CV and mean they can use SUM and make a table look presentable. Most employers know this. The skills below are what actually come up when Excel is tested seriously — in assessments, in interviews, and in the first week of a new job.
These are the most commonly tested formulas in any Excel assessment. If you can't write a VLOOKUP confidently from memory, interviewers notice. XLOOKUP is the modern version — if you're learning now, start with XLOOKUP.
Almost every office job involves looking something up in a table — finding a customer's details, matching codes to descriptions, pulling prices from a list. VLOOKUP and XLOOKUP automate all of that.
Add up numbers that meet a condition — total sales for one region, expenses in one category, hours for one project. This comes up constantly and surprisingly few candidates can write it correctly under pressure.
=SUMIF(A2:A100, "North", C2:C100)
IF is the formula that shows someone understands logic in Excel, not just arithmetic. Knowing how to write a basic IF is expected. Knowing how to nest them or use IFS puts you ahead of most candidates.
=IFS(B2>=70, "High", B2>=40, "Medium", TRUE, "Low")
PivotTables are the fastest way to summarise large datasets. They're mentioned in almost every job description that says "strong Excel skills". You don't need to be an expert — you need to be able to create one from scratch in under 2 minutes, and know how to filter, group, and add calculated fields.
Most people have used a PivotTable someone else created. Far fewer can build one from a blank dataset with confidence. That's the skill employers are testing.
The difference between =A2*B2 and =A2*$B$2 seems minor until you copy a formula down 100 rows and everything goes wrong. Understanding when to lock a reference with $ is a sign of genuine Excel knowledge, not just formula memorisation.
If a cell reference should stay the same when you copy the formula (like a tax rate in one cell), use $. If it should change with each row (like the value in each row), don't.
The combination of INDEX and MATCH is more flexible than VLOOKUP — it can look left, it doesn't break when columns are added, and it's faster on large datasets. Knowing INDEX MATCH signals to an interviewer that you've gone beyond the basics.
=INDEX(B2:B100, MATCH(E2, A2:A100, 0))
Real-world data is messy. Names with extra spaces, dates stored as text, inconsistent capitalisation — all of these cause lookups to fail and calculations to break. Knowing how to clean data quickly is more valuable than most people realise.
The four formulas you need: TRIM (removes extra spaces), PROPER (fixes capitalisation), TEXT (formats numbers and dates), SUBSTITUTE (finds and replaces within cells).
The conditional versions of COUNT and AVERAGE. How many entries meet a condition? What's the average for one category? These are fundamental analysis questions and these formulas answer them in seconds.
=COUNTIF(B2:B100, ">1000")
=AVERAGEIF(A2:A100, "North", C2:C100)
Dates are everywhere in data — invoices, contracts, deadlines, staff records. Knowing how to calculate days between dates, find the end of a month, add working days, and extract year/month from a date is useful in almost every sector.
Key formulas: DATEDIF, WORKDAY, EOMONTH, NETWORKDAYS, TODAY().
Everyone can insert a chart. Fewer people know how to make one that clearly communicates the right insight — correct chart type for the data, clear title, labelled axes, clean formatting. A well-made chart in an interview task or a work presentation shows commercial awareness, not just technical skill.
Bar charts for comparisons. Line charts for trends. Pie charts almost never (they're hard to read). Combination charts for mixed data. And always — remove the chart junk (gridlines, borders, default colours).
Most Excel training focuses on showing you features. What actually matters is whether you can sit down with a messy dataset and produce a clean, accurate result quickly. That requires practising the formulas — not just reading about them.
ExcelPro covers every formula on this list — with real exercises in a live spreadsheet. Free to start, no download needed.
Start practising free →