One of Excel's most useful features is that it allows users to create custom formulas to perform calculations on their data. Excel also contains built-in formulas called functions that make it easy to perform common calculations on data. Here you will find step by step tutorials, tips and shortcuts on how to use formulas and the common and less common functions available in Excel.
Formula Basics
Formulas in Microsoft Excel begin with an equal sign. The equal sign tells Excel that the succeeding characters constitute a formula. If you don't enter the equal sign, Excel will treat your entry as text and the calculation will fail.
To show how formulas work, we'll begin with a simple exercise by selecting blank cell A1. Then type =5+5, and press Enter. Excel performs the calculation and produces a result of 10 in cell A1.
Notice the formula bar shows the formula you just typed. What appears in the cell is the result; what appears in the formula bar is the underlying value, which is a formula in this case.
Order of Operation
When performing calculations in a formula, Excel follows certain rules of precedence: Excel calculates expressions within parentheses first. Excel calculates multiplication and division before addition and subtraction. Excel calculates consecutive operators with the same level of precedence from left to right.
For example, the formula = 10+10*2 gives a result of 30 as Excel multiplies 10 by 2 and then adds 10. However, the formula =(10+10)*2 produces a result of 40. This is because Excel calculates the expression (10+10) within the parentheses first. It then multiplies by 2.
If you are unsure of the order in which Excel calculates, use parentheses - even if the parentheses aren't necessary. Parentheses also make your formulas easier to read.
Operations in Formulas
|
:
|
Colon
|
|
,
|
Comma
|
|
-
|
Negation (as in -1)
|
|
%
|
Percent
|
|
^
|
Exponentiation
|
|
*
|
Multiplication
|
|
/
|
Division
|
|
+
|
Addition
|
|
-
|
Subtraction & Connects two strings
of text
|
|
=
|
Comparison (Equal to)
|
|
<
|
Less Than
|
|
>
|
Greater Than
|
|
>=
|
Greater Than or Equal to
|
|
<=
|
Less Than or Equal to
|
|
<>
|
Not Equal to
|
