Unit I · Excel for Data Analysis & EDA (≈10 hours)
1. Excel Workspace Essentials
- Interface tour: Ribbon, Formula Bar, Name Box, Sheets, Status Bar.
- Data types & cell formatting: number, date/time, text; custom formats.
- Fast entry: Fill Handle, Flash Fill, absolute/relative refs ($A$1 vs A1).
2. Descriptive Statistics Toolkit
- Summary:
AVERAGE, MEDIAN, MODE.SNGL, STDEV.S, VAR.S, MIN, MAX, QUARTILE.EXC, PERCENTILE.INC.
- Frequency tables:
FREQUENCY (array), COUNTIF(S), UNIQUE, SORT, FILTER.
- Data Analysis ToolPak: Descriptive Statistics report (enable add-in).
3. Visualization & Pivot Analysis
- Charts: column/line/area, combo, scatter (trendlines), box plot via chart tricks.
- Conditional Formatting: color scales, data bars, icon sets; rule priority & stop-if-true.
- PivotTables: build, group by dates/bins, % of total, show values as running total.
- PivotCharts and Slicers for interactivity; timelines for date filtering.
4. Data Quality & EDA Patterns
- Cleaning: trim, remove duplicates, find & replace, data validation lists.
- Lookups:
VLOOKUP/XLOOKUP, HLOOKUP, INDEX+MATCH, approximate vs exact match.
- Filtering & sorting; outlier checks with z-scores and box plots (IQR).
Industry mini-cases: Retail seasonality scan; HR attrition snapshot; Appointment delay profile in Healthcare.