Excel for Data Analysts
📊 Excel for Data Analysts
Excel is still mentioned in 87% of data analyst job postings. Every analyst uses it — whether they admit it or not. The goal isn't to be an Excel wizard; it's to be fast and competent with the tools that matter.
What Analysts Actually Use Excel For
- Quick data exploration — opening a CSV and getting a fast read on the data
- Pivot tables — the fastest way to summarise data by category without writing a single line of code
- Dashboards for non-technical stakeholders — many managers still prefer Excel over Power BI
- Financial modelling — especially in finance, consulting, and banking
- Ad-hoc analysis — when you need an answer in 10 minutes and SQL is overkill
Excel Skills Every Analyst Needs
Tier 1 — Must Know (Week 1–2)
| Skill | Why it matters |
|---|---|
| Pivot Tables | Group and summarise any dataset in seconds. Used in nearly every analyst role. |
| VLOOKUP / XLOOKUP | Join data from two tables by a key value. The Excel version of a JOIN. |
| IF / IFS | Conditional logic in cells. Build categories, flags, and business rules. |
| SUMIF / COUNTIF / AVERAGEIF | Aggregate data with conditions. The Excel version of GROUP BY + HAVING. |
| Sorting & Filtering | Fast exploration and ad-hoc analysis. |
Tier 2 — Important (Week 3–4)
| Skill | Why it matters |
|---|---|
| INDEX + MATCH | More flexible than VLOOKUP. Handles left lookups and dynamic references. |
| Power Query | Clean and transform data without formulas. The modern way to wrangle messy data. |
| Charts & conditional formatting | Communicate findings visually. Essential for stakeholder presentations. |
| Named ranges & data validation | Build clean, error-resistant spreadsheets that others can use without breaking. |
| TEXT functions | TRIM, CLEAN, LEFT, RIGHT, MID, CONCATENATE — for cleaning messy text data. |
Tier 3 — Advanced (Month 2+)
- Dynamic arrays — FILTER, SORT, UNIQUE, SEQUENCE (Excel 365)
- Power Pivot — Excel's in-built data model for handling large datasets and DAX measures
- Macro recording / Basic VBA — automate repetitive tasks without writing complex code
Essential Excel Formulas Quick Reference
| Formula | What it does |
|---|---|
=XLOOKUP(A2,Table[ID],Table[Name]) |
Look up a value from another table (modern VLOOKUP) |
=SUMIF(B:B,"North",C:C) |
Sum column C where column B equals "North" |
=COUNTIFS(A:A,"Active",B:B,">50000") |
Count rows matching multiple conditions |
=IF(C2>=90,"A",IF(C2>=80,"B","C")) |
Nested IF for banded categories |
=TEXT(A2,"mmm yyyy") |
Format a date as "Jan 2025" |
=IFERROR(VLOOKUP(...),-) |
Return a fallback value instead of #N/A error |
=UNIQUE(A2:A100) |
Return a deduplicated list (Excel 365) |
Free Excel Resources
- 📺 ExcelIsFun (YouTube) — enormous free library, every formula explained
- 📚 Microsoft Excel Help Center — official documentation for every function
- 🎮 Chandoo.org — practical tutorials with business-focused examples
Ready-Made Excel Dashboard Templates
Our Dashboard Templates Bundle includes a Financial KPI Dashboard and a Marketing Campaign Tracker — both in Excel, ready to plug your data into.