"Advanced Excel" is in almost every analyst JD. It scares freshers into learning VBA they'll never use.
What they actually test is 8 practical skills. Lookups, pivots, clean data, one clear dashboard.
Practise these this week.
Skills 1-4: lookups, pivots, totals, cleaning
- 01
Lookups across sheets
=XLOOKUP(A2, Emp[ID], Emp[Name], "Not found")Pull a name or price from another sheet by ID.
- 02
Pivot tables + slicers
Alt + N + V (Insert PivotTable)Region x month summary in under a minute. Insert > PivotTable.
- 03
Conditional totals
=SUMIFS(Amt, Region, "North", Month, "Jan")Sales for one region and one month.
- 04
Data cleaning
=TRIM(PROPER(A2)) Ctrl + E (Flash Fill)Spaces, duplicates, split columns. The Data tab has Remove Duplicates.
Skills 5-8: dates, arrays, automate, show
- 05
Date formulas
=EOMONTH(A2, 0) =TEXT(A2, "mmm-yy")Month-end, month labels, days between two dates.
- 06
FILTER, UNIQUE, SORT
=SORT(UNIQUE(FILTER(City, Amt>500)))One formula returns a whole list. Excel 365 and 2021.
- 07
Power Query
Data > Get Data > From FolderCombine 12 monthly files once. Next month, click Refresh.
- 08
A one-page dashboard
Alt + F1 (instant chart)Pivot charts, slicers, 3-4 KPIs on top. Nothing 3D.
JD line vs what they test
| JD says | They test |
|---|---|
| Advanced Excel | Lookup + pivot in 20 min |
| MIS reporting | SUMIFS, weekly pivot report |
| Data cleaning | TRIM, duplicates, Power Query |
| Dashboarding | Pivot charts + slicers |
| Attention to detail | Totals that tie out |
What it does not mean
Not needed first
- VBA macros
- Fancy 3D charts
- Every function in Excel
Needed first
- Lookups + pivots
- Clean data fast
- One clear dashboard