Microsoft® Excel 97 Quick Reference
Formulas enable you to enter calculations in a worksheet. After you enter a formula, you can change the values in the referenced cells; Excel automatically recalculates values based on the cell changes.
Functions are predefined formulas that perform a specific operation, such as determining loan payments or calculating investment returns. Functions accept information, called arguments, and return a result. In most cases, the result is a calculation, but functions also return results that are text, references, logical values, arrays, or information about the worksheet.
For information on specific functions, refer to the "Functions Mini-Reference" located near the end of this book. This reference includes detailed information on many of the commonly used Excel functions from each of the function categories.
Excel enables you to set up relationships among the various cells in the worksheet. However, if you run into an error in the worksheet, you may need to trace those various relationships to find the error. You can use the Auditing toolbar when you need to trace an error.
Excel can display seven different types of error values. These error values are listed and described in the following table.
| Error | Description |
| #DIV/0! | The formula is attempting to divide by zero. Check the cell references for blanks or zeros that may have resulted if you deleted a cell referenced by the formula. |
| #N/A | The formula refers to a cell with a #N/A entry or a cell that contains no value. This error value warns you that not all the data referenced by a formula is available. |
| #NAME? | Excel doesn't recognize a name you entered in a formula. Verify that all names used in the formula exist, and define any missing names. If applicable, verify that you used the correct function name. |
| #NULL! | The formula specifies two areas that don't intersect. Check to see if you entered the cell or range reference incorrectly. Remember to use commas (not spaces) between function arguments. |
| #NUM! | There is a problem with a number used in the formula. Check for the correct use of function arguments. |
| #REF! | A cell reference in the formula is incorrect. Check for changes to cell reference caused by deleting cells, rows, or columns referenced by the formula. |
| #VALUE! | The formula contains the wrong type of argument or operator. Check for the correct syntax of the formula. |
You can display tracer lines to find precedents (cells that are referred to by a formula), dependents (cells that contain formulas that refer to other cells), and errors in any cell. Most of the time when you use the Auditing feature, you probably will want to trace the precedents to a formula to find out what other cells contribute to the formula in that cell. (See also "Auditing: Locating Errors in a Worksheet.")
NOTE: The tracer lines show the flow of data through the worksheet by connecting the active cell with related cells. The line ends with an arrow pointing to a formula.
TIP: Double-click the arrow to go directly to the cell the arrow points to. Double-click the arrow a second time to highlight the formula precedents. This feature works as a toggle.
(See also "Formulas: Displaying Formulas.")
When a cell contains a formula with references to other cells, you can use several methods to handle those references. Each method causes a different result and may be utilized differently by Excel, so understanding the differences is important.
Excel normally uses relative references for cell addresses in a formula, unless you specify otherwise. When you use relative references, the cell references in a formula automatically adjust after you copy the formula to another cell or range. If cell B10 contains the formula =SUM(B3:B9), for example, and you copy this formula from cell B10 to cell C10, the new formula in cell C10 automatically adjusts to read =SUM(C3:C9). In most cases, you want formulas to use relative cell references.
To prevent a cell reference in a formula from changing when you copy that formula to another cell or range, use an absolute reference. You indicate absolute references by typing a dollar sign ($) in front of the column letter and the row number. In a sales worksheet, for example, if you have a column of formulas that multiply a value by the commission percentage located in cell D7, you could use $D$7 to refer to that percentage in the first cell; then copy the formula down the column.
If you want only the row number or column letter (not both) to remain fixed when you copy a formula, use a mixed reference to refer to the cell address. The reference $C3 prevents the column from changing, for example, but the row changes relative to a new copied location. If you use C$3 as the reference, the column adjusts to a new location but the row remains fixed when you copy the formula.
(See "Formulas: Entering Formulas" before you complete this task.)
TIP: You can use the F4 key to cycle between absolute, mixed, and relative references when editing an existing formula.
You can replace a formula in the worksheet with its calculated result if you no longer want or need to use the formula. When you convert the formula to a value, Excel permanently removes the formula (you can later reenter the formula if necessary).
For example, if you want to delete the cells or name to which a formula refers, first convert the formula to a value before deleting the cells or name. (See "Formulas: Entering Formulas" before you complete this task.)
At times, you may need to create a formula that joins the contents of two cells. Excel refers to this action as concatenation.
If a worksheet includes first names in one column and last names in another column, for example, you can enter a formula in a third column that joins the first name with the last name. Suppose that the first names are located in column B and the last names are located in column C; row 3 is the first row in the list. The following line shows an example of the formula for this situation:
=B3&" "&C3
The ampersand (&) in the preceding formula is the concatenation operator that joins text, numbers, and dates into one long text string. Note that if you used the formula =B3&C3 in this example, the first and last names would be joined together without a space. Therefore, you must use two quotation marks with a space between them (" ") to indicate that Excel should insert a space between the two text strings. (See "Formulas: Entering Formulas" before you complete this task.)
(See also "Text Functions" in the "Functions Mini-Reference" section.)
You may at times prefer to see the formulas in your worksheets (such as =Jan+Feb) rather than the results of the formulas (such as 2,537). Perhaps you want to evaluate the structure of a worksheet to see that the formulas are correct, even if the worksheet contains no known errors. For example, you may notice incorrect cell references in displayed formulas that are causing inaccurate results (but not error values) in the worksheet.
Displaying all worksheet formulas also enables you to easily print all formulas used in the worksheet, for purposes of documentation. This step is a good safeguard in case you accidentally delete or lose the worksheet, or if the worksheet becomes damaged. (See "Formulas: Entering Formulas" before you complete this task.)
To display the results again, choose Tools, Options; then clear the Formulas check box on the View tab.
TIP: Press Ctrl+ ' (the grave accent, usually located on the same key as the tilde character) to toggle between viewing results and formulas.
Formulas enable you to perform calculations by using values in the worksheet. When you use a formula to create a calculation, and you change any of the values referenced by the formula, Excel automatically recalculates the result. You can enter formulas in two ways: type the formula directly in the cell, or point to the cells that you want the formula to compute. Arithmetic operators that you can use in formulas are listed in the following table. (See also "Functions: Entering Functions.")
| Operator |
Description |
| + (plus sign) | Addition |
| - (minus sign) | Subtraction |
| * (asterisk) | Multiplication |
| / (slash) | Division |
| % (percent symbol) | Percentage |
| ^ (caret) | Exponentiation |
The cell displays the formula result. When you select the cell containing the formula, you see the formula in the formula bar.
TIP: Instead of typing an equal sign in step 1 of the preceding steps, you can click the Edit Formula button (the = in the formula bar). If you click the Edit Formula button, Excel automatically inserts an equal sign and displays the results of the formula as you enter it. When you are done, click OK or press Enter to complete the formula.
NOTE: Many formula errors occur when the arithmetic operators are not entered in the proper order in which Excel performs the operations. Exponentiation occurs before multiplication and division, and multiplication and division occur before addition and subtraction. This is sometimes called the order of precedence. You can alter this order by enclosing segments of a formula in parentheses. For example, in the formula =3*(7+5), you force Excel to add 7 and 5, and then multiply that result by 3.
(See also "Naming Cells and Ranges" in the "Getting Started" section.)
You can use Ctrl+Enter to quickly fill cells as you enter data or formulas. When you press Ctrl+Enter, formulas and values fill into all selected cells just as though you used a Fill or Copy and Paste command. This method also works with nonadjacent multiple selections. (See "Formulas: Entering Formulas" before you complete this task.)
You can refer to other sheets in a workbook by including a sheet reference as well as a cell reference in a formula. To refer to cell C7 on Sheet3, for example, type Sheet3!C7 in the formula. Use an exclamation mark (!) to separate the sheet reference from the cell reference. If you have named the sheet, simply use the sheet name and then the cell reference, such as Details!C7. If the sheet name includes spaces, you must surround the sheet reference with single quotation marks, such as `Sales Details'!C7. (See "Formulas: Entering Formulas" before you complete this task.)
(See also "Linking: Cells by Pointing" in the section "Linking and Embedding.")
Cell protection prevents someone from accidentally entering data on top of a formula and prevents unauthorized users from changing your formulas. You also can specify whether a cell's contents are visible in the formula bar. Even when the cell contents are hidden from the formula bar, however, the cell's value or formula results still appear in the worksheet. (See "Formulas: Entering Formulas" in this section and "Protecting: Cell Data" in the section "Editing Workbooks" before you complete this task.)
You can enter functions manually if you know the function name and what data to supply for the arguments in a function. In most cases, you will probably type only simple functions that require one argument, such as a range of data. For more complex functions that require multiple arguments, you can use the Paste Function. (See "Functions: Inserting with the Paste Function.")
The Paste Function displays a list of functions from which you can choose the function you want, based on a description that appears when you select a function. The Paste Function also assists you in building the function and explains the purpose of each argument in a function.
Use the Paste Function if you want to enter a complex function that requires multiple arguments or if you are unsure of the syntax required for a specific function. (See also "Functions: Entering Functions.")
If you need to find a quick total in a worksheet, but you don't want or need to include that total in the worksheet, you can use the AutoCalculate feature. For example, you may want to sum a list and then use that sum in a formula. You could grab a calculator and add up the figures using the calculator. Or you can use Excel's AutoCalculate feature.
If you select additional ranges, Excel uses the most recent function you selected on the AutoCalculate button.
The SUM function totals the numeric value of all cells in the range(s) it references. An AutoSum button, which you can use to sum adjacent columns or rows automatically, appears on the Standard toolbar. In addition to entering the SUM function automatically, the AutoSum button selects the cells in the column above or in the row to the left of the current cell.
NOTE: If you select a range of cells and then click the AutoSum button, Excel automatically enters the formula results for the entire range.
Subtotals are a quick and easy way to summarize data in an Excel list. Suppose you have a list of sales information that includes the date, account, product, unit, price, and revenue. You can specify that you want to see subtotals by account, or subtotals by product, and so on.
With Excel's Subtotals command, you don't need to create the formulas. Excel creates the formula, inserts the subtotal row(s), and outlines the data automatically. The resulting data is easy to format, chart, and print.
TIP: To quickly remove the subtotals from your list, select a cell in the list. Then choose Data, Subtotals, and click Remove All.
CAUTION: Excel lists work best when it can readily distinguish the column labels and data entries in the list. Include a row of column labels across the top and, with no blank rows, place your data in the appropriate columns just below the labels.
If you want additional subtotals within each group (nested subtotals), you can create two sets of subtotals. For instance, you might want to total all accounts and also include subtotals for each product within an account.
In addition to the SUM function (used for creating subtotals in Excel lists), a number of other Excel functions are useful in lists. For instance, you may want to use COUNT to summarize the number of items in the list, AVERAGE to give the average values in the list, and MAX to give the largest value in a list.
©Copyright, Macmillan Computer Publishing. All rights reserved.