Excel has no SUBTRACT function: to take B2 from A2, type =A2-B2 and press Enter. Multiply with an asterisk, =A2*B2, divide with a forward slash, =A2/B2, and add with a plus sign, =A2+B2.
C2 = 8 There’s no SUBTRACT function. The minus sign takes B2 from A2, and each copy does its own row.
Excel has no SUBTRACT or DIVIDE function. You write arithmetic with a sign between two cells, the way you would on a calculator: a minus sign subtracts, an asterisk multiplies and a forward slash divides. The formula starts with an equals sign, and because it reads the cells rather than the numbers in them, its answer changes when they do.
First, work through an invoice that four teams share: each line’s quantity times its price, less a discount, and then each team’s share. After that each sign gets a small sheet of its own, where you can try the mistakes beginners make: brackets left out, a reference that moves when it should stay put, and a division by an empty cell.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
Type an equals sign, the cell to subtract from, a minus sign and the cell to take away: =A2-B2, then press Enter. To take several cells off, put a minus sign before each one, =B2-C2-D2-E2, or add them up with SUM and take off the total: =B2-SUM(C2:E2).
F2 = 450 Each minus sign takes one cell off, so Sales has 450 of its 900 left.
Plus signs in a list of costs are the mistake to watch for. Excel works along a formula from left to
right, so only the first cost comes off and the rest are added on. Brackets around the costs fix it,
and so does SUM. A formula can add and subtract together, too: =B2+C2-D2 adds C2 and takes
away D2. A result below zero shows with a minus sign, such as -50.
The asterisk, *, is Excel’s multiplication sign, not an x. Quantity times price is =B2*C2, and copied down a column, each copy multiplies its own row’s figures.
D3 = 120 Each copy multiplies its own row’s quantity by its own row’s price: three lamps at 40 come to 120.
To multiply a whole column by one number, such as an hourly rate or an exchange rate, put the number in
a cell of its own and lock the reference with dollar signs: =B2*$E$1. Copied down, every
row reads E1, and changing E1 changes them all. A number that never changes can go straight into the
formula, so doubling is =B2*2.
Excel also has a function for it. =PRODUCT(B2:D2) multiplies every number in a range,
which saves a chain of asterisks across a long row. Witsheet doesn’t have PRODUCT yet, so try that one
in your own Excel. Multiplying by a percentage, to add 10% to a price, is on the percentages page.
The forward slash, /, divides. =A2/B2 divides A2 by B2, so the number being
split goes first and what it’s split by goes after the slash. To divide by 2, type =A2/2.
To divide a whole column by one cell, lock that cell with dollar signs, as the invoice did with the
number of teams: =B2/$E$2. Put the cursor on the reference and press F4 (Fn + F4 on most Mac
keyboards) to add them instead of typing.
C3 = 120 The $ signs keep every copy on E2, so each cost is divided by the same head count, and changing E2 updates the column.
Excel doesn’t just work from left to right. It multiplies and divides first, then adds and subtracts,
the order taught at school as PEMDAS or BODMAS, so =10+5*2 is 20, not 30. Brackets
(parentheses) change the order: what’s inside them is worked out first, so =(10+5)*2 is 30.
B5 = 150 The brackets make Excel subtract first: 600 is left to pay, split four ways.
The invoice above needed no brackets, because the discount comes off after the multiplying, the order
Excel works in anyway. Adding them, =(B2*C2)-D2, does no harm, and some people find it
easier to read. Brackets can sit inside other brackets, and Excel works out the innermost first.
One place where Excel parts from the school order: a minus sign in front of a number comes before a
power. ^ raises to a power, and =-2^2 is 4 in Excel, where a maths teacher
would say -4, because Excel squares -2. Write =-(2^2) when you mean the other.
#DIV/0! is Excel saying it was asked to divide by zero. That happens when the cell you divide
by holds 0, or is empty, because an empty cell counts as 0 in arithmetic. Most often it’s a reference
that moved when the formula was copied, as with the head count above, or a figure that hasn’t been
filled in yet.
D3 = #DIV/0! No lamps yet, so the cost is divided by 0. The vases’ empty cell counts as 0 too, and the cards’ TBD isn’t a number at all: #VALUE!.
Fix the cause when there is one: lock a reference that moved, or fill in the missing figure. When a zero
or a gap is expected, =IF(C2=0, "", B2/C2) leaves the cell blank until there’s something to
divide by. IFERROR is shorter, but it blanks every other error as well.
To change numbers where they are, without a second column, type the number in a spare cell and copy it. Select the column, open Paste Special (Ctrl+Alt+V, or ⌘+Control+V on a Mac), pick Multiply, Divide, Add or Subtract under Operation, and click OK. Every selected cell changes by the number you copied, and the spare cell can go. Nothing on the sheet shows what was done, so it’s worth a note.
The plus sign adds a few cells: =A2+B2+C2. For a column, =SUM(B2:B20) does the
same in one short formula, however many rows there are. Sum a column practice totals one with AutoSum and with SUM.
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches arithmetic: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →