Excel for Data Analysts: Skills, Formulas and What to Learn Next

Even in teams that use SQL, Python and BI tools, Excel is where a lot of analysis starts and ends: quick checks, ad-hoc requests, and files stakeholders actually open. Analyst job tests often include an Excel task.

Core skills

Cleaning data

Most of an analyst's time. TRIM, split text, remove duplicates, dates stored as text, numbers stored as text, inconsistent categories.

Lookups and joins

XLOOKUP and INDEX MATCH to combine tables, the Excel equivalent of a SQL join. Know what happens with missing or duplicate keys.

Conditional aggregation

SUMIFS, COUNTIFS, AVERAGEIFS: GROUP BY with formulas.

Pivot tables

Fast summaries, grouping dates by month, percent of total, slicers.

Dynamic arrays

FILTER, UNIQUE, SORT for live, formula-driven tables.

Statistics

AVERAGE vs MEDIAN, percentiles, standard deviation, percent change, growth rates.

Charts

Picking the right chart, labelling clearly, avoiding clutter.

Power Query

Repeatable cleaning and merging of exports.

A typical analyst test

1. Clean a messy export.
2. Join a second table.
3. Summarise by two dimensions.
4. Answer three business questions.
5. Make one clear chart.

Habits that impress

• Keep raw data untouched; clean in a separate sheet or Power Query.
• Lock references properly (see mixed references for two-way summaries).
• Add check totals.
• Write the answer, not just the table.

Frequently asked questions

Do data analysts still use Excel?

Yes. It's used alongside SQL and BI tools for quick analysis and sharing results.

What Excel skills do data analysts need?

Data cleaning, lookups, SUMIFS/COUNTIFS, pivot tables, dynamic arrays, statistics and charts.

Is Excel enough to become a data analyst?

It's a strong start. Most roles also ask for SQL and a BI tool.

What is the hardest Excel skill for analysts?

Cleaning messy data reliably, and building summaries that stay correct as data changes.