The ten Excel and Google Sheets functions that cover most everyday tasks, with plain-English explanations, worked examples and the common mistakes to avoid.
You do not need to learn hundreds of functions to be good at spreadsheets. A small set covers most everyday jobs: adding things up, counting, looking values up, tidying text and handling errors. All ten below work in current versions of Microsoft Excel and in Google Sheets, with the same syntax unless noted.
1. SUM: add up a range
=SUM(B2:B20) adds every number in B2 to B20. Use ranges rather than adding cells one by one (=B2+B3+B4), which is slower to write and easy to break when you insert rows.
2. AVERAGE, MIN and MAX
=AVERAGE(B2:B20), =MIN(B2:B20) and =MAX(B2:B20) give the mean, smallest and largest values. AVERAGE ignores empty cells but counts zeros, which can matter if you enter 0 for "no data".
3. COUNT, COUNTA and COUNTBLANK
=COUNT(B2:B20) counts cells containing numbers, =COUNTA(A2:A20) counts non-empty cells (text or numbers), and =COUNTBLANK(A2:A20) counts empty ones. Useful for checking how complete a list is.
4. IF: make a decision
=IF(C2>100,"Over budget","OK") returns one result if a condition is true and another if it is false. You can combine conditions with AND and OR: =IF(AND(C2>0,C2<=100),"In range","Check"). For several outcomes, IFS is easier to read than nested IFs.
5. SUMIF and SUMIFS: add up with conditions
=SUMIF(A2:A100,"Food",C2:C100) adds amounts in column C where column A says "Food". SUMIFS takes several conditions, with the sum range first: =SUMIFS(C2:C100,A2:A100,"Food",B2:B100,">=01/01/2026"). Dates in conditions depend on your locale settings, so referring to a cell containing the date is more reliable.
6. COUNTIF and COUNTIFS
The counting equivalents: =COUNTIF(D2:D100,"Paid") counts how many rows say "Paid". Wildcards work: "*London*" matches any text containing London.
7. XLOOKUP: find a value
=XLOOKUP(F2,A2:A100,C2:C100,"Not found") looks for F2 in column A and returns the matching value from column C, or "Not found". It is available in Microsoft 365 and Excel 2021 or later, and in Google Sheets. It replaces most uses of VLOOKUP, which breaks if columns are inserted and only looks to the right. In older Excel versions, use INDEX with MATCH instead.
8. TRIM, PROPER and text joining
=TRIM(A2) removes extra spaces, a common cause of failed lookups. =PROPER(A2) capitalises each word. Join text with & or TEXTJOIN: =TEXTJOIN(", ",TRUE,A2:C2) joins three cells with commas, skipping blanks.
9. TODAY and date maths
=TODAY() returns today's date and updates automatically. Dates are stored as numbers, so =B2-TODAY() gives the number of days until a deadline. Format the result as a number, not a date.
10. IFERROR: handle errors cleanly
=IFERROR(C2/B2,0) returns 0 instead of a #DIV/0! error. Use it sparingly: hiding all errors can also hide genuine mistakes.
Five habits that prevent mistakes
- Use absolute references (
$A$1) for fixed cells, such as a tax rate, when copying formulas. - Turn data into a table (Excel: Insert > Table; Sheets: Format > Convert to table) so formulas extend automatically.
- Keep raw data and calculations separate.
- Check totals another way, for example with the status bar sum.
- Avoid typing numbers inside formulas; put them in labelled cells.
Frequently asked questions
Why does my formula show as text?
The cell may be formatted as Text, or the formula may start with a space. Format as General and re-enter it starting with =.
Do formulas use commas or semicolons?
It depends on your regional settings. UK settings use commas.
Which should I learn first?
Start with SUM, IF and SUMIFS, which solve most everyday tasks, then XLOOKUP. Practise on a real list of your own, such as household bills or a club membership list, rather than abstract examples. You will remember a function far better once you have used it to answer a question you actually care about.
What are the newer "dynamic array" functions?
FILTER, SORT and UNIQUE return several results that "spill" into neighbouring cells. =UNIQUE(A2:A100) lists each distinct value once, and =FILTER(A2:C100,C2:C100>100) shows only rows where column C is over 100. They work in Microsoft 365, Excel 2021 or later, and Google Sheets.
Sources and further reading
- Microsoft Support: Excel functions (by category)
- Google Docs Editors Help: Google Sheets function list
- Microsoft Support: XLOOKUP function
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.