# Formulas in Google Sheets (Google Sheets lesson 1 of 17)

> Sheets formulas work like Excel's, plus one habit worth learning early: ranges with no last row.

Source: https://spreadsheetpractice.com/google-sheets/learn/formulas-in-google-sheets · Updated October 29, 2026

- **Course:** [Google Sheets course](https://spreadsheetpractice.com/google-sheets), track "Sheets essentials"
- **Time:** about 8 minutes, 3 graded exercises
- **Functions:** `SUM`, `AVERAGE`, `COUNTA`, `IF`, `B2:B`
- **Access:** free
- **Key formula:** `=SUM(D2:D)`: Add everything from D2 down, including rows added later

## How it works

A formula in Google Sheets starts with `=` and points at cells, just as in Excel. As you type a function name, Sheets lists the functions that match and shows the arguments each one expects, so you don't need to memorize the order.

Sheets lets you leave the row number off the end of a range. `B2:B` means "from B2 to the bottom of the sheet", so `=SUM(B2:B)` keeps counting new rows as you add them. A closed range like `B2:B9` stops at row 9, and the total quietly misses row 10. Excel users usually get the same effect with a whole column such as `B:B` or with a table.

SUM, AVERAGE and COUNTA skip empty cells, so the blank rows at the bottom of an open range don't change the answer. `AVERAGE(B2:B)` divides by the cells that hold numbers, not by the length of the column.

Keep the formula out of the range it reads. `=SUM(B2:B)` typed into B20 would include its own cell, and Sheets reports a circular dependency. Put totals in a different column, or above the data.

## Syntax

| Formula | What it does |
|---|---|
| `=SUM(D2:D)` | Add everything from D2 down, including rows added later |
| `=AVERAGE(D2:D)` | Mean of the numbers in the column |
| `=COUNTA(A2:A)` | How many rows have an entry |
| `=IF(C2>=25,"Large","Standard")` | One result if the test passes, another if it fails |
| `=SUM(D2:D9)` | A closed range: stops at row 9 |

## Watch out for

- A closed range such as D2:D7 ignores row 8 when someone adds it. Use D2:D when the list will grow.
- Don't put =SUM(D2:D) inside column D. The range would include its own cell, and Sheets reports a circular dependency.
- Typing the numbers you see (=86.4+204) is right once and wrong after the next edit.
- Depending on the spreadsheet's locale, Sheets may expect semicolons instead of commas between arguments.

## The exercises

Solve them in the live grid at [Formulas in Google Sheets](https://spreadsheetpractice.com/google-sheets/learn/formulas-in-google-sheets). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **A total that grows.** In G2, add up the order totals in column D. Use an open-ended range that starts at D2, so orders added below row 7 are counted too.
2. **Average order and order count.** In G3, find the average order total from column D. In G4, count the orders by counting the order numbers in column A. Use open-ended ranges starting at row 2 for both.
3. **Flag the large orders.** In E2, show "Large" when the order has 25 or more items, and "Standard" otherwise. Fill the formula down to E7.

Next: [COUNTIF, SUMIF and the IFS versions](https://spreadsheetpractice.com/google-sheets/learn/countif-sumif)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
