Excel Formula List
1. Basic Formulas
Formula Purpose Example
=SUM(A1:A10) Adds numbers in a range Total sales
=AVERAGE(B1:B10) Calculates the average Average score
=MIN(C1:C10) Finds the minimum value Lowest temperature
=MAX(D1:D10) Finds the maximum value Highest sales
=COUNT(E1:E10) Counts numeric values Number of entries
=COUNTA(F1:F10) Counts all non-empty cells Number of responses
=ROUND(G1,2) Rounds a number to 2 decimals 5.678 → 5.68
=NOW() Current date and time Auto-update
=TODAY() Current date only Daily report
2. Logical Formulas
Formula Purpose Example
=IF(A1>100,"Yes","No") If condition is true, return "Yes" Bonus eligibility
=AND(A1>0,B1<10) True if both conditions are true In-range test
=OR(A1>100,B1>100) True if any condition is true Check multiple sales targets
=NOT(A1="Completed") Reverses logic Task not completed
3. Lookup and Reference Formulas
Formula Purpose Example
=VLOOKUP(101,A2:C100,2,FALSE)
Find a value Find product name
vertically by ID
=HLOOKUP(90,A1:Z2,2,TRUE)
Find a value
Find grade by score
horizontally
=INDEX(A2:C10,2,3)
Find value by
Get specific data
row/column
=MATCH(200,B1:B20,0)
Find position of a
Find row number
value
=XLOOKUP("Apple",A2:A10,B2:B10,"Not Easier modern
Found") Find price of Apple
lookup
=OFFSET(A1,2,3)
Reference a cell
Dynamic ranges
offset
4. Text Formulas
Formula Purpose Example
=CONCAT(A1," ",B1) Joins text Full name
=TEXT(A1,"mm/dd/yyyy") Format number as text Display date
=LEFT(A1,5) Extracts first 5 characters Part of a code
=RIGHT(A1,3) Extracts last 3 characters Last digits of ID
=MID(A1,3,4) Extracts from middle Characters 3–6
=LEN(A1) Counts characters Check entry length
=TRIM(A1) Removes extra spaces Clean data
5. Date and Time Formulas
Formula Purpose Example
=YEAR(A1) Extracts year from a date 2025
=MONTH(A1) Extracts month 04 (April)
=DAY(A1) Extracts day number 17
=DATEDIF(A1,A2,"d") Days between two dates Project duration
=WORKDAY(A1,10) 10 working days after date Delivery deadline
=NETWORKDAYS(A1,A2) Workdays between dates Available working days
6. Advanced and Useful Formulas
Formula Purpose Example
=IFERROR(A1/B1,"Error")
Handle errors Prevent
gracefully #DIV/0!
=SUMIF(A1:A10,">100")
Add only if Sales above
condition met 100
=COUNTIF(B1:B10,"Yes")
Count only Count
matching items approvals
=SUMIFS(A1:A10,B1:B10,"North",C1:C10,">1000")
Add with multiple Sales over
conditions 1000 in North
=ARRAYFORMULA(A1:A5*B1:B5)
Multiply arrays Bulk
(Google Sheets) calculations
List unique values Unique
=UNIQUE(A1:A10) (Excel 365/Google customer
Sheets) names
Bonus: Popular Shortcuts for Formula
Efficiency
Shortcut Action
Ctrl + Shift + Enter Enter array formulas (older Excel versions)
Ctrl + D Copy formula down
Ctrl + R Copy formula right
Alt + = AutoSum
F4 Toggle absolute/relative references
Final Tip:
✅ Always double-check your formulas by spot-testing with simple numbers!
✅ Use named ranges to make complex formulas easier to read.
