Google Sheets Formulas: A Complete Guide to Spreadsheet Functions

Google Sheets formulas turn a plain grid of numbers into a tool that calculates, checks and reorganises data for you automatically. For households, tradespeople, small businesses and students across New Zealand, learning a handful of these formulas is the quickest way to stop retyping totals by hand and let the spreadsheet do the arithmetic. Whether you are an Auckland landlord working out rental yields, a Christchurch retailer tracking weekly sales, a Wellington sole trader setting aside GST, or a Dunedin student crunching lab data, the same core functions apply.

This guide explains what a formula actually is, how it is built, and which functions matter most in day-to-day use. It covers maths and logic functions, data lookups with VLOOKUP and XLOOKUP, text clean-up, array and database tools, New Zealand GST calculations, the most common formula errors and how to fix them, and the privacy rules that apply when your data lives in the cloud. Every term is explained the first time it appears, so you can follow along even if today is your first time in a spreadsheet.

What are Google Sheets formulas, and are they free?

A Google Sheets formula is an instruction you type into a cell that tells the app to calculate a result rather than just store text. Google Sheets is free to use with any standard Google account, and every function described here is included at no cost — there is no separate licence or add-on to buy. Paid Google Workspace plans add admin controls, more storage and support, but they do not unlock extra formulas.

It helps to separate two words that are often used interchangeably. A formula is the whole expression you type, and it always begins with an equals sign, for example =B2+B3. A function is a named, pre-built calculation inside the app — such as SUM, IF or VLOOKUP — that you call from within a formula to do a specific job. In short: you write formulas, and those formulas use functions.

How a Google Sheets formula is structured

Every formula follows the same simple pattern, and getting that pattern right prevents most beginner errors. Learn the three parts below once and you can read almost any formula you meet.

The equals sign and function syntax

Every formula must start with an equals sign (=). That character is what tells Google Sheets to treat what follows as a calculation instead of plain text. After the equals sign comes the function name — for example SUM — followed immediately by a pair of round brackets. The values you put inside the brackets are called arguments: these can be numbers, text in quotation marks, a single cell reference such as B4, or a range of cells joined by a colon such as C2:C25. A complete example is =SUM(C2:C25), which adds every number from cell C2 down to C25.

Relative and absolute cell references

Cell references decide what happens when you copy a formula to other cells. By default Google Sheets uses relative references: if you copy =A2*B2 down one row, it automatically becomes =A3*B3, which is usually what you want. Sometimes, though, you need one cell to stay fixed — for example a single GST rate or exchange rate sitting in cell B1 that every row should point to. To lock it, add dollar signs before the column letter and row number, giving an absolute reference like $B$1. A formula such as =A2*$B$1 keeps multiplying each row by the one fixed rate even as you fill it down a long table.

Essential maths functions for everyday use

A small set of maths functions covers most household budgets, freelance tracking and small-business admin. Instead of adding figures on a calculator and typing the answer, these functions read whole ranges of cells and update the moment the underlying numbers change. If you are building a running total of expenses, these are the functions to start with.

• =SUM(range) — adds every number in a range. Example: =SUM(D2:D50) totals a column of expenses.
• =AVERAGE(range) — returns the arithmetic mean of the selected cells, handy for a typical monthly power bill.
• =COUNT(range) — counts only the cells that contain numbers, ignoring blanks and text.
• =COUNTA(range) — counts every cell that is not empty, so you can tally client rows regardless of format.
• =ROUND(value, places) — trims decimals to a set number of places, for example =ROUND(A2, 2) for clean dollar-and-cent amounts.

Calculating 15% GST in New Zealand

New Zealand’s Goods and Services Tax (GST) is charged at 15%, and a few small formulas handle it reliably. Businesses must register for GST once their taxable turnover passes NZD 60,000 in any rolling 12-month period — not $90,000, a figure that is sometimes quoted in error. The $60,000 test looks both backwards (turnover over the past 12 months) and forwards (turnover you reasonably expect over the next 12 months), so it is worth tracking in your sheet.

To pull the GST portion out of a GST-inclusive total in cell B2, use =ROUND(B2*15/115, 2). To add GST to a GST-exclusive price, use =ROUND(B2*0.15, 2), and to turn an exclusive price into the inclusive total use =ROUND(B2*1.15, 2). If you issue invoices as well, you can see how these figures sit on a compliant document in our Google Docs GST invoice template guide.

Logical functions that automate decisions

Logical functions let a spreadsheet make simple decisions for you, which is what turns a flat list into something that flags problems on its own. The core tool is the IF function: it checks whether a condition is true, then shows one result if it is and a different result if it is not. For example, =IF(B2>30, “Overdue”, “OK”) reads a number of days in B2 and labels the row accordingly.

For a property manager chasing late accounts or a shop owner sorting sales by category, logical functions remove the need to scan rows by eye. The IF family also includes targeted counting and summing versions so you can total or tally cells only when they meet a rule.

• =IF(test, value_if_true, value_if_false) — shows one of two outcomes based on a condition.
• =SUMIF(range, criterion, sum_range) — adds values only where they match a rule, such as totalling rows marked “Petrol”.
• =COUNTIF(range, criterion) — counts cells that match a condition, for example how many tasks are marked “Late”.
• =IFS(condition1, value1, condition2, value2, …) — checks several conditions in order without nesting many IF statements.
• =AND(…) / =OR(…) — combine conditions so a result needs either all of them (AND) or just one (OR) to be true.

Looking up data: VLOOKUP versus XLOOKUP

Lookup functions find a value in a large table so you do not have to scroll and search by hand. If you keep a price list, a membership roll or a product catalogue, a lookup pulls the right detail into place in an instant. For years the standard was VLOOKUP, a vertical lookup that searches the first column of a table and returns a value from a column to its right.

Google Sheets added the newer XLOOKUP function in August 2022, and for most new work it is the better choice. XLOOKUP can search in any direction, does not break when you insert a column, matches exactly by default, and has a built-in slot for the message to show when nothing is found. The table below compares the two so you can pick the right one.

Cleaning and combining text

Text functions tidy messy data so lookups and filters work properly. Lists imported from forms, old accounting tools or other systems often arrive with stray spaces, inconsistent capitals or names split across columns, and those small inconsistencies quietly break matching. A sports club compiling a member directory, for instance, can normalise hundreds of entries in seconds rather than editing each by hand.

• =TRIM(cell) — removes extra leading, trailing and double spaces that cause lookup mismatches.
• =PROPER(cell) — capitalises the first letter of each word and lowercases the rest.
• =TEXTJOIN(separator, TRUE, range) — merges several cells into one, with your chosen separator (CONCATENATE still works for simpler joins).
• =SPLIT(text, separator) — breaks one cell into several based on a divider such as a comma or space.
• =LOWER(cell) / =UPPER(cell) — force text fully into lower or upper case.

Array formulas and dynamic data tools

Array and database functions apply one instruction to a whole range at once, which saves dragging a formula down thousands of rows. The headline tool is ARRAYFORMULA: wrap it around an ordinary calculation and you only type the formula in the top cell, while the result fills the column automatically and the file stays lean. The functions below extend this idea into sorting, filtering and querying.

• =ARRAYFORMULA(expression) — runs a formula across a whole range from a single cell.
• =FILTER(range, condition1, …) — shows only the rows that meet your conditions.
• =SORT(range, column, ascending) — reorders rows without changing the original data.
• =UNIQUE(range) — strips duplicates to leave one of each value.
• =QUERY(data, “query string”) — runs a SQL-like question to filter, group and summarise data in one step.
• =IMPORTRANGE(“url”, “sheet!range”) — pulls live data from another Google Sheets file so a master report stays current.

If you would rather start from ready-made layouts than build from scratch, our free NZ budget template and Google Sheets calendar guide show these functions working inside real sheets.

Fixing common formula errors

When a formula fails, Google Sheets shows a short error code that points to the cause, so the fix is usually quick once you know what each one means. The codes appear in the cell itself; hovering over the small marker in the corner expands a note explaining the problem. Most errors come from simple slips — a typo, a missing bracket, or maths run on text instead of numbers. The table below lists the ones you will see most often.

Wrapping formulas in IFERROR

IFERROR keeps dashboards and reports looking tidy by replacing an error with a message you choose. Wrap a lookup or division inside it and, if the calculation fails, the cell shows a blank, a zero or a note like “Not found” instead of a raw error code. For example, =IFERROR(VLOOKUP(A2, D:E, 2, FALSE), “Not found”) returns a readable message whenever the lookup draws a blank, which is far clearer for anyone else reading the sheet.

Common mistakes to avoid

Most formula problems trace back to a few recurring habits rather than anything advanced. Watching for the mistakes below will save far more time than learning another function.

• Forgetting the equals sign — without a leading =, the cell shows your text instead of calculating it.
• Mismatched brackets — every opening bracket needs a closing one; long formulas often fail on a single missing “)”.
• Numbers stored as text — values imported with spaces or symbols will not add up; clean them with TRIM or VALUE first.
• Using relative references where you need absolute — a rate that should stay fixed will drift down the column unless you lock it with $ signs.
• Hard-coding GST or prices inside a formula — put rates in their own cell and reference them, so one edit updates every row.
• Deleting columns a formula depends on — this produces #REF! errors; check what relies on a column before removing it.

Privacy, security and record-keeping in New Zealand

Because Google Sheets stores and processes your data on Google’s servers rather than your own computer, privacy and security settings matter for any file that holds personal or financial information. Under New Zealand’s Privacy Act 2020 — in force since 1 December 2020 and overseen by the Office of the Privacy Commissioner — organisations are responsible for keeping the personal information they hold secure, and the Act can apply to overseas providers handling New Zealanders’ data. The practical steps below reduce the risk of a breach.

• Turn on two-step verification — this is the single most effective protection for your account, requiring a second code at sign-in.
• Share to named people, not “anyone with the link” — restrict sensitive files to specific email addresses.
• Review edit versus view access — give most people view-only rights and keep editing to those who need it.
• Protect key ranges — right-click a range, choose “Protect range”, and set who may change your formulas.
• Keep records for seven years — the IRD requires GST and business records to be retained for at least seven years, and cloud sheets are accepted as valid records.

For a deeper look at account protection across Google and Microsoft tools, our guide to secure Office 365 sign in covers multi-factor authentication in more detail.

Working with formulas offline

Google Sheets can keep working without an internet connection if you switch on offline access first, which matters for anyone doing fieldwork beyond reliable coverage. When offline access is enabled, the app saves a local copy of your recent files to your browser, letting you open and edit them and recalculate formulas as normal; changes sync automatically once you reconnect. Two limits are worth knowing: IMPORTRANGE and other functions that pull live data from the web will not refresh while you are offline, and if two people edit the same file offline their changes merge only when both reconnect. Keeping reference tables in the same workbook avoids most offline surprises.

Who Google Sheets formulas suit, and how to start

Google Sheets suits anyone who wants capable, collaborative spreadsheets without paying for desktop software — sole traders, clubs, students and growing teams in particular. To begin, open a blank sheet, type a simple =SUM() over a column of numbers, then build up to IF, a lookup and IFERROR as your needs grow. If you prefer an offline, installed suite instead, compare the free options in our roundup of OpenOffice alternatives; both approaches use the same formula logic described here.

Frequently asked questions (FAQ)

Are Google Sheets formulas free to use in New Zealand?

Yes. Every formula and function in Google Sheets is free with a standard Google account, including SUM, IF, VLOOKUP, XLOOKUP, QUERY and the array functions. Paid Google Workspace plans add storage, admin controls and support, but they do not unlock additional formulas.

What is the difference between a formula and a function?

A formula is the whole expression you type into a cell, and it always starts with an equals sign, such as =B2+B3. A function is a named, pre-built calculation inside Google Sheets — like SUM, IF or VLOOKUP — that you call from within a formula to perform a specific task.

How do I extract the 15% GST from a total price?

To pull the GST portion out of a GST-inclusive total in cell B2, type =ROUND(B2*15/115, 2). This isolates the tax at New Zealand’s 15% rate and rounds the result to two decimal places. To add GST to a GST-exclusive price instead, use =ROUND(B2*0.15, 2).

Why does my cell show a #NAME? error?

The #NAME? error almost always means the function name is misspelled or text is missing its quotation marks. For example, typing =SUMM(B2:B15) instead of =SUM(B2:B15) triggers it. Check the spelling of the function and make sure any text values sit inside straight quotation marks.

Do my formulas still work when I am offline?

Yes, provided you turn on offline access before you lose connection. Google Sheets then saves a local copy of recent files and recalculates formulas normally while you work, syncing your changes when you reconnect. Functions that fetch live web data, such as IMPORTRANGE, will not update until you are back online.