Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. When you know how to use formulas and functions together, you can build worksheets that do exactly what you want. Excel has hundreds of functions that can be used to build an almost unlimited number of formulas. This article is an introduction to the basics.
What is a formula?
A formula in Excel is an expression that returns a specific result. For example:
All formulas in Excel return a result, even when the result is an error.
In the examples above, values are “hardcoded”. That means results won’t change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet.
Instead, use cell references so values can be changed at any time. In the screen below, C1 contains the following formula:
Notice because we are using cell references for A1, A2, and A3, these values can be changed at any time and C1 will still show an accurate result.
Copy and paste formulas
The beauty of cell references is that they automatically update when a formula is copied to a new location. This means you don’t need to enter the same basic formula again and again. In the screen below, the formula in C1 has been copied to the clipboard with Control + C:
Below: formula pasted to cell E2 with Control + P. Notice cell references have changed:
Same formula pasted to E3. Cell addresses are updated again:
Relative and absolute references
The cell references above are called relative references. This means the reference is relative to the cell it lives in. The formula in E1 above is:
=B1+C1+D1 // formula in E1
Literally, this means “cell 3 columns left “+ “cell 2 columns left” + “cell 1 column left”. That’s why, when the formula is copied down to cell E2, it continues to work the same way.
Relative references are extremely useful, but there are times when you don’t want a cell reference to change. A cell reference that won’t change when copied is called an absolute reference. To make a reference absolute, use the dollar symbol ($):=A1 // relative reference =$A$1 // absolute reference
For example, in the screen below, we want to multiply each value in column D by 10, which is entered in A1. By using an absolute reference for A1, we “lock” that reference so it won’t change when the formula is copied to E2 and E3:
Here are the final formulas in E1, E2, and E3:=D1*$A$1 // formula in E1 =D2*$A$1 // formula in E2 =D3*$A$1 // formula in E3
Notice the reference to D1 updates when the formula is copied, but the reference to A1 never changes. Now we can easily change the value in A1, and all three formulas recalculate. Below the value in A1 has changed from 10 to 12:
This simple example also shows why it doesn’t make sense to hardcode values into a formula. By storing the value in A1 in one place, and referring to A1 with an absolute reference, the value can be changed at any time and all associated formulas will update instantly.
How to enter a formula
To enter a formula:
- Select a cell
- Enter an equals sign (=)
- Type the formula, and press enter.
Instead of typing cell references, you can point and click, as seen below. Note references are color-coded:
All formulas in Excel must begin with an equals sign (=). No equals sign, no formula:
How to change a formula
To edit a formula, you have 3 options:
- Select the cell, edit in the formula bar
- Double-click the cell, edit directly
- Select the cell, press F2, edit directly
No matter which option you use, press Enter to confirm changes when done.
Video: 20 tips for entering formulas
What is a function?
Working in Excel, you will hear the words “formula” and “function” used frequently, sometimes interchangeably. They are closely related, but not exactly the same. Technically, a formula is any expression that begins with an equals sign (=).
A function is a specially named formula that solves a specific problem. In most cases, functions have names that reflect their purpose. For example, you probably know the SUM function already, which returns the sum of given references:=SUM(1,2,3) // returns 6 =SUM(A1:A3) // returns A1+A2+A3
The AVERAGE function, as you would expect, returns the average of given references:
Excel contains hundreds of specific functions. If you’re just starting out, see this list of about 100 key functions you should know about.
Almost all functions require inputs to return a result. These inputs are called “arguments”, and the pattern looks like this:=FUNCTIONNAME(argument1,argument2,argument3)
Notice arguments appear inside parentheses, separated by commas. All functions require a matching opening and closing parentheses ().
For example, the COUNTIF function counts cells that meet criteria, and takes two arguments, range and criteria:=COUNTIF(range,criteria) // two arguments
In the screen below, range is A1:A5 and criteria is “red”. The formula in C1 is:
Not all arguments are required. Argument shown square brackets are optional. For example, the YEARFRAC function returns fractional number of years between a start date and end date:
Start date and end date are requires arguments, basis is an optional argument. See below for an example of YEARFRAC to calculate age.
How to enter a function
If you know the name of the function, just start typing. Here are the steps:
1. Enter equals sign (=) and start tying. Excel will list of matching functions based as you type:
When you see the function you want in the list, use the arrow keys to select
2. Type the Tab key to accept a function. Excel will complete the function:
3. Fill in required arguments:
4. Press Enter to confirm formula:
Many Excel formulas use more than one function, and functions can be “nested” inside each other. For example, below we have a birthdate in B1 and we want to calculate current age in B2:
The YEARFRAC function will calculate years with a start date and end date:
We can use B1 for start date, then use the TODAY function to supply the end date:
When we press Enter to confirm, we get current age based on today’s date:
Notice we are using the TODAY function to feed an end date to the YEARFRAC function. In other words, the TODAY function can be nested inside the YEARFRAC function to provide the end date argument. We can take the formula one step further and use the INT function to chop off the decimal value:
Here, the original YEARFRAC formula returns 20.4 to the INT function, and the INT function returns a final result of 20.
The classic example of nesting functions is the “nested IF” to handle logic with multiple IF/ELSE steps. This article has a full explanation with many examples.
Note: the current date in images above is February 22, 2019. Also, the TODAY function is one of the rate Excel functions with no required arguments.
Order of operations, Operators
Below are guides to help you learn more about Excel’s formulas and functions.