Home / Topics

Excel for Business Analysts

Excel is still where most business analysis work ends up: requirement lists, RAID logs, traceability matrices, RACI charts and quick checks on data someone sent you. These tutorials cover the Excel skills a BA uses every week, each on a small sheet you can copy and try yourself.

What you will learn

Lookups come first, because joining two lists is the job you will do most. You will see VLOOKUP, XLOOKUP and INDEX MATCH, when to use each, and how to fix the #N/A error. Then pivot tables, which turn a long list into a summary in a few clicks, including counting unique values and adding a pivot chart. Finally, the BA documents themselves: a RAID log with conditional formatting, a requirements traceability matrix and a RACI matrix.

XLOOKUP needs Microsoft 365, Excel 2021 or later, or Excel for the web. If your company is on Excel 2019 or older, use the VLOOKUP and INDEX MATCH lessons instead; they do the same jobs.

Where to start

Start with VLOOKUP vs XLOOKUP. It shows both functions side by side on one table, so you learn the idea once and can use whichever your version has. Then do the 5-minute pivot table. With those two skills you can answer most of the questions a manager asks about a spreadsheet: "what is the price of this item?" and "how many of each do we have?"

Habits that prevent mistakes

Lock your lookup ranges with dollar signs before you fill a formula down, or the range slides and starts missing rows. Turn lists into tables (Ctrl+T) so new rows are picked up by formulas and pivots. Check for hidden spaces and numbers stored as text when a lookup fails on a value you can see. And keep raw data on one sheet and your summary on another, so nobody types over the source.