Formulas and operators

Introduction

Formulas are what make a spreadsheet more than a table. A formula calculates a result from other cells, and because it refers to the cells rather than copying their values, it recalculates the moment any of them changes. Change a price and every total that depends on it updates at once. This lesson covers how to write formulas, the operators you can use, the order Excel works them out in, and the error values you will meet.

How it works

Every formula starts with an equals sign. After it, you can use numbers, cell references, operators and functions. Instead of typing references, you can click cells while writing the formula: Excel inserts their addresses and colours them so you can see what the formula uses.

The arithmetic operators are + (add), - (subtract), * (multiply), / (divide), ^ (power) and % (percent, so =50*10% gives 5). The text operator & joins text: =A2&" "&B2 joins a first and last name with a space. The comparison operators =, <> (not equal), <, >, <= and >= give a logical value, TRUE or FALSE: =B2>100 is TRUE when B2 is more than 100.

Excel follows an order of operations: percent, then powers, then multiplication and division from left to right, then addition and subtraction, then joining text, then comparisons. So =2+3*4 is 14, not 20; write =(2+3)*4 to add first. One trap: a minus sign at the very start counts as negation and is applied before powers, so =-2^2 gives 4, while =0-2^2 gives -4.

When a formula cannot be worked out, it returns an error value that names the problem:

  • #DIV/0!: division by zero or by an empty cell.
  • #VALUE!: the wrong kind of value, such as adding text that is not a number.
  • #NAME?: Excel does not recognise a name, usually a misspelt function such as =SUMM(A1:A3).
  • #REF!: the formula refers to a cell that no longer exists, often because a row or column was deleted.
  • #N/A: a value is not available, typically from a lookup that found nothing.
  • #NUM!: an impossible number, such as the square root of a negative number.

Errors spread: any formula that uses a cell with an error shows an error too. Fix the first one and the rest usually disappear.

To see every formula on a sheet at once instead of its results, press Ctrl+Grave accent (the key left of 1) on Windows or Control+Grave accent on Mac; press it again to switch back.

Example

An order sheet needs a total and a discount check:

ABCD
1ItemPriceQuantityTotal
2Pen20150
3Notebook6080

Line by line

  1. In D2 type =B2*C2 and press Enter: price times quantity.
  2. Select D2 and double-click the fill handle to copy the formula to D3, where it becomes =B3*C3.
  3. In D4 type =D2+D3 for the order total.
  4. In E4 type =D4>=7500 to check whether the order earns a discount.

Expected result

D2 shows 3000, D3 shows 4800, D4 shows 7800 and E4 shows TRUE. If you change C3 to 50, D3 becomes 3000, D4 becomes 6000 and E4 changes to FALSE, all at once.

In the real world

Budgets, invoices, payroll and school mark sheets all depend on formulas that recalculate when inputs change. Good spreadsheets keep inputs (prices, rates, quantities) in their own cells and refer to them, so that a change of tax rate is one edit rather than a hunt through hundreds of formulas.

Common mistakes

  • Forgetting the equals sign, so Excel stores the formula as text.
  • Typing numbers into formulas, such as =B2*1.15, instead of referring to a cell that holds the rate.
  • Expecting =2+3*4 to give 20; use brackets to control the order.
  • Ignoring the first error in a chain and trying to fix the later ones.

Best practices

  • Refer to cells rather than typing values into formulas.
  • Use brackets whenever the order is not obvious to a reader.
  • Show formulas with Ctrl+Grave accent (Control+Grave accent on Mac) to review a sheet before sharing it.

Keyboard shortcuts

ActionWindowsMac
Show or hide formulasCtrl+Grave accentControl+Grave accent
Edit the active cellF2Control+U
Recalculate all open workbooksF9F9
Insert a functionShift+F3Shift+F3
Expand or collapse the formula barCtrl+Shift+UControl+Shift+U
Select the cells a formula refers to directlyCtrl+[Control+[

Summary

Formulas start with = and calculate from other cells, recalculating when those cells change. Arithmetic, text and comparison operators combine values, following a fixed order that brackets can change. Error values such as #DIV/0!, #VALUE!, #NAME? and #REF! say what went wrong, and fixing the first error in a chain usually fixes the rest.

Quick check

1. What does =2+3*4 return?
2. Which error usually means a function name is misspelt?
3. What does =B2>100 return when B2 holds 150?
4. Which operator joins two pieces of text?

Key takeaways

  • A formula starts with = and recalculates whenever a cell it refers to changes.
  • Excel works out ^ before * and / before + and -, and brackets change the order.
  • Error values name the problem: dividing by zero, the wrong kind of value, an unknown name or a deleted reference.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.