How to Learn Excel Formulas — Complete Beginner's Guide

You've watched 100+ YouTube tutorials but still can't remember VLOOKUP syntax when you need it at work. Sound familiar? That's because watching isn't learning — practice is learning.

This guide gives you the complete roadmap: which formulas to learn first, in what order, and how to actually remember them.

The Problem: Why You Forget Formulas

Research shows that passive learning (watching videos) has a retention rate of only 5-10%. Active practice (solving problems yourself) has a retention rate of 75%+. This is why you watch a VLOOKUP tutorial, understand it perfectly, and then blank out in an interview.

The solution is spaced repetition through practice — solving formula problems repeatedly until the syntax becomes automatic, like typing.

The Learning Path: 5 Levels

Don't try to learn all 400+ Excel functions at once. Focus on these 5 levels in order.

Level 1: Foundation (Week 1-2)

These 10 formulas handle 60% of all Excel work.

SUM, AVERAGE, COUNT

=SUM(A1:A100) — adds all numbers in A1 to A100.
=AVERAGE(A1:A100) — average of all numbers.
=COUNT(A1:A100) — counts cells with numbers only.

MAX, MIN

=MAX(B2:B500) — highest value. =MIN(B2:B500) — lowest value. Used in every MIS report.

IF

=IF(A1>100, "Pass", "Fail") — the most important formula in Excel. If the condition is true, return one thing; otherwise, return another.

CONCATENATE / &

=A1 & " " & B1 — joins text from two cells with a space between them.

LEN, LEFT, RIGHT, MID

=LEN(A1) — character count. =LEFT(A1, 3) — first 3 characters. =RIGHT(A1, 4) — last 4. =MID(A1, 3, 5) — 5 characters starting from position 3.

TRIM, UPPER, LOWER, PROPER

=TRIM(A1) — removes extra spaces. =UPPER(A1) — converts to uppercase. Essential for cleaning messy data.

Level 2: Conditional Formulas (Week 3-4)

SUMIF / SUMIFS

=SUMIF(A:A, "Delhi", C:C) — sum column C where column A is "Delhi".
=SUMIFS(C:C, A:A, "Delhi", B:B, ">1000") — sum with multiple conditions. This is in every interview.

COUNTIF / COUNTIFS

=COUNTIF(A:A, "Sales") — how many cells say "Sales".
=COUNTIFS(A:A, "Sales", B:B, ">5000") — count with multiple conditions.

AVERAGEIF / AVERAGEIFS

=AVERAGEIF(A:A, "Delhi", C:C) — average salary for Delhi employees only.

Nested IF

=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "F"))) — multiple conditions with grades. Modern alternative: IFS function.

Level 3: Lookup Formulas (Week 5-7)

VLOOKUP

=VLOOKUP(lookup_value, table, col_num, FALSE) — the #1 most-asked formula in job interviews. Always use FALSE for exact match.

INDEX-MATCH

=INDEX(C:C, MATCH(F2, A:A, 0)) — more flexible than VLOOKUP. Can look left. Doesn't break when columns move. Every data analyst prefers this.

XLOOKUP (Excel 365)

=XLOOKUP(F2, A:A, C:C, "Not Found") — replaces VLOOKUP entirely. Simpler syntax, built-in error handling.

HLOOKUP

=HLOOKUP(lookup_value, table, row_num, FALSE) — horizontal version of VLOOKUP. Less common but still tested.

Level 4: Date & Text (Week 8-10)

Date Functions

=TODAY(), =NOW(), =YEAR(A1), =MONTH(A1), =DAY(A1)
=DATEDIF(A1, B1, "M") — months between two dates.
=NETWORKDAYS(A1, B1) — working days between dates (excludes weekends).
=TEXT(A1, "DD-MMM-YYYY") — format dates as text for reports.

Text Functions

=SUBSTITUTE(A1, "old", "new") — replace text.
=FIND("@", A1) — position of @ in email.
=VALUE(A1) — convert text number to actual number.
=TEXTJOIN(", ", TRUE, A1:A10) — join multiple cells with a separator.

Level 5: Advanced (Week 11-14)

Array Formulas

=SUMPRODUCT((A2:A100="Delhi")*(C2:C100)) — multi-condition calculations without helper columns.

Dynamic Arrays (Excel 365)

=UNIQUE(A2:A100) — unique values.
=SORT(A2:A100) — sorted list.
=FILTER(A:C, B:B="Sales") — filtered table.

Logical Functions

=AND(A1>0, B1>0), =OR(A1>0, B1>0), =NOT(A1>0)
=SWITCH(A1, 1, "Jan", 2, "Feb", 3, "Mar") — cleaner than nested IF.

The Most Effective Way to Learn

1. Learn one formula at a time — don't jump between topics.
2. Practice with real data — toy examples don't stick. Use Indian business datasets (sales, attendance, payroll).
3. Type the formula yourself — never copy-paste from a tutorial.
4. Solve 10-20 problems per formula — repetition builds muscle memory.
5. Test yourself without notes — if you need to look up the syntax, you haven't practiced enough.

How Long Does It Take?

With 30 minutes of daily practice:

• 2 weeks — comfortable with basic formulas (SUM, IF, COUNT)
• 4 weeks — confident with SUMIFS, COUNTIFS, conditional formulas
• 8 weeks — VLOOKUP and INDEX-MATCH become automatic
• 12 weeks — ready for any Excel interview test

The key is consistency, not hours. 30 minutes daily beats 5 hours on weekends.