Quick Links
Formulas and functions, the nuts and bolts of Excel, both enable you to perform calculations, but they differ in how they’re created, what they do, and how they work. In this article, we’ll strip the two right back to their bare bones, so you can use Excel like a pro, both at home and at work.
If you encounter any unfamiliar terms in this article, check outour A-Z of Excel jargon.
What Are Functions and Formulas?
The key difference between formulas and functions is that anyone can create a formula, whereas functions are predefined by Microsoft’s programmers.
Excel Formulas
Excel’s formulas enable you to perform basic mathematical calculations. To create a formula, start by typing the equals (=) sign, and then create the parameters for the calculation.
For example, typing
into cell B2 and pressing Enter would result in Excel adding 20 and 40, producing the outcome (60) in the cell where the formula is typed.
Excel can also calculate numerical values that are already in the spreadsheet. Typing
into cell B3 and pressing Enter would multiply the value in cell B2 by ten.
Similarly, typing
into cell B4 and pressing Enter would multiply the values in cells B2 (60) and B3 (600) together, resulting in 36,000.
That said, you’re not limited to two arguments when creating an Excel formula. For example, typing
into cell B5 would sum two and eight, then multiply the value in B3 by five, before subtracting the former from the latter, with an outcome of 2,990.
Excel follows thestandard mathematical order of operations—PEMDAS. In other words, it first performs calculations in parentheses, then evaluates exponents (or indices), then deals with multiplication and division, and finishes with addition and subtraction.
Excel Functions
Excel’s functions work in a similar way. Indeed, they start with the = sign, and enable you to perform calculations. However, where Excel’s formulas are limited to the basic mathematical operations, its functions let you do a lot more.
For example,the AVERAGE functiontakes a set of numbers and finds the mean, and MAX tells you the largest number in a range.
Excel’s functions follow a very specific syntax:
whereais the name of the function (such as AVERAGE or MAX), andbare the arguments used to enable that function to perform calculations.
into cell A1 and pressing Enter would calculate the average of 20 and 30, returning 25.
We could also type
in cell B2 to make Excel calculate the mean of all the values in cells A1 to A5 (the colon tells Excel to include the cells mentioned and all those in between).
There are hundreds of Excel functions, ranging from themost basic functionsto themore complex ones. Remembering them all is pretty much impossible, especially given that Microsoft’s developers are always adding new ones to the list. Instead, Excel is ready to help you choose which function best suits the job you need to carry out, and helps you through the process.
To launch this assistant, click the “fx” icon above the first row of your spreadsheet, or press Shift+F3.
You can then type some words in the Search For A Function field to find the function you need. The Select A Category drop-down shows you the different function groups, including the financial, statistical, and logical categories. When you select a function in the list under the categories, you will see a brief description that tells you what the function does.
When you’ve found the function you want to use, click “OK.” Then, you will see a new dialog box that walks you through the process.
Formulas and Functions Together
Formulas and functions don’t have to be used independently. For example, typing
would add the values in cells A1 to A10, before dividing the total by two.
Cells vs. The Formula Bar
Once you’ve typed a formula or used a function in a given cell in Excel, it is replaced by the result. For example, when we type
into cell A3 and press Enter, we no longer see what we typed in that cell. Instead, we see the result.
If you realize you made an error and need to amend the formula you typed, this is where you can use the Formula Bar, which runs along the top of your Excel worksheet. You can also seethe Name Box in the top-left corner, which indicates the active cell.
In other words, in the example below, the Name Box tells us that A3 is the active cell, the Formula Bar tells us what we typed into cell A3, and cell A3 itself tells us the result of what we typed.
Duplicating Formulas and Functions
As mentioned above, Excel is always ready to help make life easier, and it’s worth remembering this when using formulas and functions.
In the example below, we want to add all the values in cells A1 to A8, so we will type
into cell A9, and press Enter.
We also want to add the values in cells B1 to B8. However, instead of typing a new formula into B9 using the SUM function, we can either
Because cell references within formulas are relative by default, what we typed in cell A9 to calculate the values in cells A1 to A8 will also apply in B9 to calculate the values in cells B1 to B8.
There may be occasions when you donotwant the references within formulas to be relative. In this case, take a few moments to learn thedifference between relative, absolute, and mixed references.
10 Basic Functions to Get You Going
If you’re new to Excel or its functions, open a new spreadsheet, and enter some random numerical values into cells A1 to A9 (leaving A10 blank). Then, give some of these functions a try:
In cell
Type this and press Enter
What this will do
B1
=SUM(A1:A10)
The SUM functionwill add all the values in cells A1 to A10.
B2
=AVERAGE(A1:A10)
The AVERAGE functionwill find the mean of all the values in cells A1 to A10.
B3
=CONCAT(A1:A3)
The CONCAT functionwill string together all the values in cells A1 to A3.
B4
=COUNT(A1:A10)
The COUNT functionwill tell you the number of cells containing numbers in A1 to A10.
B5
=COUNTA(A1:A10)
The COUNTA functionwill tell you the number of cells containing any value (in other words, the cells that are not empty).
B6
=COUNTBLANK(A1:A10)
The COUNTBLANK functionwill tell you the number of blank cells in A1 to A10.
B7
=MIN(A1:A10)
This will tell you the smallest number in cells A1 to A10.
B8
=MAX(A1:A10)
This will tell you the largest number in cells A1 to A10.
=TODAY()
B10
=RAND()
The volatile RAND functionreturns a random number between 0 and 1.
A volatile function recalculates anytime you make any changes to or reopen your Excel spreadsheet.
5 More Advanced Functions
On a new worksheet or in a new workbook, typeLaura,Lucy,Liam,Lilly,Liz, andLukeinto cells A1 to A6, and numbers 1 to 6 in cells B1 to B6.
C1
=IF(B6>1,“YES”,“NO”)
The IF functionwill evaluate whether the value in cell B6 is greater than 1, returning “YES” if it is, and “NO” if it isn’t. In this case, it’ll return “YES.”
C2
=VLOOKUP(“Liam”,A1:B6,2)
The VLOOKUP functionwill look for “Liam” in cells A1 to B6, returning the number in the second column where it finds that word. In this case, it’ll return “3.”
C3
=SUMIF(A1:B6,“Li*",B1:B6)
The SUMIF functionwill look for values starting “Li” in cells A1 to B6, returning the sum of the values in cells B1 to B6 where this is true. In this case, it’ll return “12,” as the numbers next to Liam, Lilly, and Liz add up to 12.
C4
=COUNTIF(B1:B6,3)
The COUNTIF functionwill tell you how many cells in B1 to B6 contain the number 3. In this case, it’ll return “1,” because only cell B3 contains this value.
C5
=LEFT(A1,3)
The LEFT functionwill tell you the three left-most characters in cell A1, which, in this case, are “Lau.”
Learning about Excel’s formulas and functions doesn’t stop there—in fact, it never stops at all! At How-To Geek, we havedozens of Excel-related articlesthat you may work through to become a power user.
