Excel Formulas Cheat Sheet: 25 Essential Formulas Everyone Should Know

You don't need to know 500 Excel functions. These 25 cover about 95% of real-world spreadsheet work. Bookmark this page — each formula has its syntax and a plain-English example.

Math & totals

1. SUM — =SUM(A1:A10) — adds a range.

2. AVERAGE — =AVERAGE(B2:B20) — the mean of a range.

3. COUNT / COUNTA — =COUNT(A:A) counts numbers; =COUNTA(A:A) counts anything non-empty.

4. ROUND — =ROUND(A1,2) — rounds to 2 decimal places.

5. SUMIF / SUMIFS — =SUMIF(A:A,"UK",B:B) — adds B where A says "UK". SUMIFS takes multiple conditions.

6. COUNTIF / COUNTIFS — =COUNTIF(C:C,">100") — counts cells over 100.

Lookups (the career-makers)

7. XLOOKUP — =XLOOKUP(E2,A:A,C:C,"not found") — finds E2 in column A, returns the match from column C. The modern replacement for VLOOKUP: no column counting, searches any direction, built-in error fallback.

8. VLOOKUP — =VLOOKUP(E2,A:C,3,FALSE) — the classic you'll still meet in older workbooks.

9. INDEX + MATCH — =INDEX(C:C,MATCH(E2,A:A,0)) — the flexible power combo.

10. FILTER — =FILTER(A2:C100,C2:C100>500) — spills every row where C exceeds 500. (Excel 2021 and later.)

11. UNIQUE — =UNIQUE(A2:A100) — de-duplicates a list instantly.

12. SORT — =SORT(A2:B100,2,-1) — sorts by column 2, descending.

Logic

13. IF — =IF(B2>=50,"Pass","Fail").

14. IFS — =IFS(B2>90,"A",B2>80,"B",TRUE,"C") — multiple conditions without nesting.

15. IFERROR — =IFERROR(A1/B1,0) — replaces errors with a fallback.

16. AND / OR — =IF(AND(B2>50,C2="Yes"),"OK","No").

Text

17. TEXTJOIN — =TEXTJOIN(", ",TRUE,A2:A5) — merges cells with a separator.

18. LEFT / RIGHT / MID — =LEFT(A2,3) — slices text.

19. TRIM — =TRIM(A2) — strips stray spaces (fixes half of all "broken" lookups).

20. TEXTSPLIT — =TEXTSPLIT(A2,",") — splits text by a delimiter. (Excel 2024/365.)

21. CONCAT / & — =A2&" "&B2 — joins first and last names.

Dates

22. TODAY / NOW — =TODAY() — current date, updates automatically.

23. DATEDIF — =DATEDIF(A2,TODAY(),"Y") — full years between dates (ages, tenure).

24. EOMONTH — =EOMONTH(A2,0) — last day of a month (invoicing deadlines).

25. NETWORKDAYS — =NETWORKDAYS(A2,B2) — working days between dates.

Which Excel do you need?

Dynamic-array formulas (FILTER, UNIQUE, SORT, XLOOKUP) need Excel 2021 or newer; TEXTSPLIT and the newest functions (GROUPBY, PIVOTBY, REGEX) need Excel 2024 or Microsoft 365. A lifetime Office 2021 or 2024 key gets you there with a one-time payment — see our version comparison if you're unsure which.

Want to go from formulas to full fluency — pivot tables, Power Query, dashboards? Our e-book Excel: The Ultimate Guide from Zero to Power User is an instant download. And if you're just starting out, begin with our Excel tips for beginners.