Day 01 — Part 2: Excel for Analytics: Agenda¶
Excel is the most widely used analytics tool on earth. Long before — and long after — you learn SQL and Python, you'll use Excel. This session covers the Excel that matters for analysts: formulas that do real work, pivot tables, lookups, conditional formatting, and the shortcuts that make you fast.
Session Overview¶
Duration: 2.5–3 hours Prerequisite: Introduction to Data Analytics Tools: Excel 365 (most features work in 2019+; version notes included where relevant)
Learning Objectives¶
By the end of this session you will be able to:
Beginners
- Use core formulas: SUM, AVERAGE, COUNT, IF, and cell referencing
- Build a pivot table to summarise data
- Use XLOOKUP / VLOOKUP to combine data from different tables
- Apply conditional formatting to highlight what matters
Mid-level
- Use SUMIFS, COUNTIFS, INDEX/MATCH fluently
- Build dynamic pivot tables with slicers
- Understand absolute vs relative references and when each breaks
Senior
- Know when Excel is the right tool vs when to move to SQL/Power BI
- Use dynamic array formulas (FILTER, SORT, UNIQUE) in Excel 365
- Build reusable, auditable spreadsheet models
Session Flow¶
| # | Topic | File |
|---|---|---|
| 1 | Basic formulas and cell referencing | 01-basic-formulas |
| 2 | Pivot tables | 02-pivot-tables |
| 3 | Lookup functions (XLOOKUP, VLOOKUP, INDEX/MATCH) | 03-lookup-functions |
| 4 | Conditional formatting | 04-conditional-formatting |
| 5 | Excel shortcuts for speed | 05-excel-shortcuts |
| 6 | Mini exercises | 06-mini-exercises |
| 7 | Interview questions | 07-interview-questions |
Why Excel Still Matters¶
Excel is not "beginner" — it's universal
Senior analysts at top companies use Excel daily. It's where business users live, where quick models get built, and where data lands before it goes anywhere else. Dismissing Excel is a junior mistake. Mastering it — including Power Query and Power Pivot (covered in Week 02) — makes you immediately useful in any organisation.
The skill isn't knowing every function — it's knowing which tool fits the task:
| Task | Best in Excel? |
|---|---|
| Quick pivot of a few thousand rows | Yes |
| Ad-hoc what-if model | Yes |
| Sharing a self-contained analysis | Yes |
| Querying millions of rows | No — use SQL |
| A dashboard for 50 users, auto-refreshed | No — use Power BI |
Previous: Intro Interview Questions | Next: 01-basic-formulas