500 MOST-USED DATA ANALYST FORMULAS CHEAT SHEET

CATEGORY 1 — BASIC MATH & CALCULATIONS (1–50)

  1. SUM()
  2. AVERAGE()
  3. MIN()
  4. MAX()
  5. COUNT()
  6. COUNTA()
  7. COUNTBLANK()
  8. PRODUCT()
  9. ABS()
  10. ROUND()
  11. ROUNDUP()
  12. ROUNDDOWN()
  13. INT()
  14. MOD()
  15. POWER()
  16. SQRT()
  17. EXP()
  18. LN()
  19. LOG()
  20. LOG10()
  21. CEILING()
  22. FLOOR()
  23. EVEN()
  24. ODD()
  25. RAND()
  26. RANDBETWEEN()
  27. SIGN()
  28. SUBTOTAL()
  29. AGGREGATE()
  30. SUMPRODUCT()
  31. % Calculation
  32. Growth Rate Formula
  33. CAGR Formula
  34. Running Total
  35. Difference Calculation
  36. Ratio Formula
  37. Margin %
  38. Weighted Average
  39. Moving Average
  40. Variance
  41. Standard Error
  42. Z-Score
  43. Mean Absolute Deviation
  44. Geometric Mean
  45. Harmonic Mean
  46. Square Difference
  47. Relative Change
  48. Percentage Point Change
  49. Index Value
  50. Profit Calculation

CATEGORY 2 — LOGICAL FUNCTIONS (51–100)

  1. IF()
  2. IFS()
  3. AND()
  4. OR()
  5. NOT()
  6. IFERROR()
  7. IFNA()
  8. Nested IF
  9. Multi-condition IF
  10. Boolean TRUE/FALSE
  11. Conditional Labels
  12. Score Banding
  13. Pass/Fail Logic
  14. KPI Classification
  15. Risk Flagging
  16. Status Automation
  17. Outlier Detection
  18. Threshold Alert
  19. Revenue Tier Logic
  20. Bonus Eligibility Logic
  21. Region Mapping
  22. Sales Flag
  23. Performance Grading
  24. Exception Detection
  25. Compliance Check
  26. Missing Value Logic
  27. Zero Handling Logic
  28. Error Replacement
  29. Negative Value Check
  30. Duplicate Warning
  31. SLA Breach Flag
  32. Category Assignment
  33. Decision Tree Formula
  34. Forecast Condition Logic
  35. Dynamic Comment Logic
  36. Approval Status
  37. Escalation Rule
  38. Priority Ranking
  39. Automation Flag
  40. Late Delivery Flag
  41. Inventory Alert
  42. Age Bucket Logic
  43. Customer Segmentation Logic
  44. Gender Classification
  45. Income Classification
  46. Target Achievement Logic
  47. Refund Eligibility
  48. Active/Inactive Check
  49. Business Rule Formula
  50. Dynamic Message Output

CATEGORY 3 — TEXT FUNCTIONS (101–150)

  1. LEFT()
  2. RIGHT()
  3. MID()
  4. LEN()
  5. TRIM()
  6. CLEAN()
  7. UPPER()
  8. LOWER()
  9. PROPER()
  10. CONCAT()
  11. CONCATENATE()
  12. TEXTJOIN()
  13. REPLACE()
  14. SUBSTITUTE()
  15. FIND()
  16. SEARCH()
  17. EXACT()
  18. VALUE()
  19. TEXT()
  20. CHAR()
  21. CODE()
  22. Extract First Name
  23. Extract Last Name
  24. Email Username Extract
  25. Domain Extract
  26. Remove Special Characters
  27. Add Prefix
  28. Add Suffix
  29. Phone Format
  30. ID Standardization
  31. Capitalization Fix
  32. Text Length Check
  33. Keyword Detection
  34. Word Count
  35. Remove Numbers
  36. Remove Letters
  37. Split Full Address
  38. Create Labels
  39. Dynamic Titles
  40. Clean Imported Data
  41. Replace NULL Text
  42. Sentence Case Formula
  43. Initial Extraction
  44. SKU Parsing
  45. File Name Extraction
  46. Remove Extra Spaces
  47. Format Codes
  48. Combine Columns
  49. Comment Generator
  50. Auto Description Builder

CATEGORY 4 — DATE & TIME FUNCTIONS (151–200)

  1. TODAY()
  2. NOW()
  3. DATE()
  4. YEAR()
  5. MONTH()
  6. DAY()
  7. HOUR()
  8. MINUTE()
  9. SECOND()
  10. DATEDIF()
  11. EDATE()
  12. EOMONTH()
  13. NETWORKDAYS()
  14. WORKDAY()
  15. WEEKDAY()
  16. WEEKNUM()
  17. ISOWEEKNUM()
  18. TEXT(date)
  19. Age Calculation
  20. Tenure Calculation
  21. Days Remaining
  22. Fiscal Year Logic
  23. Quarter Extraction
  24. Month Name
  25. Week Name
  26. Shift Timing Logic
  27. Delivery Delay Days
  28. SLA Duration
  29. Rolling Month Analysis
  30. Rolling Year Analysis
  31. Cohort Month
  32. Retention Period
  33. Time Difference
  34. Timestamp Cleaning
  35. Convert Text to Date
  36. Next Working Day
  37. Business Hours Calc
  38. Shift Classification
  39. Weekend Detection
  40. Holiday Detection
  41. Subscription Renewal
  42. Invoice Due Date
  43. Aging Analysis
  44. Customer Lifetime
  45. Event Duration
  46. Booking Window
  47. Time Bucket Creation
  48. Monthly Aggregation
  49. Quarterly Comparison
  50. YoY Comparison

CATEGORY 5 — LOOKUP & RELATIONAL ANALYSIS (201–250)

  1. VLOOKUP()
  2. HLOOKUP()
  3. XLOOKUP()
  4. LOOKUP()
  5. INDEX()
  6. MATCH()
  7. INDEX+MATCH
  8. XMATCH()
  9. OFFSET()
  10. INDIRECT()
  11. Two-way Lookup
  12. Dynamic Lookup
  13. Approx Match Lookup
  14. Exact Match Lookup
  15. Reverse Lookup
  16. Left Lookup
  17. Multi-column Lookup
  18. Multi-condition Lookup
  19. Latest Record Lookup
  20. Previous Record Lookup
  21. Join Tables Logic
  22. ID Mapping
  23. Category Mapping
  24. Pricing Lookup
  25. Region Lookup
  26. Manager Lookup
  27. Department Mapping
  28. SKU Lookup
  29. Customer History Lookup
  30. Latest Date Match
  31. Inventory Check Lookup
  32. Salary Band Lookup
  33. Commission Rate Lookup
  34. Score Lookup
  35. Status Retrieval
  36. Employee Info Pull
  37. Data Merge Formula
  38. Dynamic Dashboard Lookup
  39. Hierarchical Lookup
  40. Parent Child Lookup
  41. Lookup with Errors Handling
  42. Lookup with Default Value
  43. Lookup by Rank
  44. Lookup Nth Value
  45. Lookup Closest Value
  46. Lookup Multiple Results
  47. Spill Lookup Results
  48. Lookup Using FILTER
  49. Lookup by Date Range
  50. Cross-sheet Lookup

CATEGORY 6 — CONDITIONAL AGGREGATION (251–300)

  1. COUNTIF()
  2. COUNTIFS()
  3. SUMIF()
  4. SUMIFS()
  5. AVERAGEIF()
  6. AVERAGEIFS()
  7. Conditional Median
  8. Conditional Max
  9. Conditional Min
  10. Unique Count
  11. Region-wise Sales
  12. Product-wise Sales
  13. Monthly Revenue
  14. Department Count
  15. Active Users Count
  16. Cancelled Orders Count
  17. High Value Customers
  18. Repeat Buyers
  19. Top Performer Sum
  20. Bottom Performer Analysis
  21. Gender Count
  22. State Analysis
  23. Revenue Segmentation
  24. Age Bucket Analysis
  25. KPI Achievement Count
  26. Late Delivery Count
  27. SLA Compliance %
  28. Refund Rate
  29. Conversion Rate
  30. Engagement Rate
  31. Attendance %
  32. Inventory Level Sum
  33. Cost Center Total
  34. Budget vs Actual
  35. Forecast vs Actual
  36. Exception Count
  37. Risk Category Sum
  38. Campaign Performance
  39. Channel Analysis
  40. Customer Segment Revenue
  41. Sales Target Progress
  42. Subscription Active Count
  43. Renewal Rate
  44. Churn Rate
  45. Order Frequency
  46. Discount Impact
  47. Average Ticket Size
  48. Lead Funnel Analysis
  49. Customer Acquisition Cost
  50. Lifetime Value Aggregation

CATEGORY 7 — STATISTICS & DATA SCIENCE (301–350)

  1. MEDIAN()
  2. MODE()
  3. STDEV.S()
  4. STDEV.P()
  5. VAR.S()
  6. VAR.P()
  7. PERCENTILE()
  8. QUARTILE()
  9. RANK()
  10. RANK.EQ()
  11. RANK.AVG()
  12. CORREL()
  13. COVARIANCE()
  14. SKEW()
  15. KURT()
  16. Z.TEST()
  17. T.TEST()
  18. NORM.DIST()
  19. NORM.INV()
  20. LINEST()
  21. Linear Regression
  22. Trendline Forecast
  23. Moving Average Forecast
  24. Exponential Smoothing
  25. Confidence Interval
  26. Standard Error
  27. Residual Calculation
  28. Error Rate
  29. Accuracy %
  30. Precision
  31. Recall
  32. F1 Score
  33. Outlier Detection
  34. Distribution Check
  35. Variability Analysis
  36. Correlation Matrix
  37. Regression Prediction
  38. Demand Forecast
  39. Seasonality Index
  40. Time Series Decomposition
  41. Rolling Mean
  42. Rolling Std Dev
  43. Percent Rank
  44. Relative Frequency
  45. Histogram Prep Formula
  46. Probability Calculation
  47. Sampling Randomizer
  48. Monte Carlo Base Logic
  49. Normalization Formula
  50. Standardization Formula

CATEGORY 8 — DATA CLEANING & QUALITY (351–400)

  1. ISBLANK()
  2. ISNUMBER()
  3. ISTEXT()
  4. ISERROR()
  5. ISNA()
  6. ERROR.TYPE()
  7. Missing Value Detection
  8. Duplicate Detection
  9. Invalid Email Check
  10. Phone Length Check
  11. Range Validation
  12. Negative Value Check
  13. Data Type Validation
  14. Format Correction
  15. Null Replacement
  16. Zero Replacement
  17. Blank Fill Logic
  18. Outlier Flag
  19. Range Warning
  20. Standard Code Mapping
  21. Remove Leading Zeros
  22. Fix Case Errors
  23. Format Currency
  24. Fix Dates
  25. Clean Import File
  26. Remove Hidden Characters
  27. Normalize Text
  28. Remove Duplicates Logic
  29. Consistency Check
  30. Mandatory Field Check
  31. Record Completeness Score
  32. Quality Score Formula
  33. Error Log Flag
  34. Record Status Flag
  35. Invalid Record Removal
  36. Valid Dataset Filter
  37. Unique Record Generator
  38. Dataset Comparison
  39. Change Detection
  40. Data Drift Detection
  41. Update Tracking
  42. Record Version Check
  43. Audit Trail Logic
  44. Reconciliation Check
  45. Balance Verification
  46. Source vs Target Match
  47. Control Total Check
  48. Data Freshness Check
  49. Data Integrity Rule
  50. Validation Dashboard Logic

CATEGORY 9 — DYNAMIC ARRAY & MODERN EXCEL (401–450)

  1. FILTER()
  2. SORT()
  3. SORTBY()
  4. UNIQUE()
  5. SEQUENCE()
  6. RANDARRAY()
  7. Spill Range #
  8. Dynamic Dropdown
  9. Auto Expanding Table
  10. Dynamic Dashboard Source
  11. Top N Filter
  12. Bottom N Filter
  13. Dynamic Ranking
  14. Dynamic Pivot Source
  15. Dynamic Charts
  16. Auto Category List
  17. Dependent Dropdown
  18. Multi Select List Logic
  19. Live Search Box
  20. Interactive Dashboard Filter
  21. Real-time KPI Feed
  22. Auto Update Reports
  23. Dynamic Headers
  24. Scenario Switching
  25. Forecast Selection Logic
  26. Dynamic Time Range
  27. Rolling Window Filter
  28. Interactive Reports
  29. Auto Data Expansion
  30. No Helper Column Analysis
  31. Spill Lookup
  32. Dynamic Aggregation
  33. Dynamic Join Logic
  34. Matrix Generator
  35. Simulation Generator
  36. Random Sampling Table
  37. Dataset Split Generator
  38. Auto Segmentation
  39. Cohort Generator
  40. Calendar Generator
  41. Timeline Builder
  42. Heatmap Data Prep
  43. Dynamic KPI Cards
  44. Scenario Modeling
  45. Sensitivity Analysis
  46. Dashboard Parameter Control
  47. Interactive Filters
  48. Real-time Scorecards
  49. Dynamic Metrics Selector
  50. Automated Reporting Logic

CATEGORY 10 — BUSINESS KPI & ANALYST METRICS (451–500)

  1. Revenue Growth %
  2. Profit Margin %
  3. Gross Margin
  4. Net Margin
  5. Operating Margin
  6. Conversion Rate
  7. Bounce Rate
  8. Retention Rate
  9. Churn Rate
  10. Customer Lifetime Value
  11. CAC (Customer Acquisition Cost)
  12. ROI
  13. ROAS
  14. ARPU
  15. Average Order Value
  16. Lead Conversion %
  17. Sales Target %
  18. Productivity Index
  19. Utilization Rate
  20. Employee Efficiency
  21. Inventory Turnover
  22. Stock Coverage Days
  23. Fill Rate
  24. Order Cycle Time
  25. Delivery Accuracy
  26. SLA Compliance
  27. Defect Rate
  28. Quality Score
  29. Satisfaction Score
  30. NPS Calculation
  31. Repeat Purchase Rate
  32. Funnel Drop %
  33. Marketing Attribution
  34. Campaign ROI
  35. Channel Contribution %
  36. Forecast Accuracy
  37. Budget Variance
  38. Cost per Lead
  39. Revenue per Employee
  40. Burn Rate
  41. Cash Runway
  42. Risk Exposure Score
  43. Engagement Index
  44. Adoption Rate
  45. Active User %
  46. Growth Index
  47. Performance Scorecard
  48. Business Health Index
  49. Executive Dashboard KPI
  50. Decision Support Metric

The Analyst’s Facts

Tools evolve.
Dashboards change.
Software gets rewritten every decade.

But these 500 patterns —
clean → calculate → compare → explain —
are the timeless craft of data analysis.