Formulas And Functions In Excel: The Complete Guide

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 Excel course offerings here.

Understanding Formulas In Excel

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.

It is worth noting that you can use named ranges in place of cell references if you want for more details see our article: Using Named Ranges In Excel

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.

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.

Once you have a good understanding of this fundamental tool you can learn how to use more advanced tools for functions, such as Excel’s random numbers features.

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.

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:

=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.

To create more complex formulas, you can learn about different types of Cell References here.

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).

How To Use The SUM Function To Add

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

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’
NB if you are typing the cell names in, you will need commas between each cell name.

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 sum all values selected – as shown in the previous example. This is one of the most important formulas in excel.

2) Average

Lets you find the average of all values selected, very useful for creating useful stastics and presenting them.

3) VLookup

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.

4) If

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”.

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.