Excel Complete Formula List — Data Analytics

XLOOKUP, SUMIFS, FILTER, UNIQUE, TEXTJOIN se lekar Pivot Tables aur dynamic arrays tak — har formula ka syntax, example aur expected output. Data cleaning, date formulas, outlier detection aur supply chain status formulas ke saath. Interview + job ready cheat sheet.

📑 Is Article Mein Kya Hai:

• 🎯 PART 1 — Lookup Formulas (Interview ka #1 topic)
• 🧮 PART 2 — Conditional Sum / Count (SUMIFS family)
• 🔀 PART 3 — Logical Formulas
• 🔤 PART 4 — Text Formulas (Data Cleaning)
• 📅 PART 5 — Date Formulas
• 📈 PART 6 — Math & Statistics
• 🌀 PART 7 — Dynamic Arrays (Microsoft 365) ⭐
• 📊 PART 8 — Pivot Table (Excel ka superpower)
• 🎨 PART 9 — Conditional Formatting & Data Validation
• ⚡ PART 10 — Power Query (Get & Transform)
• 🚚 PART 11 — Supply Chain & Business Formulas
• ⌨️ PART 12 — Keyboard Shortcuts (interview me poochte hain)
• 🧹 PART 13 — Data Cleaning Workflow (Excel)
• 🚫 PART 14 — Common Excel Mistakes
• 🎤 PART 15 — Interview One-Liners (Excel)
• ✅ Revision Priority

1.1 XLOOKUP ⭐ (modern best — Microsoft 365)

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

-- Basic (exact match, default)
=XLOOKUP(F2, B2:B100, C2:C100)

-- "Not found" handle karo (IFERROR ki zaroorat nahi) ⭐
=XLOOKUP(F2, B2:B100, C2:C100, "Not Found")

-- Multiple columns return (dynamic array spill) #
=XLOOKUP(F2, B2:B100, C2:E100)

-- Left lookup (VLOOKUP ye nahi kar sakta) ⭐
=XLOOKUP(F2, D2:D100, B2:B100)

-- Approximate (next smaller) — tax slab / discount tier ke liye
=XLOOKUP(F2, A2:A10, B2:B10, ,-1)

-- Reverse search (last se shuru)
=XLOOKUP(F2, B2:B100, C2:C100, ,-1)

-- Wildcard match
=XLOOKUP("Jatin*", B2:B100, C2:C100, ,2)

match_mode values (doc-verified):

search_mode values:

1.2 VLOOKUP (purana par interview me poochte hain)

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

=VLOOKUP(F2, B2:E100, 3, FALSE)     -- FALSE = exact match ⭐
=VLOOKUP(F2, B2:E100, 3, 0)         -- 0 = FALSE, same cheez
=VLOOKUP(F2, B2:E100, 3, TRUE)      -- TRUE = approximate (data SORTED hona chahiye)

XLOOKUP vs VLOOKUP — interview answer ⭐

1.3 INDEX + MATCH (XLOOKUP se pehle ka standard) ⭐

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

=INDEX(C2:C100, MATCH(F2, B2:B100, 0))

-- 2D lookup (row + column dono se) ⭐
=INDEX(B2:E100, MATCH(G2, A2:A100, 0), MATCH(F2, B1:E1, 0))

-- Two-way lookup with XLOOKUP #
=XLOOKUP(G2, A2:A100, XLOOKUP(F2, B1:E1, B2:E100))

MATCH ka teesra argument:

• 0 = exact match ⭐ (hamesha yehi use karo)
• 1 = largest value ≤ lookup (ascending sorted)
• -1 = smallest value ≥ lookup (descending sorted)

2.1 SUMIFS ⭐ (sabse zyada use hone wala)

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

-- Single condition
=SUMIFS(C2:C100, A2:A100, "Electronics")

-- Multiple conditions (AND)
=SUMIFS(D2:D100, A2:A100, "Electronics", B2:B100, "W1")

-- Date range ⭐
=SUMIFS(D2:D100, C2:C100, ">="&DATE(2024,1,1), C2:C100, "<="&DATE(2024,12,31))

-- Greater than a cell value
=SUMIFS(D2:D100, D2:D100, ">"&F2)

-- Wildcard (contains) ⭐
=SUMIFS(D2:D100, A2:A100, "*Phone*")

-- Not equal
=SUMIFS(D2:D100, B2:B100, "<>W1")

-- Blank / not blank
=SUMIFS(D2:D100, E2:E100, "")
=SUMIFS(D2:D100, E2:E100, "<>")

2.2 COUNTIFS ⭐

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=COUNTIFS(A2:A100, "Delayed")
=COUNTIFS(A2:A100, "Delayed", B2:B100, "W1")
=COUNTIFS(C2:C100, ">=2024-01-01", C2:C100, "<=2024-12-31")
=COUNTIFS(D2:D100, ">10000", D2:D100, "<20000")
=COUNTIFS(A2:A100, "*Electronics*")

2.3 AVERAGEIFS / MAXIFS / MINIFS

=AVERAGEIFS(D2:D100, A2:A100, "Electronics", B2:B100, "W1")
=MAXIFS(D2:D100, A2:A100, "Electronics")
=MINIFS(D2:D100, A2:A100, "Electronics")

2.4 Wildcard Cheatsheet ⭐

-- IF ⭐
=IF(B2>100, "High", "Low")

-- Nested IF
=IF(B2>20000, "A", IF(B2>12000, "B", "C"))

-- IFS (cleaner than nested) ⭐
=IFS(B2>20000, "A", B2>12000, "B", TRUE, "C")

-- AND / OR / NOT
=IF(AND(B2>100, C2="W1"), "Yes", "No")
=IF(OR(B2="Delayed", C2="Cancelled"), "Flag", "OK")
=IF(NOT(B2=0), "Non-zero", "Zero")

-- IFERROR / IFNA ⭐
=IFERROR(VLOOKUP(F2,B2:E100,3,FALSE), "Not Found")
=IFNA(A2/B2, 0)                        -- sirf #N/A handle karta hai

-- Switch
=SWITCH(B2, "W1","North", "W2","South", "W3","West", "Unknown")

🏆 Supply Chain Status Formula (interview favorite)

=IF(D2<=E2, "CRITICAL - Reorder Now",
  IF(D2<=E2*1.5, "WATCH", "SAFE"))

Verified math: stock=45, daily_avg=9, lead_time=10 → DOS = 45/9 = 5 days ≤ 10 → CRITICAL

-- Trim / Clean ⭐
=TRIM(A2)                    -- extra spaces hatata hai
=CLEAN(A2)                   -- non-printable characters
=TRIM(CLEAN(A2))             -- dono ek saath ⭐

-- Case
=UPPER(A2)  ·  =LOWER(A2)  ·  =PROPER(A2)

-- Extract ⭐
=LEFT(A2, 3)                 -- pehle 3 characters (pincode prefix)
=RIGHT(A2, 4)                -- last 4
=MID(A2, 4, 3)               -- position 4 se 3 characters

-- Find position
=FIND("@", A2)               -- case-sensitive, error deta hai
=SEARCH("@", A2)             -- case-insensitive, wildcard support ⭐
=IFERROR(FIND("@",A2), 0)

-- Length
=LEN(A2)

-- Join ⭐
=A2 & " " & B2
=CONCAT(A2, " ", B2)
=TEXTJOIN(", ", TRUE, A2:A10)         -- TRUE = blanks ignore ⭐ #

-- Replace / Substitute ⭐
=SUBSTITUTE(A2, ",", "")              -- specific text replace (sab jagah)
=REPLACE(A2, 1, 3, "IN")              -- position based

-- Split (Microsoft 365) #
=TEXTSPLIT(A2, "@")
=TEXTBEFORE(A2, "@")
=TEXTAFTER(A2, "@")

-- Number to text with format ⭐
=TEXT(15150.5678, "#,##0.00")         -- "15,150.57"
=TEXT(A2, "0.00%")                    -- "82.00%"
=TEXT(A2, "MMM-YYYY")                 -- "Jan-2024"
=TEXT(A2, "DD-MMM-YYYY")              -- "05-Jan-2024"

-- Text to number ⭐
=VALUE("1234")
=--A2                                  -- double negative trick (text → number)
=NUMBERVALUE("1,200")

-- Padding (pincode fix) ⭐
=TEXT(A2, "000000")                    -- 121 → "000121"

🧹 Real Cleaning Formula Combo

-- Email se domain nikalna
=TEXTAFTER(LOWER(TRIM(A2)), "@")

-- "1,200" → 1200 (number)
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"₹",""))

-- Full name se first name
=TEXTBEFORE(TRIM(A2), " ")

-- Duplicate flag
=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "First")

-- Extract ⭐
=YEAR(A2)  ·  =MONTH(A2)  ·  =DAY(A2)
=WEEKDAY(A2, 2)             -- 1=Monday ... 7=Sunday ⭐
=WEEKNUM(A2)  ·  =QUARTER -- (QUARTER nahi hota, yeh use karo:)
=ROUNDUP(MONTH(A2)/3, 0)    -- quarter number

-- Current ⭐
=TODAY()                     -- aaj ki date (dynamic)
=NOW()                       -- date + time

-- Build date
=DATE(2024, 1, 31)
=DATE(YEAR(A2), MONTH(A2)+1, 1)      -- next month first day

-- Difference ⭐
=A2 - B2                     -- days ka farq (simple subtraction)
=DATEDIF(A2, B2, "D")        -- days (hidden function, docs me nahi)
=DATEDIF(A2, B2, "M")        -- complete months
=DATEDIF(A2, B2, "Y")        -- complete years
=DATEDIF(A2, B2, "YM")       -- months after years

-- Add / Subtract ⭐
=EDATE(A2, 1)                -- +1 month (same day)
=EDATE(A2, -1)               -- -1 month
=EOMONTH(A2, 0)              -- us month ka last day ⭐
=EOMONTH(A2, -1)             -- pichle month ka last day
=A2 + 7                      -- +7 days

-- Working days ⭐
=NETWORKDAYS(A2, B2)                        -- Mon-Fri count
=NETWORKDAYS(A2, B2, Holidays)              -- holidays exclude
=NETWORKDAYS.INTL(A2, B2, "0000011")        -- custom weekend (Sun only)
=WORKDAY(A2, 10)                            -- 10 working days baad ki date
=WORKDAY.INTL(A2, 10, "0000011", Holidays)

Verified: NETWORKDAYS(2024-01-01, 2024-01-31) = 23 working days

Month-wise grouping (pivot ke bina)

=TEXT(A2, "YYYY-MM")          -- "2024-01"  ⭐ sabse aasan
=EOMONTH(A2, 0)               -- month-end date (proper date type)
=DATE(YEAR(A2), MONTH(A2), 1) -- month-start date

🏆 Delivery TAT & SLA Breach

-- TAT in days
=C2 - B2

-- SLA breach flag
=IF(C2 > B2, "Breached", "Met")

-- SLA breach % (SUMIFS se)
=COUNTIFS(D2:D100, "Breached") / COUNTA(D2:D100)

-- Business days TAT (weekends exclude)
=NETWORKDAYS(B2, C2) - 1

-- Basic ⭐
=SUM(A2:A100)  ·  =AVERAGE(A2:A100)  ·  =COUNT(A2:A100)     -- numbers only
=COUNTA(A2:A100)              -- non-blank cells ⭐
=COUNTBLANK(A2:A100)
=MAX(A2:A100)  ·  =MIN(A2:A100)  ·  =PRODUCT(A2:A100)

-- Rounding ⭐
=ROUND(A2, 2)      -- 2 decimals
=ROUNDUP(A2, 0)    -- always up
=ROUNDDOWN(A2, 0)  -- always down
=INT(A2)           -- integer part
=MROUND(A2, 50)    -- nearest 50
=CEILING(A2, 10)   -- up to nearest 10
=FLOOR(A2, 10)

-- Math
=ABS(A2)  ·  =SQRT(A2)  ·  =POWER(A2, 2)  ·  =MOD(A2, 2)  ·  =EXP(A2)  ·  =LN(A2)

-- Statistics ⭐
=MEDIAN(A2:A100)
=MODE.SNGL(A2:A100)
=STDEV.P(A2:A100)             -- population
=STDEV.S(A2:A100)             -- sample ⭐ (analytics me yehi)
=VAR.S(A2:A100)
=QUARTILE.INC(A2:A100, 1)     -- Q1
=QUARTILE.INC(A2:A100, 3)     -- Q3
=PERCENTILE.INC(A2:A100, 0.75)
=LARGE(A2:A100, 2)            -- 2nd largest ⭐
=SMALL(A2:A100, 2)            -- 2nd smallest
=RANK.EQ(A2, A2:A100, 0)      -- rank (0 = descending)

🚨 Outlier Detection Formula (IQR) ⭐

-- Q1, Q3, IQR cells me rakho (maan lo H1=Q1, H2=Q3)
H1:  =QUARTILE.INC(A2:A100, 1)
H2:  =QUARTILE.INC(A2:A100, 3)
H3:  =H2 - H1                                     -- IQR
H4:  =H1 - 1.5*H3                                 -- lower bound
H5:  =H2 + 1.5*H3                                 -- upper bound

-- Outlier flag
=IF(OR(A2<$H$4, A2>$H$5), "OUTLIER", "OK")

-- Outlier count
=COUNTIFS(A2:A100, "<"&$H$4) + COUNTIFS(A2:A100, ">"&$H$5)

Correlation

=CORREL(A2:A100, B2:B100)             -- Pearson (-1 to +1)
=RSQ(A2:A100, B2:B100)                -- R-squared
=SLOPE(B2:B100, A2:A100)              -- regression slope
=INTERCEPT(B2:B100, A2:A100)
=FORECAST.LINEAR(50, B2:B100, A2:A100)  -- prediction

-- UNIQUE ⭐
=UNIQUE(A2:A100)                       -- distinct values
=UNIQUE(A2:C100)                       -- distinct rows
=COUNTA(UNIQUE(A2:A100))               -- distinct count ⭐

-- SORT ⭐
=SORT(A2:C100)                         -- first column se ascending
=SORT(A2:C100, 3, -1)                  -- 3rd column, descending ⭐

-- SORTBY (dusre column ke basis pe)
=SORTBY(A2:A100, B2:B100, -1)

-- FILTER ⭐ (sabse powerful)
=FILTER(A2:E100, B2:B100="Delayed")
=FILTER(A2:E100, (B2:B100="Delayed") * (C2:C100="W1"))     -- AND (*)
=FILTER(A2:E100, (B2:B100="Delayed") + (B2:B100="Cancelled"))  -- OR (+)
=FILTER(A2:E100, D2:D100>10000, "No records")

-- SEQUENCE
=SEQUENCE(12)                          -- 1 to 12
=SEQUENCE(12, 1, DATE(2024,1,1), 31)   -- 12 months

-- TAKE / DROP #
=TAKE(A2:C100, 5)                      -- top 5 rows
=DROP(A2:C100, 5)                      -- skip 5 rows
=CHOOSECOLS(A2:E100, 1, 3)             -- sirf column 1 aur 3

-- TRANSPOSE
=TRANSPOSE(A2:E10)

-- VSTACK / HSTACK #
=VSTACK(A2:C10, A15:C25)               -- stack vertically

-- LET (variables — performance + readability) ⭐
=LET(
    sales, D2:D100,
    wh,    B2:B100,
    total, SUMIFS(sales, wh, "W1"),
    total / SUM(sales)
)

🏆 Top N per Category (Power Query ke bina)

=SORT(FILTER(A2:E100, C2:C100=H1), 4, -1)      -- H1 = selected category

Steps

• Data select karo → Insert → PivotTable
• Rows me dimension daalo (Category, Warehouse)
• Values me measure (Sales) — default SUM hota hai
• Value Field Settings se change karo:
• Summarize by: Sum / Count / Average / Max / Min / Distinct Count
• Show Values As: % of Grand Total / % of Column Total / Running Total / Difference From

Must-know Pivot Features ⭐

Calculated Field examples

Delayed Rate  = Delayed / Total_Orders
Profit        = Sales - Cost
AOV           = Sales / Orders

🚨 Pivot Table ki 5 galtiyan

• Data me blank rows/columns — pivot range tut jata hai
• Header row duplicate names — error
• Data Excel Table me convert nahi kiya — naye rows auto-add nahi honge ⭐
• Refresh karna bhool gaye
• Date field ko group kiye bina daily level pe analysis

Conditional Formatting

Home → Conditional Formatting

Formula-based rules (sabse powerful) ⭐

-- Poori row highlight jab status "Delayed" ho
=$D2="Delayed"

-- Duplicate rows highlight
=COUNTIF($A$2:$A$100, $A2)>1

-- Stock critical highlight
=$E2<=$F2

-- Weekend dates highlight
=WEEKDAY($A2,2)>5

Data Validation (dropdown)

Data → Data Validation → List → Source: =Category_List

-- Dynamic dropdown (Table se)
=INDIRECT("tblCategories[Category]")

-- Date restriction
Data Validation → Date → between → =TODAY() and =TODAY()+30

-- Custom formula (duplicate entry block) ⭐
=COUNTIF($A$2:$A$100, $A2)=1

Data → Get Data — yeh Excel ka sabse underrated tool hai.

Common Power Query Steps

M Language basics

= Table.SelectRows(Source, each [Status] = "Delayed")     -- WHERE
= Table.Group(Source, {"Warehouse"}, {{"Total", each List.Sum([Sales]), type number}})  -- GROUP BY
= Table.Distinct(Source, {"OrderID"})                     -- DISTINCT
= Table.Sort(Source, {{"Sales", Order.Descending}})       -- ORDER BY
= if [Sales] > 10000 then "High" else "Low"               -- IF

11.1 Inventory ⭐

-- Days of Stock (DOS)
=B2/C2                                  -- current_stock / daily_avg_sales

-- Reorder Point (ROP)
=C2*D2                                  -- daily_avg_sales × lead_time

-- ROP with Safety Stock
=C2*D2 + E2

-- Safety Stock = Z × σ(demand) × √lead_time ⭐
=1.65 * STDEV.S(F2:F100) * SQRT(D2)     -- 95% service level

-- Reorder Status ⭐
=IF(B2/C2 <= D2, "CRITICAL - Reorder Now", "SAFE")

-- EOQ = √(2DS/H) ⭐
=SQRT(2*B2*C2/D2)                       -- D=annual demand, S=order cost, H=holding cost

-- Orders per year
=B2/E2                                  -- annual demand / EOQ

-- Inventory Turnover ⭐
=COGS / Average_Inventory

-- Days of Inventory (DOH)
=365 / Inventory_Turnover

Verified math:

Days of Stock  = 45/9   = 5.00 days   -> CRITICAL (<= lead time 10)
Reorder Point  = 9*10   = 90 units
Safety Stock   = 1.65 * 12.5 * √7     = 54.57 units
ROP + SS       = 90 + 54.57           = 144.57 units
EOQ            = √(2*12000*500/60)    = 447.21 units
Orders/year    = 12000/447.21         = 26.8 orders
Turnover       = 1200000/150000       = 8.00x  |  DOH = 45.62 days

11.2 Delivery Metrics ⭐

-- On-Time Delivery %
=COUNTIF(D2:D100,"On-Time") / COUNTA(D2:D100)

-- OTIF (On-Time AND In-Full)
=COUNTIFS(D2:D100,"On-Time", E2:E100,"Complete") / COUNTA(D2:D100)

-- Fill Rate
=SUM(Units_Delivered) / SUM(Units_Ordered)

-- SLA Breach %
=COUNTIFS(Actual_Date, ">"&Promised_Date) / COUNTA(Order_IDs)

-- Average TAT
=AVERAGE(C2:C100 - B2:B100)             -- array formula (Ctrl+Shift+Enter in old Excel)
=AVERAGE(D2:D100)                       -- agar TAT column already ho

-- Cost per Delivery
=Total_Logistics_Cost / Total_Shipments

Verified: OTD 82/100 = 0.8200 → format 82.00% · Fill Rate 9700/10000 = 97.00%

11.3 Return / RTO ⭐

-- Return rate
=COUNTIF(Status,"Returned") / COUNTA(Status)

-- COD vs Prepaid return rate
=COUNTIFS(Payment,"COD", Status,"Returned") / COUNTIF(Payment,"COD")

-- RTO cost impact ⭐
=Return_Count * Cost_Per_RTO

-- Savings calculation
=(Old_RTO% - New_RTO%) * Total_Orders * Cost_Per_RTO

Verified: 1300 RTO × ₹90 = ₹1,17,000 loss. RTO 21%→12% on 10,000 orders = 900 × ₹90 = ₹81,000 saved 🎯

11.4 Growth & Pareto

-- MoM Growth %
=(B2 - B1) / B1

-- CAGR ⭐
=(End_Value/Start_Value)^(1/Years) - 1

-- Pareto cumulative % ⭐
D2:  =SUM($C$2:C2) / SUM($C$2:$C$100)      -- running cumulative
E2:  =IF(D2<=0.8, "A", IF(D2<=0.95, "B", "C"))

Verified: CAGR (100000→161051, 5 yr) = 10.00% Pareto verified: Fashion 45.31% (A) · Electronics 79.42% (A) · Grocery 95.39% (C) · Accessories 100% (C)

Step-by-step

1. Ctrl+T  → Table banao
2. Home → Find & Select → Go To Special → Blanks  → nulls dekho
3. Data → Remove Duplicates
4. Data → Text to Columns  → split karo
5. TRIM / CLEAN / PROPER  → text normalize
6. VALUE / DATEVALUE  → type convert
7. Conditional Formatting → Duplicates highlight
8. Data Validation  → future entries control
9. Power Query  → automate karo ⭐

Quick checks

-- Data quality dashboard formulas ⭐
Total rows      =ROWS(A2:A1000)
Blank cells     =COUNTBLANK(A2:A1000)
Null %          =COUNTBLANK(A2:A1000)/ROWS(A2:A1000)
Duplicates      =SUMPRODUCT(--(COUNTIF(A2:A1000,A2:A1000)>1))
Distinct values =COUNTA(UNIQUE(A2:A1000))
Outliers        =COUNTIFS(A2:A1000,"<"&Q1-1.5*IQR) + COUNTIFS(A2:A1000,">"&Q3+1.5*IQR)
Text in numbers =SUMPRODUCT(--ISTEXT(B2:B1000))

Tier 1 — MUST (yeh 20 aana hi chahiye): XLOOKUP · VLOOKUP(...,FALSE) · INDEX+MATCH · SUMIFS · COUNTIFS · IF/IFS · IFERROR · TRIM · TEXT · LEFT/RIGHT/MID · TODAY/EDATE/EOMONTH · NETWORKDAYS · SUM/AVERAGE/COUNT/COUNTA · Pivot Table · Ctrl+T · F4 · Remove Duplicates

Tier 2 — Strong impression: SUMPRODUCT · TEXTJOIN · FILTER/UNIQUE/SORT · LET · QUARTILE + outlier flag · Conditional formatting with formula · Data Validation · GETPIVOTDATA · CORREL · SLOPE

Tier 3 — Differentiator: Power Query + M basics · Power Pivot / Data Model · SEQUENCE/CHOOSECOLS/VSTACK · Dynamic named ranges · Volatile function awareness · Excel→SQL migration story

Formula syntax Microsoft Support docs se verify kiya gaya. Business math Python me run karke check kiya gaya (_verify/excel_logic_check.py).

Excel Formula Cheat Sheet — Complete!

Lookup, conditional sum/count, logical, text, date, math & statistics, dynamic arrays aur Pivot Tables — sab cover ho gaya. Formula syntax Microsoft Support docs se verify ki gayi hai, aur peeche ka math Python me run karke check kiya gaya hai.

Happy Learning & Keep Exploring! 🚀