When you work with large amounts of data, it’s often useful to identify groups of cells that contain related data. For example, you can create a worksheet in which cells C4:I4 hold the number of packages Consolidated Messenger’s Northeast processing facility handled from 5:00 P.M. to 12:00 A.M. on the previous day.

You can name a range of data and reference the entire range by using only the name.

Instead of specifying the cells individually every time you want to use the data they contain, you can define those cells as a range (also called a named range). For example, you can group the items from the cells described in the preceding paragraph into a range named NortheastPreviousDay. Whenever you want to use the contents of that range in a calculation, you can simply use the name of the range instead of specifying each cell individually.

To create a named range, select the cells you want to include in your range, click the Formulas tab, and then, in the Defined Names group, click Define Name to display the New Name dialog box. In the New Name dialog box, type a name in the Name field, verify that the cells you selected appear in the Refers To field, and then click OK. You can also add a comment about the range in the Comment field and select whether you want to make the name available for formulas in the entire workbook or just on an individual worksheet.

If the cells you want to define as a named range have labels in a row or column that’s part of the cell group, you can use those labels as the names of the named ranges. For example, if your data appears in worksheet cells B4:I12 and the values in column B are the row labels, you can make each row its own named range. To create a series of named ranges from a group of cells, select all of the data cells, including the labels, display the Formulas tab and then, in the Defined Names group, click Create From Selection to display the Create Names From Selection dialog box. In the Create Names From Selection dialog box, select the check box that represents the labels’ position in the selected range, and then click OK.

You can name ranges by their row or column labels.

A final way to create a named range is to select the cells you want in the range, click in the Name box next to the formula box, and then type the name for the range.

You can display the ranges available in a workbook by clicking the Name arrow.

To manage the named ranges in a workbook, display the Formulas tab, and then, in the Defined Names group, click Name Manager to display the Name Manager dialog box.

When you click a named range, Excel displays the cells it encompasses in the Refers To field.

Clicking the Edit button displays the Edit Name dialog box, which is a version of the New Name dialog box, enabling you to change a named range’s definition; for example, by adding a column. You can also use the controls in the Name Manager dialog box to delete a named range (the range, not the data) by clicking it, clicking the Delete button, and then clicking OK in the confirmation dialog box that opens.

In this exercise, you’ll create named ranges to streamline references to groups of cells.

After you add your data to a worksheet and define ranges to simplify data references, you can create a formula, which is an expression that performs calculations on your data. For example, you can calculate the total cost of a customer’s shipments, figure the average number of packages for all Wednesdays in the month of January, or find the highest and lowest daily package volumes for a week, month, or year.

To write an Excel formula, you begin the cell’s contents with an equal (=) sign; when Excel sees it, it knows that the expression following it should be interpreted as a calculation, not text. After the equal sign, type the formula. For example, you can find the sum of the numbers in cells C2 and C3 by using the formula =C2+C3. After you have entered a formula into a cell, you can revise it by clicking the cell and then editing the formula in the formula box. For example, you can change the preceding formula to =C3-C2, which calculates the difference between the contents of cells C2 and C3.

Typing the cell references for 15 or 20 cells in a calculation would be tedious, but Excel makes it easy to enter complex calculations. To create a new calculation, click the Formulas tab, and then in the Function Library group, click Insert Function. The Insert Function dialog box opens, with a list of functions, or predefined formulas, from which you can choose.

You can locate a function, if you don’t know its name, by entering key descriptors in the Search For A Function box and then clicking Go.

The following table describes some of the most useful functions in the list.

Two other functions you might use are the NOW and PMT functions. The NOW function displays the time Excel updated the workbook’s formulas, so the value will change every time the workbook recalculates. The proper form for this function is =NOW(). To update the value to the current date and time, just press the F9 key or display the Formulas tab and then, in the Calculation group, click the Calculate Now button. You could, for example, use the NOW function to calculate the elapsed time from when you started a process to the present time.

The PMT function is a bit more complex. It calculates payments due on a loan, assuming a constant interest rate and constant payments. To perform its calculations, the PMT function requires an interest rate, the number of payments, and the starting balance. The elements to be entered into the function are called arguments and must be entered in a certain order. That order is written as PMT(rate, nper, pv, fv, type). The following table summarizes the arguments in the PMT function.

If Consolidated Messenger wanted to borrow $2,000,000 at a 6 percent interest rate and pay the loan back over 24 months, you could use the PMT function to figure out the monthly payments. In this case, the function would be written =PMT(6%/12, 24, 2000000), which calculates a monthly payment of $88,641.22.

You can also use the names of any ranges you defined to supply values for a formula. For example, if the named range NortheastPreviousDay refers to cells C4:I4, you can calculate the average of cells C4:I4 with the formula =AVERAGE(NortheastPreviousDay). With Excel, you can add functions, named ranges, and table references to your formulas more efficiently by using the Formula AutoComplete capability. Just as AutoComplete offers to fill in a cell’s text value when Excel recognizes that the value you’re typing matches a previous entry, Formula AutoComplete offers to help you fill in a function, named range, or table reference while you create a formula.

As an example, consider a worksheet that contains a two-column Excel table named Exceptions. The first column is labeled Route; the second is labeled Count.

You can reference and entire column in a formula by using the column name.

You refer to a table by typing the table name, followed by the column or row name in square brackets. For example, the table reference Exceptions[Count] would refer to the Count column in the Exceptions table.

To create a formula that finds the total number of exceptions by using the SUM function, you begin by typing =SU. When you type the letter S, Formula AutoComplete lists functions that begin with the letter S; when you type the letter U, Excel narrows the list down to the functions that start with the letters SU.

The AutoComplete list suggests functions that begin with the letters you type.

To add the SUM function (followed by an opening parenthesis) to the formula, click SUM and then press Tab. To begin adding the table reference, type the letter E. Excel displays a list of available functions, tables, and named ranges that start with the letter E. Click Exceptions, and press Tab to add the table reference to the formula. Then, because you want to summarize the values in the table’s Count column, type a left square bracket and then, in the list of available table items, click Count. To finish creating the formula, type a right square bracket followed by a right parenthesis to create the formula =SUM(Exceptions[Count]).

If you want to include a series of contiguous cells in a formula, but you haven’t defined the cells as a named range, you can click the first cell in the range and drag to the last cell. If the cells aren’t contiguous, hold down the Ctrl key and select all of the cells to be included. In both cases, when you release the mouse button, the references of the cells you selected appear in the formula.

You can enter cells and cell ranges in a formula by selecting the cells while creating the formula.

After you create a formula, you can copy it and paste it into another cell. When you do, Excel tries to change the formula so that it works in the new cells. For instance, suppose you have a worksheet where cell D8 contains the formula =SUM(C2:C6). Clicking cell D8, copying the cell’s contents, and then pasting the result into cell D16 writes =SUM(C10:C14) into cell D16. Excel has reinterpreted the formula so that it fits the surrounding cells! Excel knows it can reinterpret the cells used in the formula because the formula uses a relative reference, or a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column (for example, C14).

Relative references are useful when you summarize rows of data and want to use the same formula for each row. As an example, suppose you have a worksheet with two columns of data, labeled SalePrice and Rate, and you want to calculate your sales representative’s commission by multiplying the two values in a row. To calculate the commission for the first sale, you would type the formula =B4*C4 in cell D4.

The formula is displayed in the formula bar, and its result is displayed in the cell.

Selecting cell D4 and dragging the fill handle until it covers cells D4:D9 copies the formula from cell D4 into each of the other cells. Because you created the formula using relative references, Excel updates each cell’s formula to reflect its position relative to the starting cell (in this case, cell D4.) The formula in cell D9, for example, is =B9*C9.

Copying a formula to other cells automatically updates cell references to reflect the new location.

You can use a similar technique when you add a formula to an Excel table column. If the sale price and rate data were in an Excel table and you created the formula =B4*C4 in cell D4, Excel would apply the formula to every other cell in the column. Because you used relative references in the formula, the formulas would change to reflect each cell’s distance from the original cell.

In an Excel table, changing a formula in one cell automatically changes it in related cells.

If you want a cell reference to remain constant when the formula using it is copied to another cell, you can use an absolute reference. To write a cell reference as an absolute reference, type $ before the row letter and the column number. For example, if you want the formula in cell D16 to show the sum of values in cells C10 through C14 regardless of the cell into which it is pasted, you can write the formula as =SUM($C$10:$C$14).

One quick way to change a cell reference from relative to absolute is to select the cell reference in the formula box and then press F4. Pressing F4 cycles a cell reference through the four possible types of references:

In this exercise, you’ll create a formula manually, revise it to include additional cells, create a formula that contains an Excel table reference, create a formula with relative references, and change the formula so it contains absolute references.

Another use for formulas is to display messages when certain conditions are met. For instance, Consolidated Messenger’s Vice President of Marketing, Craig Dewar, might have agreed to examine the rates charged to corporate customers who were billed for more than $100,000 during a calendar year. This kind of formula is called a conditional formula; one way to create a conditional formula in Excel is to use the IF function. To create a conditional formula, you click the cell to hold the formula and open the Insert Function dialog box. From within the dialog box, click IF in the list of available functions, and then click OK. When you do, the Function Arguments dialog box opens.

The IF function returns one value if a specified condition is true, and another if it is false.

When you work with an IF function, the Function Arguments dialog box has three boxes: Logical_test, Value_if_true, and Value_if_false. The Logical_test box holds the condition you want to check. If the customer’s year-to-date shipping bill appears in cell G8, the expression would be G8>100000.

Now you need to have Excel display messages that indicate whether Craig Dewar should evaluate the account for a possible rate adjustment. To have Excel print a message from an IF function, you enclose the message in quotes in the Value_if_true or Value_if_false box. In this case, you would type “High-volume shipper—evaluate for rate decrease.” in the Value_if_true box and “Does not qualify at this time.” in the Value_if_false box.

Excel also includes several other conditional functions you can use to summarize your data, shown in the following table.

You can use the IFERROR function to display a custom error message, instead of relying on the default Excel error messages to explain what happened. For example, you could use an IFERROR formula when looking up the CustomerID value from cell G8 in the Customers table by using the VLOOKUP function. One way to create such a formula is =IFERROR(VLOOKUP(G8,Customers,2,false),“Customer not found”). If the function finds a match for the CustomerID in cell G8, it displays the customer’s name; if it doesn’t find a match, it displays the text Customer not found.

Just as the COUNTIF function counts the number of cells that meet a criterion and the SUMIF function finds the total of values in cells that meet a criterion, the AVERAGEIF function finds the average of values in cells that meet a criterion. To create a formula using the AVERAGEIF function, you define the range to be examined for the criterion, the criterion, and, if required, the range from which to draw the values. As an example, consider a worksheet that lists each customer’s ID number, name, state, and total monthly shipping bill.

A sample worksheet containing values necessary to create a formula.

If you want to find the average order of customers from the state of Washington (abbreviated in the worksheet as WA), you can create the formula =AVERAGEIF(D3:D6, “WA”, E3:E6).

The AVERAGEIFS, SUMIFS, and COUNTIFS functions extend the capabilities of the AVERAGEIF, SUMIF, and COUNTIF functions to allow for multiple criteria. If you want to find the sum of all orders of at least $100,000 placed by companies in Washington, you can create the formula =SUMIFS(E3:E6, D3:D6, “=WA”, E3:E6, “>=100000”).

The AVERAGEIFS and SUMIFS functions start with a data range that contains values that the formula summarizes; you then list the data ranges and the criteria to apply to that range. In generic terms, the syntax runs =AVERAGEIFS(data_range, criteria_range1, criteria1[,criteria_range2, criteria2…]). The part of the syntax in square brackets (which aren’t used when you create the formula) is optional, so an AVERAGEIFS or SUMIFS formula that contains a single criterion will work. The COUNTIFS function, which doesn’t perform any calculations, doesn’t need a data range—you just provide the criteria ranges and criteria. For example, you could find the number of customers from Washington who were billed at least $100,000 by using the formula =COUNTIFS(D3:D6, “=WA”, E3:E6, “>=100000”).

In this exercise, you’ll create a conditional formula that displays a message if a condition is true, find the average of worksheet values that meet one criterion, and find the sum of worksheet values that meet two criteria.

Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it is possible for errors to creep into your formulas. With Excel, you can find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors is referred to as auditing.

Excel identifies errors in several ways. The first way is to display an error code in the cell holding the formula generating the error.

Error codes begin with a number sign (#).

When a cell with an erroneous formula is the active cell, an Error button is displayed next to it. Pointing to the Error button displays an arrow. Clicking the arrow displays a menu with options that provide information about the error and offer to help you fix it.

The following table lists the most common error codes and what they mean.

Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. For example, you might want to calculate the total number of deliveries for a service level, but you could accidentally create a formula referring to the service levels’ names instead of their package quantities. You can identify the source of an error by having Excel trace a cell’s precedents, which are the cells with values used in the active cell’s formula. To do so, click the Formulas tab, and then in the Formula Auditing group, click Trace Precedents. When you do, Excel identifies those cells by drawing a blue tracer arrow from the precedents to the active cell.

You can also audit your worksheet by identifying cells with formulas that use a value from a given cell. For example, you might use one region’s daily package total in a formula that calculates the average number of packages delivered for all regions on a given day. Cells that use another cell’s value in their calculations are known as dependents, meaning that they depend on the value in the other cell to derive their own value. As with tracing precedents, you can click the Formulas tab, and then in the Formula Auditing group, click Trace Dependents.

Excel draws blue arrows from the active cell to those cells that have calculations based on that value.

If the cells identified by the tracer arrows aren’t the correct cells, you can hide the arrows and correct the formula. To hide the tracer arrows on a worksheet, display the Formulas tab, and then in the Formula Auditing group, click Remove Arrows.

If you prefer to have the elements of a formula error presented as text in a dialog box, you can use the Error Checking dialog box to view the error and the formula in the cell in which the error occurs. To display the Error Checking dialog box, display the Formulas tab, and then in the Formula Auditing group, click the Error Checking button. You can use the controls in the Error Checking dialog box to move through the formula one step at a time, to choose to ignore the error, or to move to the next or the previous error. If you click the Options button in the dialog box, you can also use the controls in the Excel Options dialog box to change how Excel determines what is an error and what isn’t.

You can have the Error Checking tool ignore formulas that don’t use every cell in a region (such as a row or column).

For times when you just want to display the results of each step of a formula and don’t need the full power of the Error Checking tool, you can use the Evaluate Formula dialog box to move through each element of the formula. To display the Evaluate Formula dialog box, you display the Formulas tab and then, in the Formula Auditing group, click the Evaluate Formula button. The Evaluate Formula dialog box is much more useful for examining formulas that don’t produce an error but aren’t generating the result you expect.

Finally, you can monitor the value in a cell regardless of where in your workbook you are by opening a Watch Window that displays the value in the cell. For example, if one of your formulas uses values from cells in other worksheets or even other workbooks, you can set a watch on the cell that contains the formula and then change the values in the other cells. To set a watch, click the cell you want to monitor, and then on the Formulas tab, in the Formula Auditing group, click Watch Window. Click Add Watch to have Excel monitor the selected cell.

As soon as you type in the new value, the Watch Window displays the new result of the formula. When you’re done watching the formula, select the watch, click Delete Watch, and close the Watch Window.

In this exercise, you’ll use the formula-auditing capabilities in Excel to identify and correct errors in a formula.