A formula can contain any or all the following – functions, references, operators, and constants. Constants are values that are not calculated as they always remain the same. Below are the step by step instructions for using various Excel Formulas and Functions Features.

#### Excel Formulas and Functions — Using Calculation Operators

Operators specify the type of calculation you want to perform on the elements of the formula. There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

##### Arithmetic Operators

To perform basic mathematical operations, use the following arithmetic operators.

Arithmetic operator | Meaning | Example |

+ (plus sign) | Addition | 2+2 |

– (minus sign) | Subtraction | 2-2 |

* (asterisk) | Multiplication | 2*2 |

/ (forward slash) | Division | 2/2 |

% (percent sign) | Percent | 2% |

^ (caret) | Exponentiation | 2^2 |

##### Comparison Operators

You can compare two values with the following operators. When two values are compared by using operators, the result is a logical value – either TRUE or FALSE.

Comparison operator | Meaning | Example |

= (equal sign) | Equal to | A1=B1 |

> (greater than sign) | Greater than | A1>B1 |

< (less than sign) | Less than | A1<B1 |

>= (greater than or equal to sign) | Greater than or equal to | A1>=B1 |

<= (less than or equal to sign) | Less than or equal to | A1<=B1 |

<> (not equal to sign) | Not equal to | A1<>B1 |

##### Text Concatenation Operator

The & (ampersand) is used to concatenate (join) one or more text strings to produce a single piece of text.

Text operator | Meaning | Example |

& (ampersand) | Joins, or concatenates, two values to produce one continuous text value | “North”&”West” results in “NorthWest” |

##### Reference Operators

The following reference operators are used to combine ranges of cells for calculations.

Reference operator | Meaning | Example |

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

, (comma) | Union operator, which combines multiple references into one reference | SUM(A1:A5,B1:B5) |

(space) | Intersection operator, which produces one reference to cells common to the two references | (A1:C5 B1:D5) |

##### Calculation Order

Formulas always begin with an equal sign (=) and calculate in a specific order. Excel interprets the characters that follow the equal sign as a formula. Excel calculates the formula from left to right, according to a specific order for each operator in the formula, but can be changed by using parentheses. The part of the formula enclosed in parentheses is calculated first. If there are multiple parts enclosed in parentheses, those parts are calculated first, from left to right**. Example: =(A1+B1)/(C2-D1) – The results of A1+B1 is divided by the results of C2-D1.**

Operator Order of Precedence | Description |

: (colon)
(single space) , (comma) |
Reference operators |

– (minus sign) | Negative |

% | Percent |

^ | Exponentiation |

* and / | Multiplication and division |

+ and – | Addition and subtraction |

& | Connects two strings of text (concatenation) |

+
< > <= >= <> |
Comparison |

#### Excel Formulas and Functions — Entering Formulas

- Click the cell to contain the formula.
- To denote a formula will be used in the cell, type
(equal) followed by the formula.*=* - Press
when finished typing the formula.*Enter*

#### Excel Formulas and Functions — Using Cell References in a Formula

A cell reference refers to a cell or a range of cells on a worksheet. When you create either a simple formula or one that uses a function, you can refer to the data in cells by using cell references in the formula arguments. Example: ** A1 refers to the cell at the intersection of column A and row 1**. Cell references can be used to refer to the following –

- Data from one or more contiguous cells on a worksheet
- Data contained in different areas of a worksheet
- Data on other worksheets in the same workbook

To refer to | Use |

The cell in column A and row 1 | A1 |

The range of cells in column A and rows 1 through 20 | A1:A20 |

The range of cells in row 1 and columns A through C | A1:C1 |

All cells in row 1 | 1:1 |

All cells in rows 1 through 20 | 1:20 |

All cells in column A | A:A |

All cells in columns A through C | A:C |

The range of cells in columns A through C and rows 1 through 10 | A1:C10 |

##### Creating a Cell Reference

- Click the cell where you need to enter the formula.
- In the formula bar, type equal sign (=).
- Do one of the following, select the cell that contains the value you want or type its cell reference. You can refer to a single cell, range of cells, location in another worksheet, or location in another workbook. When selecting a range of cells, you drag the border of the cell selection to move the selection or drag the corner of the border to expand the selection.
- The first reference in this example is B2. The color is blue and the border is blue with square corners
- The second reference in this example is the range B3toB4. The color is red and the border is red with square corners.

**Note: If there are no square corners on a color-coded border, the reference is to a named range. **

- Press Enter to complete the formula.

#### Excel Formulas and Functions — Defining and Using Names in Formulas

Using names in formulas can make them much easier to understand and work with. You can define a name for a cell range, formula, constant, or table. Example: =SUM(FirstQtrInv)

**Note: By default, names use absolute cell references. **

You can define names by using the following:

**Name box on the formula bar**– This works best for creating a workbook level name for a selected range.**Create a name from a range**– You can create a name from an existing row and column labels by using a selection of cells in the worksheet.**New Name dialog box**– The works best when you want more flexibility in creating names.

- Highlight the cell or range of cells to be defined.
- Click the
tab on the ribbon.*Formulas* - Click
option in the Defined Names group.*Define Name* - Enter the following information in the
dialog box –**Define Name** - Click
to accept the name.*OK*

##### Deleting or Editing Range Names

- Click the
tab on the ribbon.*Formulas* - Click
option in the Defined Names group.*Name Manager* - The
dialog box will open.*Name Manager* - Click on the name you want to delete or edit.

- To delete the name, click the
button*Delete**.* - To edit the name, click the
button to open the*Edit*dialog box. Make the necessary changes and click*Edit Name*.*OK***ote: The Refers to can be changed by highlighting the existing range in the dialog box and the selecting the new range in the worksheet to replace it.**

- Click
to accept the changes.*Close*

#### Excel Formulas and Functions — Using the Insert Function Command

- Click the cell to contain the formula.
- On the
, click the*Formula Bar*icon.*Insert Function* - Click the needed function and click
.**OK** - Select the needed range of cells.
- Click
when finished.**OK**

#### Using AutoFill to Copy Formulas

- Click in the cell containing the formula to be copied.
- Place the cursor on the drag fill handle, which is located at the bottom right corner of the cell.
- Once the crosshair appears, drag through the range of cells to include the formula. In this example, it is dragged to the right.
- Once the mouse is released, the formula is copied and the results are listed. The AutoFill dialog box appears, giving you the options to
**Copy Cells**(default),**Fill Formatting Only**, or**Fill Without Formatting**. Since we want to copy the formula and list the results the same as what we are copying, we will leave Copy Cells selected.

#### Excel Formulas and Functions — Switching Between Relative and Absolute References

When you create a formula that refers to another cell or range, that cell reference can either be absolute or relative. An absolute cell reference does not change when the formula is moved. A relative cell reference does change to its new location with the formation is moved or copied.

- Click the cell containing the cell reference you need to change.
- In the
, click the cell reference that you want to change.**formula bar** - Press
to toggle though the combinations or manually type in the dollar sign (*Fn + F4*) in the appropriate spot.**$**

Cell Reference | Description |

$B$6 | Absolute column and row |

B$6 | Relative column and absolute row |

$B6 | Absolute column and relative row |

B6 | Relative column and relative row |