ABS, POWER and SQRT in Excel: how to use it
ABS gives a number without its minus sign, POWER raises a number to a power and SQRT gives the square root.
Start practising hereIn the lesson Order of operations and brackets, free
What do ABS, POWER and SQRT do?
ABS removes the minus sign: it turns -40 into 40, and 40 stays 40. You use it when only the size of a difference matters, not whether it's a shortfall or a surplus.
POWER multiplies a number by itself a number of times: POWER(3,2) is 3 × 3. You can write the same with the caret: 3^2. SQRT does the opposite of squaring: SQRT(9) is 3.
When do you use ABS, POWER and SQRT?
- The difference between planned and actual, without a minus sign: =ABS(B2-A2).
- Compound interest over a number of years: =A2*POWER(1+B2,C2).
- The area of a square: =POWER(A2,2) or =A2^2.
- The side of a square when you know the area: =SQRT(A2).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The number without a minus sign: -40 becomes 40.
Filled in: =ABS(B2-A2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
ABS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | ABS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number | The number you're calculating with: a cell, a number or a calculation such as B2-A2. | B2-A2 |
ABS, POWER and SQRT step by step
D1 contains =ABS(B2-A2). The difference is -40, but ABS shows it without the minus sign. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click the cell where the difference should go, here D1, and type ABS with a bracket.
=ABS( - Inside the brackets, write the difference: the actual value minus the planned one.
=ABS(B2-A2 - Close the bracket and press Enter. B2-A2 gives -40, and ABS turns it into 40.
=ABS(B2-A2)
More examples
Compound interest
E1 contains =ROUND(A2*POWER(1+B2,C2),2). 1000 at 3% a year, for ten years: every year 3% is added, including on the interest from the years before. ROUND keeps two decimal places.
A square with the caret
C1 contains =A2^2. A square room with sides of 4.5 m has an area of 20.25 m². =POWER(A2,2) gives the same.
The side for an area
C1 contains =SQRT(A2). A square of 49 m² has sides of 7 m.
POWER and the caret
=POWER(2,3) gives 8, just like =2^3. It's handy for compound interest: =A2*POWER(1+B2,C2) gives what an amount is worth after a number of years.
SQRT
=SQRT(16) gives 4. For another root, such as a cube root, write it as a power: =POWER(27,1/3) gives 3.
Where it often goes wrong
- SQRT of a negative number gives #NUM!.
- Excel applies the minus sign first: =-2^2 gives 4. If you want -4, write =-(2^2).
Practise this in the lesson Order of operations and brackets. It's free.
Frequently asked questions
How do I work out a difference without a minus sign?
With ABS: =ABS(B2-A2) gives the difference between two numbers, always positive.