12 basic Excel formulas everyone should know, with examples you can try

What's the difference between a formula and a function in Excel?

A formula is anything in a cell that starts with =. A function is a ready-made calculation with a name, such as SUM or IF, that you use inside a formula. So =B2*C2 is a formula without a function, and =SUM(C2:C7) is a formula with one. The arguments go between the brackets, separated by commas: the cells, numbers or conditions the function works with.

You start a formula by typing =. As soon as you type the first letters of a function, Excel suggests names: press Tab to take the suggestion and Enter to confirm the formula. Every example below works on the same small sheet, the first six sales of the week at the Second Chance charity shop in Gloucester, one of the clients in our Excel course, so you can check every result yourself.

Why learn a few formulas really well?

Because employers keep asking for them. Across US job ads in 2025, Microsoft Excel was named in 3,211,598 of 46,885,153 unique postings, roughly one in fifteen, and only "Microsoft Office software" came up more often (O*NET). That's a lot for one program, and a handful of formulas you really master gets you most of the way.

Yet spreadsheets stay the weak spot. In the Netherlands, where 84% have at least basic digital skills, the highest share in the EU, people are least skilled with software such as word processing and spreadsheets: 68% have more than basic skills there, against 99% for online communication (CBS).

Across the EU, 60% of people aged 16 to 74 had at least basic digital skills in 2025, against a target of 80% by 2030 (Eurostat). Belgium, at 61%, sits just above that average, up from 54% in 2021 (Statbel).

Adding and counting: which Excel formulas do you need most?

Six formulas cover most adding and counting, from a plain SUM to SUMIFS with several conditions at once. Start with the first two: according to Microsoft, SUM and AVERAGE are Excel's most used functions, with VLOOKUP in third place (Microsoft).

1. SUM: add everything up

=SUM(C2:C7)

SUM adds up every number in a range, here £35. The catch: a number stored as text gets skipped, with no error to warn you. You'll spot such a cell by its left alignment and often a small green triangle in the corner. And avoid =C2+C3+C4+C5+C6+C7: insert a row in between later and a range grows with it, while a chain of single cells doesn't.

2. AVERAGE: the mean

=AVERAGE(C2:C7)

AVERAGE divides the sum by how many numbers there are: £35 divided by 6, about £5.83. Mind the difference between empty and zero. An empty cell isn't counted, but a cell with 0 is. If someone types 0 for an item that has no price yet, the average drops even though nothing got cheaper. For an average with a condition, the median or a weighted average, see how to calculate an average in Excel.

3. MIN and MAX: the smallest and the largest

=MIN(C2:C7)
=MAX(C2:C7)

MIN gives the smallest number and MAX the largest: £2 for the book and £12 for the dress. But MAX tells you what the most expensive item costs, not which item it is. For that, combine it with XLOOKUP, formula 9 below: =XLOOKUP(MAX(C2:C7),C2:C7,A2:A7) returns Dress green.

4. COUNT: how many numbers?

=COUNT(C2:C7)
=COUNTA(A2:A7)

COUNT counts the cells that hold a number, and only those: here 6. On column A it gives 0, because that's all text. To count every cell that isn't empty, text included, use COUNTA. The trap is text that looks like a number: a price stored as text isn't counted by COUNT, so a total and a count can quietly disagree.

5. COUNTIF: count what meets a condition

=COUNTIF(B2:B7,"Clothing")
=COUNTIF(C2:C7,">5")

COUNTIF counts the cells that meet one condition: three items of clothing, and three items over £5. The classic mistake is a condition that uses a cell. If the limit is in G2, ">G2" looks for the literal text G2 and finds nothing. Join the operator and the cell with &, as in =COUNTIF(C2:C7,">"&G2), and the formula recounts as soon as you change the limit. There are more examples, with wildcards, blanks and duplicates, in COUNTIF examples.

6. SUMIF and SUMIFS: add up with conditions

=SUMIF(B2:B7,"Clothing",C2:C7)
=SUMIFS(C2:C7,B2:B7,"Clothing",E2:E7,"Sophie")

SUMIF adds up what meets one condition, SUMIFS what meets several at once. The clothing brought in £21 in total, and the clothing Sophie sold £12. Watch the order: in SUMIF the sum range comes last, in SUMIFS it comes first, followed by pairs of a range and its criteria. Microsoft warns about it on its SUMIFS page (Microsoft). Examples with dates and wildcards are in SUMIF examples.

The same page says you can give SUMIFS up to 127 pairs of a range and its criteria. The simplest fix for the order trap: always use SUMIFS, even with a single condition. Then you only remember one order, and when a second condition comes along, you just add it at the end.

Making decisions: how do IF, AND and OR work?

IF lets a formula choose: if the test is true, you get one result, otherwise the other. AND and OR combine several tests into one, so that IF can look at more than one thing at a time, such as the category and the price of an item.

7. IF: choose between two results

=IF(C2>=10,"Pricey","Bargain")

IF takes three arguments: a test, what to show when the test is true, and what to show when it isn't. For the £12 dress it shows Pricey. Text goes in quotation marks, numbers don't. To Excel, "10" is text, and text never equals the number 10.

Need more than two outcomes? Excel lets you nest up to 64 IF functions, but Microsoft itself says it's "not at all advisable" (Microsoft). With IFS you list the tests one after another and the first one that's true wins. There's more in Excel IF function examples.

8. AND and OR: more than one condition

=IF(AND(B2="Clothing",C2>5),"Window","Rail")
=OR(D2="Mon",D2="Tue")

AND returns TRUE when every condition holds, OR when at least one does. The first formula sends clothing over £5 to the shop window, and the green dress goes in. The second returns TRUE for everything sold on Monday or Tuesday. The conditions go inside the function as arguments: B2="Clothing" AND C2>5 isn't something Excel understands.

And pick the right one. If you want "the clothing and the books", you mean OR: =AND(B2="Clothing",B2="Books") is always FALSE, because one cell can't be both. Say the condition out loud for a single row, "this row is clothing or books", and you'll hear it.

Looking things up: how does XLOOKUP work?

XLOOKUP finds a value in one column and returns what's on the same row in another. Microsoft announced it in 2019 as the "successor to the iconic VLOOKUP function" (Microsoft), and unlike VLOOKUP, which looks for an approximate match unless you tell it otherwise (Microsoft), it matches exactly by default.

9. XLOOKUP: look up in any direction

=XLOOKUP("Lamp",A2:A7,C2:C7)
=XLOOKUP(8,C2:C7,A2:A7)
=XLOOKUP("Radio",A2:A7,C2:C7,"Not found")

You give it three things: what you're looking for, where to look, and what to return. The first formula returns the lamp's price, £8. The second looks to the left, which VLOOKUP can't do: which item costs £8? The lamp. When XLOOKUP finds nothing it returns #N/A, unless you add a fourth argument. The radio isn't in this part of the sheet, so the third formula shows Not found.

Two traps. XLOOKUP isn't available in Excel 2016 or Excel 2019, where it shows #NAME? (Microsoft). And the value has to match exactly: "Lamp " with a trailing space won't be found. Still on VLOOKUP? Read VLOOKUP vs XLOOKUP: which one to use, or practise right away in the lesson looking up in any direction with XLOOKUP.

10. IFERROR: catch an error value

=IFERROR(XLOOKUP(H2,A2:A7,C2:C7),"Unknown")

IFERROR shows something else when a formula returns an error value. If someone types an item in H2 that isn't on the list, this formula shows Unknown instead of #N/A. That looks tidier, especially on a sheet other people read, but it comes at a price.

IFERROR hides every error, including your own. Type XLOOKP instead of XLOOKUP and the cell shows Unknown for every item, while the #NAME? you should have seen is gone.

So only wrap IFERROR around a formula once it works. If all you want to catch is "not found", use IFNA instead: it only catches #N/A and lets every other error through. You'll practise catching error values without missing one in the lesson catching errors with IFERROR.

Keeping it tidy: rounding, joining text and locking references

The last two formulas and the habit make a sheet you can trust: numbers that are really rounded, text people can read, and references that stay where they belong. Small things, but that's where a sheet you fill down or pass on usually goes wrong.

11. ROUND: really round, don't just display

=ROUND(AVERAGE(C2:C7),2)

ROUND rounds a number to the number of decimal places you give it, so the average from before really becomes 5.83. Showing fewer decimals with the formatting buttons is something else. Excel then only rounds on screen and keeps calculating with the full number: three cells holding 0.333 each show 0.33, but their total shows 1.00.

12. Joining text with &

=A2&" ("&B2&")"

With & you join text together, including text from different cells. Text you add yourself, such as a space or brackets, goes in quotation marks, and this formula returns Dress green (Clothing). Forget the space and you get Dress greenClothing. And what comes out is text: =A2&": "&C2 gives Dress green: 12, without the pound formatting, and you can't calculate with the result any more.

The habit: lock references with $ and F4

The dollar sign isn't a formula, but it's the habit that most often makes the difference. When you fill a formula down, every reference moves along with it. That's usually exactly what you want, except for a cell that has to stay the same for every row, such as a price at the top. Here's how the Green Fingers allotments in Harrogate, another client in the course, work out the rent for their plots:

In C5, =B5*B1 is right: 50 times £1.60 is £80. But filled down, C6 becomes =B6*B2, which is 25 times the water charge, £300. In C7 the price points at the empty B3, so P03 costs nothing. With a $ before the column letter and before the row number, the reference stays put: =B5*$B$1 filled down becomes =B6*$B$1, =B7*$B$1, and so on.

You don't have to type the dollars yourself. Put the cursor in the reference and press F4, and Excel switches between the reference types (Microsoft), from $B$1 to B$1, $B1 and back to plain B1. Now try it yourself, on the real Green Fingers sheet.

The formula you just filled down didn't run on a server somewhere far away. It ran in your own browser. Lurndo checks your answer on its result, and then again on a second, hidden version of the sheet with different numbers. Type 1.60 instead of $B$1 and it works here but gets caught there.

What do #DIV/0!, #N/A and other Excel errors mean?

An error value is Excel's way of saying a formula can't calculate. These are the five you'll meet most often, what causes them and what to do. In a Dutch version of Excel they show up under Dutch names (Microsoft), handy to know when a colleague abroad asks for help.

An error value is actually good news: at least you can see something's wrong. A wrong number that looks believable is far more dangerous. When economists Herndon, Ash and Pollin redid the calculations behind a widely cited study on public debt, they found selectively excluded data, coding errors and inappropriate weighting. Corrected, high-debt countries averaged 2.2% real annual growth, not -0.1% (Cambridge Journal of Economics).

Comma or semicolon? Excel formulas in other languages

Most English-language help separates arguments with commas, but that comes from your settings, not from Excel itself. According to Microsoft, the most common list separators are the comma and the semicolon, and which one you get depends on your operating system's locale and your Excel settings (Microsoft). Where the comma is the decimal mark, as in much of mainland Europe, Excel usually uses a semicolon instead.

So if a formula from a European colleague or forum won't work, check two things: the separators and the function names. Decimals change too: =ROUND(2.5,0) in English becomes =AFRONDEN(2,5;0) in Dutch. A workbook doesn't need translating, though. Excel stores formulas in a fixed form and shows them in the language of whoever opens the file.

Which Excel formulas should you learn first?

Start with SUM and the dollar sign: with those two you can already work out a whole column without retyping a single amount. Then come IF, COUNTIF and SUMIF, and only then XLOOKUP, because looking things up builds on everything before it. That's the order our course follows too, with the lessons adding up with SUM, locking references with a dollar, choosing with IF and adding up what matches with SUMIF.

Every lesson starts with an explanation on a real sheet, then has you type formulas yourself, which we check on their result. Learn Excel as a complete beginner shows how the modules build on each other, Excel practice exercises with explanations lists them by topic, and the Excel cheat sheet puts every function on one page. The first three modules are free.