Formula = a calculation you enter yourself
Function = a pre-programmed formula, commonly shortened to ‘fx’
When you enter a formula into Excel you have to let Excel know that it is a formula and that it needs to do something. The way to do this is to put an = sign in the cell first, nothing will happen with it otherwise!
To perform a calculation you need to enter each figure into it’s own cell and then have a cell with the calculation in.
E.g. 5 + 6
Enter 5 into cell B2, enter 6 into cell B3
In cell B4 you can enter the calculation =B2+B3
Excel will automatically calculate the total. When you click on cell B4 you can see the formula in the formula bar, prefixed with fx.
This seems like a lot of work for a simple calculation but once you have done it, you can change the numbers in cells B2 & B3 and Excel will always give you the answer in B4
This format can be used for all calculations – addtion, subtraction, multiplication and division.
You will need to know the symbols that Excel/computers use for multiplication and division as they look slightly different:
Multiplication * Division /
E.g. 20 divided by 5 looks like 20/5 and 3 multiplied by 7 looks like 3*7
For more complex calculations, you need to use the BODMAS rule of arithmatic as that is the order Excel uses.
A simple calculation can have two answers unless BODMAS is applied.
e.g. 5+ 2 * 10 could equal 70 or 25. The correct answer is 25
The multiplication is 2nd level so do that first and then do the level 3 addition
A slightly more complicated example: 3 – 4 /2 + 6*5
You have to to the multiplication and division BEFORE any addition and subtraction
4/2 = 2
6*5 = 30
So it now looks like this 3 – 2 + 30
Addition and subtraction are completed from left – right so the answer is 31.
If there are brackets in a calculation, they have to be worked out first, everything else still follows BODMAS
10+(10*2)/5 = 14
(10*2) = 20 – 1st level
20/5 = 4 – 2nd level
10+4 = 14 – 3rd level
Functions are there to make using large formulas easy. Imagine having to add up 100 cells, that’s an awful lot of typing and the chance of making an error increases.
To make life easier this and many, many more formulas covering a wide range of categories from statistics through to engineering have been added in Excel as automated functions.
The exact number of functions Excel isn’t published but runs into the hundreds. For people working in finance, engineering and statistical professions learning to use the functions will make a huge difference. Simple functions are covered in our intermediate courses whereas advanced functions are covered in our advanced and business intelligence courses.
The sum function, for instance, can reduce this:
Under the ‘Formulas’ tab in Excel you will find the ‘Function Library’ where you can choose from all of the available functions divided up into their categories
From here you can click on insert function, the FX button, and this box will pop up:
Here you are able to search for a function to do what you want or search the list in the category section to find it. You can also list all available functions. I have just Googled how many functions there are in Excel 2010 and it says 400!
If you are familiar with the function you want to use you can just type it directly into the cell. You still need to put the ‘=’ sign in first, the same as you do with a formula.
This is the most commonly used function in Excel and there are so many ways to use this, I will show you the way I find easiest.
When you have clicked in the cell where you want the total to be, click on ‘AutoSum’ and then ‘Sum’. This will insert the function.
As you can see, it puts a dotted line (marquee) around the cells it thinks you want to add, if this is correct press the ‘Enter’ key. If it is not, if you hit ‘Backspace’ it will remove the cell references but keep the function. You can then either highlight the cells you want with your mouse, or enter the range manually.
To enter it manually
What is non-contiguous?? It means ranges and cells that are not next to each other, they can be spread all around your worksheet and you can sum as many of them as you wish.
To sum the cells in the picture above I first clicked in cell B26, type in =sum( , or click on the the sum button, hold the ‘CNTRL’ key down and click on B9, B14, B19 and B24 and press enter.
To save entering the almost identical formula into each column to total them, you can drag the formula across the cells and it will automatically adjust to sum the cells in the same rows but in the new columns. To do this click on the cell with the formula and hover over the bottom right-hand corner a little + sign will appear, when it does, hold down the left mouse button and drag the formula across as many cells as you need it.
Unfortunately the + sign doesn’t capture in the screen shot below.
The AVERAGE function allows you to calculate the average value of a range of cells. It is used in the same way as SUM
MAX and MIN both work in identical ways, I have highlighted the cells in blue and green to show it picks up the values from the selected ranges.
Want to book onto one of our Excel courses? Follow this link.
Want to find out more about Excel 2010? Follow either of the articles below.