Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 29 ยท Formulas and Functions

Your First Formulas

Write your first Google Sheets formulas: the equals sign, operators, order of operations, cell references and the first error messages you will meet.

A formula is what turns a table of numbers into a calculating machine. Type = in a cell, followed by an expression, and Sheets shows the result and recomputes it whenever the inputs change. A total that updates when you correct one price is the reason spreadsheets exist.

In this lesson you will write formulas with operators and cell references, learn the order in which Sheets calculates, and meet the first error messages. The real formula engine is Google's and runs in your browser. The TryIt blocks build a tiny evaluator stand-in in JavaScript so you can see how an expression becomes a number. It handles only a few operators and is not Google's code.

The equals sign and operators

Every formula starts with =. Without it, Sheets stores what you typed as text. After the sign, write numbers, operators and parentheses, as in =2+3*4.

The arithmetic operators

Add. =5+2 gives 7
Subtract. =5-2 gives 3
Multiply. =5*2 gives 10
/ Divide. =5/2 gives 2.5
1 / 2

Spaces around operators are fine. Sheets also has comparison operators (=, <>, <, >, <=, >=), which return TRUE or FALSE. You will use them in IF, AND, OR and IFS.

Order of operations

Sheets follows the rules you learned in school: parentheses first, then powers, then multiplication and division from left to right, then addition and subtraction from left to right. So =2+3*4 is 14, and =(2+3)*4 is 20. When in doubt, add parentheses; they cost nothing and make your intent clear to the next reader.

JS A tiny formula evaluator

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice =10-4-3 is 3, not 9: subtraction goes from left to right. Hand-checking formulas this way, as the evaluator does, is a useful debugging habit.

Cell references

The real power comes from references: instead of typing 12.5*8, write =B2*C2. The formula now reads the current values of those cells. Edit B2 and the result updates. You can mix references with numbers (=B2*1.2 adds 20%) and reference cells on other tabs, covered in Relative, Absolute and Mixed References.

The easy way to build a reference is to type =, then click the cell. Sheets inserts the address and colors the cell border to match the address in the formula, which makes complex formulas easier to read. Press Enter when done.

A cell without content counts as 0 in arithmetic, and as an empty string in joins. Text that looks like a number, such as "3", converts to 3. Text that doesn't, such as "abc", makes #VALUE!.

JS Formulas that read cells

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line shows the key behavior: change an input and every dependent formula gives the new result. The stand-in recalculates on each call, whereas Sheets keeps track of which cells depend on which and recalculates only what changed.

Errors you will see first

A broken formula shows an error code in the cell. The four you will see in the first week:

Error Meaning Typical cause
#DIV/0! Division by zero =A1/B1 where B1 is 0 or empty
#VALUE! Wrong kind of value Adding text such as "abc" to a number
#NAME? Sheets does not recognise a word A typo like =SUMM(A1:A3), or text without quotes
#REF! The reference is no longer valid A deleted row or column the formula used

Hover over the red-cornered cell for an explanation. Two other cases: if your formula is incomplete, Sheets may show #ERROR! ("formula parse error"), and a formula that refers to itself causes a circular dependency. All of these are explained in Understanding Formula Errors.

JS Reading the first errors

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The remedy is nearly always the same: read the message, then inspect the cells the formula points to.

Good habits from day one

  • Do not type numbers into formulas when they might change. Put a tax rate in its own cell and reference it, so one edit updates everything.
  • Check results with a calculator on two or three rows before trusting a sheet.
  • Press Ctrl and the backtick key to show all formulas at once when auditing.
  • Keep inputs, calculations and results visibly separate, with labels and formatting (Formatting Cells).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does =2+3*4 return?

    Multiplication comes before addition: 3*4 is 12, plus 2 is 14.
  2. 2. Which entry is stored as text rather than as a formula?

    Without a leading equals sign, Sheets does not treat the entry as a formula.
  3. 3. What error does =A1/B1 show when B1 is empty?

    An empty cell counts as zero in division, so Sheets reports #DIV/0!.
  4. 4. Why is =B2C2 better than =12.58?

    A reference reads the cell's current value, so edits flow through to the result.

Summary

  • A formula starts with = and combines numbers, operators, parentheses and cell references.
  • Order of operations: parentheses, powers, * and /, then + and -, left to right.
  • References make results update when inputs change; click a cell to insert its address.
  • The first errors are #DIV/0!, #VALUE!, #NAME? and #REF!; read the message and check the inputs.
  • Keep changeable numbers in cells, not inside formulas.

Next: Relative, Absolute and Mixed References