MASTERY CHECKLIST · EXCEL

Excel Mastery Checklist — Data Analyst Roadmap

The complete Excel journey for aspiring data analysts. 32 priority-ranked topics, a formula bible, a 30-60-90 day plan, 11 portfolio projects, real-world scenarios, and interview prep — everything you need to be job-ready.

Beginner → Advanced ~130 hrs totalIndustry-focused · Interview-ready

Progress0 / 32

Done

Topics

Easy

Medium

Hard

~158h

Total Time

Priority-Based Topic Checklist

Ordered by recommended learning sequence. Expand each for context; check off as you master them.

01.Excel Fundamentals — Interface, Navigation & Data Types
Must Know
Easy 2–3 hrsOrder #1● Critical
02.Essential Lookup & Reference Formulas — XLOOKUP, INDEX-MATCH
Must Know
Medium 5–6 hrsOrder #2● Critical
03.Logical & Conditional Formulas — IF, IFS, AND, OR, SWITCH
Must Know
Easy 3–4 hrsOrder #3● Critical
04.Aggregation Formulas — SUMIF, COUNTIF, AVERAGEIF & Array Variants
Must Know
Medium 4–5 hrsOrder #4● Critical
05.Text Functions — Cleaning, Extracting & Combining Text
Must Know
Easy 3–4 hrsOrder #5● Critical
06.Date & Time Functions — Calculations, Formatting & Business Days
Medium 3–4 hrsOrder #6● High
07.Dynamic Array Formulas — FILTER, SORT, UNIQUE, SEQUENCE
Must Know
Medium 4–5 hrsOrder #7● Critical
08.Data Cleaning & Preparation — Techniques & Tools
Must Know
Medium 4–5 hrsOrder #8● Critical
09.Pivot Tables — The Most Powerful Analytical Tool in Excel
Most Critical
Medium 6–8 hrsOrder #9● Critical
10.Charts & Visualizations — Choosing, Building & Formatting
Medium 5–6 hrsOrder #10● High
11.Dashboard Creation — KPI, Executive & Interactive Dashboards
Must Know
Hard 8–10 hrsOrder #11● Critical
12.What-If Analysis — Goal Seek, Scenario Manager & Solver
Medium 4–5 hrsOrder #12● High
13.Statistical & Financial Formulas — Analysis & Reporting
Medium 4–5 hrsOrder #13● High
14.Advanced Filtering — Advanced Filter, Criteria Ranges & FILTER formula
Medium 2–3 hrsOrder #14● Medium
15.Power Query — Modern ETL for Excel Analysts
Must Know
Hard 8–10 hrsOrder #15● Critical
16.Power Pivot & Data Model — In-Memory Analytics in Excel
Hard 8–10 hrsOrder #16● High
17.VBA & Macros — Automation Without Coding Background
Hard 8–10 hrsOrder #17● Medium
18.OFFSET, INDIRECT & Volatile Functions — Dynamic References
Hard 3–4 hrsOrder #18
19.Financial Reporting — P&L, Cash Flow & Budget Models
Hard 8–10 hrsOrder #19
20.MIS Reporting & Daily/Weekly/Monthly Automation
Medium 5–6 hrsOrder #20
21.Data Reconciliation & Validation Techniques
Medium 3–4 hrsOrder #21
22.Performance & Workbook Optimization
Medium 3–4 hrsOrder #22
23.Error Auditing & Formula Tracing
Easy 2 hrsOrder #23
24.Excel + SQL Integration — Importing & Refreshing Data
Medium 4–5 hrsOrder #24
25.Excel + Power BI Integration — Bridge Between Tools
Medium 3–4 hrsOrder #25
26.Customer & Sales Analytics — CRM & Revenue Reporting
Medium 5–6 hrsOrder #26
27.HR Analytics — Workforce Reporting
Medium 4–5 hrsOrder #27
28.Inventory & Supply Chain Analysis
Medium 4–5 hrsOrder #28
29.Forecasting & Trend Analysis
Medium 4–5 hrsOrder #29
30.Advanced Conditional Formatting & Heatmaps
Easy 2–3 hrsOrder #30
31.Form Controls & Interactive Elements (No VBA)
Medium 3–4 hrsOrder #31
32.Advanced Excel — LET, LAMBDA, BYROW, MAP & Custom Functions
Hard 5–6 hrsOrder #32

Non-Negotiable

Must Know for Every Data Analyst

You will use all of these in the first week of any data analyst role.

PivotTable (Day 1)

›Create from any tabular data in < 30 seconds
›Group dates by month, quarter, year
›Show Values As: % of Total, Running Total, Rank
›Add Slicers + Timelines for interactivity
›Connect multiple PivotTables to one slicer
›Calculated field for derived metrics

Lookup Formulas (Every Project)

›XLOOKUP for all new work
›VLOOKUP for legacy compatibility
›INDEX+MATCH for 2-way and multi-criteria
›IFERROR/IFNA to handle not-found gracefully
›Nested XLOOKUP for hierarchical lookup

Aggregation (Every Report)

›SUMIFS with multiple criteria including dates
›COUNTIFS for frequency analysis
›AVERAGEIFS for conditional averages
›MAXIFS/MINIFS for range extremes
›SUMPRODUCT for weighted calculations

Data Cleaning (Before Any Analysis)

›TRIM + CLEAN for text
›Remove Duplicates (with subset)
›Text to Columns for splitting
›Flash Fill (Ctrl+E) for pattern recognition
›Find & Replace with wildcards
›Go To Special → Blanks

Dynamic Arrays (Modern Excel)

›FILTER for dynamic lists
›UNIQUE for distinct value extraction
›SORT / SORTBY for ranked output
›SEQUENCE for generating number/date series
›Spill range reference: A1# syntax

Date Functions (Reporting)

›TODAY(), EOMONTH() for dynamic dates
›DATEDIF for tenure/age calculation
›NETWORKDAYS for working day counts
›TEXT() for date formatting in labels
›YEAR/MONTH for period grouping

Reference

Excel Formula Bible

The most important formulas organized by category. Master these and you'll handle 95% of real-world analysis tasks.

Lookup & Reference

=XLOOKUP(val, look_rng, ret_rng, "NF", 0) — modern standard
=XLOOKUP(val, rng, rng2:rng3) — return multiple columns
=INDEX(rng, MATCH(val, look, 0)) — classic flexible
=MATCH(1,(A1=r1)*(B1=r2),0) — multi-criteria
=VLOOKUP(val, range, col, 0) — legacy
=CHOOSE(n, opt1, opt2, opt3) — pick by index
=OFFSET(ref, r, c, h, w) — dynamic range
=INDIRECT(text_ref) — reference from string

Dynamic Arrays

=FILTER(arr, include, "empty")
=SORT(arr, col_index, 1)
=SORTBY(arr, by_col, -1)
=UNIQUE(arr, by_col, once)
=SEQUENCE(r, c, start, step)
=VSTACK(arr1, arr2)
=LET(x, val, x*2) — named variables
=LAMBDA(x, x^2) — custom function

Date & Time

=TODAY(), =NOW()
=DATE(y,m,d), =DATEVALUE(text)
=YEAR/MONTH/DAY/WEEKDAY(date)
=DATEDIF(start, end, "YMD")
=NETWORKDAYS(start, end, hols)
=WORKDAY(start, n, hols)
=EOMONTH(date, months)
=TEXT(date, "DD-MMM-YYYY")

Aggregation

=SUMIFS(sum, r1, c1, r2, c2)
=COUNTIFS(r1, c1, r2, c2)
=AVERAGEIFS(avg, r1, c1)
=MAXIFS(max_rng, r1, c1)
=MINIFS(min_rng, r1, c1)
=SUMPRODUCT(arr1, arr2)
=SUBTOTAL(9, range) — respects filter
=AGGREGATE(func, 5, range) — ignores errors

Text Functions

=TRIM(text) — remove extra spaces
=CLEAN(text) — remove non-printable
=PROPER/UPPER/LOWER(text)
=LEFT/RIGHT/MID(text, n)
=FIND / =SEARCH(find, within, start)
=SUBSTITUTE(text, old, new)
=TEXTJOIN(", ", TRUE, range)
=TEXTSPLIT(text, ",", ";")

Statistical

=MEDIAN(range)
=MODE.SNGL(range)
=STDEV.S / STDEV.P(range)
=PERCENTILE.INC(range, 0.9)
=QUARTILE.INC(range, 1)
=RANK.EQ(val, range, 0)
=LARGE / SMALL(range, k)
=CORREL(arr1, arr2)

Error Handling

=IFERROR(formula, fallback) — catch all errors
=IFNA(formula, "Not Found") — catch #N/A only
=ISERROR(val), =ISNA(val)
=ISNUMBER / ISTEXT / ISBLANK(val)
Errors: #REF!, #N/A, #VALUE!, #DIV/0!, #NAME?, #SPILL!

Financial

=NPV(rate, val1, val2, ...)
=IRR(values, guess)
=PMT(rate, nper, pv) — loan payment
=XNPV(rate, vals, dates) — irregular cashflows
=XIRR(vals, dates, guess)
CAGR = (End/Start)^(1/n) - 1
=SLN(cost, salvage, life) — depreciation

Structured

30-60-90 Day Excel Learning Plan

For a working analyst putting in 1–2 hrs/day. Weekends include 3–4 hr project sessions.

Days 1–30

Foundation & Core Skills

Fundamentals, Formulas, PivotTables, Data Cleaning, Basic Dashboard

Week 1 · Days 1–5

Excel Fundamentals + Lookup Formulas

Cell refs, Tables, Named Ranges, XLOOKUP, INDEX-MATCH, VLOOKUP

Days 6–7

Weekend Project

Product pricing lookup: XLOOKUP + INDEX-MATCH side by side

Week 2 · Days 8–12

Aggregation + Text + Date

SUMIFS, COUNTIFS, all text functions, DATEDIF, NETWORKDAYS

Days 13–14

Project: Employee Report

Tenure, age band, salary analysis, department headcount

Week 3 · Days 15–19

PivotTables + Charts

10 PivotTables; calculated fields; slicers; combo charts; sparklines

Days 20–21

Project: Sales PivotTable Suite

5 PivotTables + 3 slicers + 3 connected charts

Week 4 · Days 22–26

Data Cleaning + Dynamic Arrays

TRIM/CLEAN, Flash Fill, FILTER, SORT, UNIQUE, LET

Days 27–30

Project: First Dashboard

1-page sales KPI dashboard with 6 KPIs, 3 charts, 1 dropdown

Days 31–60

Intermediate — Power Query, VBA, Advanced Analysis

Power Query, What-If, Financial Formulas, Advanced Dashboards, Automation

Days 31–36

Power Query Deep Dive

Connect 5 sources; append 12 files; merge, unpivot, parameters

Days 37–38

Power Query Project

Monthly CSV folder → combined → cleaned → loaded pipeline

Days 39–43

What-If + Financial

Goal Seek, Data Tables, Scenario Manager, Solver, NPV, IRR, XIRR

Days 44–45

Financial Model Project

5-year P&L with Base/Best/Worst + NPV/IRR

Days 46–50

VBA & Macros Basics

Recorder; edit code; loops; automate formatting + PDF export

Days 51–52

Automation Project

One-click monthly report: PQ refresh + format + PDF + timestamp

Days 53–57

Advanced Dashboard Design

Form controls, dynamic charts, bookmark navigation

Days 58–60

Portfolio Dashboard #2

HR analytics: headcount, attrition, tenure, department breakdown

Days 61–90

Advanced — Power Pivot, DAX, Business Analytics, SQL

Power Pivot, DAX, Domain Dashboards, SQL, Python/PBI Integration

Days 61–66

Power Pivot + Data Model

Multi-table model, relationships, DAX measures, CALCULATE, time intelligence

Days 67–68

Power Pivot Project

Star schema: fact + 3 dimensions + 10 DAX measures

Days 69–73

Advanced DAX + LAMBDA/LET

CALCULATE, FILTER, RELATED, TOTALYTD, BYROW, MAP

Days 74–75

SQL + Excel Integration

PQ to SQL Server; native SQL query; parameterized queries

Days 76–80

Business Domain Dashboards

Finance P&L, Supply Chain, Customer Analytics, Forecasting

Days 81–83

Performance Optimization

Remove volatiles; reduce size; calc mode; formula audit

Days 84–87

Excel + Power BI Bridge

Analyze in Excel; rebuild in PBI; compare DAX transfer

Days 88–90

Capstone Project

PQ pipeline → Power Pivot model → 30+ formulas → 3-page dashboard → Macros

Interview Prep

4-Phase Interview Preparation Roadmap

Expect live Excel exercises — practice for speed and accuracy.

Foundation — Core Formulas Cold

Week 1 · Daily 1.5 hrs

Must nail without looking

›Write XLOOKUP with not-found handler
›Write SUMIFS with 3 criteria including dates
›Explain absolute vs relative reference
›Build a PivotTable from scratch in under 2 min
›Write IF/IFS with 4+ tiers

Speed practice

›Time yourself: each task in < 60 seconds
›Practice on unfamiliar datasets
›Explain keystrokes out loud
›Make and fix deliberate errors

Live Exercise Practice — Do-This-Now Problems

Week 2 · Daily 2 hrs

Common live tasks

›Revenue by region and month → PivotTable + Slicer in 5 min
›VLOOKUP the customer name from this table
›Remove duplicates and clean this column
›Make this formula not show an error
›Why does this SUMIFS return 0?

What interviewers assess

›Plan before acting
›Keyboard shortcut fluency
›Error recovery / debugging
›Communication while working
›Design decisions & tradeoffs

Advanced — Power Query & Dashboard

Week 3 · Daily 1.5 hrs

Advanced questions

›Combine multiple Excel files automatically?
›Power Query vs VLOOKUP for merging?
›Interactive dashboard without VBA?
›Prevent users from breaking formulas?
›Automate a monthly MIS report?

Portfolio talking points

›Real project where Excel saved significant time
›Quantify impact: "3-hour task → 10-min refresh"
›Mention Power Query, PivotTable, Macros by name
›Know when to use SQL / Power BI instead

Take-Home Task — Deliver Interview Quality

Week 4 · 4–6 hrs per task

Delivery checklist

›Clean layout with clear sheet tabs
›Dashboard: no gridlines, consistent colors
›All formulas wrapped with IFERROR
›Named cells + formula sheet comments
›Protect sheets; unlock only inputs
›File under 5MB, saved as .xlsx

Standout elements

›Power Query for import (not copy-paste)
›Dynamic chart with dropdown selection
›RAG conditional formatting on KPIs
›Summary PivotTable + Slicer
›A "README" sheet explaining the model

Top Excel Shortcuts Every Analyst Should Know

Shortcut fluency signals experience. Practice these until they're muscle memory.

Navigation

Ctrl+Home — A1
Ctrl+End — last cell
Ctrl+Arrow — jump to edge
Ctrl+Shift+Arrow — select to edge
Ctrl+Space — entire column
Shift+Space — entire row
Ctrl+A — select all
Ctrl+PgUp/Dn — switch sheets

Formatting

Ctrl+B — Bold
Ctrl+1 — Format cells
Ctrl+Shift+4 — Currency
Ctrl+Shift+5 — Percent
Alt+H+O+I — Autofit col
Alt+H+O+A — Autofit row
Alt+H+M+C — Merge & center
Alt+H+F+P — Format painter

Formulas & Auditing

Alt+M+V — Evaluate
Alt+M+P — Trace precedents
Alt+M+D — Trace dependents
Ctrl+` — Show formulas
F9 — Step into formula
Alt+= — AutoSum
Ctrl+Shift+Enter — Legacy array
Tab — Enter & move right

Editing

F4 — Toggle $ ref
Ctrl+D — Fill down
Ctrl+R — Fill right
Ctrl+E — Flash Fill
Ctrl+H — Find & Replace
F5 — Go To Special
Ctrl+Alt+V then V — Paste values
Ctrl+Shift++ — Insert row/col

Data & Analysis

Ctrl+T — Create Table
Alt+N+V — Insert PivotTable
Alt+F5 — Refresh Pivot
Ctrl+Shift+L — AutoFilter
Alt+A+M — Remove Duplicates
Alt+A+S+S — Sort dialog
Ctrl+F3 — Name Manager
F9 — Recalculate

File & View

Ctrl+S — Save
F12 — Save As
Ctrl+F2 — Print Preview
Alt+W+F+F — Freeze panes
Alt+F11 — VBA editor
Alt+F8 — Run macro
Alt+W+G — Zoom to selection
Shift+F11 — New sheet

Real-World

Business Scenarios You'll Actually Face

Common asks from actual analyst roles. Know how to solve each end-to-end.

Scenario 1 — Monthly Sales MIS Report

Data source

›12 regional managers submit Excel files
›Combine into one master
›Old way: manual copy (3 hrs)
›New way: Power Query folder connector

Solution build

›PQ: connect to folder → append
›Filter current month, clean names
›PivotTable: region × product × metric
›SUMIFS cross-check totals
›Conditional formatting RAG

Automation

›VBA: refresh PQ → format → PDF → email
›Result: 3-hour task → 5-minute refresh
›Add "Last Refreshed: "&TEXT(NOW(),...)

Scenario 2 — Data Reconciliation

Problem

›CRM: 1,250 orders, ERP: 1,247
›Revenue differs by ₹45,000
›Find the differing records

Excel solution

›XLOOKUP across sheets
›ISNA to flag one-side-only records
›SUMIFS on matched → amount diff
›Conditional formatting mismatches

Output

›Matched / unmatched counts
›Variance amount column
›Filter to mismatches only
›Notes for finance review

Scenario 3 — CFO Revenue Target

Request

›Current margin: 12%
›Target: ₹5 Cr EBITDA
›Fixed & variable cost known
›What revenue target is needed?

Excel solution

›Simple P&L model with inputs
›Goal Seek: EBITDA = 5Cr → Revenue
›2-way Data Table: Revenue × Margin
›Scenario Manager: conservative/base/stretch

Presentation

›Interactive sensitivity table
›Waterfall: revenue → EBITDA bridge
›Scenario summary report

Scenario 4 — HR Headcount by Dept & Grade

Data available

›Employee master: dept, grade, join/exit
›No monthly snapshots — must derive
›Movement data: hire & exit dates

Solution

›Active flag: =IF(OR(exit blank, exit>EOM),1,0)
›COUNTIFS per dept, grade, month
›Opening + Add − Exits = Closing
›Attrition = Exits / ((Open+Close)/2)

Dashboard

›Headcount waterfall
›Attrition trend line
›Matrix: department × grade w/ color scale
›Slicer for department view

Standards

Excel Best Practices Used at Real Companies

Standards followed by professional analysts at consulting firms, banks, FMCG, and tech orgs.

File Structure

›Separate sheets: Data, Calculations, Dashboard, Assumptions
›Color-code tabs: blue=data, green=output, yellow=input
›Always use Tables (not ranges)
›Never mix data, calcs, output on one sheet
›All hardcoded inputs on one Assumptions sheet
›Use a README sheet

Dashboard Standards

›One focused question per dashboard page
›No more than 7 visuals per view
›Consistent brand color palette
›Remove gridlines from dashboards
›Business-focused chart titles
›Include "As of [date]" on every dashboard

Performance

›Manual calc mode (F9) for heavy models
›Replace volatile functions with Table-based alternatives
›Power Query instead of in-sheet storage
›Delete unused names/styles/dead columns
›Limit CF to specific ranges, not whole columns
›Compress images

Formula Standards

›Blue = input, black = formula, green = external link
›Always wrap lookups with IFERROR/IFNA
›Avoid volatile functions in large models
›Named ranges for key assumptions
›Comments on complex logic
›Never nest 2–3+ functions deep without LET

Data Integrity

›Lock formula cells; unlock only inputs
›Data Validation on all manual inputs
›Row count checks atop each data sheet
›CF to flag anomalies
›Verify: source total = calculated total
›Never delete rows — hide or filter

Sharing

›.xlsx (not .xls)
›File version + date in filename
›No spaces in script-friendly filenames
›Protect sheets before sharing for editing
›OneDrive/SharePoint for version control
›"How to Use" guide for shared templates

Excel Mastery Checklist — Data Analyst Roadmap

Priority-Based Topic Checklist

01.Excel Fundamentals — Interface, Navigation & Data Types

02.Essential Lookup & Reference Formulas — XLOOKUP, INDEX-MATCH

03.Logical & Conditional Formulas — IF, IFS, AND, OR, SWITCH

04.Aggregation Formulas — SUMIF, COUNTIF, AVERAGEIF & Array Variants

05.Text Functions — Cleaning, Extracting & Combining Text

06.Date & Time Functions — Calculations, Formatting & Business Days

07.Dynamic Array Formulas — FILTER, SORT, UNIQUE, SEQUENCE

08.Data Cleaning & Preparation — Techniques & Tools

09.Pivot Tables — The Most Powerful Analytical Tool in Excel

10.Charts & Visualizations — Choosing, Building & Formatting

11.Dashboard Creation — KPI, Executive & Interactive Dashboards

12.What-If Analysis — Goal Seek, Scenario Manager & Solver

13.Statistical & Financial Formulas — Analysis & Reporting

14.Advanced Filtering — Advanced Filter, Criteria Ranges & FILTER formula

15.Power Query — Modern ETL for Excel Analysts

16.Power Pivot & Data Model — In-Memory Analytics in Excel

17.VBA & Macros — Automation Without Coding Background

18.OFFSET, INDIRECT & Volatile Functions — Dynamic References

19.Financial Reporting — P&L, Cash Flow & Budget Models

20.MIS Reporting & Daily/Weekly/Monthly Automation

21.Data Reconciliation & Validation Techniques

22.Performance & Workbook Optimization

23.Error Auditing & Formula Tracing

24.Excel + SQL Integration — Importing & Refreshing Data

25.Excel + Power BI Integration — Bridge Between Tools

26.Customer & Sales Analytics — CRM & Revenue Reporting

27.HR Analytics — Workforce Reporting

28.Inventory & Supply Chain Analysis

29.Forecasting & Trend Analysis

30.Advanced Conditional Formatting & Heatmaps

31.Form Controls & Interactive Elements (No VBA)

32.Advanced Excel — LET, LAMBDA, BYROW, MAP & Custom Functions