Stop 05 · Data Analysis for Business AnalystsTopics · 2/10

Excel for Business Analysis

In short

Fancy tools come and go, but every company still runs on spreadsheets. A BA who can clean data, look things up and build a pivot table in ten minutes is useful from day one.

Still the fastest, most universally available tool for quick analysis — PivotTables, VLOOKUP/XLOOKUP, and basic formulas cover the vast majority of a BA's real analytical needs.

The purple blob writes in a big ledger at a hostel mess counter with steel plates stacked behind.

Think of it like this

The hostel mess register, upgraded

The mess manager keeps a register of who ate, which meal and what they owe. Finding one student's monthly total means flipping pages for an hour. Put that register in a spreadsheet and the same answer takes one formula and two seconds.

Watch it happen

How XLOOKUP finds a customer's city

C101

C102

C103

C104

C105

1/5Your orders sheet has customer IDs. The city lives in another sheet. XLOOKUP joins them.

The big ideas

What you need to know

Lookups join data

XLOOKUP or VLOOKUP pulls matching data from another table, like a customer's city into an orders list.

Conditional totals

SUMIFS and COUNTIFS add or count rows that match conditions, like refunds over 2 days in Pune.

Pivot tables

Summarise thousands of rows by any category in seconds. Drag fields, no formulas needed.

Tables and filters

Format data as a table, freeze the header row and use filters. Clean structure saves hours later.

See it in code

A tiny example

excel

1=XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found")2=SUMIFS(Orders!E:E, Orders!B:B, "Pune", Orders!D:D, ">="&DATE(2026,1,1))3=COUNTIFS(Tickets!C:C, "Refund", Tickets!F:F, ">2")4=IFERROR(E2/D2, 0)

Four formulas a BA uses weekly: look up a city, total Pune sales this year, count slow refund tickets, and divide safely without #DIV/0! errors.

Save yourself the pain

Common mistakes

Oops

Typing numbers directly into formulas, like =B2*0.18.

Do this instead

Put rates and thresholds in their own labelled cells and refer to them, so one change updates everything.

Oops

Merging cells and leaving blank rows in your data.

Do this instead

Keep raw data as a clean table: one header row, one record per row, no merged cells.

Your mission

Analyse 50 orders

~40 min

Quick check

You need total sales for Mumbai in March only. Which function fits best?

Go deeper

1Build a PivotTable summarizing a sample dataset by category and time period.
2Practice using XLOOKUP to pull matching data from a related table.
3Learn to build a simple, clean dashboard using Excel charts.