Article Date: 3rd January 2018
Excelling in Excel
Microsoft Office Excel can do more than just add and subtract. Once you’ve learned the basic formulae (or formulas) in Excel, you can do almost anything you want. Well, you can’t fly to the moon and back, but you can definitely calculate how to do so! Implementing a certain formula can make home and student life much easier, and will bring an end to those Big Brother-style arguments over the shopping budget!
Firstly, you need to know that every formula you enter in Excel has to begin with the Equals symbol “=“, for example =SUM(A1:A10) Please note that every time a specific formula is mentioned in this review, it will be printed in RED. Using formulae also means that when you update a particular cell, you don’t need to change the total too, it will update automatically!
Adding, Subtracting, Multiplying, and Dividing Figures
Adding two separate figures is very simple. Just enter an equals sign, then the first figure, the plus symbol, and then the second figure. If you want to add more than two figures, just keep entering plus and then the number. e.g. =12+13+14+15+16
Subtracting is just as easy, enter a similar formula with the Minus symbol (a hyphen or dash) in the middle. e.g. =20-3
Multiplying and Dividing also follow the same principle, with * as the multiply symbol and / for divide. e.g. =20*3 and =21/3
You can also combine these functions to calculate more complicated equations. =15+6-9
However, if you are using the multiply and divide functions, you may need to separate different parts of the equation. Do this using parentheses to separate each part. e.g. =15+(9/3) and =(15+9)/3 give very different answers!
You may also use the Exponentiation symbol ^ to raise a number to a higher power, say if you were squaring or cubing a number. e.g. =18^3
Adding multiple cells, tabs or columns
This feature requires a more complicated formula than merely entering the + symbol. When adding columns or cells, you will need to use the =SUM command. Once you’ve got to grips with the =SUM function, it’s very easy, and you’ll be using it in every Excel spreadsheet. Firstly enter =SUM, then an open bracket, then the cell you want to begin from, the colon separator, the final cell, and then a close bracket. Like this:
If the cells aren’t in a row or column, it’s also quite simple. You can separate all the cells you wish to add using commas. e.g. =SUM(A3,B2,C7)
You can also use the maths functions to work with individual cells, e.g. =A2/B4 or =(C3*D1)-F2
Other useful functions
You won’t need to add figures all the time, sometimes you might need to find an average figure or the highest number in a range. For these different types of calculations, there are dozens of different functions. For example, to get an average of a range of numbers, you use the =AVERAGE function i.e. =AVERAGE(B2:B6)
Here is a list of other useful functions:
| Function | Description | Example |
| =AVERAGE | Calculates the Average of a range of figures | =AVERAGE(C3:C17) |
| =COUNT | Counts (not adds) how many numbers are in the range | =COUNT(C3:C17) |
| =MAX | Displays the highest figure in the range | =MAX(C3:C17) |
| =MEDIAN | Shows the Median number of a range | =MEDIAN(C3:C17) |
| =MIN | Displays the lowest figure in the range | =MIN(C3:C17) |
| =MODE | Shows the most frequent value in a range | =MODE(C3:C17) |
| =PI() | Enters the number Pi into the equation | =PI()*2 |
| =PRODUCT | Multiplies the specified range | =PRODUCT(B2:B6) |
| =ROMAN | Convert a number to Roman Numerals (i.e. MMVIII) | =ROMAN(C3) |
| =ROUNDDOWN | Rounds figures down to the nearest whole number | =ROUNDDOWN(C3:C17) |
| =ROUNDUP | Rounds figures up to the nearest whole number | =ROUNDUP(C3:C17) |
Common Error Messages
Sometimes you may enter a formula incorrectly. If this happens, an error will be displayed. There are two main errors and one display error. The display error is simplest of all to solve.
#### If you see a bunch of hash or number symbols in a cell e.g. ####, this simply means that the cell is not wide enough to display the figure. All you need to do is widen the column, and you will be able to see your full result.
#REF! This warning means that your formula referred to a cell that doesn’t exist, and thus the result cannot be displayed. For example, how can you get the average of a number of cells if one of them does not exist?
#NAME? This appears if a wrong function was entered. Maybe you spelled the function name incorrectly or tried to use a function where it should not be used – like a math function on plain text.
Now you know how to use these intuitive formulae to get the most from your Microsoft Excel spreadsheets.
————————————————————
Software4Students.co.uk – Official Microsoft Partner