1. Basic Excel Skills
Excel interface & ribbon
Workbook vs Worksheet
Cell references: Relative, Absolute, Mixed
Data entry, formatting, alignment
Copy-paste, drag fill, shortcuts (Ctrl+C, Ctrl+Z, etc.)
2. Formulas & Functions
Basic:
SUM,AVERAGE,MIN,MAX,COUNT,COUNTALogical:
IF,IFERROR,AND,OR,NOTLookup:
VLOOKUP,HLOOKUP,INDEX,MATCH,XLOOKUPText:
CONCATENATE,TEXT,LEFT,RIGHT,LEN,TRIM,UPPER,LOWERDate/Time:
TODAY(),NOW(),DATEDIF(),EDATE()Nested functions: e.g.
IF + AND,INDEX + MATCH
3. Data Sorting & Filtering
Sort by one or multiple columns
AutoFilter and Advanced Filter
Filter by condition, color, top 10
4. Data Cleaning & Validation
Remove DuplicatesText to ColumnsFind & ReplaceData Validation(Dropdown lists, rules)Handling blanks and errors (
IFERROR,ISBLANK)
5. Charts & Visualization
Column, Bar, Line, Pie, Area, Combo charts
Creating dynamic charts using named ranges
Chart formatting (titles, labels, gridlines)
6. Pivot Tables & Pivot Charts
Creating PivotTables
Grouping data (by month, quarter, etc.)
Value field settings (Sum, Count, Average)
Slicers and PivotCharts
Calculated fields in PivotTables
7. Conditional Formatting
Highlight rules (greater than, duplicates, etc.)
Color scales, icon sets, data bars
Custom formulas for formatting
8. Advanced Excel
Named ranges
Dynamic named ranges using
OFFSETArray formulas (Legacy & Dynamic arrays like
FILTER,UNIQUE,SORT)Use of
LET,LAMBDA(for advanced Excel users)
9. Excel Tables
Creating and managing tables
Structured references
Table filters and total rows
10. Excel Shortcuts (Asked Often!)
Ctrl + T→ Create tableCtrl + Shift + L→ Apply filterCtrl + Arrow Keys→ Navigate dataAlt + E + S + V→ Paste specialCtrl + ;→ Insert current date
11. Macros & VBA (If Required)
What is a macro?
Recording a macro
Editing macro in VBA editor
Writing basic VBA code (optional but a bonus in advanced roles)
12. Excel in Business Scenarios
Monthly sales report
Budget tracker
MIS reports
Reconciliation sheets
Data entry automation
