Creating Simple Formulas

Formulas perform calculations on the data you enter. You can add (+), subtract (-), multiply (*), divide (/), and raise to powers (exponentiation, ^). You can also use more complex formulas that come with Excel, such as calculating interest or mortgage payments.

Every formula must start with an equal sign.

Excel uses standard rules of order in deciding how to calculate formulas. In short, here is the order of calculations:

1. Anything in parentheses.
2. Exponentiation
3. Multiplication and division
4. Addition and subtraction

Otherwise, calculations are performed from left to right.

You can put numbers and cell names in a formula. For example, to find the average of 2 numbers in cells A1 andA2, you would use this formula: =(A1+A2)/2. The parentheses are necessary to make Excel add the numbers first and then divide. (Indicate a range of cells using a colon (:). For example, typeA1:B12 to indicate the range of cells from A1 to B12, including A1 to A12 and B1 to B12.)

There are two ways to create this formula:

Method 1

1. Select the cell in which you want the formula's calculation to appear.
2. Type an equal (=) sign
3. Type the formula. The formula appears in the Formula bar.
4. Press Enter/Return. Excel calculates the result.

Method 2

1. Click the cell where to answer is to appear.
2. Click the equal (=) sign to the left of the Formula bar.
3. Click cell A1.
4. Press plus (+) on the numeric pad of your keyboard.
5. Click cell A2.
6. Press divide (/) on the numeric pad of your keyboard.
7. Type 2.
8. Click at the beginning of the formula and type the opening parenthesis.
9. Click before the division sign and type the closing parenthesis.
10. Press Enter/Return. Excel calculates the result.

The second method seems more complex, but in some cases it is easier than typing the whole formula. Use whichever one you want.

Creating quick sums:

You can quickly create sums of data in a column or row in two ways.

Method 1: Highlight the cells plus one additional empty cell for the sum. Click Autosum on the Standard toolbar.

Method 2: Click the cell where you want the sum to appear, then click Autosum on the Standard toolbar. Excel makes a guess of the cells that you want to add and surrounds them with a moving dashed line. If Excel guessed right, press Enter/Return. If not, drag over the cells you want to add and press Enter/Return.

Excel uses the =SUM formula to create these sums.

To use Excel's formulas, click the equal (+) sign and choose a formula from the formula drop-down list at the left of the Formula bar's row. Excel opens up a dialog box to help you complete the formula. You can type in numbers or cell names or click the point box at the right of the text boxes to click a cell on the spreadsheet. Click OK when you 're done to create the formula.

Copying formulas

Let's say you have a budget for the entire year and you have put the months across the top in a row. In the left column, you have the various types of expenses and revenues. You want to add up the columns for each month. You create a sum for the January column. Now you want to copy the formula that adds the column to all the other columns for the rest of the year. Here's how:

1. Click the cell that contains the formula you want to copy.
2. Point tothe bottom right of the cell so you see the cell's fill handle, a thin, black plus sign.
3. Drag to the right until you reach the December column. Excel automatically copies the summing formula toall the columns and adds all the columns.

If the cells you want to copy a formula to are not right next to and in the same row or column as the formula use this method:

1. Click the cell that contains the formula you want to copy.
2. Click Copy on the Standard toolbar.
3. Click the cell you want to copy the formula to
4. Click Paste on the Standard toolbar.

Moving formulas:

Excel automatically maintains formulas when you move a cell. Let's say you create a sum in cell B15. It adds cells B3 to B14 (B3:B14). But then you decide you want to display the sum in cell C15. Just move the cell (drag it) to cell C15. It still adds cells B3 to B14.

If you move a cell that is the basis for a formula elsewhere, Excel changes the formula so you get the same result. For example, in the same example as the previous paragraph, if you move cells B3:B14 to C3:C14, Then B15, which used to have the formula =SUM(B3:B14) now has the formula =SUM(C3:C14). As a result, B15 still shows the same sum it did before you moves the cells.