Formulas And Functions In Excel [Getting Started!]
Contents
Formulas and Functions are some of Excels most powerful tools.
They allow the user to make complex and insightful spreadsheets.
Understanding Formulas In Excel
An Excel formula is an expression that returns a result. They save you time and effort.
To create one, click on a cell and type the = sign in it first. Nothing will happen to 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.
Having some trouble learning how to build your formulas? There are lots of great AI tools that can create and explain formulas for you, which is a great way to learn.
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 your formula is producing unexpected results, you can audit your formulas with built-in tools to find the problem!
BODMAS And Operator Precedence
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.
Understandings the basics of maths is a core foundation of Excel, but can be a topic people struggle with.
This is why we make the maths side of our Excel courses as simple, as it is very important to understand!
Understanding Functions 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.
A practical example, is reducing this following formula:
=E8+E9+E10+E11+E12+E13
to this:
=SUM(E8:E13)
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.
If you learn how cell references work, you can expand these formulas to work across big data sets!
How To Copy A Formula
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).
Example: Using The SUM Function
This is the most commonly used function in Excel, so let’s go through 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 not, hit ‘Backspace’ to 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
- Type =sum(
- either type in the first cell name or click on the cell
- Click on or type in the next cell to sum
- Type ) and press ‘Enter’
Top 5 Most Common Functions In Excel
Excel has countless useful functions, but these are some of the most commonly used and important to learn about.
1) Sum
The SUM Function allows you to add up all values selected – as shown in the previous example.
This is one of the most important formulas in Excel.
2) Average
The AVERAGE Function averages out all values selected, very useful for creating useful statistics and presenting them.
3) VLookup
The VLookup Function is used to look up information in a list and extract the data into another list, as well as for matching.
4) If
The IF Function is a logical function, and 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”.
5) MAX and MIN
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.
