*Please enable cookies in your browser to watch the videos*
Wait, before you run for the hills!
There’s no way around it: you can’t get what you want out of Microsoft Excel without a basic understanding of Maths. Luckily, that’s what today’s lesson is about.
At the heart of any Excel spreadsheet are the numbers within the data. Using basic maths functions to manipulate those numbers is one capability that makes Excel so powerful.
Calculations can be entered into the Excel formula bar or into a cell. It’s also possible to build a calculation in Excel by using the values in multiple cells.
All functions and formulae in Excel start a calculation with the = sign.
You can type the calculation you want to perform directly into the cell or the formula bar and when you press Enter the answer will show in the cell.
Another option is to use multiple cells to construct the formulae, as we see here (where cell A1 (or 87) plus cell A2 (or 16) equals cell A3 (or 103)):
Precedence (+ – * / )
When working with numbers and calculations in Excel, operators specify the type of calculation you wish to perform. The usual rules for the order of calculations apply in Excel, but you can change this order by using parentheses in your formula. In order to perform the basic mathematical operations such as addition, subtraction, multiplication, or division we use the following arithmetic operators:
- + (plus sign) for addition
- – (minus sign) for subtraction
- * (asterisk) for multiplication
- / (forward slash) for division
The following video shows these operators in action.
Excel interprets the = (equals) sign as indicating a calculation to be performed and calculates according to the operators indicated from left to right. To change the order in which Excel performs the calculations, enclose in parentheses the part of the formula to be calculated first.
For example if you have a formula: =6-3*4
You can use parentheses to make sure the 6-3 is calculated before the multiplication: =(6-3)*4
You can use a basic formula to create calculation exponents in Excel. To do so, use the = sign and the ^ (caret) symbol:
=3^2 OR =32^10
If the result of your calculation is very long, Excel will show the answer in scientific notation (as seen below):
There are specific formulas that will perform maths functions in Excel. The first is SUM which adds up a group of cells:
Counting entries in spreadsheets can be done in a similar way to ‘summing’ them. There are a few subtleties which we cover here.
The COUNT() function counts the number of cells that contain numbers. That includes cells that Excel recognises as date-format. The function won’t count cells that contain text.
In the sheet below, the formula in A1 is =COUNT(C1:H3)
This counts how many of the 18 cells (three rows by six columns) that are specified contain a number – so the answer is 6.
As with SUM(), COUNT() works with any number of inputs, and those inputs can be ranges and the Status Bar can be used to COUNT() the number of selected cells containing numbers. However, on the Status Bar COUNT will count the number of cells containing numbers or text. To count just numbers on the Status Bar, we need to customise it to ‘Numerical Count’.
Take care when using COUNT(). If numbers are forced to be interpreted as text (by specifying text format before entering a number for example), they will not be counted. We need Excel actively to convert the content to a number for COUNT() to recognise the entries as numbers—simply changing the cell format from Text to Number won’t rectify the problem:
Counting anything at all
The COUNTA() function counts the number of cells that have any content. Replacing COUNT() with COUNTA() in the example above means the two cells with text content are also counted:
The formula in A1 is now =COUNTA(C1:H3)
This counts how many of the 18 cells that are specified as inputs to the function contain any entry – so the answer is 9.
Note: cells containing only spaces (so they appear blank) will still be counted by COUNTA().
The final, and very important function you need to understand in Excel when performing maths calculations are absolute references, this video explains it all:
Totaling a column and copying the formula to total another column: Download Practice Document
Adding a formula to count cells (containing numbers) in a range; then a formula for counting cells with any contents. Download Practice Document
AAT students and professional members can access a wide range of Excel resources and training.
Filtered provides algorithmically personalised Excel courses written by subject matter expert.