Excel for Data Analysis

Excel is where most data analysis careers start — and, surprisingly, where many finished analyses end up. It is fast for small and medium datasets, remarkably powerful for cleaning, and your manager will almost certainly ask you to deliver the final report in a spreadsheet.

This topic teaches Excel the way an analyst uses it: organization first, then formulas, then lookups, then cleaning, then PivotTables and dashboards. You will learn exactly which functions do the heavy lifting (SUMIFS, XLOOKUP, INDEX-MATCH) and how to keep your work fast on million-row files. By the end you will be able to take a messy export and turn it into a clean, charted, presentable analysis — a skill that carries straight into your next topic, SQL.

Just this path's Data Analysis Basics — a data type vocabulary. Everything Excel-specific is taught from zero. You need a copy of Microsoft Excel (or the free online/Google Sheets, which works for most of it).

Analysts use Excel in three phases: (1) bring data in (paste a CSV, import a query), (2) shape it (clean, add calculated columns, filter), and (3) summarize it (PivotTables, charts, dashboard). The golden rule: keep raw data untouched on its own sheet and do all work in calculated versions. That way your report is explainable and repeatable — if the source changes, you re-import, not redo.

A workbook is the file; each worksheet is a tab inside it. A cell is one box (e.g. B3 = column B, row 3). A range is a rectangle of cells: A2:C15. Formulas start with = and reference cells so they update automatically.

• Relative reference =A1+B1 — fills down and adjusts per row (perfect for per-row math)
• Absolute reference =$A$1 — locks the cell when you copy the formula (perfect for a fixed tax rate or target). Press F4 to toggle.

Consistent references are the difference between a formula that fills 10,000 rows perfectly and one that silently breaks.

Converting a data range into an Excel Table (Ctrl+T) brings big analyst wins: automatic filtering dropdowns, structured references that survive inserts, and a row stripe that grows as you add rows. From any table you can sort a column or filter to a subset in two clicks — always filter/sort from a Table, never by selecting columns manually (that hides rows and corrupts later formulas).

Tip for analysts: filter with the dropdown or Filter by Color during exploration; for repeatable analysis, use IF/SUMIFS instead, so the analysis doesn't depend on the current screen state.

These five are your daily vocabulary:

Conditional aggregations are the analyst's super-power: SUMIF(range, criteria, sum_range), SUMIFS, COUNTIF, COUNTIFS, AVERAGEIFS. Example: total revenue for Category="Electronics" and Region="North" → =SUMIFS(Sales, Category,"Electronics", Region,"North"). Learn SUMIFS/COUNTIFS well — they are Excel's version of the SQL filters you will meet next.

Lookups fetch a related value from another table — e.g. give me the customer name for this order id.

XLOOKUP (Excel 365) is the modern way: =XLOOKUP(lookup_value, lookup_array, return_array). Simple, handles missing values with a default.

INDEX + MATCH is the classic, still required in older files: MATCH finds the position of a value in a column; INDEX returns the value at that position in another column. Combined: =INDEX(Names, MATCH(lookup_id, Ids, 0)).

Developers use lookups any time two tables share a key (customer_id, product code). This is exactly what JOINs do in SQL — so succeed here now and SQL will feel familiar.

Analysts constantly reshape dates and text that arrive in messy form.

Date functions: YEAR(), MONTH(), DAY(), DATEDIF(), EOMONTH(), WEEKNUM(). Build month labels like =TEXT(A2,"mmm-yyyy") to group monthly KPI data.

Text functions: LEFT/RIGHT/MID (extract), TRIM (remove stray spaces), CLEAN (remove hidden line breaks), UPPER/LOWER/PROPER, LEN, SUBSTITUTE (replace), CONCAT/TEXTJOIN (join), FIND/SEARCH (locate). Example: split "John; 25; Cairo" using SEARCH/MID or the Text-to-Columns feature.

Text-and-date cleaning is where ugly exports become analysis-ready — and doubles as the next topic's data-cleaning toolbox.

Excel can fix most data-quality problems without a single formula:

• Remove duplicates — Data → Remove Duplicates (but always check why rows duplicated first)
• Missing values — filter blanks (ISBLANK/COUNTBLANK) and decide: fill with a sensible default or keep and flag them
• Text to Columns — split one messy column into several by delimiter (Data → Text to Columns)
• Data validation — restrict what users/users can enter (lists, ranges, rules) so future data arrives clean
• Conditional formatting — color cells by rule (top 10, above average, duplicate values) to visually spot outliers fast
• Consistency checks — with SUBSTITUTE/lowercase/trim normalization: standardize "New York" vs "NY"

These exact steps appear again in Power Query, SQL and pandas — once you know the concept of cleaning, only the button changes.

A PivotTable is Excel's flagship analysis tool: drag a field into Rows (a dimension), another into Values (a measure), and Excel aggregates instantly. One click gives you sales by region; drag again and you get it by month and region.

1. Select the data table → Insert → PivotTable
2. Drop Category into Rows, Sales into Values (set to Sum)
3. Add OrderDate to Rows (group by month) for a trend view
4. Insert a PivotChart from the pivot, add slicers (Region, Channel) for interactivity

A dashboard is a report sheet where several pivots/charts answer one business question at a glance — a KPI card up top (Revenue, Orders, Conversion), a trend line, a breakdown bar, and slicers that filter everything. Keep the layout clean: one question per dashboard, most important number top-left.

Use a public sales CSV (500+ rows). Build one workbook with Raw/Clean/Report sheets and complete:

1. Import data as a Table; add a calculated column Revenue = Quantity × Price with absolute references
2. Classify rows with IFS: segment orders into Small / Medium / Large by revenue
3. Answer 4 business questions using SUMIFS/COUNTIFS (revenue by category, orders by region above a threshold…)
4. Join customer names from a second table with XLOOKUP and re-verify with INDEX+MATCH
5. Clean: remove duplicates, split a combined column, standardize case, flag missing values with conditional formatting
6. Build a PivotTable (category by month) + PivotChart + one-sheet dashboard with 3 KPIs and a slicer

In most first analyst jobs Excel is the desktop tool: you receive a weekly export, clean it in Power Query or formulas, summarize with pivots, and email a dashboard. Managers trust a file that updates itself — formulas instead of typed numbers — and a report whose assumptions are visible. Meanwhile, the same cleaning concepts scale up: the Excel you learn here is the on-ramp to Power BI (which uses the same logic engine) and SQL (which expresses lookups and conditional sums differently). Excel is rarely glamorous, but it is the fastest way to look competent in week one of a new job.

Business problem: The store manager wants one sheet that shows monthly performance to decide where to focus the next campaign.

Dataset: the sales CSV from your practice (or any 500+ row sales export with order date, category, region, salesperson, revenue columns).

Objective: deliver a self-updating dashboard workbook, not a static screenshot.