Google Sheets Formulas: A Practical Guide for New Zealand Users

A formula is what turns a Google Sheets file from a static table into a calculator that updates itself. Type an instruction into a cell, and the sheet works out totals, checks conditions, looks up prices and cleans up messy text automatically every time the underlying numbers change. For a Wellington sole trader tracking provisional tax, an Auckland landlord comparing rental yields, a Christchurch retailer watching weekly sales or a Dunedin student crunching lab data, learning a handful of formulas is the fastest way to save hours of manual work.

This guide explains how Google Sheets formulas are structured, walks through the functions most people actually use, and shows worked examples aimed at New Zealand situations — including the correct way to calculate 15% GST. It also covers the mistakes that trip up beginners, how the Privacy Act 2020 affects cloud spreadsheets, and how formulas behave when you lose your internet connection. The concept of the spreadsheet itself dates back decades; you can read the background on Wikipedia.

How a Google Sheets formula is built

Every formula starts with an equals sign (=). The equals sign is the signal that tells Google Sheets to calculate rather than simply display text. After it, you either write a direct calculation such as =B2+B3, or you call a function — a ready-made command like SUM or IF — followed by round brackets.

Whatever you put inside the brackets is called the arguments: the inputs the function works on. An argument can be a number, a piece of text in double quotation marks (“Paid”), a single cell such as B4, or a range of cells joined by a colon such as C2:C25. For example, =SUM(C2:C25) adds every number from cell C2 down to C25. Function names are not case-sensitive, but they are conventionally written in capitals so formulas are easier to read.

Relative and absolute cell references

Cell references decide what happens when you copy a formula. By default Google Sheets uses relative references: copy =A2*B2 down one row and it automatically becomes =A3*B3, which is usually what you want. Sometimes, though, you need a reference to stay fixed — for example when every row must multiply by one GST rate stored in a single cell. Adding dollar signs locks it: $B$1 always points at B1 no matter where you copy the formula. This is called an absolute reference, and it is the single most common fix for formulas that “drift” and produce wrong answers after a copy-and-paste.

Essential maths functions for everyday admin

Most day-to-day spreadsheet work rests on a small set of arithmetic functions. Instead of adding receipts on a calculator, these functions total, average and count entire ranges instantly and keep updating as you add rows. They need no programming knowledge — if you can select a range of cells, you can use them.

• =SUM(range) — adds every number in a range, e.g. =SUM(D2:D50) for total sales.
• =AVERAGE(range) — the arithmetic mean of a range, useful for average monthly power or fuel costs.
• =COUNT(range) — counts how many cells contain numbers, ignoring blanks and text.
• =COUNTA(range) — counts every cell that is not empty, so it works for lists of names or invoice numbers too.
• =ROUND(value, places) — rounds a number to a set number of decimal places, e.g. =ROUND(A2, 2) for clean dollar-and-cents amounts.

If you are building a home budget from scratch, a ready-made free NZ budget template gives you a working example of these functions already wired together.

Calculating GST in Google Sheets

New Zealand GST is charged at 15%, and a business must register for GST once its taxable turnover passes $60,000 over any rolling 12-month period (this threshold has been unchanged since 2009 — note it is $60,000, not $90,000). Once you are registered, two simple formulas handle almost all of your GST maths.

• Extract GST from a GST-inclusive price in cell B2: =ROUND(B2*15/115, 2). If B2 holds $115, this returns $15.
• Add GST to a GST-exclusive price in cell B2: =ROUND(B2*0.15, 2). If B2 holds $100, this returns $15, and =B2+ROUND(B2*0.15, 2) gives the $115 total.

Wrapping both in ROUND keeps your figures to two decimal places so totals match what you actually invoice. These formulas help you prepare, but they are a convenience, not tax advice — confirm your obligations with Inland Revenue or an accountant.

Logical functions that make decisions for you

Logical functions turn a flat list into something that reacts to its own data. The core one is IF, which checks a condition and returns one result if it is true and another if it is false. For instance, =IF(C2>30, “Overdue”, “Current”) reads the number of days in C2 and labels each invoice automatically. A property manager or accounts clerk no longer has to scroll through rows hunting for late payments.

• =IF(test, value_if_true, value_if_false) — the basic true/false decision.
• =SUMIF(range, criterion, sum_range) — adds numbers only where a condition is met, e.g. total spend tagged “Fuel”.
• =COUNTIF(range, criterion) — counts cells matching a condition, e.g. how many tasks are marked “Late”.
• =IFS(condition1, value1, condition2, value2, …) — tests several conditions in order without deeply nesting IF statements.
• =AND(…) / =OR(…) — combine conditions so that all, or just one, must be true before a result is returned.

Looking up data: VLOOKUP and XLOOKUP

Lookup functions find a value in one place and return matching information from another — for example, type a product code and pull back its price. For years the standard tool was VLOOKUP, which searches down the first column of a table. Google Sheets added the more flexible XLOOKUP in August 2022, and for new work it is usually the better choice. The table below shows why.

A basic VLOOKUP looks like =VLOOKUP(A2, Products!A:C, 3, FALSE) — find the code in A2 within the Products sheet and return the value from the third column. The XLOOKUP equivalent is =XLOOKUP(A2, Products!A:A, Products!C:C, “Not found”), which is easier to read and safer to maintain.

Cleaning up text with string functions

Data imported from other systems or web forms often arrives messy — stray spaces, inconsistent capitalisation, names crammed into one cell. Text functions tidy it up so filters and lookups work reliably. A common problem is a lookup that fails because a cell has a hidden trailing space; TRIM removes it in one step.

• =TRIM(cell) — removes leading, trailing and repeated spaces.
• =PROPER(cell) — capitalises the first letter of each word, e.g. “jane smith” becomes “Jane Smith”.
• =LOWER(cell) / =UPPER(cell) — force text to all lower or all upper case.
• =CONCATENATE(a, b) or =a&” “&b — join text from several cells into one.
• =SPLIT(text, delimiter) — break one cell into several, e.g. split a full name at the space.

Array and database functions for larger sheets

When a sheet holds thousands of rows, applying a formula line by line is slow and clutters the file. Array and database functions calculate whole ranges at once and pull out exactly the rows you want. ARRAYFORMULA lets you write a calculation once in the top row and have it apply down an entire column automatically, so new rows are covered without dragging anything down.

• =ARRAYFORMULA(expression) — applies one formula across a whole range from a single cell.
• =FILTER(range, condition) — shows only the rows that meet your criteria.
• =SORT(range, column, ascending) — sorts data without changing the original.
• =UNIQUE(range) — returns a list with duplicates removed.
• =QUERY(data, “select …”) — runs SQL-style questions to filter, group and total data in one formula.
• =IMPORTRANGE(url, “sheet!range”) — pulls live data from a separate spreadsheet file.

If you keep recurring dates and schedules in a sheet, these same functions pair well with a Google Sheets calendar layout for planning.

Common mistakes and how to fix error codes

Most formula errors come from small, fixable slips rather than anything complicated — a misspelt function name, a deleted cell, or maths run on text instead of numbers. Google Sheets flags these with short error codes and a red triangle you can hover over for a hint. The table below covers the ones you will meet most often.

Using IFERROR to keep reports tidy

IFERROR is the safety net for formulas that might fail. Wrap a lookup or division in it and, instead of an ugly error code, the cell shows whatever you choose — a zero, a blank, or a note. For example, =IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), “Not found”) returns “Not found” when a code is missing, so a client-facing dashboard stays clean. Add IFERROR after you have confirmed the formula works, not before, so you do not accidentally hide a real problem.

Privacy, security and the Privacy Act 2020

Because Google Sheets stores your data on cloud servers rather than your own hard drive, security deserves attention — especially if your sheets hold customer, payroll or financial information. Under New Zealand’s Privacy Act 2020, any organisation that collects personal information has a legal duty to keep it secure and to use it only for its intended purpose. Using a cloud spreadsheet does not remove that duty; it shares it between the provider’s safeguards and your own habits.

Google encrypts data both while it travels to its servers and while it is stored, but the biggest risk to most small teams is weak account security. The practical steps that matter most are:

• Turn on two-step verification (multi-factor authentication) so a stolen password alone cannot open your account.
• Share files with named email addresses rather than “anyone with the link”, and review sharing settings regularly.
• Use the “Protect range” option to lock formula cells so collaborators cannot overwrite them by accident.
• Check the version history and, on business accounts, the audit logs to see who changed what.

For a deeper look at handling business information responsibly, see our guide to strategic data management.

How formulas behave offline

Google Sheets can keep working without an internet connection, which matters for anyone doing fieldwork in areas with patchy coverage — farms, forestry blocks or the West Coast. You must switch on offline access in advance: with the Google Docs Offline extension installed in Chrome and offline mode enabled in Drive settings, Sheets saves an encrypted copy of your recent files to the browser so you can keep editing and calculating.

The main limitation is functions that fetch live data. IMPORTRANGE, IMPORTDATA and similar functions cannot refresh while you are offline because the source lives on another server, so they show their last cached result until you reconnect. If you know you will be offline, keep the reference tables you need inside the same workbook. Any edits you make sync automatically once you are back online.

How Google Sheets compares to other spreadsheets

Google Sheets is free with any Google account and runs in the browser, which is its main draw for individuals and small teams. Microsoft Excel remains more powerful for very large datasets and specialised financial modelling, while free desktop suites suit people who prefer to keep files on their own machine. The functions in this guide — SUM, IF, VLOOKUP, XLOOKUP and the rest — work almost identically across all of them, so skills transfer easily.

If you would rather work offline in a free desktop program, the open-source alternatives use the ODS spreadsheet format, and our roundup of free and paid office suites compares the main options side by side. If long sessions strain your eyes, you can also switch on dark mode in Google Sheets.

Who Google Sheets formulas suit

Formulas reward anyone who works with numbers or lists regularly: sole traders and tradespeople tracking income and GST, small businesses managing stock and invoices, clubs and community groups keeping membership records, and students handling data. You do not need to learn them all at once. Start with SUM, AVERAGE and IF, add VLOOKUP or XLOOKUP when you need to match records, and reach for FILTER, QUERY and ARRAYFORMULA as your sheets grow. Because everything recalculates automatically, the time you invest once keeps paying off every time your data changes.

Frequently asked questions

Are Google Sheets formulas free to use in New Zealand?

Yes. Every formula and function in Google Sheets is available free with an ordinary Google account, with no subscription or licence fee. Paid Google Workspace plans add business features such as more storage and admin controls, but the formulas themselves are identical on the free version.

What is the difference between a formula and a function?

A formula is the whole instruction you type into a cell, and it always begins with an equals sign. A function is a ready-made command — such as SUM, IF or VLOOKUP — that you use inside a formula to do a specific job. So =SUM(A1:A10) is a formula that uses the SUM function.

How do I calculate 15% GST from a total price?

To pull the GST out of a GST-inclusive amount in cell B2, use =ROUND(B2*15/115, 2). To add GST to a GST-exclusive amount, use =ROUND(B2*0.15, 2). New Zealand GST is 15%, and businesses must register once taxable turnover passes $60,000 in any 12-month period.

Why does my cell show a #NAME? error?

#NAME? almost always means a function name is misspelt or a text value is missing its quotation marks. For example, typing =SUMM(B2:B15) instead of =SUM(B2:B15) triggers it. Check the spelling against the function list and make sure any text inside the formula is wrapped in double quotes.

Should I use VLOOKUP or XLOOKUP?

For new spreadsheets, XLOOKUP is usually the better choice: it searches in any direction, defaults to exact matches, survives column changes and has a built-in message for when no match is found. VLOOKUP still works well and is fine for simple, stable tables, but it is more fragile when a sheet changes.