Data Analytics Full Program: Excel to BI

Most people think they know Excel, until they open a forty-thousand-row dataset and reach for the mouse to manually delete duplicates. This week moves you from 'I can make a chart' to 'I can turn a messy dataset into a decision in ten minutes.'

Start with the formulas analysts actually use daily: XLOOKUP (it looks left, survives inserted columns, and replaced VLOOKUP for good reason) and SUMIFS/COUNTIFS for totaling or counting against multiple conditions at once: think 'total revenue where region is East AND month is March.' Then there's the PivotTable, arguably the single highest-leverage tool in Excel: four clicks gets you a new question answered instead of a new formula written from scratch.

The second half is the statistics vocabulary you'll lean on for the rest of the program. Mean is the average; median is the middle value, and it's what you want when extreme outliers (like executive salaries) are skewing things. Standard deviation tells you how spread out your data is, and a z-score tells you how many standard deviations a point sits from the mean: how you formally spot an outlier instead of eyeballing it.

This week: advanced formulas (INDEX/MATCH, XLOOKUP, SUMIFS/COUNTIFS, array formulas), PivotTables & PivotCharts, data cleaning (TRIM, duplicates, validation), descriptive statistics, distributions & z-scores, and an early warning against confusing correlation with causation.