Using Excel for Analysis: Presentation Checklists, Approximate-Match VLOOKUP, SUMIFS, COUNTIFS, and Power Pivot Past the Row Limit

Hygiene: consistent fonts and sizes, chart titles, labelled axes, sources and assumptions with explicit months (not 'last 6 months'), no long sentences, charts over text. Advanced: the deck tells a story; every slide has a reason to exist (extras to the appendix); explain why and versus which period rather than restating numbers; every slide has a call to action; end with actions and the insights behind them. He suggests screenshotting the checklist.

Bucketing: ASP = GMV / units; lookup table of floors (0, 500, 1000, 2000, 3000) with labels; VLOOKUP(value, table anchored with F4, 2, TRUE) drops any number into its band; 14 negative-ASP rows (returns, promos) return #N/A and are filtered. Makeup sells most at 2,000-3,000 then 1,000-2,000; skincare 1,000-2,000 then 0-500 (face masks). SUMIFS(sum_range, range1, crit1, ...): GMV for owned retail, makeup, dates 1-7 July - 4.03 crore without dates, ~17 lakh with; verify against a pivot; use it when a pivot is too heavy. COUNTIFS: order IDs repeat, so build an order-level pivot (order number in rows, quantity as values) and then COUNTIFS(range, '>=3') = 8,778 orders with 3+ items. Also mentioned: INDEX-MATCH, XLOOKUP, macros for 30,000 SKUs x 20 stores.

Excel's grid limit is 1,048,576 rows; a bigger CSV warns 'dataset too large' and loses data on save. PROCEDURE: File > Options > Add-ins > Manage: COM Add-ins > tick Power Pivot; then Power Pivot > Manage > Get external data > From other sources > Text file; first row as headers. Demo: 25.8 lakh rows, 34,000 SKUs over 24 months, then insert a pivot. He also points to Tableau and Power BI for dashboards and says Python and R are unnecessary for this size of data.