Many powerful features in Excel can be used for various mathematical, statistical and financial calculations.
A formula can be used to automate certain calculations, such as adding numbers. A formula is entered in a cell and the result of the formula is then displayed in the cell, not the actual formula that produced it.
When creating formulas, it is good practice to use cell references rather than numbers. In this way, the values can change without having to change the formula.
A formula in Excel always starts with an equals sign. This indicates that some calculation is to follow.
The standard arithmetical operators can be used to calculate a result:
- Addition uses the plus sign, i.e. +
- Subtraction uses the dash, i.e. –
- Multiply uses the star, i.e. *
- Divide uses the forward slash, i.e. /
To add the numbers in two cell references, for example A1 and B1, the formula would be:
The formula is usually typed in an adjacent cell, possibly C1. The result of this formula would be displayed in C1 and would change based on the values in cells A1 and B1.
You can combine arithmetical operations in Excel as you would in any traditional, mathematical formula. The rules regarding multiplication, division, addition and subtraction and the use of brackets are the same.
A function is a special formula, such as SUM which calculates the total for a group of cells. There are lots of different functions available in Excel. Using a function is an efficient way of obtaining the result of a calculation because the formulas have already been worked out for you.
A function starts with an equals sign, as with formulas. Then the name of the function is used, followed by the cell references inside brackets. Cell references are separated by a comma.
For example: =SUM(A1,B1)
The example above will add the numbers in cells A1 and B1 together.
If a range of cells are to be summed, the first and last cell in the range are used, separated by a colon.
For example: =SUM(A1:D1)
The example above will add the numbers in cells A1, B1, C1 and D1 together. The formula itself is written in another cell, possibly E1, and the result would be displayed in this cell.
The SUM function is probably the most commonly used. But the Formula ribbon gives many different categories of functions. Press Alt + M for the Formula ribbon and Tab through the options.
There is also a tool called AutoSum which is accessed by pressing Alt + =. This will guess which range of cells you want to add up, and automatically enter the whole SUM formula for you. When you use Autosum, the suggested formula is displayed. If it is correct, just press Enter to accept.
The Autosum function may not always guess the right range of cells so needs to be checked.