Formulas and functions are the bread and butter of Excel.
They drive almost everything interesting and useful you will ever do in a spreadsheet.
This article introduces the basic concepts you should probably know to be proficient with formulas in Excel.More examples here.
What is a formula?
A formula in Excel is an expression that returns a specific result.
For example:
Note: all formulas in Excel must begin with an equals sign (=).
Cell references
In the examples above, values are “hardcoded”.
That means results won’t change unless you edit the formula again and change a value manually.
Instead, usecell referencesso values can be changed at any time.
Below a formula is used to calculate percent change.
The formula returns a correct result in D2 and D3, but returns a #DIV/0!
error in D4, because B4 is empty:
There are different ways of handling errors.
This means you don’t need to enter the same basic formula again and again.
Cell addresses are updated again:
Relative and absolute references
The cell references above are calledrelativereferences.
This means the reference is relative to the cell it lives in.
Now we can easily change the value in A1, and all three formulas recalculate.
Tip: you might toggle between relative and absolute syntax with theF4 key.
Note references are color-coded:
All formulas in Excel must begin with an equals sign (=).
If you want to cancel, and leave the formula unchanged, tap the Escape key.
Video:20 tips for entering formulas
What is a function?
They are closely related, but not exactly the same.
Technically, a formula isanyexpression that begins with an equals sign (=).
A function, on the other hand, is a formula with a special name and purpose.
In most cases, functions have names that reflect their intended use.
To get started, see101 Key Excel functions.
Function arguments
Most functions require inputs to return a result.
These inputs are called “arguments”.
A function’s arguments appear after the function name, inside parentheses, separated by commas.
All have a name, and an opening and closing parentheses ().
The formula in C1 is:
Video:How to use the COUNTIF function
Not all arguments are required.
Arguments shown in square brackets [ ] are optional.
See below for an example of how to use YEARFRAC to calculate current age based on birthdate.
How to enter a function
If you know the name of the function, just start typing.
Here are the steps:
1.
Enter an equals sign (=) and start typing.
- pop in the Tab key to accept a function.
Excel will complete the function:
3.
Fill in required arguments:
4.
First, any expressions in parentheses are evaluated.
Next Excel will solve for any exponents.
After exponents, Excel will perform multiplication and division, then addition and subtraction.
If the formula involvesconcatenation, this will happen after standard math operations.
Finally, Excel will evaluatelogical operators, if present.
Tip: you could use theEvaluate featureto watch Excel solve formulas step-by-step.
This overwrites the formulas with the values they return.
Video:Paste Special Shortcuts
What’s next?
Below are guides to help you learn more about Excel’s formulas and functions.
We also offeronline video training.
