Excel Formulas & Functions Reference
Master every Excel formula with our comprehensive reference guide. Search, learn, and practice with real examples.
🔍 Find Any Formula Instantly
Search our database of 400+ Excel functions with syntax, examples, and use cases
Open Function Finder Tool⭐ Most Popular Formulas
SUMIF
Sum cells that meet a condition
VLOOKUP
Look up values in a table
IF
Test conditions and return values
CONCATENATE
Join text strings together
INDEX/MATCH
Advanced lookup combination
COUNTIF
Count cells that meet criteria
XLOOKUP
Modern replacement for VLOOKUP
SUMPRODUCT
Multiply and sum arrays
IFERROR
Handle formula errors gracefully
📚 Browse by Category
Math & Trig
Basic calculations, rounding, and trigonometry
Text Functions
Manipulate and format text strings
Logical Functions
Make decisions and test conditions
Lookup & Reference
Find and reference data across sheets
Date & Time
Work with dates and time values
Financial
Financial calculations and analysis
Statistical
Statistical analysis and calculations
🎯 Formula Types Explained
Basic Formulas
Start with arithmetic operations using cell references:
Array Formulas
Process multiple values and return multiple results:
⚠️ Understanding Formula Errors
#REF!
Invalid cell reference
Occurs when a formula refers to deleted cells
#DIV/0!
Division by zero
Check for empty cells or zeros in divisor
#N/A
Value not available
Common in VLOOKUP when match not found
#VALUE!
Wrong data type
Text where number expected or vice versa
#NAME?
Unrecognized text
Misspelled function name or missing quotes
#NUM!
Invalid numeric value
Number too large/small or invalid operation
📖 In-Depth Formula Guides
🔧 Common Formula Patterns
Conditional Calculations
=SUMIF(A:A, ">100", B:B)Sum values in B where A is greater than 100
=AVERAGEIF(A:A, "Complete", B:B)Average of B where A equals "Complete"
=COUNTIFS(A:A, ">0", B:B, "<100")Count rows meeting multiple criteria
Data Cleaning
=TRIM(CLEAN(A1))Remove extra spaces and non-printing characters
=PROPER(LOWER(A1))Convert text to proper case
=SUBSTITUTE(A1, "old", "new")Replace specific text within a string
Date Calculations
=DATEDIF(A1, B1, "D")Days between two dates
=EOMONTH(TODAY(), 0)Last day of current month
=WORKDAY(A1, 10)Date 10 working days from A1
Error Handling
=IFERROR(VLOOKUP(...), "Not Found")Return custom message if formula errors
=IFNA(MATCH(...), 0)Handle #N/A errors specifically
=ISERROR(A1/B1)Check if formula would produce error
🚀 Advanced Formula Techniques
Dynamic Arrays (Excel 365)
New formulas that automatically spill results into multiple cells:
FILTER
=FILTER(A:B, C:C="Yes")UNIQUE
=UNIQUE(A:A)SORT
=SORT(A:B, 2, -1)Nested Formulas
Combine multiple functions for powerful results:
INDEX-MATCH-MATCH (2D Lookup)
=INDEX(data, MATCH(row_val, row_range, 0), MATCH(col_val, col_range, 0))Look up values using both row and column criteria
Dynamic Sum Range
=SUM(INDIRECT("A1:A"&COUNTA(A:A)))Sum a range that automatically expands with data
✅ Formula Best Practices
Do's
- ✓Use named ranges for clarity
- ✓Break complex formulas into steps
- ✓Document complex formulas with comments
- ✓Use Tables for dynamic ranges
- ✓Test formulas with sample data
Don'ts
- ✗Avoid hardcoding values in formulas
- ✗Don't nest more than 7 levels deep
- ✗Avoid volatile functions in large datasets
- ✗Don't use entire column references unnecessarily
- ✗Avoid circular references
💡 Pro Tips for Excel Formulas
Keyboard Shortcuts
- •F4: Toggle between relative and absolute references
- •F9: Evaluate parts of your formula
- •Ctrl+`: Show formulas instead of results
- •Tab: Autocomplete function names
Debugging Techniques
- •Evaluate Formula: Step through calculations
- •Trace Precedents: See which cells feed a formula
- •Error Checking: Use built-in error checker
- •Watch Window: Monitor specific cells
Ready to Master Excel Formulas?
Start with our beginner-friendly tutorials or dive deep into advanced functions