Excel Formula List: The Top 30 Functions Every Analyst Uses (with Examples)

While Microsoft Excel features nearly 500 built-in calculation functions, professional data analysts and financial modelers do not memorize them all. True spreadsheet fluency is about mastering the essential 30 formulas that form the foundation of corporate reporting, KPI tracking, and commercial data wrangling.

Whether you are preparing for a technical modeling screen, building a recurring executive dashboard, or troubleshooting corrupted spreadsheets, having a structured excel formula list organized by analytical function accelerates your modeling speed and eliminates guesswork.

In this master reference guide, you will find clean syntax templates, practical examples, and troubleshooting rules for the top 30 functions.

To explore shortcuts for entering these formulas, see our guide on Excel formulas shortcut keys and review basic Excel formulas.

Complete Excel Formula List: 30 Functions by Category

We have categorized the top 30 formulas into six functional domains:

Category 1: Lookup and Reference Formulas

Used to search, match, and join data across disparate tables.

1. XLOOKUP (Modern Relational Lookup)

The modern standard for joining columns in any direction:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
=XLOOKUP(A2, $G$2:$G$100, $H$2:$H$100, "Not Found")

See our dedicated XLOOKUP formula tutorial.

2. VLOOKUP (Legacy Vertical Lookup)

Universal cross-table lookup searching the leftmost column:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(A2, $E$2:$G$100, 3, FALSE)

Read our complete VLOOKUP formula guide.

3. INDEX and MATCH (Two-Way Matrix Lookup)

Combines coordinate row and column searches:

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

Explore our INDEX MATCH tutorial.

4. FILTER (Dynamic Array Filtering)

Extracts records matching criteria without modifying source data:

=FILTER(A2:D50, D2:D50="Completed", "No Results")

See our FILTER formula in Excel guide.

5. UNIQUE (Dynamic Deduplication)

Spills distinct values from a range into a clean new array:

=UNIQUE(A2:A100)

Learn how to remove duplicates in Excel.

6. TRANSPOSE (Matrix Rotation)

Rotates horizontal tables into vertical rows and vice versa:

=TRANSPOSE(A1:E2)

See our complete tutorial on transposing in Excel.

Category 2: Math and Aggregation Formulas

Used for summary metrics, financial balances, and descriptive totals.

7. SUM (Basic Totaling)

Adds numbers or contiguous cell ranges:

=SUM(B2:B50)
Shortcut: Alt + =

Explore the SUM formula in Excel guide.

8. SUMIFS (Multi-Criteria Summation)

Sums values meeting multiple conditional criteria:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2])
=SUMIFS(D2:D100, B2:B100, "Enterprise", C2:C100, ">=2026-01-01")

9. SUBTOTAL (Filter-Aware Math)

Calculates sums or averages while ignoring filtered-out or hidden rows:

=SUBTOTAL(109, B2:B100)  -- 109 = SUM ignoring manually hidden rows

Read our SUBTOTAL formula in Excel guide.

10. ROUND (Decimal Precision)

Rounds numbers to a specified decimal precision:

=ROUND(A2, 2)    -- Rounds 12.3456 to 12.35

11. CAGR (Compound Annual Growth Rate)

Calculates annualized investment or revenue compounding:

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

Read our CAGR formula in Excel guide.

Category 3: Logical and Conditional Formulas

Used for branching decisions, segmentation, and error guards.

12. IF (Basic Conditional Branching)

=IF(logical_test, value_if_true, [value_if_false])
=IF(C2 >= 100000, "Target Met", "Under Quota")

13. IFS (Multi-Condition Branching)

Evaluates multiple conditions without messy nested parentheses:

=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F")

14. AND / OR (Logical Connectors)

Combines multiple boolean checks:

=IF(AND(A2>50, B2="Active"), "Qualified", "Disqualified")

15. IFERROR (Defensive Error Catching)

Replaces ugly #N/A or #DIV/0! errors with friendly fallbacks:

=IFERROR(VLOOKUP(A2, E:F, 2, FALSE), "Not Found")

Category 4: Text Manipulation Formulas

Used to sanitize dirty database dumps, split strings, and concatenate keys.

16. TRIM (Stray Space Removal)

Eliminates invisible leading, trailing, and repeated inter-word spaces:

=TRIM(A2)

17. TEXTJOIN (Delimited String Combination)

Combines ranges with custom delimiters while ignoring blanks:

=TEXTJOIN(", ", TRUE, A2:D2)

See how to combine two columns in Excel.

18. TEXT (Number and Date Formatting)

Converts numeric values to formatted text strings:

=TEXT(B2, "$#,##0.00")
=TEXT(C2, "yyyy-mm-dd")

See our Excel TEXT function guide.

19. LEFT, RIGHT, MID (Sub-String Extraction)

Extracts characters from specific positions inside a string:

=LEFT(A2, 3)        -- First 3 characters
=RIGHT(A2, 4)       -- Last 4 characters
=MID(A2, 5, 3)      -- 3 characters starting at position 5

20. LEN (Character Length Measurement)

=LEN(A2)            -- Counts character count

21. PROPER (Title Casing)

=PROPER("priya sharma")  -- Returns "Priya Sharma"

Category 5: Date and Time Formulas

Essential for cohort timelines, aging reports, and seasonality.

22. TODAY & NOW (Dynamic System Clock)

=TODAY()   -- Current date (volatile)
=NOW()     -- Current date and timestamp

23. EOMONTH (End of Month Calculation)

Returns the last day of the month $N$ months in the past or future:

=EOMONTH(TODAY(), 0)   -- Last day of current month
=EOMONTH(TODAY(), -1)  -- Last day of previous month

24. DATEDIF (Date Difference Calculator)

Calculates elapsed days, months, or years between dates:

=DATEDIF(start_date, end_date, "Y")   -- Full years elapsed
=DATEDIF(start_date, end_date, "M")   -- Full months elapsed

Category 6: Statistical and Ranking Formulas

Used for distribution modeling and leaderboards.

25. AVERAGE (Arithmetic Mean)

=AVERAGE(B2:B50)

26. COUNT & COUNTA (Data Volume Verification)

=COUNT(A2:A100)    -- Counts cells with numbers only
=COUNTA(A2:A100)   -- Counts all non-empty cells

27. COUNTIFS (Conditional Row Counting)

=COUNTIFS(B2:B100, "Completed", C2:C100, ">5000")

28. RANK.EQ (Leaderboard Ranking)

Ranks a value within a range:

=RANK.EQ(B2, $B$2:$B$100, 0)

Read our complete RANK formula in Excel guide.

29. MIN & MAX (Distribution Extremes)

=MIN(B2:B100)
=MAX(B2:B100)

30. PERCENTAGE (Variance & Growth Calculation)

=(New_Value - Old_Value) / Old_Value

See our dedicated percentage formula in Excel guide.

Excel Formula List: Master Matrix

Excel Formula List: Modern Dynamic Array and Lambda Functions (Excel 365)

Modern spreadsheet engineering has transformed Excel from a cell-by-cell calculator into a fully vectorized functional programming engine. Explore our comprehensive Excel Tutorials hub to master the full modern suite.

The Modern Dynamic Array Suite

Writing Reusable Custom Functions with LAMBDA

Before Microsoft 365, writing custom spreadsheet functions required legacy VBA macros (.xlsm files) that frequently triggered corporate macro security warnings. With =LAMBDA(), you can build clean, reusable functional formulas natively:

=LAMBDA(revenue, cost, (revenue - cost) / revenue)

1. Navigate to Formulas > Name Manager > New.
2. Set Name to GROSS_MARGIN.
3. In the "Refers to" field, paste =LAMBDA(rev, cost, IF(rev=0, 0, (rev - cost) / rev)).
4. In any worksheet cell, you can now call your custom function just like built-in Excel formulas:

   excel

   Copy code

   =GROSS_MARGIN(C2, D2)

This modular approach centralizes core financial logic, preventing duplicate formula errors across massive team models.

Common Spreadsheet Errors and What They Mean

When Analysts Rely on this Excel Formula List

Interview Preparation. Quick review before live 45-minute technical modeling assessments for financial analyst and business intelligence positions.

ETL Pipeline Cleaning. Sanitizing incoming customer CSV files using TRIM, TEXTJOIN, and UNIQUE prior to loading into cloud databases.

Financial Reporting Automation. Building dynamic P&L scorecards using SUMIFS, EOMONTH, and XLOOKUP that update automatically each fiscal period.

To streamline formula execution, learn the 15 must-know Excel formulas shortcut keys and explore our guide on how to freeze rows in Excel.

Related Excel Tutorials

• Excel Formulas: The Complete Guide
• XLOOKUP Formula in Excel
• SUM Formula in Excel
• Excel Pivot Tables Guide
• Explore All Guides in the Excel Tutorials Hub