Excel Formulas: A Beginner’s Guide to Essential Functions

A formula is the reason a spreadsheet is more than a grid of numbers. Type one into a cell and Excel does the maths for you — adding a column, working out an average, flagging which invoices are overdue — and it updates the moment the underlying numbers change. If you are new to spreadsheets, the good news is that you only need a handful of formulas to handle most everyday jobs. This guide explains what formulas are, how to write them, and the essential functions worth learning first, with plain examples you can copy straight into a sheet.

What is an Excel formula?

An Excel formula is an instruction you type into a cell that tells Excel to calculate a result. Every formula begins with an equals sign (=), which is how Excel knows you want a calculation rather than plain text. After the equals sign you combine values, cell references and operators — for example =10+5 returns 15, and =A1+A2 adds whatever is in those two cells.

People often use the words “formula” and “function” as if they mean the same thing, but there is a useful difference. A formula is any calculation you write. A function is a named, built-in shortcut for a common calculation — such as SUM, AVERAGE or IF — that saves you writing out the long version by hand. You use functions inside formulas. Instead of =A1+A2+A3+A4+A5, you can write =SUM(A1:A5), which is shorter, easier to read and less error-prone. Excel includes several hundred functions, but beginners rarely need more than ten or so to start being productive.

How to write a formula in Excel

Click a cell, type the equals sign, build your calculation, and press Enter. The cell shows the result, while the formula bar above the grid shows the formula itself. To edit a formula, click the cell and change it in the formula bar, or double-click the cell to edit in place.

The parts of a formula

Most formulas are made from four kinds of building block:

• Constants — fixed values you type directly, such as 15 or 0.15.
• Cell references — the address of a cell, such as B2, or a range such as B2:B20. References let a formula react automatically when the data changes.
• Operators — symbols that tell Excel what to do: + (add), – (subtract), * (multiply), / (divide), ^ (raise to a power), & (join text) and comparison signs such as =, >, <, >= and <=.
• Functions — named shortcuts such as SUM() or AVERAGE(), with their inputs (called arguments) inside the brackets.

A worked example: =SUM(B2:B13)*0.15 adds every number from B2 to B13, then multiplies the total by 0.15. It uses a function (SUM), a range (B2:B13), an operator (*) and a constant (0.15) all at once.

Relative and absolute cell references

Cell references are the single most important idea for beginners to get right, because they control what happens when you copy a formula. By default, references are relative: if you write =A1*B1 in row 1 and drag it down, row 2 becomes =A2*B2 and row 3 becomes =A3*B3. Excel shifts the references to match each new row, which is usually exactly what you want.

Sometimes, though, you want one reference to stay fixed — for instance, when every row multiplies by the same GST rate sitting in cell E1. You lock a reference by adding dollar signs: $E$1 never moves when copied. This is called an absolute reference. You can also lock just the column ($E1) or just the row (E$1), which is known as a mixed reference. Pressing the F4 key while editing a reference cycles through the options, so you do not have to type the dollar signs by hand.

The order Excel does the maths

Excel follows a fixed order of operations, so a long formula does not always calculate left to right. According to Microsoft, the order from first to last is: references, negation (a minus sign that makes a number negative), percentages, exponents, then multiplication and division, then addition and subtraction, then text joining with &, and finally comparisons. When two operators share the same rank, Excel works left to right.

That means =5+2*3 returns 11, not 21, because the multiplication happens first. To force a different order, wrap the part you want calculated first in brackets: =(5+2)*3 returns 21. When a result looks wrong, missing or mismatched brackets are the usual culprit.

The essential Excel functions every beginner should know

These are the functions that cover the overwhelming majority of everyday spreadsheet tasks. The table below is a quick reference; the sections underneath explain the ones worth a closer look.

SUM, AVERAGE and the counting functions

SUM is the function almost everyone learns first. =SUM(B2:B20) adds everything in the range. AVERAGE works the same way — =AVERAGE(B2:B20) gives the mean. For totals and averages you can also select the cells and read the figure off the status bar at the bottom of the window, but a formula keeps the result on the sheet and updates it live.

The counting functions answer “how many?”. COUNT counts only cells that contain numbers, while COUNTA counts any non-empty cell, including text. So =COUNT(B2:B20) tells you how many rows hold a number, and =COUNTA(A2:A20) tells you how many names are in a list. MIN and MAX return the smallest and largest values in a range — handy for finding the cheapest supplier or the busiest day.

IF: making Excel decide

IF is the function that turns a spreadsheet into a decision-maker. It checks a condition and returns one thing if the condition is true and another if it is false. The structure is =IF(test, value if true, value if false).

For example, =IF(B2>=50, “Pass”, “Fail”) writes “Pass” when the score in B2 is 50 or more and “Fail” otherwise. Text inside a formula must be wrapped in double quotation marks so Excel treats it as words, not a function name. You can nest IF statements for more than two outcomes, but once you need several bands it is cleaner to use the IFS function (available from Excel 2019 onwards), which lets you list condition-and-result pairs without piling brackets on top of each other.

SUMIF and COUNTIF: totals with a condition

SUMIF and COUNTIF are IF applied to a whole range. SUMIF adds only the values that meet a condition: =SUMIF(C2:C100, “Auckland”, D2:D100) totals the sales in column D, but only for rows where column C says “Auckland”. COUNTIF counts the matching rows instead of adding them: =COUNTIF(C2:C100, “Auckland”) tells you how many Auckland sales there were. Their plural cousins, SUMIFS and COUNTIFS, let you apply several conditions at once — for example, Auckland sales above $500 in a particular month.

VLOOKUP and XLOOKUP: finding matching data

Lookup functions fetch a value from a table by searching for a matching entry — for instance, pulling a product price from a price list by typing its code. VLOOKUP is the long-standing tool for this and is still worth knowing because you will meet it in countless existing spreadsheets. Because it is fiddly for newcomers (it searches only left to right and breaks if you insert a column), we cover it in full in our dedicated guide to how to use VLOOKUP in Excel.

XLOOKUP is the modern replacement. It is simpler, searches in any direction, and returns a friendly result instead of an error when nothing is found. The catch is availability: Microsoft lists XLOOKUP as working in Microsoft 365, Excel 2021 and Excel 2024, but not in Excel 2016 or 2019. If you share a workbook that uses XLOOKUP with someone on an older version, the formula will not calculate for them, so VLOOKUP remains the safer choice for files that travel widely.

Text and date functions

Text functions tidy and combine words. The ampersand joins text directly — =A2&” “&B2 merges a first name and surname with a space between them. CONCAT and TEXTJOIN do the same job for longer lists, with TEXTJOIN able to drop a separator such as a comma between every item automatically. LEFT, RIGHT and MID pull out part of a cell’s text; for example =LEFT(A2,3) returns the first three characters. CONCAT, TEXTJOIN and the IFS function all arrived in Excel 2019, so they are present in Excel 2019, 2021, 2024 and Microsoft 365.

Date functions keep a sheet current. =TODAY() inserts today’s date and refreshes every time the file opens, while =NOW() adds the date and time. Because Excel stores dates as numbers, you can do arithmetic with them — =A2-TODAY() counts the days until a due date in A2. ROUND controls decimal places: =ROUND(B2,2) rounds to two places, which matters for currency so totals do not drift by a cent.

A worked example: a simple GST sheet

Putting a few functions together shows how quickly a formula sheet becomes useful. Imagine a list of sale amounts (excluding GST) in cells B2 to B13. In New Zealand, GST has been 15% since 1 October 2010, so:

• GST on each row: =B2*0.15, copied down the column. Because B2 is a relative reference, each row multiplies its own amount.
• GST-inclusive price: =B2*1.15 adds the 15% in one step.
• Total sales for the period: =SUM(B2:B13).
• Total GST to pass on to Inland Revenue: =SUM(B2:B13)*0.15.
• Working backwards from a GST-inclusive figure in C2: =C2/1.15 gives the amount before GST, and =C2-C2/1.15 gives the GST portion.

The same formulas work in a free spreadsheet too. If you prefer a no-cost tool, OpenOffice Calc uses almost identical function names and syntax, and the formulas transfer across with little change. The approach is also close to what we describe in our guide to Google Sheets formulas, which is handy if your team works in the browser.

Newer Excel functions worth knowing

If you are on Microsoft 365 or Excel 2024, several newer functions can replace older, clumsier techniques. Dynamic arrays let a single formula “spill” results across many cells automatically — FILTER returns every row that matches a condition, SORT reorders a range, and UNIQUE strips out duplicates, each without copying a formula down by hand. GROUPBY and PIVOTBY, which Microsoft began rolling out in 2024, summarise data the way a PivotTable does but as a live formula that updates on its own.

These are powerful, but check availability before you rely on them. Microsoft positions GROUPBY, PIVOTBY and the full dynamic-array set in Microsoft 365 and Excel 2024; older perpetual versions either lack them or show an error. Microsoft also ended support for Office 2016 and Office 2019 on 14 October 2025, so if you are still on one of those, upgrading is worth considering for security as much as for new functions.

Common Excel formula mistakes to avoid

Most beginner frustration comes from a short list of avoidable errors. Knowing what Excel’s error codes mean turns a baffling red flag into a quick fix.

• #DIV/0! — you divided by zero or by an empty cell. Wrap the formula in IFERROR, for example =IFERROR(A2/B2, 0), to show a tidy value instead.
• #VALUE! — the formula is doing maths on something that is not a number, such as text that looks numeric. Check that your inputs are real numbers, not numbers stored as text.
• #N/A — a lookup found no match. Check spelling and stray spaces, or switch to XLOOKUP, which lets you set a “not found” message.
• #NAME? — Excel does not recognise a function name, usually a typo (such as SUU instead of SUM) or a function your version does not have.
• #REF! — a reference points at a cell you have since deleted. Undo the deletion or rebuild the reference.
• Circular reference warning — a formula refers to its own cell, directly or through a chain. Trace it back and point it at the right cells.

Two subtler traps catch people out. First, forgetting the equals sign: without it, Excel treats SUM(A1:A5) as plain text and shows the words, not a total. Second, the order of operations surprise — because Excel ranks negation above exponents, =-2^2 returns 4, not -4. When in doubt, add brackets to make your intention explicit. Finally, lock the references that should not move with dollar signs before you copy a formula, or the results will quietly shift out of line.

Do you need to pay for Excel to use formulas?

No. Formulas are a standard spreadsheet feature, not a premium add-on, so you can learn and use every function above without a paid Excel licence. There are three main free routes. Excel for the web is Microsoft’s free browser version and supports the core functions; our guide to Excel online free in New Zealand explains what it can and cannot do. Free desktop suites such as LibreOffice Calc and OpenOffice Calc run the same formulas offline and open .xlsx files. Google Sheets offers the same functions in the browser with live collaboration. The formula knowledge you build in one of these carries across the others almost unchanged — the function names and logic are shared, with only occasional differences in the newest features.

If you are comparing suites before committing, it helps to know how the apps line up on compatibility and cost. Our comparison of LibreOffice vs Microsoft Office walks through where free tools match Excel and where they fall short, so you can pick the option that fits how you actually work.

Frequently Asked Questions (FAQ)

What is the difference between a formula and a function in Excel?

A formula is any calculation you type into a cell after an equals sign, such as =A1+A2. A function is a named, built-in shortcut for a common calculation, such as SUM or AVERAGE, that you use inside a formula. Every function is part of a formula, but not every formula contains a function.

How do I start a formula in Excel?

Click the cell where you want the answer, type an equals sign (=), then build your calculation using numbers, cell references and operators or a function name. Press Enter to see the result. The cell shows the answer, while the formula bar at the top shows the formula you entered, which is where you edit it later.

What are the most useful Excel formulas for beginners?

SUM, AVERAGE, COUNT, MIN and MAX cover basic maths and summaries. IF handles decisions, while SUMIF and COUNTIF total or count rows that meet a condition. VLOOKUP or the newer XLOOKUP fetch matching data from a table. Learning these ten or so functions is enough for the majority of everyday spreadsheet tasks.

Why does my Excel formula show an error like #VALUE! or #N/A?

Each error code points to a specific problem: #VALUE! means the formula is doing maths on text; #N/A means a lookup found no match; #DIV/0! means you divided by zero; #NAME? means a function name is misspelt; and #REF! means a referenced cell was deleted. Wrapping a formula in IFERROR can replace the code with a tidy value.

Can I use Excel formulas for free?

Yes. Formulas are a standard feature, not a paid extra. You can use them free in Excel for the web, in free desktop suites such as LibreOffice Calc and OpenOffice Calc, and in Google Sheets. The function names and logic are largely shared, so skills learned in one tool transfer to the others with only minor differences.