Subtract, multiply, divide in Excel: how it works
To subtract, multiply or divide in Excel, you don't use a function but an operator: =A2-B2, =A2*B2 and =A2/B2. Only adding has its own function, SUM.
Start practising hereIn the lesson Getting started with formulas, free
How do you calculate without a function?
For the four basic operations, you don't need a function in Excel. You type an equals sign and then the calculation with operators: + to add, - to subtract, * to multiply and / to divide.
Usually you calculate with cells instead of numbers: =A2-B2 instead of =120-45. If you then change a number in A2 or B2, Excel recalculates the result straight away. Only for adding up is there a handy function, SUM, which adds up a whole range in one go.
When do you use which operator?
- Subtracting, such as what's left of a budget: =B2-C2.
- Multiplying, such as price times quantity: =A2*B2.
- Dividing, such as a bill per person: =B2/C2.
- A discount or tax as a percentage: =A2*(1-15%) or =A2*20%.
Step by step
C2 contains =A2*B2. Price times quantity: 12.50 × 4 = 50. Change the numbers or the formula and see what happens.
This is how to go about it:
- Click C2, where the result should go, and type an equals sign.
= - Click the price in A2.
=A2 - Type the multiplication sign: an asterisk.
=A2* - Click the quantity in B2 and press Enter: 12.50 times 4 is 50.
=A2*B2
More examples
Subtracting
C2 contains =A2-B2. What's left of the budget: 500 minus 365.
Dividing
C2 contains =A2/B2. The bill divided by the number of people: 31 per person.
Adding a negative number with SUM
D1 contains =SUM(A2,-B2). A minus sign in front of B2 makes the number negative, and SUM adds it. That comes down to subtracting: the same as =A2-B2.
The four operators
+ adds, - subtracts, * multiplies and / divides. You'll also find the asterisk and the slash on the numeric keypad. Excel doesn't know the × or ÷ signs.
What about SUM?
SUM always adds, a whole range in one go: =SUM(B2:B100). There's no separate function for subtracting: you use the minus sign, =A2-B2. You can add a negative number with SUM, though, and that comes down to subtracting: =SUM(A2,-B2) gives the same as =A2-B2.
Where it often goes wrong
- A formula always starts with =. Without it, Excel reads 12-4 as text, or even as a date.
- Dividing by an empty cell or by 0 gives #DIV/0!.
Practise this in the lesson Getting started with formulas. It's free.
Frequently asked questions
Is there a function to multiply?
Yes, PRODUCT multiplies a whole range of cells in one go. Usually * is simpler. SUMPRODUCT multiplies two columns row by row and adds up the results.