News & tips
Formulas and Functions are some of Excels most powerful tools. They allow the user to make complex and useful spreadsheets.
This article shows you how to use both Formulas and Functions in Excel, as well as take you through some examples.
For more information on how to properly take advantage of Excel, view our course offerings here.
An Excel formula is an expression that returns a result usually based on other data in your spreadsheet. Excel formulas save you time and effort.
When you enter a formula in 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 select a cell and put an = sign in it first. Nothing will happen with it otherwise!
To perform a calculation, you need to enter each figure into its own cell and then have a select cell with the calculation in.
E.g. 5 + 6
Select cell B2, enter 5 , select cell B3 and enter 6.
In cell B4 you can enter the calculation =B2+B3
Excel will automatically calculate the total. When you select 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 number of cells B2 & B3 and Excel will always give you the answer in B4.
This format can be used for all calculations – addition, 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 arithmetic as that is the order Excel formulas use.
If you have any concerns about whether or not your Excel formula is working as intended, please visit our guide on auditing excel formulas to ensure they are correct.
BODMAS stands for: Brackets, Order, Divide, Multiply, Add and Subtract – and is the order for which Excel performs calculations.
A simple calculation would have two answers if BODMAS wasn’t applied.
e.g. 5+ 2 * 10 could equal 70 or 25.
Following left to right, 5 + 2 = 7, then 7 * 10 = 70.
However using BODMAS, we would do the multiplication first – 2 * 10 = 20, then add the 5 to get 25. This order of operations can be seen in the BODMAS image above, and remembered using the acronym.
If you are ever unsure on BODMAS, the easiest thing to do is use brackets, as you know what is inside the brackets will be calculated before applying that result to anything else.
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
Understanding BODMAS is key to writing any formula in Excel.
Functions are Excel formulas with special names and purposes. These names typically reflect the purpose. The SUM function for example, returns the sum of given data.
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 Excel functions runs into the hundreds. For people working in finance, engineering and statistical professions, learning to use these functions will make a huge difference.
The sum function, for instance, can reduce this following formula:
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. As of Excel 2019 there are over 475 Excel formulas!
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.
To create more complex formulas, you can learn about different types of Cell References here.
First select a cell and write in your formula.
I have a simple example of 3 Columns, where I want Column C to be the result of Column A + Column B for each row.
Then, click the Square at the bottom right of the cell containing the formula, and drag it down as long as you want the formula to extend.
As a result, the formula stretches down to a range of cells, and Excel corrects itself for each of the cell references! As you can see below, the values in cells C2 to C6 is correct, and has a unique formula (here C6 has A6+B6).
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 Formulas to bring up the formula bar.
From there, click ‘AutoSum’ in the formula bar and then ‘Sum’. This will insert the Excel function.
As you can see, it puts a dotted line (marquee) around the range of cells it thinks you want to add, if this is correct press ‘Enter’. If it is not, if you hit ‘Backspace’ it will remove the cell references but keep the function. You can then either highlight the range of cells you want with your mouse, or enter the range manually.
To enter it manually
Excel has countless useful functions, but these are some of the most commonly used and important to learn about.
The Sum Function allows you sum all values selected – as shown in the previous example. This is one of the most important formulas in excel.
Lets you find the average of all values selected, very useful for creating useful stastics and presenting them.
The VLookup formula is used to look up information in a list and extract the data into another list, as well as for matching. For a guide on VLookup, click here.
The If Function can be used for countless reasons, as it allows you to add conditions to a formula. For example, =IF(A1<A2,’TRUE’,’FALSE’) will check if cell A1 is less than cell A2. If it is, then the cell value holding the formula will be “TRUE”, if not it will be “FALSE”.
The MAX and MIN Functions simply find the Maximum or Minimum value in a range of given values. Functions like this save lots of time when sorting through large datasets.