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.
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
=20+40
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
=B2*10
into cell B3 and pressing Enter would multiply the value in cell B2 by ten.
Similarly, typing
=B2*B3
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
=B3*5-(2+8)
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 the
standard 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 function takes 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:
=a(b)
where a is the name of the function (such as AVERAGE or MAX), and b are the arguments used to enable that function to perform calculations.
For example, typing
=AVERAGE(20,30)
into cell A1 and pressing Enter would calculate the average of 20 and 30, returning 25.
We could also type
=AVERAGE(A1:A5)
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 the most basic functions to the more 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
=SUM(A1:A10)/2
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
=SUM(A1,B1)
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 see the 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
=SUM(A1:A8)
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
- Select cell A9, press Ctrl+C, and then press Ctrl+V in cell B9, or
- Drag the fill handle in the bottom-right corner of cell A9 across to cell B9.
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.
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 function will add all the values in cells A1 to A10. |
|
B2 |
=AVERAGE(A1:A10) |
The AVERAGE function will find the mean of all the values in cells A1 to A10. |
|
B3 |
=CONCAT(A1:A3) |
The CONCAT function will string together all the values in cells A1 to A3. |
|
B4 |
=COUNT(A1:A10) |
The COUNT function will tell you the number of cells containing numbers in A1 to A10. |
|
B5 |
=COUNTA(A1:A10) |
The COUNTA function will tell you the number of cells containing any value (in other words, the cells that are not empty). |
|
B6 |
=COUNTBLANK(A1:A10) |
The COUNTBLANK function will 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. |
|
B9 |
=TODAY() |
The volatile TODAY function returns today’s date. |
|
B10 |
=RAND() |
The volatile RAND function returns 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, type Laura, Lucy, Liam, Lilly, Liz, and Luke into cells A1 to A6, and numbers 1 to 6 in cells B1 to B6.
Now, try some of these functions:
|
In cell |
Type this and press Enter |
What this will do |
|---|---|---|
|
C1 |
=IF(B6>1,”YES”,”NO”) |
The IF function will 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 function will 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 function will 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 function will 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 function will 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 have dozens of Excel-related articles that you can work through to become a power user.
