Understanding Formulas and Functions in Excel

In this tutorial I will cover the basic concepts of Formulas and Functions in Excel.

A formula is an expression which helps perform calculations (E.g. 2 + 1 + 5 + 4 = 12, E = MC²). Excel Formulas allow you to calculate the value of a cell based on a specified equation.  This can range from simple addition, subtraction etc. to the more complex such as economic modelling. For example, I could calculate the answer to 2 + 1 + 5 + 4 using an Excel formula:

Alternatively I could calculate the average of these values:

An extension of Excel Formulas is Excel Functions.

Excel Functions are basically pre-written formulas which have been programmed into Excel. They can be written into Formulas and provide extra tools in order to simplify complicated calculations. For example I could have saved time and used the Sum and Average Functions to do the calculations in the previous example. (Note: Average is a slightly more complicated equation yet the Average Function is just as simple as Sum)

Functions can do many things, including text manipulation which simply wouldnt be possible with Formulas (without manually re-writing the text).

 Say I have an order number which contains dashes every 3 numbers (123-456-789). Using the Left, Right and Mid Functions I can extract each part of the order number:

These examples are a bit more complex but show you how valuable Functions are in Excel.

Functions can be nested within each other whereas Formulas cannot. There is only 1 formula per cell but there can be numerous functions beside and within each other within the 1 formula. So, technically, a Function is a formula but a formula is not a Function.

Looking at text manipulation again, all 3 parts of the order number could be combined to get the number without dashes by using the Left, Mid and Right Functions in combination with the Concatenate Function:

Dont worry too much about whats going on here, nested Functions will be covered in a later tutorial. This is just a demonstration of Functions within Functions to show you how valuable they can be.

Another useful feature of Formula/Functions is that they can be copied down a list to save time. You just select the cell which contains the Formula you wish to copy and then click and drag the bottom right corner of the selected cell in the direction you wish to copy the Formula.

So looking at the previous example, I could copy this Formula down a list of order numbers. This means I only have to write the formula once as opposed to 5 times. (Note: This feature becomes ever more useful the larger the data set as you can copy Formula down a list no matter what size it is)

One last useful feature of Functions is that they provide built-in wizards for how to use them. For example if I wanted to use the Vlookup Function but I wasnt completely sure about how to use it. I could type it into the formula bar and a definition would then pop-up.

If I start to type the rest of the Function the wizard will continue to help me fill in each part of the Vlookup Function.

Note the different arguments for the Function are listed in the pop-up below where the function is being entered and the argument that you are on will always be highlighted.

(Arguments are the pieces of information the complete a function when you enter it into Excel.)

The Vlookup Function is useful for getting commonly used values and text. For example if I have a list of 5 sales reps, I could use a Vlookup to change the rep names to an abbreviation to save space:

Again, dont be too concerned with how the Vlookup Function works. This will be covered in a later tutorial. This is just an introduction into what Excel is capable of through the use of Formulas and Functions.

Note: Accompanying Excel workbook contains all the examples covered in this tutorial. Where possible (first 2 examples) the Formula-only equivalents are provided alongside the Functions.

Make sure to read the next tutorial to get an even better understanding of how you enter, use, and manage formulas and functions in Excel.