Excel remains the most widely used analytics tool in the world — and for good reason. It is fast, flexible, and available everywhere. But most analysts use only 10–15% of what Excel is capable of. This guide covers the 30 formulas that separate an average analyst from an expert one.
Each formula includes a clean, real-world example with the exact syntax you can copy into your workbook immediately.
Version Note
Formulas 27–30 (Dynamic Arrays: FILTER, SORT, UNIQUE, SEQUENCE) require Excel 2019, Microsoft 365, or Excel for the Web. If you see a #SPILL! error, confirm you're on a supported version. All other formulas work in Excel 2013 and above.
Category 1 — Lookup & Reference (1–8)
These are the formulas you'll use in almost every analytical workbook. Master them and you'll eliminate 80% of manual copy-paste work.
1. XLOOKUP — The Modern Replacement for VLOOKUP
XLOOKUP is the most important formula upgrade in Excel's history. It searches any direction, returns multiple columns, handles errors natively, and works with exact or approximate matches.
Excel Formula
-- Basic XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
-- Example: Find customer name by ID
=XLOOKUP(A2, CustomerTable[CustomerID], CustomerTable[CustomerName], "Not Found")
-- Return multiple columns at once
=XLOOKUP(A2, CustomerTable[CustomerID], CustomerTable[[Name]:[City]:[Phone]], "Not Found")
-- Reverse lookup (search from last to first)
=XLOOKUP(A2, SalesTable[OrderID], SalesTable[Amount], , 0, -1)
Why XLOOKUP Over VLOOKUP?
VLOOKUP breaks when you insert a column because it uses a column number (e.g., 3rd column). XLOOKUP uses column names, so it is immune to structural changes. It also searches left-to-right and right-to-left, which VLOOKUP cannot do.
2. VLOOKUP — Still Essential for Legacy Files
Excel Formula
-- Exact match (always use FALSE for data analysis)
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
-- Example: Fetch product category from a lookup table
=VLOOKUP(B2, $F$2:$H$500, 2, FALSE)
-- Nested VLOOKUP with IFERROR
=IFERROR(VLOOKUP(A2, ProductTable, 3, FALSE), "Not Found")3. INDEX + MATCH — The Power Combination
INDEX + MATCH is more flexible than VLOOKUP and works in all Excel versions. It can search by row AND column simultaneously — making it ideal for two-dimensional lookups.
Excel Formula
-- Basic INDEX + MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
-- Example: Find Q3 revenue for a specific region
=INDEX(B2:E10, MATCH("South", A2:A10, 0), MATCH("Q3", B1:E1, 0))
-- Left lookup (searches column to the LEFT of data)
=INDEX(A2:A100, MATCH("Premium", C2:C100, 0))4. INDIRECT — Dynamic Range References
Excel Formula
-- Reference a sheet name stored in a cell
=INDIRECT("'"&A1&"'!B2")
-- Dynamic named range
=SUM(INDIRECT(A1&"Sales"))
-- Example: Sum a column whose name is in cell B1
=SUM(INDIRECT("Table1["&B1&"]"))5. OFFSET — Dynamic Range That Moves
Excel Formula
-- OFFSET(reference, rows, cols, [height], [width])
=OFFSET(A1, 2, 1) -- Cell 2 rows down, 1 column right = B3
-- Dynamic sum of last 3 months
=SUM(OFFSET(A1, 0, COUNTA(1:1)-3, 1, 3))
-- Use with COUNTA to create auto-expanding ranges
=AVERAGE(OFFSET(B2, 0, 0, COUNTA(B:B)-1, 1))6. MATCH — Find Position of a Value
Excel Formula
-- Returns the position (row number) of a value in a range
=MATCH("Delhi", A2:A100, 0) -- exact match → position number
=MATCH(MAX(B:B), B:B, 0) -- position of maximum value
-- Check if a value exists in a list
=ISNUMBER(MATCH(A2, ValidList, 0))7. CHOOSE — Select from a List by Index
Excel Formula
-- Return different values based on a number
=CHOOSE(2, "Jan", "Feb", "Mar", "Apr") -- returns "Feb"
-- Dynamic column selection in VLOOKUP
=VLOOKUP(A2, Table1, CHOOSE(B2, 2, 4, 6), FALSE)
-- Quarter label from month number
=CHOOSE(MONTH(A2), "Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")8. HYPERLINK — Clickable Links Inside Excel
Excel Formula
-- Dynamic link to another sheet
=HYPERLINK("#Sheet2!A1", "Go to Summary")
-- Link to website with dynamic URL
=HYPERLINK("https://etlguru.in/blog/"&A2, "Open Article")
-- Email link
=HYPERLINK("mailto:"&B2&"?subject=Follow Up", "Send Email")Category 2 — Conditional Aggregation (9–13)
These are the workhorses of data analysis in Excel — summing, counting, and averaging based on one or more conditions.
9. SUMIFS — Sum With Multiple Conditions
Excel Formula
-- SUMIFS(sum_range, criteria_range1, criteria1, [range2, criteria2]...)
=SUMIFS(C:C, A:A, "Delhi", B:B, "Electronics")
-- With date range condition
=SUMIFS(D:D, A:A, "Delhi", B:B, ">="&DATE(2024,1,1), B:B, "<="&DATE(2024,12,31))
-- Wildcard — all products starting with "PRD"
=SUMIFS(C:C, A:A, "PRD*")
-- Dynamic criteria from a cell reference
=SUMIFS(SalesTable[Revenue], SalesTable[Region], F2, SalesTable[Category], G2)10. COUNTIFS — Count With Multiple Conditions
Excel Formula
-- Count orders from Delhi above ₹10,000
=COUNTIFS(A:A, "Delhi", C:C, ">10000")
-- Count unique values (array formula)
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
-- Count non-blank entries meeting a condition
=COUNTIFS(A:A, "Active", B:B, "<>")11. AVERAGEIFS — Conditional Average
Excel Formula
-- Average revenue for Electronics in Q1
=AVERAGEIFS(C:C, A:A, "Electronics", B:B, "Q1")
-- Exclude zeros from average
=AVERAGEIFS(C:C, A:A, "Delhi", C:C, ">0")12. SUMPRODUCT — The Swiss Army Knife Formula
SUMPRODUCT is one of the most powerful formulas in Excel. It multiplies arrays together and sums the result — making it ideal for weighted calculations, conditional sums, and multi-criteria analysis without array entry (Ctrl+Shift+Enter).
Excel Formula
-- Weighted average (e.g., weighted score)
=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)
-- Conditional sum without SUMIFS
=SUMPRODUCT((A2:A100="Delhi") * (B2:B100="Electronics") * C2:C100)
-- Count unique values in a range
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
-- Revenue per unit (array multiplication then sum)
=SUMPRODUCT(Qty, Price, Discount) -- qty * price * discount summed
-- Rank without duplicates
=SUMPRODUCT((B2:B100>B2)*1) + 113. AGGREGATE — Ignore Errors and Hidden Rows
Excel Formula
-- AGGREGATE(function_num, options, array)
-- function: 1=AVERAGE, 4=MAX, 5=MIN, 9=SUM, 14=LARGE, 15=SMALL
-- options: 5=ignore hidden rows, 6=ignore errors, 7=ignore both
-- Sum ignoring errors
=AGGREGATE(9, 6, C2:C100)
-- Max ignoring hidden rows and errors
=AGGREGATE(4, 7, C2:C100)
-- 2nd largest value (ignoring errors)
=AGGREGATE(14, 6, C2:C100, 2)Category 3 — Text Formulas (14–18)
14. TEXTJOIN — Join Text With a Delimiter
Excel Formula
-- TEXTJOIN(delimiter, ignore_empty, text1, [text2]...)
=TEXTJOIN(", ", TRUE, A2:A10) -- "Delhi, Mumbai, Pune"
-- Join only non-blank values
=TEXTJOIN(" | ", TRUE, B2:B20)
-- Combine first and last name
=TEXTJOIN(" ", TRUE, A2, B2) -- "Ankit Kumar"
-- List values that meet a condition (array formula)
=TEXTJOIN(", ", TRUE, IF(B2:B100="Active", A2:A100, ""))15. TEXT — Format Numbers as Readable Text
Excel Formula
-- Common TEXT format codes
=TEXT(A2, "DD MMM YYYY") -- "15 Feb 2025"
=TEXT(A2, "₹#,##0.00") -- "₹1,25,000.00"
=TEXT(A2, "0.0%") -- "87.5%"
=TEXT(A2, "MMMM") -- "February"
=TEXT(A2, "DDD") -- "Sat"
-- Dynamic email subject with formatted date
="Sales Report — "&TEXT(TODAY(),"DD MMM YYYY")16. TRIM + CLEAN — Remove Unwanted Spaces and Characters
Excel Formula
-- Remove leading, trailing, and extra spaces
=TRIM(A2)
-- Remove non-printable characters (from imported data)
=CLEAN(A2)
-- Combined: clean AND trim
=TRIM(CLEAN(A2))
-- Remove ALL spaces (e.g., for ID matching)
=SUBSTITUTE(A2, " ", "")17. LEFT / RIGHT / MID — Extract Text Substrings
Excel Formula
-- Extract first 4 characters (product code prefix)
=LEFT(A2, 4) -- "PROD" from "PROD-001"
-- Extract last 3 characters
=RIGHT(A2, 3) -- "001" from "PROD-001"
-- Extract from middle: start at pos 6, take 3 chars
=MID(A2, 6, 3)
-- Extract year from "Jan-2024"
=RIGHT(A2, 4) -- "2024"
-- Dynamic: extract text before a delimiter
=LEFT(A2, FIND("-", A2) - 1) -- "PROD" from "PROD-001"
-- Extract text after a delimiter
=MID(A2, FIND("-", A2) + 1, LEN(A2))18. SUBSTITUTE — Find and Replace Within a Formula
Excel Formula
-- Replace specific text
=SUBSTITUTE(A2, "Pvt Ltd", "") -- removes "Pvt Ltd"
=SUBSTITUTE(A2, "-", "/") -- replaces dashes with slashes
-- Replace only the 2nd occurrence
=SUBSTITUTE(A2, "a", "A", 2)
-- Count occurrences of a character
=(LEN(A2) - LEN(SUBSTITUTE(A2, ",", ""))) -- count commasCategory 4 — Logical Formulas (19–22)
19. IFS — Multiple Conditions Without Nesting
Excel Formula
-- IFS replaces deeply nested IF statements
=IFS(
A2 >= 90, "Excellent",
A2 >= 75, "Good",
A2 >= 60, "Average",
A2 >= 40, "Below Average",
TRUE, "Poor"
)
-- Revenue tier classification
=IFS(
B2 >= 1000000, "Enterprise",
B2 >= 500000, "Large",
B2 >= 100000, "Medium",
TRUE, "Small"
)20. SWITCH — Match a Value Against a List
Excel Formula
-- SWITCH(expression, value1, result1, [value2, result2]..., [default])
=SWITCH(A2,
1, "January",
2, "February",
3, "March",
"Other Month"
)
-- Status code to label
=SWITCH(B2,
"A", "Active",
"I", "Inactive",
"P", "Pending",
"Unknown"
)21. IFERROR + IFNA — Handle Errors Gracefully
Excel Formula
-- IFERROR: catch any error (#N/A, #DIV/0!, #REF!, #VALUE!)
=IFERROR(VLOOKUP(A2, Table1, 2, FALSE), "Not Found")
=IFERROR(A2/B2, 0) -- return 0 on divide by zero
-- IFNA: catch ONLY #N/A errors (more precise)
=IFNA(XLOOKUP(A2, Names, Values), "Missing")
-- Nested IFERROR for two lookup attempts
=IFERROR(
VLOOKUP(A2, Table1, 2, FALSE),
IFERROR(VLOOKUP(A2, Table2, 2, FALSE), "Not Found")
)22. AND / OR / NOT — Build Complex Conditions
Excel Formula
-- AND: all conditions must be true
=IF(AND(A2="Active", B2>10000), "Qualified", "Not Qualified")
-- OR: at least one condition must be true
=IF(OR(A2="Delhi", A2="Mumbai", A2="Bangalore"), "Tier 1", "Other")
-- NOT: reverse a condition
=IF(NOT(ISBLANK(A2)), "Filled", "Empty")
-- Combined: active customer with revenue in range
=IF(AND(A2="Active", OR(B2<5000, B2>50000)), "Flag", "OK")Category 5 — Date & Time Formulas (23–26)
23. EDATE + EOMONTH — Date Arithmetic
Excel Formula
-- EDATE: add/subtract months from a date
=EDATE(A2, 3) -- 3 months after date in A2
=EDATE(A2, -6) -- 6 months before date in A2
-- EOMONTH: last day of a month
=EOMONTH(A2, 0) -- last day of A2's month
=EOMONTH(A2, 1) -- last day of next month
=EOMONTH(A2, 0)+1 -- first day of next month
-- Days remaining in current month
=EOMONTH(TODAY(), 0) - TODAY()24. NETWORKDAYS — Working Days Between Dates
Excel Formula
-- Working days between two dates (excludes weekends)
=NETWORKDAYS(A2, B2)
-- Exclude holidays listed in a range
=NETWORKDAYS(A2, B2, HolidayList)
-- NETWORKDAYS.INTL: custom weekend definition
-- weekend "0000011" = Sat-Sun off
=NETWORKDAYS.INTL(A2, B2, "0000011")
-- Days overdue (negative = overdue)
=NETWORKDAYS(DueDate, TODAY()) - 125. DATEDIF — Age and Tenure Calculation
Excel Formula
-- DATEDIF(start_date, end_date, unit)
-- Units: "Y"=years, "M"=months, "D"=days, "YM"=months ignoring years
-- Employee tenure in years
=DATEDIF(JoinDate, TODAY(), "Y")
-- Customer age in months
=DATEDIF(A2, TODAY(), "M")
-- Full "X years, Y months" format
=DATEDIF(A2,TODAY(),"Y")&" yrs, "&DATEDIF(A2,TODAY(),"YM")&" months"26. WEEKDAY + WEEKNUM — Day and Week Analysis
Excel Formula
-- WEEKDAY returns a number (1=Sun, 2=Mon ... 7=Sat)
=WEEKDAY(A2, 2) -- 2 = Monday-based (1=Mon, 7=Sun)
-- Is this date a weekend?
=IF(WEEKDAY(A2,2)>5, "Weekend", "Weekday")
-- Week number in the year
=WEEKNUM(A2, 2) -- 2 = week starts on Monday
-- Which quarter is this date in?
=ROUNDUP(MONTH(A2)/3, 0)Category 6 — Dynamic Array Formulas (27–30)
These formulas were introduced in Excel 365 and represent the biggest upgrade to Excel in 20 years. They return arrays that automatically spill into multiple cells — no more Ctrl+Shift+Enter required.
Requires Microsoft 365 or Excel 2021+
If you see a #SPILL! error, clear all cells in the spill range — another formula or value is blocking it. Dynamic arrays cannot spill into merged cells or cells that already contain data.
27. FILTER — Extract Rows That Meet Conditions
FILTER extracts matching rows from a table — like SQL WHERE clause, directly in a cell.
Excel Formula
-- All rows where Region = "Delhi"
=FILTER(A2:D100, B2:B100="Delhi", "No results")
-- Multiple conditions (AND)
=FILTER(A2:D100, (B2:B100="Delhi") * (C2:C100>10000))
-- Multiple conditions (OR)
=FILTER(A2:D100, (B2:B100="Delhi") + (B2:B100="Mumbai"))
-- Filter and return only specific columns
=FILTER(CHOOSE({1,2,3}, A2:A100, C2:C100, D2:D100), B2:B100="Active")28. SORT + SORTBY — Sort Data Dynamically
Excel Formula
-- SORT(array, sort_index, sort_order, by_col)
-- sort_order: 1 = ascending, -1 = descending
-- Sort by column 2 (revenue) descending
=SORT(A2:D100, 2, -1)
-- SORTBY: sort by a different array
=SORTBY(A2:D100, C2:C100, -1) -- sort table by revenue (col C)
-- Sort + Filter combined: Top 10 by revenue
=SORT(FILTER(A2:D100, B2:B100="Delhi"), 3, -1)29. UNIQUE — Extract Distinct Values Automatically
Excel Formula
-- Extract unique values from a column
=UNIQUE(A2:A100)
-- Unique across multiple columns
=UNIQUE(A2:B100)
-- Values that appear exactly once (not duplicated)
=UNIQUE(A2:A100, FALSE, TRUE)
-- Dynamic dropdown source list
-- Use =UNIQUE(A:A) as the named range for Data Validation
-- Count unique customers
=COUNTA(UNIQUE(CustomerID_Range))30. SEQUENCE — Generate Number Series Automatically
Excel Formula
-- SEQUENCE(rows, [cols], [start], [step])
-- Generate 1 to 10
=SEQUENCE(10)
-- 3x4 matrix of numbers (1 to 12)
=SEQUENCE(3, 4)
-- Months of the year as dates
=EDATE(DATE(2024,1,1), SEQUENCE(12,1,0))
-- All working days in 2025
=WORKDAY(DATE(2024,12,31), SEQUENCE(261))
-- Row numbers for a dynamic range
=SEQUENCE(COUNTA(A:A)-1, 1, 1)Quick Reference — All 30 Formulas
| # | Formula | Category | Primary Use |
|---|---|---|---|
| 1 | XLOOKUP | Lookup | Modern, flexible lookup in any direction |
| 2 | VLOOKUP | Lookup | Classic right-direction lookup |
| 3 | INDEX + MATCH | Lookup | Two-dimensional, left-direction lookup |
| 4 | INDIRECT | Lookup | Dynamic range references from cell values |
| 5 | OFFSET | Lookup | Moving range references |
| 6 | MATCH | Lookup | Position of a value in a range |
| 7 | CHOOSE | Lookup | Select from a list by index number |
| 8 | HYPERLINK | Lookup | Clickable links inside Excel |
| 9 | SUMIFS | Aggregation | Conditional sum with multiple criteria |
| 10 | COUNTIFS | Aggregation | Conditional count with multiple criteria |
| 11 | AVERAGEIFS | Aggregation | Conditional average |
| 12 | SUMPRODUCT | Aggregation | Weighted sums, multi-criteria analysis |
| 13 | AGGREGATE | Aggregation | Sum/Max/Min ignoring errors & hidden rows |
| 14 | TEXTJOIN | Text | Combine text with a delimiter |
| 15 | TEXT | Text | Format numbers and dates as readable text |
| 16 | TRIM + CLEAN | Text | Remove extra spaces and invisible characters |
| 17 | LEFT / RIGHT / MID | Text | Extract substrings |
| 18 | SUBSTITUTE | Text | Replace text within a formula |
| 19 | IFS | Logical | Multiple conditions without nested IF |
| 20 | SWITCH | Logical | Match a value against a list of options |
| 21 | IFERROR + IFNA | Logical | Handle errors gracefully |
| 22 | AND / OR / NOT | Logical | Build compound logical conditions |
| 23 | EDATE + EOMONTH | Date | Add months, find month-end dates |
| 24 | NETWORKDAYS | Date | Count working days between dates |
| 25 | DATEDIF | Date | Age, tenure, and date differences |
| 26 | WEEKDAY + WEEKNUM | Date | Day of week and week number analysis |
| 27 | FILTER | Dynamic Array | Extract rows matching conditions |
| 28 | SORT + SORTBY | Dynamic Array | Sort data dynamically |
| 29 | UNIQUE | Dynamic Array | Extract distinct values automatically |
| 30 | SEQUENCE | Dynamic Array | Generate number and date series |
Next Level — Combine Dynamic Arrays
The real power of dynamic arrays is in combinations. =SORT(UNIQUE(A2:A100)) gives a sorted unique list. =SORT(FILTER(A2:D100, C2:C100>10000), 3, -1) gives filtered data sorted by revenue. These combinations eliminate entire helper columns that previously cluttered worksheets.