Uncategorized July 22, 2026

100+ Excel Formulas Explained with Examples (The Ultimate Guide)

Microsoft Excel is one of the most powerful productivity tools ever created. Whether you’re a student, accountant, project manager, entrepreneur, analyst, or office professional, understanding Excel formulas can save hours of manual work, reduce errors, and improve decision-making.

This guide covers more than 100 of the most useful Excel formulas, organized by category, with explanations and practical examples. Whether you’re just starting or looking to sharpen advanced skills, you’ll find formulas that help you automate calculations, analyze data, and build smarter spreadsheets.

Mathematical Formulas
Statistical Functions
Logical Functions
Text Functions
Date & Time Functions
Lookup & Reference Functions
Dynamic Array Functions
Financial Functions
Information Functions
Error Handling Functions
Database Functions
Engineering Functions
Best Practices
Frequently Asked Questions

Why Learn Excel Formulas?

Learning formulas allows you to:

Automate repetitive calculations
Analyze large datasets quickly
Build professional dashboards
Create financial models
Reduce manual errors
Save hours every week
Improve career opportunities
Build smarter business reports

Basic Mathematical Formulas

1. SUM

Adds numbers together.

=SUM(A1:A10)

Use for budgets, invoices, and sales totals.

2. PRODUCT

Multiplies values.

=PRODUCT(B2:B5)

3. POWER

Raises a number to a power.

=POWER(5,2)

Returns 25.

4. SQRT

Square root.

=SQRT(144)

Returns 12.

5. ABS

Returns absolute value.

=ABS(-250)

Returns 250.

6. ROUND

Rounds numbers.

=ROUND(A1,2)

7. ROUNDUP

Always rounds upward.

=ROUNDUP(A1,0)

8. ROUNDDOWN

Always rounds downward.

=ROUNDDOWN(A1,0)

9. CEILING

Rounds up to a multiple.

=CEILING(A1,5)

10. FLOOR

Rounds down to a multiple.

=FLOOR(A1,5)

Statistical Functions

11. AVERAGE

=AVERAGE(B2:B20)

12. MEDIAN

=MEDIAN(B2:B20)

13. MODE

=MODE(B2:B20)

14. MAX

=MAX(B2:B20)

15. MIN

=MIN(B2:B20)

16. LARGE

=LARGE(B2:B20,2)

Second-largest value.

17. SMALL

=SMALL(B2:B20,3)

Third-smallest value.

18. COUNT

Counts numbers.

=COUNT(A:A)

19. COUNTA

Counts non-empty cells.

=COUNTA(A:A)

20. COUNTBLANK

Counts empty cells.

=COUNTBLANK(A:A)

Logical Functions

21. IF

=IF(B2>1000,"Profit","Loss")

22. IFS

=IFS(A1>90,"A",A1>80,"B")

23. AND

=AND(A1>10,B1<20)

24. OR

=OR(A1>10,B1>10)

25. NOT

=NOT(A1=100)

26. IFERROR

=IFERROR(A1/B1,0)

27. XOR

=XOR(A1>0,B1>0)

Text Functions

28. LEFT

=LEFT(A1,5)

29. RIGHT

=RIGHT(A1,4)

30. MID

=MID(A1,3,5)

31. LEN

=LEN(A1)

32. TRIM

=TRIM(A1)

33. UPPER

=UPPER(A1)

34. LOWER

=LOWER(A1)

35. PROPER

=PROPER(A1)

36. SUBSTITUTE

=SUBSTITUTE(A1,"Old","New")

37. REPLACE

=REPLACE(A1,3,2,"XX")

38. TEXTJOIN

=TEXTJOIN(", ",TRUE,A1:A5)

39. CONCAT

=CONCAT(A1,B1)

40. TEXT

=TEXT(A1,"dd/mm/yyyy")

Date & Time Functions

41. TODAY

=TODAY()

42. NOW

=NOW()

43. YEAR

=YEAR(A1)

44. MONTH

=MONTH(A1)

45. DAY

=DAY(A1)

46. WEEKDAY

=WEEKDAY(A1)

47. EDATE

=EDATE(A1,6)

48. EOMONTH

=EOMONTH(A1,0)

49. NETWORKDAYS

=NETWORKDAYS(A1,B1)

50. WORKDAY

=WORKDAY(A1,10)

Lookup Functions

51. XLOOKUP

=XLOOKUP(A2,Table[ID],Table[Price])

52. VLOOKUP

=VLOOKUP(A2,A:E,5,FALSE)

53. HLOOKUP

=HLOOKUP(A1,A1:G5,3,FALSE)

54. INDEX

=INDEX(B2:B20,5)

55. MATCH

=MATCH(A2,B:B,0)

56. INDEX + MATCH

=INDEX(C:C,MATCH(A2,A:A,0))

57. CHOOSE

=CHOOSE(2,"Red","Blue","Green")

58. OFFSET

=OFFSET(A1,2,1)

59. INDIRECT

=INDIRECT("A1")

60. ADDRESS

=ADDRESS(5,2)

Dynamic Array Functions (Microsoft 365)

61. FILTER

=FILTER(A2:D100,D2:D100="Paid")

62. SORT

=SORT(A2:D100)

63. SORTBY

=SORTBY(A2:D100,D2:D100)

64. UNIQUE

=UNIQUE(A2:A100)

65. SEQUENCE

=SEQUENCE(10)

66. RANDARRAY

=RANDARRAY(5,3)

67. TAKE

=TAKE(A1:D100,10)

68. DROP

=DROP(A1:D100,5)

69. CHOOSECOLS

=CHOOSECOLS(A1:F100,1,3,6)

70. TOROW

=TOROW(A1:D5)

Financial Functions

Examples include:

PMT
PV
FV
RATE
NPER
IPMT
PPMT
NPV
IRR
XIRR

These functions are commonly used for loan calculations, investment analysis, mortgage planning, and business forecasting.

Information Functions

ISBLANK
ISNUMBER
ISTEXT
ISERROR
ISFORMULA
CELL
TYPE
INFO
SHEET
SHEETS

Database Functions

DSUM
DCOUNT
DMAX
DMIN
DAVERAGE

Engineering & Utility Functions

RAND
RANDBETWEEN
LET
LAMBDA
BYROW
BYCOL
MAP
REDUCE
SCAN
MAKEARRAY

Best Practices for Using Excel Formulas

Use Excel Tables for dynamic references.
Prefer XLOOKUP over VLOOKUP in Microsoft 365.
Wrap complex formulas with IFERROR to avoid displaying errors.
Name important ranges to improve readability.
Break complex calculations into helper columns.
Use LET to simplify long formulas and improve performance.
Document complex formulas with comments or notes.
Avoid hardcoding values—reference cells instead.

Save Time with Ready-to-Use Excel Templates

Learning formulas is essential, but building spreadsheets from scratch can still be time-consuming. Professionally designed templates let you focus on your work instead of formatting and debugging formulas.

At Templerly, you’ll find premium Excel templates for:

Budget Planning
Financial Forecasting
Invoice Management
Project Management
Gantt Charts
Business Dashboards
Calendars
Expense Tracking
Sales Reports
Inventory Management

Each template is fully editable, includes built-in formulas where appropriate, and is designed to help individuals and businesses work more efficiently.

Frequently Asked Questions

What is the most useful Excel formula?

For most users, SUM, IF, XLOOKUP, FILTER, COUNTIF, and IFERROR are among the most valuable because they automate everyday calculations and data analysis.

Should I learn VLOOKUP or XLOOKUP?

If you’re using Microsoft 365 or Excel 2021+, start with XLOOKUP. It’s more flexible and easier to use than VLOOKUP. If you work with older versions of Excel, learning VLOOKUP is still worthwhile.

How many formulas does Excel have?

Modern versions of Microsoft Excel include 500+ built-in functions, covering mathematics, statistics, finance, engineering, text manipulation, lookup, dynamic arrays, and more.

Can Excel formulas automate my work?

Yes. Combining formulas with Tables, PivotTables, conditional formatting, and features like Power Query can automate many repetitive business tasks, reducing manual work and improving accuracy.

Conclusion

Mastering Excel formulas is one of the fastest ways to improve your productivity, whether you’re managing personal finances, analyzing business data, or building professional reports. From simple calculations like SUM and AVERAGE to advanced tools like XLOOKUP, LET, and LAMBDA, each function helps you work more efficiently and make better decisions.

The more you practice these formulas, the more powerful Excel becomes. Pair your growing skills with professionally designed templates from Templerly, and you’ll spend less time building spreadsheets from scratch and more time focusing on what matters most.

Ready-made templates for this.
From €2 · instant download Browse templates