Introduction: Why Spreadsheet Formulas Matter More Than Ever in 2025
Spreadsheets run the modern world. Whether you’re a business analyst, accountant, project manager, researcher, student, data scientist, or entrepreneur, your daily workflow relies heavily on Excel or Google Sheets. And at the center of all spreadsheet power lies one thing: formulas.
Understanding spreadsheet formulas helps you:
- Automate repetitive tasks
- Speed up data analysis
- Increase accuracy
- Reduce manual errors
- Create dashboards & reports
- Make better business decisions
In 2025, the demand for spreadsheet mastery is higher than ever due to the rise of:
- AI-powered data analytics
- Automation workflows
- Cloud collaboration
- Advanced data visualization
This 5000-word guide gives you the Top 100 Spreadsheet Formulas (fully explained) with:
- Real examples
- Use cases
- Syntax breakdown
- Best practices
- Excel + Google Sheets compatibility notes
Let’s begin.
1. Basic Arithmetic Formulas (Must-Know for Beginners & Experts)
These are the building blocks of all spreadsheets.
1. SUM()
Purpose: Adds numbers or ranges.
Syntax:=SUM(number1, number2, ...)
Example:=SUM(A1:A10) → Adds values from A1 to A10.
Use Case: Total sales, expenses, KPIs, budgets.
2. SUBTOTAL()
Purpose: Performs calculations while ignoring filtered/hidden rows.
Syntax:=SUBTOTAL(function_num, range)
Example:=SUBTOTAL(9, A1:A20) → 9 = SUM.
3. AVERAGE()
Purpose: Calculates mean value.
Syntax:=AVERAGE(range)
Example:=AVERAGE(B1:B8)
4. AVERAGEIF()
Purpose: Average values that meet one condition.
Syntax:=AVERAGEIF(range, criteria, average_range)
Example:=AVERAGEIF(A1:A10, ">100", B1:B10)
5. AVERAGEIFS()
Purpose: Average based on multiple conditions.
Syntax:=AVERAGEIFS(avg_range, criteria_range1, criteria1, ...)
6. COUNT()
Counts numbers only.
7. COUNTA()
Counts non-blank values.
8. COUNTBLANK()
Counts empty cells.
9. COUNTIF()
Count based on one condition.
10. COUNTIFS()
Count based on multiple conditions.
Real use: Count orders above $500 placed by returning customers.
2. Text Formulas (Cleaning, Joining & Formatting)
Text manipulation is critical for data cleaning and reporting.
11. CONCAT() / CONCATENATE()
Combines text from multiple cells.
=CONCAT(A1, " ", B1)
12. TEXTJOIN()
Most powerful text combining function.
=TEXTJOIN(", ", TRUE, A1:A10)
13. LEFT()
Extracts characters from the left.
=LEFT(A1, 4)
14. RIGHT()
Extracts characters from the right.
15. MID()
Extracts characters from the middle.
16. LEN()
Counts characters in a text string.
17. LOWER()
Converts text to lowercase.
18. UPPER()
Converts text to uppercase.
19. PROPER()
Capitalizes the first letter of each word.
20. TRIM()
Removes extra spaces.
21. CLEAN()
Removes non-printing characters.
22. SUBSTITUTE()
Replaces specific text.
=SUBSTITUTE(A1, "old", "new")
23. REPLACE()
Replaces text at a position.
24. FIND()
Case-sensitive search.
25. SEARCH()
Non-case-sensitive search.
3. Lookup & Reference Formulas (The Most Important Section)
Excel’s power lies in lookups and referencing.
26. VLOOKUP()
Vertical lookup (classic).
Syntax:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Limitation: Only searches left-to-right.
27. XLOOKUP()
Replaces VLOOKUP & HLOOKUP (2025 standard).
Syntax:=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Advantages:
✓ Searches left or right
✓ Can do exact match
✓ Handles missing values
✓ More flexible
28. HLOOKUP()
Horizontal lookup.
29. INDEX()
Returns a value from a row/column.
30. MATCH()
Finds the position of an item.
31. INDEX MATCH Combination
Most powerful traditional lookup.
Example:=INDEX(B1:B100, MATCH("Laptop", A1:A100, 0))
32. FILTER()
Filters data dynamically.
=FILTER(A1:C100, C1:C100 > 500)
33. SORT()
Sort ranges dynamically.
34. SORTBY()
Sort based on values in another range.
35. UNIQUE()
Extract unique list.
36. CHOOSE()
Returns a value from a list of options.
37. OFFSET()
Creates dynamic ranges.
38. INDIRECT()
Reference created from text.
39. ADDRESS()
Returns cell address as text.
40. TRANSPOSE()
Switch rows → columns.
4. Date & Time Formulas
41. TODAY()
Returns current date.
42. NOW()
Returns current date + time.
43. DATE()
Creates a date.
44. DAY()
Extracts day.
45. MONTH()
Extracts month.
46. YEAR()
Extracts year.
47. HOUR()
Extracts hour.
48. MINUTE()
Extracts minutes.
49. SECOND()
Extracts seconds.
50. EOMONTH()
End of month calculation.
51. EDATE()
Adds months to a date.
52. NETWORKDAYS()
Working days between two dates.
53. WORKDAY()
Future/past working date.
54. WEEKDAY()
Returns day of the week.
55. TEXT()
Formats numbers and dates.
5. Logical Formulas (Conditions & Automation)
56. IF()
Conditional formula.
57. IFS()
Multiple conditions without nesting.
58. AND()
Returns TRUE if all conditions are met.
59. OR()
Returns TRUE if any condition is met.
60. NOT()
Swaps TRUE ↔ FALSE.
61. XOR()
Exclusive OR logic.
62. IFERROR()
Handles errors gracefully.
63. IFNA()
Handles #N/A errors only.
6. Financial Formulas (Business & Analytics)
64. PMT()
Loan payment per period.
65. IPMT()
Interest portion of a payment.
66. PPMT()
Principal portion.
67. FV()
Future value.
68. PV()
Present value.
69. RATE()
Interest rate.
70. NPV()
Net Present Value.
71. IRR()
Internal Rate of Return.
72. XNPV()
More precise NPV using actual dates.
73. XIRR()
IRR using exact dates.
7. Statistical Formulas (Data Analysis & BI)
74. MEDIAN()
Median value.
75. MODE()
Most repeated value.
76. STDEV.S()
Sample standard deviation.
77. STDEV.P()
Population standard deviation.
78. VAR.S()
Sample variance.
79. VAR.P()
Population variance.
80. QUARTILE() / QUARTILE.INC()
Returns quartiles.
81. PERCENTILE()
Returns percentile.
82. RANK()
Rank numbers.
83. LARGE()
Returns nth largest number.
84. SMALL()
Returns nth smallest.
85. CORREL()
Correlation coefficient.
86. FORECAST() / FORECAST.LINEAR()
Predict future values.
87. TREND()
Linear regression.
8. Array Formulas & Dynamic Arrays
88. SEQUENCE()
Generate number sequences.
89. RANDARRAY()
Random array generation.
90. MMULT()
Matrix multiplication.
91. FREQUENCY()
Distribution frequency.
92. MAP()
LAMBDA-based transformations.
93. REDUCE()
Aggregate values using LAMBDA logic.
94. SCAN()
Create running totals and cumulative results.
95. WRAPROWS() / WRAPCOLS()
Reshape arrays.
9. Advanced Functions
96. LAMBDA()
Create custom functions.
97. LET()
Assign variables inside formulas—boost performance.
98. FORMULATEXT()
Display formulas as text.
99. VSTACK() / HSTACK()
Stack data vertically or horizontally.
100. TOCOL() / TOROW()
Convert data to a single column or row.
Bonus Section: Real-World Use Case Scenarios
1. Sales Reporting
- SUMIFS for revenue
- XLOOKUP for product matching
- UNIQUE for customer list
- FORECAST for projections
2. KPI Dashboards
- FILTER for dynamic tables
- SORTBY for ranking
- MAX/MIN for performance highlights
3. Financial Modeling
- IRR, NPV, RATE, PMT
- INDEX MATCH for structured models
4. Data Cleaning
- TRIM, CLEAN, SUBSTITUTE
- TEXT functions
5. Business Automation
- IF, IFS, AND, OR
- LET for better formula organization
Conclusion: Master These 100 Spreadsheet Formulas & Become Unstoppable
This comprehensive list of Top 100 Spreadsheet Formulas is designed to help beginners, professionals, analysts, financial modelers, and data-driven teams build world-class spreadsheets.
By mastering these formulas, you will:
- Save hours every week
- Improve data accuracy
- Build dashboards & analytics
- Automate workflows
- Boost productivity
- Stand out in your career