Excel skills every data analyst
needs in 2026

Why Excel still matters for data analysts

Python and SQL get more attention, but Excel remains the most-used tool in business analytics. Most stakeholders share data in .xlsx files. Most ad-hoc analysis happens in spreadsheets. Most business reports are built in Excel. Analysts who can't use it fluently are slower than those who can.

Tier 1 — You must know these

XLOOKUP

The replacement for VLOOKUP. Cleaner syntax, searches in any direction, handles errors gracefully. If you're still writing VLOOKUP daily, switch to XLOOKUP.

SUMIFS and COUNTIFS

The core of any aggregated analysis. Filter a dataset by multiple conditions and sum or count the result.

Pivot Tables

No formula required. Drag fields to summarise millions of rows in seconds. The most underrated skill in data analysis.

Tier 2 — Serious analysts need these

FILTER (Excel 365)

Extract rows that match conditions as a formula output — no helper columns, updates automatically.

UNIQUE + SORT

Extract a sorted list of distinct values dynamically. Essential for building clean reference lists and dashboards.

INDEX MATCH

More flexible than VLOOKUP, works in older Excel versions. Used heavily in financial models and legacy reports.

LAMBDA

Build custom reusable functions using standard Excel formulas. Eliminates repetitive nested formulas across large models.

Data cleaning formulas (often overlooked)

• TRIM — removes leading and trailing spaces that break lookups
• CLEAN — removes non-printable characters from imported data
• TEXT and VALUE — convert between text and numbers
• SUBSTITUTE — find and replace text within cells programmatically
• LEFT, MID, RIGHT, TEXTBEFORE, TEXTAFTER — extract parts of text strings