## Create a formula

- Select a cell.
- Type an equal sign (
`=`

).**Note:**The first character in a cell containing a formula must be`=`

. If it is any other character, including a space, Excel interprets the cell’s contents as text. - Select a cell or type its address in the selected cell.
- Enter an operator. For example,
`+`

for addition. - Select the next cell, or type its address in the selected cell.
- Press
`Enter`. The result of the operation will be displayed.

## Refer to cells & cell ranges in formulas

- Worksheet cells are identified by their column letter and then their row number.
- The top-left cell in a worksheet is in column
`A`

and row`1`

, so its cell address is`A1`

. - You can also refer to cell ranges, or collections of cells.
- To identify a cell range, type the address of the cell at the topleft corner of the range, followed by a colon (
`:`

), and then the cell at the bottom-right corner of the range. For Example, the reference for cells 1 through 5 in columns A and B would be`A1:B5`

. - To create a reference to multiple, noncontiguous groups of cells, put a comma (
`,`

) between the references. For Example, finding the sum of the values in the cell range`A2:B6`

and the range`D2:E6`

would call for the formula`=SUM(A2:B6,D2:E6)`

.

## Create relative, absolute & mixed references

### Relative reference

When you type a cell reference with just the column letter and row number, such as `A1`

, that reference can change when you copy its formula to another cell.

For Example, copying the formula `=A1+A2`

and pasting it one cell to the right would create the formula `=B1+B2`

. The destination cell is one column to the right of the original cell, so Excel changes the formula’s references based on that difference.

### Absolute reference

To create an absolute reference, one that doesn’t change when you copy a formula, type a dollar sign (`$`

) before the column and row designators.

For Example, the formula `=$A$1+$A$2`

won’t change regardless of the cell you paste it into.

### Mixed reference

You can also create a mixed reference, where either rows or columns are absolute, by adding `$`

before just the element you want to remain the same.

For Example, the cell reference `A$1`

allows changing columns and `$A1`

allows changing rows.

### Switch the types of your references

- Select the cell that contains the formula.
- In the formula bar, select the reference that you want to change.
- Press
`F4`to switch between the reference types.

### Reference cells outside the worksheet

Formulas can also refer to cells in other worksheets, and the worksheets don't even have to be in the same workbook. Excel uses a special type of notation to handle these types of references.

#### Reference cells in other worksheets

To use a reference to a cell in another worksheet in the same workbook, use this format:

`=SheetName!CellAddress`

In other words, precede the cell address with the worksheet name followed by an exclamation point. For example:

`=A1-Sheet2!A1`

**Note:** If the workbook or worksheet name contains a space, you must enclose it in single quotation marks. Excel does that automatically if you use the point-and-click method when creating the formula. For example:

`=B2*'Sales Dept'!$A$2`

#### Reference cells in other workbooks

To refer to a cell in a different workbook, use this format:

`=[WorkbookName]SheetName!CellAddress`

In this case, the workbook name (in square brackets), the worksheet name, and an exclamation point precede the cell address. For example:

`=[Sales.xlsx]Sheet1!A1`

If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name and square brackets) in single quotation marks. For example:

`=A1*'[Sales For 2023.xlsx]Sheet1'!A1`

When a formula refers to cells in a different workbook, the other workbook doesn't have to be open. If the workbook is closed, however, you must add the complete path to the reference so that Excel can find it. For example:

`=A1*'C:\My Documents\[Sales For 2023.xlsx]Sheet1'!A1`

A linked file can also reside on another system that's accessible on your corporate network. The following formula refers to a cell in a workbook in the files directory of a computer named `MyDataServer`

:

`='\\MyDataServer\files\[Sales For 2023.xlsx]Sheet1'!A1`

or IP address:

`='\\192.168.0.2\files\[Sales For 2023.xlsx]Sheet1'!A1`

**Tip**: to create formulas that refer to cells in a different worksheet or workbook,

**point to the cells**instead of entering their references manually. Excel handles the details about workbook and worksheet references. If you are using the pointing method, the workbook you reference in the formula must be open.

## Insert a function

Start creating the formula where you want to insert the function, and then do one of the following:

- Type the first letters of the function name, and either finish typing its name, or click the function on the
**AutoComplete**list that appears and press`Tab`. For example,`=SUM`

for getting the total numbers:- Click the cell
`A5`

and type`=s`

. - Excel lists functions that match so far, so you can select a function and see its description, scroll down to see more names, or continue typing. For example,
`=su`

to narrow the list. - When you find the function that you require, double-click the name or press
`Tab`, then enter the arguments that are shown. For example, double-click`SUM`

, then click range`A1:A4`

. - Type the closing parenthesis (
`)`

), and then press`Enter`. - The formula with the function is stored in the cell
`=SUM(A1:A4)`

, and the result of the operation will be displayed.

- Click the cell
- Click the
**Insert Function**button on the**Formulas**tab and select the function you want to add. Or use shortcut:`Shift`+`F3` - Go to the
**Function Library**group on the**Formulas**tab, click the category of the function you want to add, and then click the function name.

## Add an AutoSum function

- Select a cell below a column of numbers.
- Go to the
**Editing**group on the**Home**tab. - Click the
**AutoSum**button to add a**SUM**formula, or click the AutoSum button’s down arrow to select another function.

**Note:**The AutoSum function can also be found in the

**Function Library**group, on the

**Formulas**tab, along with Insert Function, Recently Used, and various sets of functions such as Financial, Logical, and Text.

**Tip:**To add an AutoSum formula that uses the SUM function, select the cell and press

`Alt`+

`=`.

## Get help using function argument ToolTips

- Start creating a formula, and enter the name of the function followed by a left parenthesis, such as
`=SUMIF(`

. - In the function ToolTip that appears below the cell that contains the formula, position the mouse pointer over the function name.
- When the function name’s text changes to a blue, underlined format, click it to display the function help.