Spreadsheet Practice

Google Sheets lesson 1 of 11, Sheets essentials

Formulas in Google Sheets

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

FreeSheets mode8 minutes, 3 exercisesSUMAVERAGECOUNTAIFB2:B

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

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

=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

  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.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

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.