← Back to Tools & Platforms Tools Guide

Excel for Business Analysts

Excel remains foundational despite newer platforms. This guide covers advanced techniques BAs actually use in requirements analysis, data validation, and stakeholder reporting.

Foundation

Why Excel Remains Essential for BAs

Despite the proliferation of specialized analytics platforms, Excel persists as the universal language of business data. Every stakeholder knows it, IT departments don't block it, and it requires no infrastructure investment or approval process. As a BA, Excel proficiency enables independence in data analysis, quick prototyping of calculations and visualizations, and stakeholder communication in familiar formats.

Universal Accessibility

Every organisation has Excel. No installation barriers, no licensing debates, no security reviews.

Stakeholder Familiarity

Executives understand Excel. Analysts trust it. Subject matter experts can collaborate directly.

Rapid Prototyping

Test calculations, validate data models, create quick dashboards before investing in formal tools.

Module 1

Pivot Tables for Multidimensional Analysis

Pivot tables transform flat data into multidimensional insights without formulas. BAs use them for requirements analysis (How many users requested feature X by department?), data validation (Do stakeholder estimates align with system data?), and impact analysis (Which business units would be affected by this change?).

Core Capabilities

  • Creating Pivot Tables — Transform raw transactional data into summary views. Insert → PivotTable → select data range.
  • Rows, Columns, Values — Drag fields to organise dimensions (rows/columns) and metrics (values). Rearrange dynamically.
  • Filters and Slicers — Add interactive filters. Slicers provide visual filtering (Insert → Slicer) for stakeholder presentations.
  • Calculated Fields — Create custom metrics within pivot tables: PivotTable Analyze → Fields, Items & Sets → Calculated Field.
  • Grouping and Subtotals — Group dates by month/quarter/year. Add subtotals at different hierarchy levels.

BA Use Cases

Requirements Analysis — Analyse requirements by priority, stakeholder, business unit. Identify patterns: "80% of Must-Have requirements come from Finance."

Data Validation — Cross-check stakeholder estimates against system data. Validate completeness: "We have user stories for 6 of 8 identified workflows."

Impact Analysis — Summarise transaction volumes by department, process, time period to quantify change impact.

Stakeholder Reporting — Create executive summaries: requirements status by priority, test coverage by module, issues by severity.

Pro Tips

  • Always use Tables (Insert → Table) for source data. Pivot tables auto-refresh when table data expands.
  • Name your tables descriptively (Table Design → Table Name) for easier pivot table management.
  • Use PivotTable Analyze → Refresh to update data after source changes.
  • Copy pivot table values (Copy → Paste Special → Values) to share static summaries without pivot infrastructure.
Module 2

Data Lookup: VLOOKUP, INDEX-MATCH, and XLOOKUP

Business analysts constantly combine data from multiple sources: requirements from documentation, user counts from databases, cost estimates from finance. Lookup functions automate this matching without manual copy-paste.

VLOOKUP

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

When to Use: Simple lookups where match column is leftmost in table. Quick for basic tasks.

Limitations:

  • Only looks right (cannot return columns left of match)
  • Column index breaks if columns are inserted
  • Slower performance on large datasets

Example:

=VLOOKUP(A2, Requirements!A:D, 3, FALSE)

Look up requirement ID in A2, return priority from column 3.

INDEX-MATCH

Syntax:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

When to Use: Professional BA work. More flexible and robust than VLOOKUP.

Advantages:

  • Returns values from any column (left or right)
  • Column references don't break when inserting columns
  • Faster performance on large datasets
  • Can perform two-way lookups (row and column)

Example:

=INDEX(Requirements!C:C, MATCH(A2, Requirements!A:A, 0))

Match A2 in Requirements column A, return corresponding value from column C.

XLOOKUP (Modern)

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

When to Use: Excel 365/2021+. Simplifies syntax while retaining INDEX-MATCH power.

Advantages:

  • Simpler syntax than INDEX-MATCH
  • Built-in error handling (if_not_found parameter)
  • Can search from bottom-up (find most recent entry)
  • Can return multiple columns at once

Example:

=XLOOKUP(A2, Requirements!A:A, Requirements!C:C, "Not Found")

Look up A2 in Requirements column A, return column C, show "Not Found" if no match.

BA Use Case: Combining Requirements with User Data

Scenario: You have requirements documented in one sheet with Requirement IDs. User research data in another sheet also references Requirement IDs. You need to combine them for stakeholder review.

Solution with INDEX-MATCH:

=INDEX(UserData!D:D, MATCH([@[Req ID]], UserData!A:A, 0))

This formula in your requirements table looks up each Req ID in the UserData sheet and returns the corresponding user count from column D. As requirements are added/removed, the formula automatically adjusts.

Module 3

Conditional Formatting for Visual Insights

Conditional formatting transforms spreadsheets from data dumps into decision-support tools. BAs use it to highlight exceptions (requirements without test coverage), show trends (increasing defect rates), and create visual dashboards (red/yellow/green status indicators).

Common Formatting Rules

  • Highlight Cells Rules — Greater Than, Less Than, Between, Equal To, Text Contains, Duplicate Values
  • Top/Bottom Rules — Top 10 Items, Bottom 10%, Above Average, Below Average
  • Data Bars — Visual bars showing relative magnitude. Great for comparing effort estimates or user counts.
  • Color Scales — Gradient from red (low) to green (high). Useful for heat maps showing priority or risk scores.
  • Icon Sets — Traffic lights, arrows, flags. Perfect for status dashboards (requirements approval status).

BA Use Cases

Requirements Traceability — Highlight requirements without linked test cases in red. Instant gap identification.

Rule: =ISBLANK(TestCaseID) → Fill Red

Priority Visualisation — Colour-code by MoSCoW priority: Must Have (red), Should Have (orange), Could Have (yellow), Won't Have (gray).

Status Dashboards — Icon sets for approval status: ✓ (Approved), ⚠ (In Review), ✗ (Rejected), ⏸ (Pending).

Outlier Detection — Highlight effort estimates > 2 standard deviations from mean for review.

Creating Custom Rules

Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format

Example: Highlight overdue requirements

=AND([@Status]<>"Complete", [@DueDate]<TODAY())

Applies formatting when status isn't "Complete" AND due date has passed.

Module 4

Power Query: Data Transformation Without Formulas

Power Query (Data → Get & Transform Data) enables BAs to clean, reshape, and combine data through a visual interface instead of complex formulas. Once configured, queries refresh with one click, automating repetitive data preparation.

Key Capabilities

  • Connecting to Data Sources — Excel files, CSV, databases, web pages, SharePoint, APIs. Import once, refresh automatically.
  • Data Cleaning — Remove duplicates, fill down values, trim spaces, change case, split/merge columns.
  • Data Transformation — Pivot/unpivot, group by, add calculated columns, filter rows, sort.
  • Combining Data — Merge queries (like SQL joins), append queries (stack tables), reference queries (reuse transformations).
  • Refreshable Connections — Data → Refresh All updates all Power Query connections. Automate with macros or Power Automate.

BA Use Cases

Requirements Import Automation — Connect to Jira/Azure DevOps exports. Transform JSON/CSV into analysis-ready tables. Refresh weekly for status tracking.

Multi-Source Data Combination — Merge user feedback from surveys, usage data from databases, and requirements from documentation into single analysis view.

Data Quality Validation — Filter for blank fields, duplicate IDs, invalid formats. Generate data quality report showing completeness.

Stakeholder Reporting — Shape system exports into executive-friendly formats. Remove technical fields, rename columns, add calculated KPIs.

Getting Started with Power Query

1

Import Data

Data → From Table/Range (or From Text/CSV, From Web, From Database)

2

Transform in Power Query Editor

Clean, reshape, combine using visual interface

3

Load to Excel

Home → Close & Load (creates refreshable connection)

4

Refresh Anytime

Data → Refresh All (updates with latest source data)

Module 5

Basic Macros for Repetitive Tasks

Macros automate repetitive multi-step tasks. BAs don't need to become VBA programmers — recording macros handles 80% of automation needs. Use for formatting reports, generating weekly status summaries, or preparing data for stakeholder review.

Recording Macros

Start Recording — View → Macros → Record Macro. Give descriptive name (no spaces). Assign shortcut key if desired.

Perform Actions — Execute the steps you want automated. Excel records every action (formatting, sorting, filtering, calculations).

Stop Recording — View → Macros → Stop Recording. Macro saved for reuse.

Run Macro — View → Macros → View Macros → Select macro → Run. Or use assigned shortcut key.

Common BA Automations

  • Report Formatting — Apply consistent formatting to requirements exports: headers bold, priority colour-coded, columns auto-sized.
  • Status Summary Generation — Copy data, create pivot table, apply filters, format as table — all automated.
  • Data Cleanup — Remove blank rows, trim extra spaces, convert text to proper case, sort by date.
  • Template Population — Copy specific cells from data sheet to template positions, apply formatting, save as new file.

Macro Security

Macros can pose security risks if from untrusted sources. Organisations often disable macros by default.

  • Only enable macros in files you created or from trusted colleagues
  • Save macro-enabled workbooks as .xlsm (not .xlsx)
  • Document what each macro does for future reference
  • Test macros on copy of data before running on originals
Learning

Practice Resources and Next Steps

Free Learning Resources

  • Microsoft Excel Training — support.microsoft.com/excel — Official tutorials and videos
  • ExcelIsFun YouTube Channel — 1,000+ video tutorials covering beginner to advanced topics
  • Leila Gharani YouTube — Power Query, pivot tables, dashboard design for business users
  • Excel Campus (Jon Acampora) — Practical tips, shortcuts, productivity techniques

Paid Courses (Optional)

  • Udemy "Microsoft Excel: Data Analysis with Excel Pivot Tables" — £19.99–59.99 during sales. 10 real-world case studies.
  • LinkedIn Learning Excel Paths — £23.99/month or £190 annually. Structured learning paths from beginner to advanced.
  • DataCamp Excel Fundamentals — £20/month. Interactive exercises with BA-focused datasets.

Downloadable Practice Files

Coming soon: Sample BA datasets with exercises covering pivot tables, lookup functions, conditional formatting, and Power Query transformations.

Practice with realistic requirements data, stakeholder analysis examples, and status reporting templates.

Continue Learning

Continue Building Your BA Toolkit

Next: SQL Fundamentals Back to Tools & Platforms