Google Sheets formulas: learn to calculate with spreadsheets

Google Sheets is Google's free spreadsheet program that lets you organize data and perform calculations. With formulas you can automatically add, subtract, calculate averages and much more. In this guide you'll learn the basic formulas you need to work effectively with Google Sheets.

What is Google Sheets?

Google Sheets is similar to Microsoft Excel, but it runs entirely online and is free. You can use it to create tables, organize data and perform automatic calculations. Just like Google Docs, your spreadsheets are saved automatically in Google Drive and you can open them from any device.

Why use formulas?

Formulas are the power of a spreadsheet. Instead of calculating by hand, you let Google Sheets do the work. When you change data, all calculations are updated automatically. This saves time and prevents calculation errors.

Getting started with Google Sheets

Opening a spreadsheet

Go to sheets.google.com to open Google Sheets. You'll see your recent spreadsheets and can start a new file by clicking "Blank" or choosing a template.

Understanding the interface

A spreadsheet consists of cells organized in rows (horizontal, numbered 1, 2, 3...) and columns (vertical, with letters A, B, C...). Every cell has a unique address, for example A1 for the cell in column A, row 1.

Creating your first formula

A formula in Google Sheets always starts with an equals sign (=). This tells the program that a calculation follows instead of plain text.

Simple arithmetic operations

You can calculate directly in a cell:

  • Add: =5+3 returns 8
  • Subtract: =10-4 returns 6
  • Multiply: =6*7 returns 42
  • Divide: =20/4 returns 5

But the real power comes when you refer to other cells:

Using cell references

Suppose cell A1 contains the number 10 and cell B1 contains the number 5. You can then type in cell C1:

  • =A1+B1 returns 15
  • =A1-B1 returns 5
  • =A1*B1 returns 50
  • =A1/B1 returns 2

The advantage is that when you change the value in A1 or B1, the result in C1 changes automatically along with it.

The most important basic functions

SUM: adding up multiple cells

The SUM function adds up all the numbers in a range. This is probably the most used function in spreadsheets.

Syntax: =SUM(range)

Examples:

  • =SUM(A1:A10): adds up all values from A1 through A10
  • =SUM(A1:A5, B1:B5): adds two ranges together
  • =SUM(A1, B1, C1): adds up specific cells

Practical example: You have a list of monthly expenses in column A, from A1 to A12. To calculate the total, type in an empty cell: =SUM(A1:A12)

AVERAGE: calculating the average

This function calculates the average of a series of numbers.

Syntax: =AVERAGE(range)

Examples:

  • =AVERAGE(B1:B10): average of B1 to B10
  • =AVERAGE(A1:A100): average of 100 values

Practical example: You want to calculate the average grade of a series of test results in column C. Type: =AVERAGE(C1:C20)

MIN and MAX: lowest and highest value

These functions find the smallest and largest value in a range, respectively.

Syntax:

  • =MIN(range)
  • =MAX(range)

Examples:

  • =MIN(A1:A50): finds the lowest value
  • =MAX(A1:A50): finds the highest value

Practical example: You have sales data per month and want to know which was the best and the worst month.

COUNT: counting cells with numbers

This function counts how many cells in a range contain numbers.

Syntax: =COUNT(range)

Example: =COUNT(A1:A100): counts how many of the 100 cells contain a number

COUNTA: counting non-empty cells

This function counts all cells that are not empty, including text.

Syntax: =COUNTA(range)

Example: =COUNTA(A1:A100): counts all filled cells

Formulas with conditions

IF: making decisions

The IF function performs a test and returns different results depending on whether the test is true or false.

Syntax: =IF(condition, value_if_true, value_if_false)

Note: In the Dutch version of Google Sheets, you use semicolons (;) instead of commas to separate arguments.

Examples:

  • =IF(A1>10, "High", "Low"): if A1 is greater than 10, show "High", otherwise "Low"
  • =IF(B1>=6, "Pass", "Fail"): handy for grade lists

Practical example: You have a list of test results and want to automatically show whether someone passed. Column A contains the grades, and in column B you type: =IF(A1>=5.5, "Passed", "Failed")

SUMIF: adding up with a condition

This function only adds up values that meet a certain condition.

Syntax: =SUMIF(range, condition, sum_range)

Example: =SUMIF(A1:A10, ">100", B1:B10): adds up the values in B where A is greater than 100

Working with text in formulas

CONCATENATE: combining text

This function combines text from multiple cells into one.

Syntax: =CONCATENATE(text1, text2, ...)

Example: If A1 contains "Jan" and B1 contains "Jansen", then =CONCATENATE(A1, " ", B1) returns the result "Jan Jansen"

LEN: counting characters

Counts how many characters a cell contains.

Syntax: =LEN(text)

Example: =LEN(A1): counts the characters in cell A1

Practical tips for formulas

Absolute and relative references

When you copy a formula to other cells, the cell references adjust automatically. This is called a relative reference. Sometimes you want a reference not to change. In that case, use a dollar sign ($) to make the reference absolute:

  • A1: relative (adjusts when copied)
  • $A$1: absolute (always stays A1)
  • $A1: column fixed, row relative
  • A$1: row fixed, column relative

Understanding error messages

Sometimes a formula returns an error. These are the most common:

  • #DIV/0!: you are trying to divide by zero
  • #VALUE!: the formula expects a different type of value
  • #REF!: a cell being referred to no longer exists
  • #NAME?: Google Sheets does not recognize the function name

Entering formulas efficiently

When you start typing a function, Google Sheets automatically shows suggestions. This helps you use the correct syntax. You can also click the function icon (fx) next to the formula bar to browse through all available functions.

Common practical examples

Creating a household budget

  1. Column A: category (Groceries, Rent, Energy, etc.)
  2. Column B: amount per month
  3. At the bottom: =SUM(B:B) for total expenses
  4. Extra cell: =AVERAGE(B:B) for the average per category

Keeping a grade list

  1. Column A: name of the pupil or student
  2. Columns B to F: test grades
  3. Column G: =AVERAGE(B1:F1) for the average grade
  4. Column H: =IF(G1>=5.5, "Pass", "Fail")

A sales overview

  1. Column A: product
  2. Column B: number sold
  3. Column C: price per item
  4. Column D: =B1*C1 for total revenue per product
  5. At the bottom: =SUM(D:D) for total revenue

Frequently asked questions

Why doesn't my formula work, and why do I see the text instead of the result?

This usually happens because you forgot to put the equals sign (=) at the beginning of the formula. Without this sign, Google Sheets treats your input as plain text. Also check that the cell is not formatted as "Plain text": you can change this via Format > Number.

Can I use Dutch function names or do I have to use English?

Google Sheets supports Dutch function names. SOM, GEMIDDELDE, ALS and other functions work in Dutch. Tip: the English names always work too (SUM, AVERAGE, IF). If a Dutch name doesn't work, try the English version.

How do I copy a formula to other cells?

Select the cell with the formula and press Ctrl+C to copy it. Then select the cells where you want to paste the formula and press Ctrl+V. You can also drag the small blue square in the bottom-right corner of the cell down or sideways to quickly copy the formula to adjacent cells.

How can I create a formula that automatically includes new data?

Use a column reference without row numbers, for example =SUM(A:A) instead of =SUM(A1:A100). This makes the formula add up all values in column A, even if you add more rows later. Note: this can slow down your spreadsheet with very large datasets.