You do not need hundreds of Excel functions to work faster. These 25 formulas cover almost everything office work asks for: totals, counting, lookups, cleaning text and working with dates. Each one comes with a short real-world example you can copy.
Quick answer
If you only learn five, learn these: SUMIFS, COUNTIFS, IF, XLOOKUP and IFERROR. Together they handle most reports and checks.
Totals and averages
1. SUM: add numbers
=SUM(C2:C100) adds all values in C2 to C100. Shortcut: select the cell below a column and press Alt + =.
2. AVERAGE: find the mean
=AVERAGE(C2:C100) returns the average sale amount.
3. SUMIF: add numbers that meet one condition
=SUMIF(B2:B100, "North", C2:C100) adds sales only for the North region.
4. SUMIFS: add numbers that meet several conditions
=SUMIFS(C2:C100, B2:B100, "North", D2:D100, "Laptop") adds North sales of laptops only. Note that the sum range comes first in SUMIFS.
5. AVERAGEIF: average with a condition
=AVERAGEIF(B2:B100, "South", C2:C100) returns the average sale in the South region.
6. ROUND: control decimals
=ROUND(C2*0.1, 2) calculates 10% and rounds it to 2 decimals. Use ROUNDUP or ROUNDDOWN to always round one way.
Counting
7. COUNTA: count filled cells
=COUNTA(A2:A100) counts how many rows have something in them, such as the number of employees in a list.
8. COUNTIF: count with one condition
=COUNTIF(E2:E100, "Pending") counts how many orders are still pending.
9. COUNTIFS: count with several conditions
=COUNTIFS(E2:E100, "Pending", B2:B100, "North") counts pending orders in the North region.
10. COUNTBLANK: find missing data
=COUNTBLANK(F2:F100) shows how many rows are missing a value, useful before sending a report.
Logic and checks
11. IF: make a decision
=IF(C2>=1000, "Target met", "Below target")
12. IFS: several conditions without nesting
=IFS(C2>=5000, "Gold", C2>=1000, "Silver", TRUE, "Bronze") The final TRUE catches everything else.
13. AND / OR: combine conditions
=IF(AND(C2>=1000, E2="Paid"), "Bonus", "") gives a bonus only when the sale is large and paid.
14. IFERROR: hide errors
=IFERROR(C2/D2, 0) shows 0 instead of #DIV/0! when D2 is empty.
Lookups
15. XLOOKUP: find matching data
=XLOOKUP(F2, A2:A100, C2:C100, "Not found") finds a product code and returns its price. See our full XLOOKUP guide.
16. VLOOKUP: the classic lookup
=VLOOKUP(F2, A2:D100, 3, FALSE) still appears in many older files, so it is worth recognising. Always use FALSE for an exact match.
17. INDEX + MATCH: lookup for older Excel versions
=INDEX(C2:C100, MATCH(F2, A2:A100, 0)) works in every version, including Excel 2016 and 2019.
Cleaning text
18. TRIM: remove extra spaces
=TRIM(A2) removes spaces at the start, at the end and doubled in the middle. Extra spaces are the number-one reason lookups fail.
19. PROPER / UPPER / LOWER: fix capitalisation
=PROPER("ahmed ali") returns Ahmed Ali.
20. TEXTJOIN: combine cells
=TEXTJOIN(" ", TRUE, A2, B2) joins first and last name with a space and skips empty cells.
21. LEFT / RIGHT / MID: extract part of a text
=LEFT(A2, 3) returns the first 3 characters, for example the branch code from BAH-00123.
22. TEXT: format numbers and dates as text
=TEXT(A2, "mmm yyyy") turns a date into Oct 2026, handy in labels and email subjects.
Dates
23. TODAY: today’s date
=TODAY()-A2 shows how many days have passed since the date in A2, for example the age of an invoice.
24. NETWORKDAYS: working days between two dates
=NETWORKDAYS(A2, B2) counts working days, skipping Saturday and Sunday. If your weekend is different (for example Friday and Saturday), use =NETWORKDAYS.INTL(A2, B2, 7).
25. EOMONTH: end of month
=EOMONTH(A2, 0) returns the last day of the month for the date in A2. Use 1 instead of 0 for the end of next month, useful for due dates.
Bonus for Microsoft 365 users
If you have Microsoft 365 or Excel 2021+, try these dynamic array functions:
=UNIQUE(B2:B100)lists each region once.=FILTER(A2:E100, E2:E100="Pending")shows only pending orders.=SORT(A2:E100, 5, -1)sorts the list by column 5, largest first.
Common mistakes
- Forgetting the $ sign: when copying a formula, use
$A$2:$A$100(press F4) so the range does not move. - Numbers stored as text: they look like numbers but SUM ignores them. Look for a small green triangle in the cell corner.
- Wrong argument order in SUMIFS: the sum range is first, unlike SUMIF.
- Typing text conditions without quotes: write
"North", notNorth.
FAQ
Do these formulas work in Google Sheets?
Yes, all 25 work in Google Sheets with the same names.
What is the fastest way to learn them?
Pick three and use them on a real file at work this week. Practice beats memorising.