 Introduction
 Understanding Formulas
 Creating a Simple Formula
 Creating a Formula Using Formula AutoComplete
 Editing a Formula
 Understanding Cell Referencing
 Using Absolute Cell References
 Using Mixed Cell References
 Using 3D Cell References
 Naming Cells and Ranges
 Entering Named Cells and Ranges
 Managing Names
 Simplifying a Formula with Ranges
 Displaying Calculations with the Status Bar
 Calculating Totals with AutoSum
 Calculating Totals with Quick Analysis
 Performing One Time Calculations
 Converting Formulas and Values
 Correcting Calculation Errors
 Correcting Formulas
 Auditing a Worksheet
 Locating Circular References
 Performing Calculations Using Functions
 Creating Functions
 Creating Functions Using the Library
 Calculating Multiple Results
 Using Nested Functions
 Using Constants and Functions in Names
Understanding Formulas
Introduction
A formula calculates values to return a result. On an Excel worksheet, you can create a formula using constant values (such as 147 or $10.00), operators (shown in the table), references, and functions. An Excel formula always begins with the equal sign (=).
A constant is a number or text value that is not calculated, such as the number 147, the text “Total Profits”, and the date 7/22/2013. On the other hand, an expression is a value that is not a constant. Constants remain the same until you or the system change them. An operator performs a calculation, such as + (plus sign) or  (minus sign). A cell reference is a cell address that returns the value in a cell. For example, A1 (column A and row 1) returns the value in cell A1 (see table below).
Cell Reference Examples
Reference 
Meaning 
A1 
Cell in column A and row 1 
A1:A10 
Range of cells in column A and rows 1 through 10 
A1:F1 
Range of cells in row 1 and columns A through F 
1:1 
All cells in row 1 
1:5 
All cells in rows 1 through 5 
A:A 
All cells in column A 
A:F 
All cells in columns A through F 
Profits!A1:A10 
Range of cells in column A and rows 1 through 10 in worksheet named Profits 
A function performs predefined calculations using specific values, called arguments. For example, the function SUM(B1:B10) returns the sum of cells B1 through B10. An argument can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #NA, or cell references. Arguments can also be constants, formulas, or other functions, known as nested functions. A function starts with the equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. For example, the function, AVERAGE(A1:A10, B1:B10), returns a number with the average for the contents of cells A1 through A10 and B1 through B10. As you type a function, a ToolTip appears with the structure and arguments needed to complete the function. You can also use the Insert Function dialog box to help you add a function to a formula.
Perform Calculations
By default, every time you make a change to a value, formula, or name, Excel performs a calculation. To change the way Excel performs calculations, click the Formulas tab, click the Calculation Options button, and then click the option you want: Automatic, Automatic Except Data Tables, or Manual. To manually recalculate all open workbooks, click the Calculate Now button (or press F9). To recalculate the active worksheet, click the Calculate Sheet button (or press Shift+F9).
Precedence Order
Formulas perform calculations from left to right, according to a specific order for each operator. Formulas containing more than one operator follow precedence order: exponentiation, multiplication and division, and then addition and subtraction. So, in the formula 2 + 5 * 7, Excel performs multiplication first and addition next for a result of 37. Excel calculates operations within parentheses first. The result of the formula (2 + 5) * 7 is 49.
Types of Operators
Operator 
Meaning 
Example 

Arithmetic 




= (plus sign) 
Addition 
2+7 

 (minus sign) 
Subtraction 
72 

* (asterisk) 
Multiplication 
2*7 

/ (forward slash) 
Division 
7/2 

% (percent) 
Percent 
70% 

^ (caret) 
Exponentiation 
2^7 
Comparison 




= (equal sign) 
Equal to 
A2=B7 

> (greater than sign) 
Greater than 
A2>B7 

< (less than sign) 
Less than 
A2<B7 

>= (greater than or equal to sign) 
Greater than or equal to 
A2>=B7 

<= (less than or equal to sign) 
Less than or equal to 
A2<=B7 

<> (not equal to sign) 
Not equal to 
A2<>B7 
Text concatenation 




& (ampersand) 
Connects, or concatenates, two values to produce one continuous text value 
“Total”&”Profit” 
Reference 




: (colon) 
Range operator, which produces one reference to all the cells between two references 
A1:A10 

, (comma) 
Union operator, which combines multiple references into one reference 
SUM(A1:A10,B1:B10) 

(space) 
Intersection operator, which produces on reference to cells common to the two references 
A1:A10 B1:B10 